# How to calculate total interest paid on a loan in Excel

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

**Excel:** `=PMT(B3/12,B4*12,-B2)*B4*12-B2` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B |
| --- | --- |
| Car loan | Value |
| Amount borrowed | 25000 |
| Annual interest rate | 0.065 |
| Term (years) | 5 |
| Total interest |  |

Formula in B5: `=PMT(B3/12,B4*12,-B2)*B4*12-B2`

Result: $4,349.22

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

## Other ways

- `=-PMT(B3/12,B4*12,B2)*B4*12-B2`: The same with the sign flipped on PMT instead of the amount.
- `=PMT(B3/12,B4*12,-B2)*B4*12`: Without 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)`.

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

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/calculate-total-interest-paid-on-a-loan