amortization of payment

[hugz via AccessMonster.com]

Guys i badly need your help!!!

I want to create a query that would track down the number of months that the
payment would cover. Here's the scenario: For example a customer has a loan
of $18000 which has a term of 18 months. So, each month the customer will
have to pay $1000 ($18000/18). I already have a query that records the actual
monthly payment of a customer. My problem is, what if a customer decided to
pay more than or less than the required monthly payment. I want to allocate
the customer payment to the monthly due. For example what if the customer
pays $1500 during the 1st month. I want to show this extra $500 to his 2nd
month allocation. Can you give me a formula on this using query? I have
created 18 fields for 18 monthly payments.. Pls response.. thnx

 

[Steve]

If a person borrows $18,000; his monthly payment will not be $1000. It will
be greater because part of his payment goes to interest. You haven't said
what the interest rate is.

Also, it is incorrect to have 18 fields for 18 payments. Your tables should
look like:
TblLoan
LoanID
LoanAmount
InterestRate
TermMonths

TblPayment
PaymentID
LoanID
PaymentDate
PaymentAmount

Steve
(e-mail address removed)