How long to clear a card balance at a fixed payment? NPER counts the months — and shows starkly how a low minimum payment stretches the debt for years.
The example
$5,000 at 20% APR, paying $150/mo → ~47 months.
| A | B | |
|---|---|---|
| 1 | Item | Value |
| 2 | Balance | $5,000 |
| 3 | Payment | $150 |
| 4 | Months | 47 |
The formula
The formula:
How it works
How it works:
NPER(rate/12, −payment, balance)returns the number of monthly payments to reach zero.- The balance is positive (you owe it); the payment negative (you pay it down).
- Divide by 12 for years. A small payment near the interest charge makes NPER huge — or infinite if the payment doesn’t cover the interest.
- Raise the payment and watch the payoff time drop sharply — the case for paying more than the minimum.
Below the interest, never paid off: if the monthly payment is less than the interest accruing, the balance grows and NPER returns an error. The payment must exceed balance × rate/12 to make any progress.
Try it: interactive demo
Balance, APR, payment.
Variations
Payment for a target time
Pay off in N months:
Total interest paid
Over the payoff:
Years
Divide by 12:
Pitfalls & errors
Payment must beat interest. If it’s below balance×rate/12, NPER errors — the balance never falls.
Sign convention. Balance positive, payment negative.
Fixed payment assumed. Real minimums shrink as the balance falls, stretching payoff even further.
Practice workbook
Frequently asked questions
How do I calculate credit card payoff time in Excel?
Why does NPER return an error?
How much should I pay to clear it in a set time?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class