The formula
Total interest (life of loan)ExcelGoogle Sheets
=-CUMIPMT(B1/12,B2*12,B3,1,B2*12,0)In E1 → $382,633.47
Interest in year 1 (payments 1-12)ExcelGoogle Sheets
=-CUMIPMT(B1/12,B2*12,B3,1,12,0)In E2 → $19,401.27
✓ Tested. All 2 Excel results in this example were recalculated in LibreOffice Calc 25.2 and matched the values shown.
Worked example
Paste the data below into a blank sheet starting at cell A1 (or download the cheat sheet, where every example is set up). Highlighted cells hold the formulas and show the expected results.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Annual rate | 6.5% | $382,633.47 | ||
| 2 | Years | 30 | $19,401.27 | ||
| 3 | Loan amount | 300,000 |
How it works
- B1/12, B2*12
- Monthly rate and total number of payments.
- B3
- Loan amount (must be positive in CUMIPMT).
- 1, B2*12
- First and last payment to include.
- 0
- Payments at the end of each month. The minus in front makes the result positive.
Common errors and fixes
Why does the formula return #NUM!?
Loan amount negative, or start period > end period.
Different from your lender?
Lenders may round each payment to cents or use daily interest. This is the standard monthly-compounding figure.
Does this work in Google Sheets?
Yes. The formulas on this page work the same way in Google Sheets.