Debt Schedule

A debt schedule is the canonical building block for any corporate finance model with leverage: a per-tranche, per-period roll-forward of opening balance, new draws, scheduled principal repayments, voluntary cash sweeps, paid-in-kind accruals, and closing balance, plus a per-tranche cash and PIK interest schedule and a covenant compliance overlay. This template lays the full mechanic on seven sheets: an Assumptions sheet with five tranches (revolver, Term Loan A, Term Loan B, senior secured note, mezzanine PIK), each with face, draw month, maturity month, cash interest rate, PIK rate, amortisation type code, initial draw percent, monthly sweep rate, and commitment fee rate, plus a monthly EBITDA baseline with a growth rate, a CFADS / EBITDA conversion factor, and three pairs of covenant thresholds (leverage on-track / watch, interest coverage on-track / watch, DSCR on-track / watch); a Debt Schedule sheet with one block of six rows per tranche (opening, draw, scheduled principal, sweep, PIK accrual, closing) across 60 months plus a panel-totals block at the bottom; an Interest Schedule sheet with one block of three rows per tranche (cash interest, PIK interest, commitment fee) across 60 months plus a panel-totals block; a Debt Summary sheet that pulls the panel rows into a single page with total debt, blended interest rate, cash and PIK interest, total interest expense, scheduled principal, cash sweep, commitment fees, total debt service, monthly EBITDA, and monthly CFADS; a Covenants sheet that converts the monthly rows into TTM leverage, interest coverage, and DSCR with pass / watch / breach status flags against the user-set thresholds; and a Dashboard sheet with peak total debt and the month it occurs, M60 closing debt, average blended rate, total interest paid over the 60-month horizon, total debt service over the horizon, average DSCR with status, peak leverage with status and the month it occurs, three breach counters, weighted-average maturity, and a per-tranche composition block with face, all-in rate, maturity, type, and M60 balance.

The scheduled-principal formula resolves by tranche type code: bullet returns opening plus the same-period PIK accrual at the maturity month and zero elsewhere; linear returns Face / (Maturity - Draw_Month) for every month strictly after draw and at or before maturity; sweep returns zero on the scheduled line and routes the principal payment through the sweep row instead. The sweep row is non-zero only for type-3 tranches and is capped at the opening balance so the revolver cannot go negative. PIK accrues each month to the prior-month closing balance, which compounds the unpaid interest into the principal, so a bullet PIK note at 4% compounding monthly accrues 22% of face by month 60. The blended-rate denominator uses the panel opening balance (not closing) so the rate stays well-defined in the maturity month when bullets repay and the closing balance collapses to near zero.

CFOs, treasurers, credit analysts, and investment bankers use this template for capital-structure sizing in LBOs and corporate carve-outs (size the senior, sub, and mezz tranches against a target EBITDA so leverage at close lands at 3-5x and DSCR clears 1.20x), quarterly credit reporting (pull leverage, interest coverage, and DSCR directly off the Covenants sheet for the credit-agreement compliance certificate), and refinancing risk analysis (stress the bullet maturity month, the cash sweep rate, the EBITDA growth assumption, or the PIK rate and watch the breach counter and peak-leverage row update). The template is designed to feed a 3-statement or LBO model: the Total Debt row drops onto the balance sheet, the Total Interest Expense row drops onto the income statement, and the Total Draws / Total Principal / Total Sweep rows drop onto the financing-activities section of the cash flow statement.