Financial
Calculate cumulative loan interest and principal for any year or period range
Compute exact Year-1, Year-2, or full-term principal and interest paid from remaining FV balances—without a 360-row schedule.
Exact cumulative principal and interest via FV balance difference
Between payment StartPer (D2) and EndPer (E2), total principal repaid equals the drop in remaining balance from period StartPer - 1 to EndPer: FV(periodRate, EndPer, pmt, Loan) - FV(periodRate, StartPer - 1, pmt, Loan).
Because every payment equals Principal + Interest, cumulative interest paid over those (EndPer - StartPer + 1) payments equals Total Cash Paid minus Principal Repaid in this idealized fixed-rate model without building an amortization table.
Assume month-end payments, no fees, a constant monthly rate equal to the nominal annual rate divided by 12, and unrounded intermediate payments. Actual schedules rounded per installment can differ. Require integer periods with 1 <= StartPer <= EndPer <= TermMonths.
| Window | StartPer..EndPer | Total Cash Paid | Principal Paid | Interest Paid |
|---|---|---|---|---|
| Year 1 | 1..12 | 4639.87 | 3536.06 | 1103.81 |
| Year 2 | 13..24 | 4639.87 | 3754.16 | 885.71 |
| Full Term | 1..60 | 23199.36 | 20000.00 | 3199.36 |
Cumulative interest and principal formulas with LET
On a $20,000 loan at 6% nominal annual interest over 60 months ($386.66/mo), Year 1 pays $1,103.81 in interest and $3,536.06 in principal.
=LET(periodRate, B2/12, pmt, PMT(periodRate, C2, A2), princ, FV(periodRate, E2, pmt, A2) - FV(periodRate, D2-1, pmt, A2), ROUND(-(E2 - D2 + 1) * pmt - princ, 2))Cumulative principal repaid formula
Over the full 60 months (1..60), cumulative principal repaid equals the exact $20,000 original loan.
=LET(periodRate, B2/12, pmt, PMT(periodRate, C2, A2), ROUND(FV(periodRate, E2, pmt, A2) - FV(periodRate, D2-1, pmt, A2), 2))Try the example
Use the original values or change them to test your understanding.