| Line item | Year 1 | Year 2 | Year 3 | Year 4 | Year 5 |
|---|---|---|---|---|---|
| EBITDA | $500,000.00 | $530,000.00 | $561,800.00 | $595,508.00 | $631,238.48 |
| Cash available for debt service | $275,000.00 | $291,500.00 | $308,990.00 | $327,529.40 | $347,181.16 |
| Cash interest and fees | $107,000.00 | $99,090.00 | $90,315.83 | $80,612.04 | $69,908.85 |
| Debt service | $221,000.00 | $225,295.00 | $229,652.91 | $234,070.72 | $826,276.24 |
| Closing revolver | $100,000.00 | $100,000.00 | $100,000.00 | $100,000.00 | $100,000.00 |
| Closing term loan | $1,086,000.00 | $959,795.00 | $820,457.91 | $666,999.23 | $498,363.08 |
| Closing PIK note | $432,000.00 | $466,560.00 | $503,884.80 | $544,195.58 | $0.00 |
| Total closing debt | $1,618,000.00 | $1,526,355.00 | $1,424,342.71 | $1,311,194.82 | $598,363.08 |
| Leverage | 3.24x | 2.88x | 2.54x | 2.20x | 0.95x |
| Interest coverage | 4.67x | 5.35x | 6.22x | 7.39x | 9.03x |
| Debt-service coverage | 1.24x | 1.29x | 1.35x | 1.40x | 0.42x |
| Roll-forward check | $0.00 | $0.00 | $0.00 | $0.00 | $0.00 |
How does the repayment waterfall work?
Opening term loan − scheduled principal − min(remaining balance, excess cash × sweep percentage)Cash interest is calculated on opening balances to avoid an undisclosed circular reference. The model pays cash interest and fees, then scheduled principal, then applies the stated percentage of residual cash to the term loan. The PIK note accrues non-cash interest and repays its balance in the selected maturity year.
Worked base case
The sample begins with $100,000.00 on the revolver, $1,200,000.00 on the term loan and $400,000.00 on the PIK note. Year 1 debt service is $221,000.00, closing debt is $1,618,000.00, and the roll-forward difference is $0.00.
How should the credit metrics be read?
Leverage divides closing debt by EBITDA. Interest coverage divides EBITDA by cash interest and fees. Debt-service coverage divides cash available for debt service by modeled interest and principal. These are model outputs, not covenant definitions; the credit agreement controls compliance testing.
What does the schedule check?
- Every tranche reconciles from opening balance through movements to closing balance.
- The revolver cannot exceed its commitment or fall below zero.
- Scheduled principal and cash sweep cannot exceed the term-loan balance.
- The PIK note repays in the selected maturity year.
Limitations and failure states
This educational schedule uses annual periods and fixed rates, calculates interest on opening balances and applies a simplified priority. It omits rate curves, SOFR floors, original issue discounts, mandatory prepayments, covenant baskets and legal definitions. Contract terms control actual debt service.
Editable workbook
Send me the debt schedule
The browser model stays free. Enter your email only if you want the editable Excel version with formulas, checks, assumptions and source notes.
Sources, version and review status
The schedule uses opening-balance-plus-movements roll-forward logic and connects to statement definitions from Beginners’ Guide to Financial Statements. Version 1.0 was last checked on 2026-08-25. Methodology review is complete; no named external expert reviewer is claimed.
Related models and formulas
Frequently asked questions
What is debt schedule?
A debt schedule rolls each borrowing tranche from opening balance through draws, repayments, interest, fees and non-cash accruals to a closing balance.
How should I use debt schedule?
Use the formula only after matching every input to the same period, unit and definition. Then compare the result with a worked example and the decision you are trying to make.
What is the most common mistake with debt schedule?
The most common mistake is calculating interest or cash sweeps from a balance that creates an undocumented circular reference.
Can I calculate debt schedule in a spreadsheet?
Yes. Reproduce the displayed equation with separate cells for each input, keep percentages as decimals, and test the spreadsheet against the worked example before replacing the sample figures.