MrExcel Publishing
Your One Stop for Excel Tips & Solutions

nper function problem


Posted by trish mangan on January 29, 2002 11:03 AM

I am attempting to solve the following:

12% annual interest
1500 monthly payment
150,000 principal

I used the following npr function

=npr(12%/12,-1500, 150,000) and I get #num
Interestingly, when I do =npr(12%/12,-447.68, 17,000) it works fine and tells me 48 months.

(I use cell references with these values, but the result is the same)

Rate 12% Rate 12%
Payment ($1,500.00) Payment ($447.68)
PV 150,000 PV 17,000

Periods(NPER) #NUM! Periods (NPER) 48.00


Rate 0.12 Rate 0.12
Payment -1500 Payment -447.68
PV 150000 PV 17000

Periods (NPER)
=NPER(B7/12,B8,B9) Periods (NPER) =NPER(E7/12,E8,E9)

What's up. Why does it work for low values and not higher values. What logic am I missing?


Posted by Mike H on February 01, 2002 6:12 AM

I tried it and it gives the result 0.1005, which should be correct.

Posted by trish mangan on February 04, 2002 8:33 AM