How It Works

When you make extra payments on a loan, you reduce the principal faster - which means less interest accrues each month. The savings compound over the life of the loan. This formula compares total interest under two scenarios: standard payments vs. payments with an extra amount.

Syntax

Total interest (standard):

=payment * NPER(rate/12, -payment, balance) - balance

Total interest (with extra):

=(payment+extra) * NPER(rate/12, -(payment+extra), balance) - balance

Interest saved:

=standard_interest - extra_payment_interest

Example

Car Loan:

Balance: $20,000
Interest Rate: 6.5% APR
Monthly Payment: $391 (60-month term)
Extra Payment: $100/month

Standard total interest:

=391 * NPER(6.5%/12, -391, 20000) - 20000 = $3,460

With extra $100:

=491 * NPER(6.5%/12, -491, 20000) - 20000 = $2,608

Interest saved: $3,460 - $2,608 = $852

Plus you’re done 13 months sooner.

Savings by Extra Payment Amount

$20,000 loan at 6.5%, 60-month term

Extra/Month	Months to Payoff	Total Interest	Interest Saved
$0	60	$3,460	-
$50	52	$2,988	$472
$100	46	$2,608	$852
$150	41	$2,296	$1,164
$200	37	$2,035	$1,425
$300	31	$1,634	$1,826

Variations

Lump Sum Extra Payment

One-time extra payment (like a tax refund):

=(payment * NPER(rate/12, -payment, balance) - balance) - (payment * NPER(rate/12, -payment, balance - lump_sum) - (balance - lump_sum))

Mortgage Savings (Large Scale)

On a $300,000 mortgage at 7%, 30 years:

Extra/Month	Years Saved	Interest Saved
$100	3.5	$55,800
$200	6.0	$93,400
$500	11.0	$163,500

The larger the loan and longer the term, the bigger the impact.

Return on Extra Payment

The effective return on extra payments equals your interest rate:

=loan_interest_rate

Paying $100 extra on a 7% loan is equivalent to investing that $100 at a guaranteed 7% return (tax-free).

Setting Up a Comparison Calculator

A	B
Loan Balance	$20,000
Annual Rate	6.5%
Monthly Payment	$391
Extra Payment	$100
Standard
Months	=NPER(B2/12, -B3, B1)
Total Paid	=B3*B6
Total Interest	=B7-B1
With Extra
Months	=NPER(B2/12, -(B3+B4), B1)
Total Paid	=(B3+B4)*B10
Total Interest	=B11-B1
Savings
Months Saved	=B6-B10
Interest Saved	=B8-B12

Pro Tips

Higher rate debts benefit most - $100 extra on a 22% credit card saves far more than on a 4% student loan
Specify “apply to principal” - when sending extra, make sure your lender applies it to principal and not as an advance on future payments
Lump sums have outsized impact early - extra payments in the first years of a loan save the most because interest has more time to compound
Compare against investing - if your loan rate is below expected investment returns, some people prefer investing the extra instead
Biweekly payments - paying half your monthly amount every two weeks results in 13 full payments per year instead of 12

Common Errors

#NUM! error - the extra payment may be larger than needed; check that total payment doesn’t exceed what’s required
Using annual rate instead of monthly - NPER needs rate/12 for monthly payments
Forgetting to negate payment - NPER requires payment as a negative value
Not accounting for minimum payment changes - some loans recalculate the minimum; use a fixed payment for accurate comparison

Interest Saved by Extra Payment