Financial
Calculate loan interest and principal paid in any given year (closed-form FV)
Estimate interest and principal during a selected full 12-month loan year using PMT and FV.
Exact Year-K interest and principal in ONE cell—without a 360-row schedule!
Suppose you have a $300,000, 30-year (360-month) mortgage at 6.00% ($1,798.65/mo -> $21,583.82 total cash paid per year), and you want exact modeled interest vs principal paid in Year 1 vs Year 30.
Every amortizing loan satisfies two exact identities at Month m 0 = (K - 1) × 12 (start of Year K) and Month m 1 = K × 12 (end of Year K): (1) Principal Paid in Year K = StartingBalance(m 0) - EndingBalance(m 1), computed directly via -FV(r, m 0, pmt, Loan) - (-FV(r, m 1, pmt, Loan))!; and (2) Interest Paid in Year K = 12 × MonthlyPayment - PrincipalPaidInYearK! Watch the dramatic shift: Year 1 pays $17,899.78 interest and $3,684.04 principal, whereas Year 30 pays just $685.41 interest and $20,898.41 principal!
Assumes a fixed nominal annual rate divided by 12, month-end payments, no fees or extra payments, and an unrounded modeled installment. Contractual cent rounding can change actual totals. A loan year begins at origination, not necessarily January 1. The calculation does not determine tax deductibility.
| Loan Year ($300k, 6.0%, 360 mos) | Total Annual Payments | Principal Paid in Year | Modeled interest paid |
|---|---|---|---|
| Year 1 (Months 1–12) | $21,583.82 | $3,684.04 (17.1%) | $17,899.78 (82.9%!) |
| Year 30 (Months 349–360) | $21,583.82 | $20,898.41 (96.8%!) | $685.41 (3.2%) |
Exact Interest Paid in Year D2 (closed-form FV balance identity)
Subtracts the balance reduction from twelve modeled monthly payments. The year must be a positive integer entirely within the loan term; partial final years need a shorter payment count.
=IF(OR(COUNT(A2:D2)<>4,A2<=0,B2<0,C2<12,MOD(C2,1)<>0,D2<1,MOD(D2,1)<>0,D2*12>C2),"Invalid full loan year",LET(monthlyRate, B2 / 12, pmt, PMT(monthlyRate, C2, A2), startBalance, -FV(monthlyRate, (D2 - 1) * 12, pmt, A2), endBalance, -FV(monthlyRate, D2 * 12, pmt, A2), ROUND(12 * (-pmt) - (startBalance - endBalance), 2)))Exact Principal Paid in Year D2 (balance reduction b0 - b1)
Equals the exact balance paid down between Month (D2 - 1)*12 and Month D2*12.
=IF(OR(COUNT(A2:D2)<>4,A2<=0,B2<0,C2<12,MOD(C2,1)<>0,D2<1,MOD(D2,1)<>0,D2*12>C2),"Invalid full loan year",LET(monthlyRate, B2 / 12, pmt, PMT(monthlyRate, C2, A2), startBalance, -FV(monthlyRate, (D2 - 1) * 12, pmt, A2), endBalance, -FV(monthlyRate, D2 * 12, pmt, A2), ROUND(startBalance - endBalance, 2)))Try the example
Use the original values or change them to test your understanding.