How Fast a Loan Pays Off (NPER)

All versionsNPER

NPER tells you how many payments it takes to clear a loan — and what happens when you pay extra each month. Bumping the payment up cuts the term dramatically, and NPER quantifies exactly how much.

Quick formula: for monthly rate B1/12, monthly payment in B2, and balance in B3:

=NPER(B1/12, -B2, B3)

Returns the number of monthly payments to reach zero. Divide by 12 for years; raise the payment and watch it drop.

Functions used (tap for the full reference guide):

NPER PMT

The example

A $25,000 loan at 6%: the standard payment vs paying $100 extra.

	A	B
1	Monthly payment	Months to payoff
2	$483.32 (standard)	60
3	$583.32 (+$100)	~47
4	$683.32 (+$200)	~39

The formula

Months to pay off at a given payment:

=NPER(6%/12, -583.32, 25000) // $100 extra → ~47 months instead of 60

How it works

NPER solves for the number of periods:

Inputs mirror PMT: the monthly rate (B1/12), the payment (negative — cash out), and the current balance.
NPER returns how many monthly payments reach a zero balance.
Increase the payment and NPER falls fast — extra principal each month compounds into big time savings.
Divide by 12 for years: a 60-month loan becomes ~47 months (under 4 years) with just $100 extra per month.

Interest saved: compare payments × payment for each scenario. Paying extra means fewer payments — the difference in total paid is the interest you avoided. NPER turns “should I pay extra?” into a concrete number.

Try it: interactive demo

Live demo

$25,000 at 6%. Add extra to the $483 payment.

Extra per month: $100

Payoff:

Variations

In years

Divide the result by 12:

=NPER(B1/12, -B2, B3) / 12

Payment to finish by a date

Flip it — solve for the payment given the periods:

=PMT(B1/12, months, B3)

With a remaining balloon

Pay down to a future value, not zero:

=NPER(B1/12, -B2, B3, -5000)

Pitfalls & errors

Payment too small. If the payment doesn’t cover the monthly interest, the balance never falls and NPER returns an error. The payment must exceed balance × rate.

Sign convention. Enter the payment as negative (cash out) with a positive balance, or NPER returns a nonsensical result.

NPER returns a fraction. 46.8 months means the loan clears partway through month 47 — round up for a whole-payment count.

Practice workbook

📊

Download the free How Fast a Loan Pays Off (NPER) practice workbook

A payoff calculator with live NPER for extra payments, the in-years and payment-to-finish variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I calculate how long it takes to pay off a loan in Excel?

Use =NPER(rate/12, -payment, balance). It returns the number of monthly payments to reach zero; divide by 12 for years.

How much faster will extra payments pay off my loan?

Add the extra to the payment inside NPER, e.g. =NPER(6%/12, -583.32, 25000). A $100 extra payment can cut a 60-month loan to about 47 months.

Why does NPER return an error?

The payment is too small to cover the interest, so the balance never decreases. Make sure the payment exceeds balance times the periodic rate, and that it's entered as negative.

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

Function references: NPER · PMT