NPER Function
Calculates how many payments it takes to pay off a loan or reach a goal.
Category
Financial
Available in
All versions, Microsoft 365
Returns
A number of periods
Want to practice NPER in a real spreadsheet? Open a blank sheet in our Practice Sandbox and try it with live data, with the AI Excel tutor beside you. No Excel needed — just a free account.Try it free in the Sandbox
Overview
NPER answers "how long?" — how many payments to clear a balance, or how many deposits to reach a target, at a fixed rate and payment.
The result is in the same units as the rate: a monthly rate gives months.
Syntax
=NPER(rate, pmt, pv, [fv], [type])
Arguments
| Argument | Required? | What it does |
|---|---|---|
| rate | Yes | The interest rate per period (annual rate ÷ 12 for monthly payments). |
| pmt | Yes | The payment each period (negative if you pay it). |
| pv | Yes | The amount owed now (or 0 when saving from scratch). |
| fv | No | The balance you want left after the last payment. Omitted = 0 (a loan paid off in full). |
| type | No | When payments fall: 0 or omitted = end of each period, 1 = start of each period. |
Examples
Example 1 — Paying off a balance
Northstar owes $20,000 on a 9% credit line and pays $600 a month.
B2fx=NPER(9%/12,-600,20000)
| A | B | |
|---|---|---|
| 1 | Goal | Months |
| 2 | Clear $20,000 debt | 38.50 |
| 3 | Save $50,000 at 4%, $800/mo | 56.87 |
Round up with ROUNDUP — 38.5 means a 39th, smaller payment.
Common mistakes
- #NUM! error. The payment is too small to cover the interest, so the debt never clears. Raise the payment.
- Signs. pmt and pv need opposite signs for a loan.
Notes

Author
Kyle Stott
Certified Microsoft Excel Expert
Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.