How does the function NPER in excel is derived?

Question

I know that to get NPER, the formula in excel would be NPER(rate,pmt,[pv],fv,type) - but how does the logic work - i.e. is there a formula or derivation?

Answer 1

The formula is based on this

pv*( 1+rate )^nper+pmt( 1+rate*type )*(( 1+rate )^nper-1 )/rate+fv=0

To simplify it, if rate is 0, then

(pmt*nper )+pv+fv=0

or

(pmt*nper )+fv=pv since pv is negative, represents money that would pay, an outgoing cash flow, and what you willing to pay now (pv) say for a car, should equal its continuous return value(pmt*nper) plus the scrape value(fv) at the end of its life.

To devrive the nper is difflicult manually, because moving around the variables to get nper is hard, what was done behind the scene in programming is when you give rate, pmt, pv, fv, type to the programme, it uses routine to feed the variable nper with a series of numbers, the number that makes the equation on both sides equal 0 is the right nper, then return that nper to you.

Hope this helps.