NPER function

Question

Does anyone know the mathematical formula behind the Excel NPER function?  Thanks.

Answer 1

Errata....  I had an extra right-parenthesis in some formulas below.  Corrected.

AXRussell wrote:

Does anyone know the mathematical formula behind the Excel NPER function?

Why do you ask?  School assignment?  Idle curiosity?  Something else?

The PV help page (click here) [1] documents the formula:

It is important to note that "pv", "fv" and "pmt" are signed cash flows; for example, negative for outflows, and positive for inflows.

Also note that "rate" is the periodic rate per unit of "n".  For example, if "n" represents months and the annual rate is 12%, the monthly rate is 1% (12%/12) or about 0.94888% [2], depending on how monthly interest rates are annualized for your purposes.

Finally, in that formula, "type" is presumed to be zero (payments at the end of periods) or one (payments at the beginning of periods).  But any non-zero "type" is interpreted as one.

So solving that formula for "nper", the usage is demonstrated as follows:

The formula in B7 is =NPER(B1,B2,B3,B4,B5).  It is provided as a check of the formula below.

The formula in B6 is:

=IF( B1=0, -(B3+B4)/B2,

LOG( (B2 * (1+B1*(B5<>0)) - B4*B1) / (B3*B1 + B2*(1+B1*(B5<>0))) ) / LOG(1+B1) )

or stylistically:

=IF( rate=0, -(pv+fv)/pmt,

LOG( (pmt * (1+rate*(type<>0)) - fv*rate) / (pv*rate + pmt*(1+rate*(type<>0))) ) / LOG(1+rate) )

That's a mouthful!  We can simplify if we know that "rate" is non-zero and "type" is zero or non-zero:

type=0:

=LOG( (B2 - B4*B1) / (B3*B1 + B2) ) / LOG(1+B1)

or

=LOG( (pmt - fv*rate) / (pv*rate + pmt) ) / LOG(1+rate)

type<>0:

=LOG( (B2*(1+B1) - B4*B1) / (B3*B1 + B2*(1+B1)) ) / LOG(1+B1)

or

=LOG( (pmt*(1+rate) - fv*rate) / (pv*rate + pmt*(1+rate)) ) / LOG(1+rate)

Note that B4 (fv) is typically zero, which leads to further simplication:  the expression -B4*B1 can be omitted.

You might note that the calculated "nper" is not exactly 360 in either case (B6 and B7).  The primary reason is:  we rounded "fv" to simplify the example.

But even if we hadn't, "nper" might not be an exact integer due to anomalies of binary arithmetic.

---

[1] https://support.office.com/en-us/article/PV-function-23879d31-0e02-4321-be01-da16e8168cbd

[2] (1+12%)^(1/12) - 1 = 0.948879293458305%

Answer 2

Hello,

NPER=Log10(Payment/(Payment+Capital+Rate))/Log10(1+Rate)

For more information, go to:

https://www.mrexcel.com/forum/excel-questions/315095-nper-mathmatical-formula.html

Regards

Answer 3

Hi Axrussell,

Any update?

Regards,

Tisky