...12 payment transactions for a specific user per month
Do you really mean 12 payments per month, 144 per year? Or do you mean 12
MONTHLY payments. I'll assume the latter.
By definition, the PMT function calculates EQUAL periodic payments, so you
don't need to create 12 of them.
Look up this function in Access VBA help. Here's an example of its use:
Say you buy a car and get a loan of $10,000 for 5 years at an interest rate
of 6% per annum, to be repaid monthly. The first payment is due 1 month
after taking delivery.
MonthlyPayment = PMT(rate, nper, pv, fv, type)
rate = .06/12, interest rate per month
nper = 5*12, 60 monthly payments
pv = 10000, the amount of loan
fv = 0, the amount owed after the last payment (optional, assumed 0 if
omitted)
type = 0, first payment at the end of the period (optional, assumed 0 if
omitted)
PMT(.06/12, 60, 10000) returns -193.33
It is negative because it is money you pay OUT. For the bank financing the
deal, fv is negative (bank paid it OUT to you), payment is positive (money
coming IN from you).
If the loan is paid on schedule, there will be 60 payments, but you don't
have to store all of them in the database. Typically, in loan databases, the
regular payment amount is stored once in the loan header that gives the
overall parameters that don't vary over the life of the loan: to whom, how
much, how long, start date, interest rate, monthly payment.
When do you need the type argument? If the first payment is due at the start
of the first period (i.e. at delivery), then type=1 (this is typical of
lease deals).
The function can also be used to calculate: How much money do I have to
deposit each month into an account paying 3% per annum to have a total of
$1,000 after a year?
Here, rate=.03/12, nper=12, pv=0, fv=1000, type=0 or 1
If type =0, you wait until the end of the month to make your first payment,
and your balance will be 1000 as soon as you make your last payment. Monthly
payment = 83.19
If type=1, you must make the first payment at the beginning of the month,
and your balance will be 1000 one month after your last payment. Monthly
payment = 81.99
Which shows that the earlier you start saving, the less you have to save to
reach a given balance.
Good luck
-TedMi
