Finance & Accounting
Annual Compound Interest Schedule: Year-by-Year Opening, Interest & Ending Balance
Build a multi-year compound interest rollforward table showing Beginning Balance, Annual Interest Earned, and Ending Balance—reconciled against P * (1 + r)^n and FV.
Why treasury teams pair a year-by-year rollforward schedule with closed-form FV!
While P * (1 + r)^n jumps straight to final maturity value, auditors and tax accountants need the year-by-year interest accrual D_t = Opening_t * r so annual taxable interest income is visible in every period—and so SUM(Interest_Earned) + Initial_Principal reconciles penny-for-penny with Ending_Balance!
Deposit $50,000.00 at r = 6.0% annual yield over 4 years: Year 1 earns $3,000.00 (ending $53,000.00), Year 2 earns $3,180.00 (ending $56,180.00), Year 3 earns $3,370.80 (ending $59,550.80), and Year 4 earns $3,573.05—closing at $63,123.85 and cumulative interest $13,123.85, matching ROUND(-FV(0.06, 4, 0, 50000), 2) = $63,123.85!
| Schedule Year (Principal = $50,000, r = 6.0%) | Opening Balance ($) | Annual Interest Earned ($) | Ending Balance ($) |
|---|---|---|---|
| Year 1 | $50,000.00 | $3,000.00 | $53,000.00 |
| Year 2 | $53,000.00 | $3,180.00 | $56,180.00 |
| Year 3 | $56,180.00 | $3,370.80 | $59,550.80 |
| Year 4 (Maturity) | $59,550.80 | $3,573.05 | $63,123.85 (Total Int = $13,123.85) |
Closed-Form Ending Compound Balance via FV Cross-Check
Verifies the 4-year ending rollforward balance from initial principal G2 ($50,000), annual rate G3 (0.06), and term G4 (4 years).
=IF(OR(G2 < 0, G3 < 0, G4 < 0), "Invalid Schedule Inputs", ROUND(-FV(G3, G4, 0, G2), 2))Cumulative Compound Interest Earned Across Schedule Rows C2:C5
Sums annual interest accruals C2:C5 and matches Ending Balance D5 minus Opening Principal B2.
=ROUND(SUM(C2:C5), 2)Try the example
Use the original values or change them to test your understanding.