Excel NPER Function
NPER is an Excel function that finds out the number of payments in a finite stream of equidistant equal cash flow of amount PMT given a single value PV at t=0 and/or a single value FV at the end of the stream of cash flows.
Just like PV, FV and PMT functions, NPER by default assumes the cash flows to occur at the end of each period. However, they all have an option that enables us to find present value, future value, periodic payment and number of periods respectively where cash flows occur in advance i.e. at start of each period.
Syntax
NPER syntax is:
NPER(rate, pmt, pv, [fv], [type])
Rate is a mandatory argument that refers to the periodic nominal interest rate, i.e. the annual nominal interest rate divided by the total number of compounding periods per year.
PMT is also mandatory and it stands for the constant cash flow that occur either at the start or end of each period.
There must be either one of PV or FV or both, PV being a single sum at t=0 and FV being a single sum at t=NPER.
[Type] is an optional argument, which is either 0 or 1; 0 being the default value specifying that the cash flow occurs at the end of the period (i.e. that the cash flow stream is an annuity) and 1 specifying that cash flows occur at the start of the period (i.e. that the stream is an annuity due).
If a present value and/or a future value is given, NPER function finds NPER using any of the following equation:
$$ \text{PV}= \\ \text{PMT}\times\frac{\text{1}-({\text{1}+\text{RATE})}^{-\text{NPER}}}{\text{RATE}} \\ +\frac{\text{FV}}{{(\text{1}+\text{RATE})}^{\text{NPER}}} $$
$$ \text{FV}= \\ \text{PV}\times\left(\text{1}+\text{RATE}\right)^{\text{NPER}} \\ +\text{PMT}\times\frac{({\text{1}+\text{RATE})}^{\text{NPER}}-\text{1}}{\text{RATE}} $$
They are just different arrangement of the same relationship.
Example
You currently have $25,000 in savings deposited at 6% per annum compounded monthly. You are saving $2,000 per month and your target is to reach $100,000 you need to enroll in a top MBA program.
Find out how many months it will take you to reach your goal.
Based on the above date, we can set up the following equation:
$$ \text{\$100,000}=\\ \text{\$25,000}\times\left(\text{1}+\frac{\text{0.06}}{\text{12}}\right)^{\text{NPER}}\\+\text{\$2,000}\times\frac{\left(\text{1}+\frac{\text{0.06}}{\text{12}}\right)^{\text{NPER}}-\text{1}}{\frac{\text{0.06}}{\text{12}}} $$
Solving the above equation requires some good mathematics. Luckily, Excel NPER function is a useful alternative. The following screenshot solves the cash flow system above to find NPER, the number of months it will take you to accumulate $100,000.
Since you are saving $2,000 each month, you must take care to enter the monthly interest rate and the cash flows signs must be opposite where the cash flow direction is opposite. At t=0, you invested $25,000 and each $2,000 saving is also deposited, so both must have same sign and since at the end of the cash flow stream, you will receive $100,000 back, it must have a sign opposite to that of PMT and PV.
by Obaidullah Jan, ACA, CFA and last modified on