VB Code to Track Payments

[bmcgree1]

I built a database for my company which services interest only loans for construction companies who need quick capital to build then get paid and pay off the loan. Everything on it works great except one part, and I can't figure out how to finish it. My database generates a payment schedule on a table called Payments for the appropriate number of months of the loan. I have code that automatically subtracts the payment made for that month on the same line as the monthly payment due (simple subtraction code), but the problem arrises when someone pays more than they owe.
When a borrower overpays on the payment of a loan for a month, the difference between the payment due and how much they paid should be taken off the following month's payment. So for example:

If the minimum payment due = $500 and a borrower pays $800 then Month1 due = $0 and Month2 = $200.

Likewise, if the if the minimum payment due = $500 and a borrower pays $1,200 then Month1 due = $0, Month2 = $0 and Month3 = $300.

If there is an overpayment on the last month or anything that causes a negative balance after the last month, then it should remain a negative balance on that final month (e.x. --> 3 Month loan, minimum payments $100 and borrower pays $500, Month1 = $0, Month2 = $0 and Month3 = -$200).
Help is VERY MUCH appreciated. Thank you.

 

[Guus2005]

Could you reread the text you wrote because is don't understand the calculations you describe and what is the question?

With a minimum payment due of $500 and a payment of $1200 I would suspect month1=0, month2=0 month3=$200 and not $300.

HTH

 

[namliam]

Guus,

Payment due, as in owed...
in three months 1500 euro 500 + 500 + 500
Thus if a customer pays 1200 he pays the first two 500s and 200 of the last: 0 + 0 + 300 left to pay.

Now, Mr Gree

I will assume due payments are rows, not columns... If they are columns you need to rethink your design.
You will have to use some vba to loop the payments and apply the payments to the "invoices" you will probably want / need a 'allocation' table to facilitate the N:M nature of payments <> Invoices, one payment can be applied to multiple invoices (1200 to three invoices of 500)
and one invoice will have multiple applications ( the final invoice having 300 left to pay will have the above 200 and 300 from atleast one other payment)

I hope this gives you a good idea where to go.

Good luck

 

[James Harris]

What forms of payment gateway are you using? Is this applicable to all?