I'm trying understand how NPER works. I used the following arguments:
loan amount: 145750
APR : 0.04
monthly payments : 9950/12
called the NPER with the following to obtain the number of periods in years :
=nper(0,04/12;9950/12;145750;0;1)/12
and it returned -11,51. This cannot be correct since 11,51 * 9950 = 114524,5 which is less than the original loan amount.
What has gone wrong?
NPER is effectively performing a balancing act, say borrowing a lump sum now and repaying it in relatively small installments for 'years' to come - or investing a capital amount now in exchange for a series of relatively small returns for 'years' to come. Either way money goes out and money comes in. =NPR sets the one-off (large) element against the stream of (small) elements and allows for the time value of money.
For this to work, the convention is that what goes out is negative and what comes in is positive, though does work either way around.
However, the two must be of opposite sign.