# NPER function in Excel

> 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.

Source: https://spreadsheetpractice.com/functions/nper · Updated October 28, 2026 · Formulas and results on this page were run by the Spreadsheet Practice formula engine on November 20, 2026

## Quick answer

- **What it does:** 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.
- **Syntax:** `=NPER(rate, pmt, pv, [fv], [type])`
- **Most common use:** `=NPER(B3/12,-B4,B2)` returns 31.92 (Months to clear the card at $250 a month)
- **Available in:** Every current version
- **Category:** [Financial functions](https://spreadsheetpractice.com/functions/category/finance)
- **Returns:** The number of periods, often with a decimal for the final partial payment.
- **Google Sheets:** Works the same in Google Sheets.

## What it does

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.

## 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 was calculated by running the formula on the sample sheet.

| 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% |

### The sample sheet

Every formula above runs on this data:

|  | A | B | C | D |
|---|---|---|---|---|
| 1 | Input | Value |  | Result |
| 2 | Card balance | $6,000 |  | 31.92 |
| 3 | APR | 22% |  | 13.68 |
| 4 | Monthly payment | $250 |  | 71.03 |
| 5 | Savings goal | $20,000 |  |  |

## 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).

## Related functions

- [PMT](https://spreadsheetpractice.com/functions/pmt): PMT calculates the fixed payment for a loan or a savings plan, based on a constant interest rate and number of payments.
- [RATE](https://spreadsheetpractice.com/functions/rate): RATE works out the interest rate per period from the number of payments, the payment and the amount borrowed or invested.
- [FV](https://spreadsheetpractice.com/functions/fv): FV calculates what an investment will be worth in the future, given regular deposits, a starting balance and a constant rate of return.
- [PV](https://spreadsheetpractice.com/functions/pv): PV calculates what a series of future payments, or a single future amount, is worth today at a given interest rate.

## 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).

## Practice

A graded NPER exercise ("Payoff timeline") is on [the NPER page](https://spreadsheetpractice.com/functions/nper). It is checked instantly, then re-checked on changed data.

---

Spreadsheet Practice (https://spreadsheetpractice.com) teaches spreadsheet formulas by practice: every exercise is graded in a live grid and re-checked on changed data.
