Spreadsheet Practice

How to calculate the remaining balance on a loan in Excel

Formula (Excel and Google Sheets)

=-FV(B3/12,B5,PMT(B3/12,B4*12,B2),B2)

Use FV with the payment from PMT: =-FV(B3/12,B5,PMT(B3/12,B4*12,B2),B2) is what is still owed after B5 payments.

Result on the example
$15,959.86 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

FV rolls the loan forward: it grows the amount borrowed by the monthly interest and takes off each of the 24 payments. PMT supplies the payment, as a negative number because payments go out. FV reports the balance still owed as a negative, so the minus sign in front shows it as a positive amount.

=-FV(B3/12,B5,PMT(B3/12,B4*12,B2),B2)

Result15959.85708

  1. B36.50%

    The value in B3.

  2. B3/120.005416666667

    The / operator divides.

  3. B524

    The value in B5.

  4. B3/120.005416666667

    The / operator divides.

  5. B45

    The value in B4.

  6. B4*1260

    The * operator multiplies.

  7. B2$25,000

    The value in B2.

  8. PMT(B3/12,B4*12,B2)-489.1537055

    PMT: Payment per period for a loan or annuity.

    • rateB3/120.005416666667
    • nperB4*1260
    • pvB2$25,000
  9. FV(B3/12,B5,PMT(B3/12,B4*12,B2),B2)-15959.85708

    FV: Future value of an investment.

    • rateB3/120.005416666667
    • nperB524
    • pmtPMT(B3/12,B4*12,B2)-489.1537055
    • pvB2$25,000
  10. -FV(B3/12,B5,PMT(B3/12,B4*12,B2),B2)15959.85708

    Makes the value negative.

In Google Sheets

The same formula works in Google Sheets: =-FV(B3/12,B5,PMT(B3/12,B4*12,B2),B2). We ran it in Google Sheets mode on the same data and it returns $15,959.86.

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=PV(B3/12,B4*12-B5,PMT(B3/12,B4*12,B2))$15,959.86Another view: the balance is the present value of the 36 payments still to come.
=B2-B5*PMT(B3/12,B4*12,-B2)$13,260.31Different resultSubtracting the payments made ignores the interest in them, so it understates the balance. Far too low: $13,260.31.

Common mistakes

  • Payments are part interest, so after 24 of 60 payments you still owe much more than 60% of the loan.
  • Extra payments make the real balance lower than this formula shows. Use a payment schedule if you've paid extra.

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 the remaining balance on a loan

In B6, calculate how much is still owed on the loan in B2:B4 after the number of monthly payments in B5.

Questions

How much principal have I paid so far?

Subtract the balance from the loan: =B2-B6, or use CUMPRINC in Excel.

How do I find the balance on a date?

Count the payments made: =DATEDIF(start_date,TODAY(),"m") in B5.

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.