I'm trying to use NPER to obtain the number of months required for a 100,000 loan/debt to be reduced to 80,000, when the interest rate is 4% annually and monthly repayments are 10000/12.
I know the answer is 39 months (I've computed it with another method that I trust).
Parametrizing NPER:
=nper(0.04/12,10000/12,-100000,-80000,1)
gives 236.0242766 months, not 39 months as expected.
What is wrong?
The amount borrowed (100k) and the amount still outstanding (80k) need to be of opposite sign. (Similar to before!) In Google Sheets and Excel each should return 37.42.
A little trickier for me to explain than why the loan and the repayments need to be of opposite sign, but the rationale is similar. Maybe think of a hiatus at 37 months. In reality, there is then still 80k outstanding – so this might be the loan amount for a new calculation (perhaps with a revised interest rate or larger/smaller monthly repayments). In such other calculation the 80K would logically have the same sign as the 100k has at present.
But at no time is the100k (or its remainder) outstanding while the 80k (or its remainder) is, hence for the 80k to feature in both calculations netting off between these (one plus and one minus) makes sense to me (leaving the borrowed amount as 100k in total).
Or consider that after 37 months it is though the 80k is paid back (opposite sign to borrowed) and a fresh borrowing of 80k starts.