NPER function
NPER calculates how many payment periods it takes to pay off a loan or reach a savings goal, at a fixed payment and interest rate. Use it to answer "how many months until this card is paid off?" The result is usually a fraction: the last payment is a partial one.
- Available in
- Every current version
- Category
- Financial functions
- Returns
- The number of periods, often with a decimal for the final partial payment.
- Google Sheets
- Works the same in Google Sheets.
Syntax
=NPER(rate, pmt, pv, [fv], [type])
| Argument | Required? | What it is |
|---|---|---|
rate | Required | The interest rate per period, such as an APR divided by 12. |
pmt | Required | The payment each period. Enter payments as negative numbers. |
pv | Required | The current balance owed, or 0 when saving toward a goal. |
fv | Optional | The balance to reach. 0 (the default) for paying off a loan; the goal amount when saving. |
type | Optional | 0 or omitted for payments at the end of each period, 1 for the beginning. |
Examples
Each result below is calculated from the sample sheet by our formula engine, the same one that grades the practice.
| Cell | Formula | Result | What it shows |
|---|---|---|---|
| D2 | =NPER(B3/12,-B4,B2) | 31.92 | Months to clear the card at $250 a month |
| D3 | =NPER(B3/12,-B4*2,B2) | 13.68 | Doubling the payment cuts the time by more than half |
| D4 | =NPER(4%/12,-B4,0,B5) | 71.03 | Months of $250 deposits to save $20,000 at 4% |
Try it live
This is the sample sheet. Change any number or text, or edit a formula, and the results update.
Show the sample data as a table
| Input | Value | Result | |
| Card balance | $6,000 | 31.92 | |
| APR | 22% | 13.68 | |
| Monthly payment | $250 | 71.03 | |
| Savings goal | $20,000 |
Practice NPER
Graded instantly, then re-checked on changed data.
Payoff timeline
B5, calculate how many monthly payments it takes to pay off the balance in B2 at the APR in B3, paying the amount in B4 each month.Common mistakes
| What you see | Why, and how to fix it |
|---|---|
| #NUM! | The payment doesn't cover the interest, so the balance never shrinks. Raise the payment above balance × monthly rate. |
| #NUM! with sensible numbers | The payment and the balance have the same sign. Enter the payment as negative and the balance as positive. |
| A result in years when you wanted months | NPER returns periods in the same unit as the rate. A monthly rate gives months; divide by 12 for years. |
Tips
- Wrap it in ROUNDUP to get the number of actual payments: =ROUNDUP(NPER(...),0).
Questions
How many months will it take to pay off my credit card?
Use =NPER(APR/12, -monthly_payment, balance). $6,000 at 22% APR with $250 a month takes about 32 months.
Why does NPER return #NUM!?
Usually the payment is too small to cover each month's interest, or the payment and balance have the same sign.
Can NPER work out how long to save for a goal?
Yes. Set pv to 0 and fv to the goal: =NPER(rate/12, -deposit, 0, goal).
Last reviewed October 2026. Every example result on this page is calculated live by the Spreadsheet Practice formula engine.