How to calculate the total interest paid on a loan
✓ Verified in LibreOffice 25.8.7.3The eye-opening number: everything you'll pay the bank beyond the principal.
The formula
| App | Formula | Notes |
|---|---|---|
| Excel | =-PMT(A2/12,B2*12,C2)*B2*12-C2 | A2=annual rate, B2=years, C2=principal: payment x number of payments, minus what you borrowed. |
| Google Sheets | =-PMT(A2/12,B2*12,C2)*B2*12-C2 | Identical. |
| LibreOffice Calc | =-PMT(A2/12,B2*12,C2)*B2*12-C2 | Identical. |
How it works
Every payment is the same, so lifetime cost is simply payment × payment-count; subtracting the principal leaves pure interest. A $300,000 mortgage at 6% for 30 years: $1,798.65 × 360 − $300,000 ≈ $347,515 — more than the house. The formula's real use is comparing scenarios: rerun it at 15 years ($155,683) or at 5.5% to see what rate shopping and shorter terms are actually worth. CUMIPMT(rate,periods,principal,1,periods,0) gives the same total with more ceremony.
Verified, not just documented
We ran =ROUND(-PMT(A2/12,B2*12,C2)*B2*12-C2,2) in LibreOffice 25.8.7.3 (headless, with forced recalculation) and it returned 347514.57 — exactly the expected result. Every formula here is confirmed by actually executing it.