Syntax
NPER(rate, pmt, pv, [fv], [type])
| Parameter | Description |
|---|---|
| rate | Parameter of the NPER function. |
| pmt | Parameter of the NPER function. |
| pv | Parameter of the NPER function. |
| [fv] | (Optional.) Parameter of the NPER function. |
| [type] | (Optional.) Parameter of the NPER function. |
Examples
Months to pay off a loan
Formula
=NPER(0.06/12, -500, 25000)
56.07. At $500/month with 6% interest, it takes about 57 months (4 years 9 months) to pay off a $25,000 loan.
Years to reach savings goal
Formula
=NPER(0.07/12, -1000, 0, 500000)
242.53. Saving $1,000/month at 7% return, you'll reach $500K in about 243 months (just over 20 years).
Impact of extra payments
Formula
=NPER(0.065/12, -2500, 350000)
243.88. Paying $2,500/month instead of the minimum $2,213 on a $350K mortgage cuts the loan from 360 months to about 244 months — saving almost 10 years.
Common Errors
#NUM!
The payment is too small to ever pay off the loan (e.g., monthly payment is less than monthly interest), making the answer infinite.
#VALUE!
One of the arguments is non-numeric.
Tips
Partial periods
NPER often returns a fractional result. Round up with CEILING(NPER(...), 1) to get the actual number of full payments you'll make.
Payoff acceleration
Compare NPER at different payment amounts to quantify exactly how much time extra payments save. Even an extra $100/month can dramatically shorten a mortgage.
Sign convention
Make the payment negative (outflow) and the present value positive (amount owed/received). Mixing signs incorrectly causes #NUM! errors.
Try NPER in Viztab
Import your data and use NPER with 370+ other formulas. No signup required.
Open Viztab