The formula
Interest in payment #12ExcelGoogle Sheets
=IPMT(B1/12,B4,B2*12,-B3)In E1 → $1,608.40
Principal in payment #12ExcelGoogle Sheets
=PPMT(B1/12,B4,B2*12,-B3)In E2 → $287.81
Balance after payment #12ExcelGoogle Sheets
=FV(B1/12,B4,PMT(B1/12,B2*12,-B3),-B3)In E3 → $296,646.82
Principal paid in year 1ExcelGoogle Sheets
=CUMPRINC(B1/12,B2*12,B3,1,12,0)In E4 → -$3,353.18
✓ Tested. All 4 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% | $1,608.40 | ||
| 2 | Years | 30 | $287.81 | ||
| 3 | Loan amount | 300,000 | $296,646.82 | ||
| 4 | Payment # | 12 | -$3,353.18 |
How it works
- B4
- The payment number (1 = first month).
- IPMT / PPMT
- Same arguments as PMT plus the period number; IPMT + PPMT = PMT.
- FV(rate,n,payment,-loan)
- Balance still owed after n payments (positive here because of the signs).
- CUMPRINC(…,1,12,0)
- Principal repaid from payment 1 to 12; 0 = payments at period end. Returns a negative number.
Common errors and fixes
Balance shows negative?
Signs are mixed. Keep the payment positive and the loan negative inside FV, as shown.
Why does the formula return #NUM! in CUMPRINC?
CUMPRINC needs a positive loan amount and whole period numbers.
Does this work in Google Sheets?
Yes. The formulas on this page work the same way in Google Sheets.