NPER calculates how many payment periods are needed to pay off a loan or reach a savings goal, given a fixed payment and rate.
NPER works out how many payment periods it will take to pay off a loan, or to reach a savings target, given a constant interest rate and fixed regular payment.
It answers questions like "how many months until this loan is paid off" or "how many years of saving $X per month until I reach $Y".
=NPER(rate, pmt, pv, [fv], [type])| Argument | Description |
|---|---|
| rate required | The interest rate per period. |
| pmt required | The payment each period (negative, since it leaves your hand). |
| pv required | The present value or loan amount. |
| fv optional | The future value or target balance. Defaults to 0. |
| type optional | 0 = payments at end of period (default), 1 = start. |
=NPER(0.05/12,-200,5000)At $200/month and 5% annual interest, a $5,000 loan takes about 26.5 months to pay off.
=NPER(0.04/12,-300,0,20000)Saving $300/month at 4% annual interest, this calculates how many months are needed to reach $20,000.
=NPER(0.05/12,-300,5000)The same $5,000 loan paid off faster at $300/month instead of $200 — fewer periods needed.
If pmt is monthly, rate must be the monthly rate (annual rate divided by 12), or the result will be wrong.
ExcelPro has 700+ hands-on Excel exercises across 7 career tracks — free to start, no download needed.
Start practicing free →