Excel like a finance pro.
NPER
FinanceHow many payments until this is paid off — the runway question, in loan form.
Difficulty
1What is it?
NPER solves the loan equation for the number of periods: given a balance, a rate and what you can afford to pay, how long until it's gone. It's the function behind every 'what if we paid an extra $200 a month' conversation, and the answer is usually more dramatic than people expect because the extra comes straight off principal. It returns a fractional number of periods — 41.7 payments — which is honest: the last payment is a partial one.
2What it looks like
NPER(rate, pmt, pv, [fv], [type])- rate
- The rate per period, so an annual rate over 12 for monthly payments.
- pmt
- What you pay each period, negative for money leaving.
- pv
- The balance owed today, positive.
- [fv]
- The balance you want left at the end. 0 — paid off — by default.
- [type]
- 0 (default) end of period, 1 beginning.
3When you use it
- Show what paying a little extra each month does to the end date.
- Work out how long a reserve lasts at the current burn.
- Sanity-check a payment plan a customer has proposed.
4See it in action
Change the inputs — the formula and result update live. Prefer the real thing? Download the Excel file and open it in Excel.
Add a little to the payment and watch the finish line move — the extra comes straight off principal.
=NPER(B3/100/12, -(B4+B5), B2)At $1,200/mo: 44.2 months (3.7 years)| A | B | C | |
|---|---|---|---|
| 1 | Input | Value | Result |
| 2 | Balance owed | At $1,200/mo: 44.2 months (3.7 years) | |
| 3 | Annual rate % | At $1,400/mo: 36.9 months | |
| 4 | Payment / month | 7.3 months sooner | |
| 5 | Extra / month |
The lime cell holds the formula — click it (or any cell) to see its contents in the bar above, just like Excel. Edit the blue cells to watch it recompute.
5Common errors
#NUM!The payment doesn't even cover the interest, so the balance never falls.Fix: This is a real answer in disguise — the loan never amortizes. Raise pmt above pv × rate.
Answer is negativepmt and pv were given the same sign.Fix: Money out is negative, the balance owed is positive.
Periods read as yearsA monthly rate was used and the result was labelled as years.Fix: NPER answers in whatever period the rate is in. Divide by 12 to show years.
6Better functions & alternatives
- PMT — Solves the same equation for the payment when the term is fixed instead.
- RATE — The third unknown in the same equation — the rate itself.
- Amortization schedule — Shows the path rather than just the finish line, including the partial final payment.
Want NPER already wired into a model? Wauvel's free tools download as branded, formula-driven Excel.
Learn the moves here — or let Wauvel run them on your numbers.
Meet your AI CFO →One CFO-grade Excel tip a week
A short, practical email for finance operators — functions, shortcuts, and the moves that save an afternoon. Free, unsubscribe anytime.