Hi Rachel!
=NPER(rate, pmt, pv, fv, type)
Will return the number of periods. Make sure that you sign the flows
correctly with amounts paid out as negative and amounts received as
positive.
The result will almost inevitably have a non-integer element. This
means that the number of payments will be the integer number with a
final larger than normal payment or will be the integer number + 1
with a final payment less than normal.
Your Lotus function is using the first approach and the Excel
equivalent will be:
=ABS(NPER(rate, pmt, pv, fv, type))
Using:
=NPER(0.06/12,-1000,100000,0,0)
Returns: 138.975721610697
But this doesn't mean that you pay off the loan 138.975721610697
months from now because payments are only made on the 138th and 139th
anniversaries.
So your final payment after 138 periods is:
=-1000+FV(6%/12,138,-1000,100000,0)
Returns: -1970.92600873805
Or a final payment after 139 periods is
=-1000+FV(6%/12,139,-1000,100000,0)
Returns: -975.780638781725
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
Rachel said:
=ABS(NPER(+K8*0.01/12,-(J8),,-O8))
This is the formula I am now using (from Lotus) to figure the
amortizing term of a loan. How do I do it in Excel?
