Financial Formula Error

New Contributor

I get a #NUM! error when using the formula: NPER(E17/12,M17,D17,0).

E17=14.15%      M17=-123.15     D7=16,643.43

When these values are used for calculating in a Amortization Table (a different spreadsheet in the same Excel workbook of the above formula) or a TVM Calculator, the results equals 110.8 periods.

Please help me understand what is the reason for this outcome.





5 Replies


The payment is far too small for this interest rate.

The monthly interest part alone is E17/12*D17 = 196.25. So the payment cannot be less than that.

Thanx for your reply. I just noticed that, in err, I typed D7 instead of D17. Oops...
Hans, I do understand your explanation. But, using the same data in an Excel amortization table, as well as a TVM calculator, both provides a result with no error. What explains this?
BTW, I encountered the error issue when changing, one at a time, the values in the "NPER" function.


Please check again - there is no way this could work.

Could you attach a sample workbook that shows otherwise?

Hans, sorry for the long delay replying. I’m new to this forum and have a lot to learn.
A Microsoft email reminded me of this.
I tried the app that I had used using the aforementioned numbers above, but now get a “NaN” result. Perhaps I made a mistake with the original numbers I tried, because the app did give me (what I thought) a valid result when I first attempted to get the calculation. Nevertheless, you are correct. I trust your advice and truly appreciate your prompt support and insight. Thanx, again…
Hans, when I responded to the Microsoft email to reply, it required a new username. That is why you will see two different usernames. Don’t understand why it wouldn’t default to my original username??