Finance & Accounting
Cumulative Loan Principal & Interest Paid Between Two Periods (FV & PMT)
Calculate exact cumulative principal and cumulative interest paid between any start period s and end period e without building a 360-row amortization schedule using FV balance differences.
Compute multi-period principal & tax-deductible interest in a single cell!
To report Year-1 or Year-3 principal reduction and interest expense on a 60-month loan without generating all 60 rows, use the exact amortization balance identity! After k level payments of PMT(r, N, PV), the remaining loan balance is -FV(r, k, PMT(r, N, PV), PV). Therefore, the exact Cumulative Principal Paid from period s through period e inclusive is Balance(s - 1) - Balance(e) = FV(r, e, PMT(r, N, PV), PV) - FV(r, s - 1, PMT(r, N, PV), PV)!
Once cumulative principal paid is known, Cumulative Interest Paid over those (e - s + 1) months equals Total Cash Payments (e - s + 1) * (-PMT(r, N, PV)) minus Cumulative Principal Paid!
| Loan Window ($100k, 6% APR, 60m) | Start Balance | End Balance | Principal Paid / Interest Paid |
|---|---|---|---|
| Year 1 (Months 1–12) | $100,000.00 | $82,319.68 | $17,680.32 / $5,519.05 |
| Year 2 (Months 13–24) | $82,319.68 | $63,549.49 | $18,770.19 / $4,429.17 |
| Full Term (Months 1–60) | $100,000.00 | $0.00 | $100,000.00 / $15,996.81 |
Cumulative Principal Paid Between Period s (B5) and Period e (B6)
Computes exact positive principal paid across periods B5..B6 on loan principal B2 at annual rate B3 over B4 months.
=IF(OR(B2 <= 0, B3 <= 0, B4 <= 0, B5 < 1, B6 < B5, B6 > B4), "Invalid Period Window", ROUND(FV(B3 / 12, B6, PMT(B3 / 12, B4, B2), B2) - FV(B3 / 12, B5 - 1, PMT(B3 / 12, B4, B2), B2), 2))Cumulative Interest Paid Between Period s (B5) and Period e (B6)
Subtracts cumulative principal reduction from total level payments made over (B6 - B5 + 1) months.
=IF(OR(B2 <= 0, B3 <= 0, B4 <= 0, B5 < 1, B6 < B5, B6 > B4), "Invalid Period Window", ROUND((B6 - B5 + 1) * (-PMT(B3 / 12, B4, B2)) - (FV(B3 / 12, B6, PMT(B3 / 12, B4, B2), B2) - FV(B3 / 12, B5 - 1, PMT(B3 / 12, B4, B2), B2)), 2))Try the example
Use the original values or change them to test your understanding.