Spreadsheet Practice

How to calculate total interest paid on a loan in Excel

Formula (Excel and Google Sheets)

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

Multiply the payment by the number of payments and subtract the amount borrowed: =PMT(B3/12,B4*12,-B2)*B4*12-B2.

Result on the example
$4,349.22 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

Every payment is part interest and part principal. Over the whole term, the principal parts add up to exactly the amount borrowed, so everything paid beyond that is interest: 60 payments of $489.15 is $29,349.22, minus $25,000 borrowed.

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

Result4349.222328

  1. B36.50%

    The value in B3.

  2. B3/120.005416666667

    The / operator divides.

  3. B45

    The value in B4.

  4. B4*1260

    The * operator multiplies.

  5. B2$25,000

    The value in B2.

  6. -B2-25000

    Makes the value negative.

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

    PMT: Payment per period for a loan or annuity.

    • rateB3/120.005416666667
    • nperB4*1260
    • pv-B2-25000
  8. PMT(B3/12,B4*12,-B2)*B42445.768527

    The * operator multiplies.

  9. PMT(B3/12,B4*12,-B2)*B4*1229349.22233

    The * operator multiplies.

  10. PMT(B3/12,B4*12,-B2)*B4*12-B24349.222328

    The - operator subtracts.

In Google Sheets

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

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=-PMT(B3/12,B4*12,B2)*B4*12-B2$4,349.22The same with the sign flipped on PMT instead of the amount.
=PMT(B3/12,B4*12,-B2)*B4*12$29,349.22Different resultWithout subtracting the loan you get the total of all payments. Total repaid: $29,349.22.

Common mistakes

  • Real payments are rounded to the cent, so the lender's total can differ from this by a few cents.
  • Extra or missed payments change the total. Use a payment schedule when the payments aren't all equal.
  • Excel's CUMIPMT also adds up interest, but its result is negative and it needs every argument, including the type: =-CUMIPMT(B3/12,B4*12,B2,1,B4*12,0).

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 total interest paid on a loan

In B5, calculate the total interest paid over the life of the loan in B2:B4.

Questions

How do I find the interest paid in the first year?

Use CUMIPMT for payments 1 to 12: =-CUMIPMT(B3/12,B4*12,B2,1,12,0).

How much interest is in one payment?

IPMT returns it: =-IPMT(B3/12,1,B4*12,B2) for the first payment.

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.