>Anyone know how to calculate the payment on a loan where the first XX months are interest free and the remaining months are charged interest? PAYMENT() doesn't work in that situation. I found a good site on loan calculations at
http://oakroadsystems.com/math/loan.htm, but it doesn't cover that scenario.
>
Problem solved with help from Stan at Oak Road Systems. Stan says:
Initially I thought that there wasn't a closed-form solution and iterative methods were required, but I hadn't thought the problem through.
As usual, the hardest part is setting up the problem. Here you really have two loans, one that is interest free and one that accrues interest.
(When you said "interest free", I'm assuming you really mean interest free. If interest accrues but is deferred, you have a different problem.)
Let N1 = interest-free number of payments
N2 = interest-charged number of payments
P = payment amount (common to both parts, per the problem)
A = amount financed
A2 = balance when the loan switches from interest free to interest
charged, i.e. after N1 payments and with N2 payments remaining
i = monthly interest (stated rate divided by 12)
Now, you know that
P = i*A2 / [1 - (1+i)^(-N2)]
and
A2 = A - P*N1
Therefore
P = i*(A - P*N1) / [1 - (1+i)^(-N2)]
It's a bit ugly, but certainly solvable for P.Stan was right. I had to help it along, but I was able to use an online algebra solver to eventually solve the formula. Using Stan's variables above, here it is:
P = -(i*A)/(((1+i)^-N2) - 1 - i*N1)
Here it is as usable program:
Lparameters lnFinan, lnRate, lnMonthsInt, lnMonthsFree
Local lnPayment
lnRate = lnRate/12
lnPayment = -(lnRate * lnFinan)/(((1+lnRate)^-lnMonthsInt) - 1 - lnRate * lnMonthsFree)
? "Calculated Payment:", Round(lnPayment, 2)
Thanks everyone for your help.