Spreadsheet Practice

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

Formula (Excel and Google Sheets)

=ROUNDUP(NPER(B3/12,-B4,B2),0)

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

Result on the example
37 in B5
Works in
Every current version
Google Sheets
Same formula

See it work

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

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.

=ROUNDUP(NPER(B3/12,-B4,B2),0)

Result37

  1. B319.9%

    The value in B3.

  2. B3/120.01658333333

    The / operator divides.

  3. B4$200

    The value in B4.

  4. -B4-200

    Makes the value negative.

  5. B2$5,400

    The value in B2.

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

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

    • rateB3/120.01658333333
    • pmt-B4-200
    • pvB2$5,400
  7. ROUNDUP(NPER(B3/12,-B4,B2),0)37

    ROUNDUP: Rounds away from zero.

    • numberNPER(B3/12,-B4,B2)36.10035105
    • num_digits0

In Google Sheets

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

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=NPER(B3/12,-B4,B2)36.10035105Different resultThe exact number of periods, for further calculations. 36.1 months, unrounded.
=ROUNDUP(NPER(B3/12,-B4,B2),0)/123.083333333Different resultDivide 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.

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.

Calculate how many months to pay off a loan

In B5, calculate how many monthly payments of B4 it takes to pay off the balance in B2 at the APR in B3, as a whole number of months.

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).

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.