NPER function
NPER returns how many payments it takes to pay off a loan or reach a savings goal. It is often used to see the effect of paying more each month.
Example
Read it as a sentence: find how many periods it takes to pay off a 5000 loan at 1% per period with 200 paid each period, which gives 28.9118097374808.
A
B
C
D
1
28.9118097374808
2
3
Syntax
NPER(rate, pmt, pv, [fv], [type])
Arguments in [square brackets] are optional.
-
rate(required) - Interest rate per period.
-
pmt(required) - Payment each period.
-
pv(required) - Present value.
-
fv(optional) - Desired future value; 0 if omitted.
-
type(optional) - 0 = payments at period end (default), 1 = at start.
Returns: Number of periods needed.
What Excel actually returns
Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.
| Formula | Excel shows | Case |
|---|---|---|
=NPER(0.01,-100,1000) | 10.58864446 | Loan |
=NPER(0.01,-100,-1000,10000,1) | 59.67386567 | With fv and type 1 |
=NPER(0,-100,1000) | 10 | Rate 0 |
=NPER(0.05,0,-100,200) | 14.20669908 | Pmt 0 with rate |
=NPER(0.01,100,1000) | -9.57859404 | Same sign pv and pmt |
=NPER("0.01","-100","1000") | 10.58864446 | Text number |
=NPER(0,0,1000) | #DIV/0! | Pmt 0 rate 0 |
=NPER(0.1,-50,1000) | #NUM! | Payment too small (never repaid) |
Watch out for
- Returns #NUM! if the payment cannot cover the interest.
- Pay and receive amounts need opposite signs.
Where it works
- Excel
- NPER is in every version of Excel from 2007 on.
- Google Sheets
- Same name, same behaviour for everyday use.
Part of Financial functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .