# How to calculate how many months to pay off a loan in Excel

Use NPER with the monthly rate, the payment and the balance, and round up: `=ROUNDUP(NPER(B3/12,-B4,B2),0)`.

**Excel:** `=ROUNDUP(NPER(B3/12,-B4,B2),0)` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B |
| --- | --- |
| Credit card | Value |
| Balance | 5400 |
| APR | 0.199 |
| Monthly payment | 200 |
| Months to pay off |  |

Formula in B5: `=ROUNDUP(NPER(B3/12,-B4,B2),0)`

Result: 37

## How it works

NPER returns the number of periods needed to pay a balance down to zero. The rate is per month, the payment goes in as a negative (money going out) and the balance as a positive (money received). NPER gives 36.1, meaning 36 full payments and a small 37th, so ROUNDUP reports 37 months.

## Other ways

- `=NPER(B3/12,-B4,B2)`: The exact number of periods, for further calculations. (36.1 months, unrounded.)
- `=ROUNDUP(NPER(B3/12,-B4,B2),0)/12`: Divide by 12 to show it in years. (About 3.1 years.)

## Common mistakes

- If the payment is no more than the monthly interest, the balance never falls and NPER returns #NUM!. Here the interest is about $90 a month.
- Give the payment and the balance opposite signs. Both positive returns #NUM!.
- NPER assumes the rate and payment never change. Card balances with new purchases need a month-by-month schedule.

## Questions

**How much interest will I pay in total?**

Total payments minus the balance: `=NPER(B3/12,-B4,B2)*B4-B2`.

**What payment pays it off in 24 months?**

Use PMT: `=PMT(B3/12,24,-B2)`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/months-to-pay-off-a-loan