Spreadsheet Practice

How many months do extra payments save on a loan in Excel

Formula (Excel and Google Sheets)

=NPER(B3/12,-B4,B2)-NPER(B3/12,-(B4+B5),B2)

Compare NPER with and without the extra amount: =NPER(B3/12,-B4,B2)-NPER(B3/12,-(B4+B5),B2).

Result on the example
46.5 in B6
Works in
Every current version
Google Sheets
Same formula

See it work

The formula is already in B6. Change any input and the result updates, or edit the formula to experiment.

How it works

NPER returns how many payments clear the balance at a given payment. The first NPER uses the normal payment, the second the payment plus the extra $200, and the difference is the time saved: about 46 months, almost four years. Extra money goes straight to principal, so less interest builds up every month after.

=NPER(B3/12,-B4,B2)-NPER(B3/12,-(B4+B5),B2)

Result46.45429249

  1. B35.75%

    The value in B3.

  2. B3/120.004791666667

    The / operator divides.

  3. B4$1,650

    The value in B4.

  4. -B4-1650

    Makes the value negative.

  5. B2$240,000

    The value in B2.

  6. NPER(B3/12,-B4,B2)249.7629138

    NPER: Number of periods to pay off or reach a target.

    • rateB3/120.004791666667
    • pmt-B4-1650
    • pvB2$240,000
  7. B3/120.004791666667

    The / operator divides.

  8. B5$200

    The value in B5.

  9. B4+B51850

    The + operator adds.

  10. -(B4+B5-1850

    Makes the value negative.

  11. NPER(B3/12,-(B4+B5),B2)203.3086213

    NPER: Number of periods to pay off or reach a target.

    • rateB3/120.004791666667
    • pmt-(B4+B5-1850
    • pvB2$240,000
  12. NPER(B3/12,-B4,B2)-NPER(B3/12,-(B4+B5),B2)46.45429249

    The - operator subtracts.

In Google Sheets

The same formula works in Google Sheets: =NPER(B3/12,-B4,B2)-NPER(B3/12,-(B4+B5),B2). We ran it in Google Sheets mode on the same data and it returns 46.5.

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=(NPER(B3/12,-B4,B2)*B4)-(NPER(B3/12,-(B4+B5),B2)*(B4+B5))35987.9Different resultThe interest saved: total paid without the extra minus total paid with it. Interest saved: about $36,000.
=NPER(B3/12,-(B4+B5),B2)203.3Different resultThe new payoff time on its own. About 203 months with the extra payment.

Common mistakes

  • NPER returns #NUM! if a payment doesn't cover the monthly interest, $1,150 here.
  • This assumes the extra is paid every month from now on. A one-off lump sum is better modeled by reducing the balance.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

How many months do extra payments save on a loan

In B6, calculate how many months sooner the balance in B2 is paid off if the extra amount in B5 is added to every monthly payment in B4.

Questions

How do I see the result in years?

Divide by 12: =B6/12.

What about a lump sum instead?

Reduce the balance: =NPER(B3/12,-B4,B2)-NPER(B3/12,-B4,B2-10000) for a $10,000 payment now.

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.