# Debt Schedule A 60-month multi-tranche corporate debt register: five tranches spanning revolver, Term Loan A, Term Loan B, senior secured note, and mezzanine PIK; per-tranche monthly opening, draw, scheduled principal, cash sweep, PIK accrual, closing balance; panel summary with total debt, blended rate, cash and PIK interest, total debt service; TTM covenants with leverage, interest coverage, and DSCR pass-fail; and a dashboard with peak debt, average rate, total interest paid, average DSCR, breach counts, weighted-average maturity, and per-tranche composition. - Canonical: https://finamodel.com/templates/debt-schedule - Excel download: https://finamodel.com/templates/debt-schedule.xlsx - Category: Corporate Finance - Model type: Operating model - Difficulty: Intermediate - Audiences: CFOs & FP&A, Investment bankers, CFOs, Treasurers, Credit analysts, FP&A teams - Tags: debt schedule, debt roll-forward, covenants, dscr, leverage ## Overview 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. ## What's included - Five tranches: revolver, Term Loan A, Term Loan B, senior secured note, mezzanine PIK - Per-tranche inputs: face, draw month, maturity, cash rate, PIK rate, amortisation type, initial draw, sweep rate, commitment fee - Debt Schedule with per-tranche monthly opening, draw, scheduled principal, cash sweep, PIK accrual, closing balance and a panel-totals block - Interest Schedule with per-tranche cash interest, PIK interest, commitment fee, and a panel-totals block - Debt Summary with total debt, blended rate, cash and PIK interest, total interest expense, scheduled and sweep principal, commitment fees, total debt service, EBITDA, CFADS - Covenants with TTM leverage, interest coverage, DSCR and pass / watch / breach status against thresholds - Dashboard with peak debt, peak leverage, average rate, total interest paid, average DSCR, breach counts, weighted-average maturity - Per-tranche composition block with face, all-in rate, maturity, type label, and M60 balance - Three amortisation profiles in one schedule: bullet, linear, and revolver sweep, switched per tranche by a type code - Per-tranche inputs: face, draw month, maturity, cash rate, PIK rate, amortisation type, initial draw, sweep, commitment fee - Debt Schedule with per-tranche monthly opening, draw, scheduled principal, cash sweep, PIK accrual, closing balance - Interest Schedule with per-tranche cash interest, PIK interest, and commitment fee - Debt Summary with total debt, blended rate, cash and PIK interest, scheduled and sweep principal, commitment fees, total debt service, EBITDA and CFADS - Covenants sheet with TTM leverage, interest coverage, DSCR, and pass / watch / breach status - Per-tranche composition block with face, all-in rate, maturity, type, and M60 balance ## How the Debt Schedule Model Works: Structure, Calculations and Practical Use This debt schedule model is a 60-month corporate debt register for a single operating entity, covering five tranches from revolver to mezzanine PIK. It rolls forward balances, computes interest and fees, tests covenants, and summarises results on a dashboard. The article explains the underlying design and key operating relationships, helping you evaluate whether the template fits your analysis. ### Operating Drivers and Assumptions The model’s behaviour is driven by a set of per-tranche assumptions entered on the Assumptions sheet. For each of the five tranches, you specify face amount, draw month, maturity, annual cash interest rate, PIK rate, amortisation type, initial draw percentage, sweep percentage, and commitment fee. - Additional columns capture rate type (fixed or floating), spread, upfront or OID fee percentage, and prepayment penalty step-downs for years one to three. A separate operating baseline provides starting EBITDA, monthly growth, and a CFADS conversion factor, with optional monthly overrides for EBITDA, CFADS, and the floating-rate index curve. - Capex and starting cash inputs plus covenant thresholds complete the driver set. These inputs allow you to flex the entire debt structure, interest expense, and covenant compliance from a single location, making the template responsive to changes in borrowing terms or operating performance. ### Calculation Flow from Draw to Closing Balance The calculation flow follows a monthly roll-forward for each tranche. Opening balance equals the prior month’s closing. - Draws occur at the specified draw month (or initial draw percentage for the revolver). Scheduled principal depends on the amortisation type: bullet repays face at maturity, linear amortises evenly, sweep repays nothing until maturity (with voluntary sweeps handled separately), and mortgage uses a PMT-based formula on the remaining tenor. - Voluntary sweep applies only to sweep-type tranches and is capped by available CFADS after cash interest and scheduled principal, never drawing additional debt. PIK accrual adds to the balance based on the PIK rate from draw to maturity. Closing balance is the opening balance plus draws minus scheduled principal and sweep plus PIK accrual, floored at zero. This sequence ensures that each tranche’s balance evolves logically and that interest calculations use the contemporaneous opening balance. Calculation summary: ```text Opening balance = the prior month’s closing ``` ### Interest, Fees and Debt Service Outputs Interest and fee calculations run alongside the balance roll-forward. Cash interest for each tranche is the opening balance times the effective rate divided by twelve; the effective rate is the fixed cash rate or, for floating tranches, the index curve plus spread. - PIK interest accrues similarly using the PIK rate. Commitment fees apply only to the revolver (amortisation type three) and are charged on the unused portion of the face amount. - Upfront and OID fees amortise straight-line over the tranche’s life. Prepayment fees step down over years one to three and apply to linear and sweep tranches. The panel aggregates these into total debt, blended rate (using an average-balance denominator), cash and PIK interest, total interest expense, principal repaid, sweeps, commitment fees, prepayment fees, and total debt service. These outputs feed the Debt Summary and Covenant sheets, giving a complete picture of monthly cash obligations. ### Covenant Testing and Dashboard Summaries Covenant compliance is tested monthly using trailing twelve-month (TTM) windows for EBITDA, interest, debt service, CFADS, and capex. Four primary ratios are calculated: leverage (total debt divided by TTM EBITDA), interest coverage (TTM EBITDA divided by TTM interest), DSCR (TTM CFADS divided by TTM debt service), and FCCR (TTM EBITDA minus TTM capex, divided by TTM debt service). - Additional tests include minimum liquidity and maximum capex. Each ratio receives a traffic-light status of pass, watch, or breach, and dollar-denominated headroom rows show the cushion or shortfall against the threshold. - The Dashboard distils the results into headline metrics: peak total debt, average blended rate, total interest paid, average DSCR, breach counts, minimum leverage and DSCR headroom in dollars, weighted-average maturity, average life, a per-tranche composition block, and a debt maturity profile bucketed by year. This summarisation supports credit monitoring and scenario analysis without requiring you to review every monthly line. ## Built for capital-structure sizing When the question is "how much debt can we layer in at this EBITDA, and which covenant will bite first?", a clean per-tranche schedule is the answer. This template lets a treasurer or LBO sponsor size a senior / sub / mezz stack against EBITDA and watch the leverage, coverage, and DSCR ratios all update at once. ## Designed for one-edit responsiveness Every input - face, draw month, maturity, rate, amortisation type, sweep, commitment fee, threshold - is a per-tranche input cell. Flex one number and the per-tranche roll-forward, panel summary, covenants, and dashboard all recompute - no formula rewrites. ## Honest about bullet maturities Bullet tranches repay opening plus the final PIK accrual at the maturity month and zero elsewhere, so the DSCR row crashes that month - exactly as it should, since a balloon payment is a real refinancing risk. The breach counter and peak-leverage row both pick it up. ## Built for capital-structure sizing When the question is "how much debt can we layer in at this EBITDA, and which covenant will bite first?", a clean per-tranche schedule is the answer. This template lets a treasurer or LBO sponsor size a senior / sub / mezz stack against EBITDA and watch the leverage, coverage, and DSCR ratios all update at once. ## Designed for one-edit responsiveness Every input - face, draw month, maturity, rate, amortisation type, sweep, commitment fee, threshold - is a per-tranche input cell. Flex one number and the per-tranche roll-forward, panel summary, covenants, and dashboard all recompute - no formula rewrites. ## Honest about bullet maturities Bullet tranches repay opening plus the final PIK accrual at the maturity month and zero elsewhere, so the DSCR row crashes that month - exactly as it should, since a balloon payment is a real refinancing risk. The breach counter and peak-leverage row both pick it up. ## Workbook structure ### Cover Workbook overview, sheet legend, and tab-colour key for navigation. - Title and scope framing - Sheet-by-sheet purpose summary - Tab-colour legend ### Assumptions Every driver in one sheet: tranche panel, operating baseline, and covenant thresholds. - Five tranches with face, draw month, maturity, cash rate, PIK rate, amortisation type, initial draw, sweep, commitment fee - Monthly EBITDA at M1 with a per-month growth rate - CFADS / EBITDA conversion factor for DSCR - Three pairs of covenant thresholds: leverage, interest coverage, DSCR ### Debt Schedule Per-tranche monthly roll-forward across six rows and 60 months plus panel totals. - Opening balance rolls from prior-month closing; first month opens at zero - Draw at draw_month for term tranches; revolver draws initial_draw * face at month 1 - Scheduled principal resolves by type code: bullet at maturity, linear over (maturity - draw), zero for revolver - Cash sweep on revolver: sweep_rate * opening, capped at opening - PIK accrual: opening * PIK_rate / 12, only while loan is outstanding - Closing balance: opening + draw - scheduled - sweep + PIK, floored at zero - Panel totals block at the bottom for all six rows ### Interest Schedule Per-tranche cash interest, PIK interest, and commitment fee plus panel totals. - Cash interest = opening * cash_rate / 12 from Debt Schedule - PIK interest mirrors the PIK accrual row on Debt Schedule - Commitment fee = (face - opening) * fee_rate / 12 for revolver tranches - Panel total rows: cash interest, PIK interest, commitment fees, total interest expense ### Debt Summary Panel monthly summary on one sheet. - Total debt outstanding = panel closing balance - Blended interest rate = total interest * 12 / panel opening (well-defined at maturity) - Cash interest, PIK interest, total interest expense - Scheduled principal, cash sweep, commitment fees, total debt service - Monthly EBITDA path (M1 * (1 + growth)^t) and CFADS (EBITDA * factor) ### Covenants TTM leverage, interest coverage, and DSCR with pass-fail status. - Debt outstanding pulled from Debt Summary - TTM EBITDA, interest, debt service, CFADS - all annualised for periods < 12 months - Leverage = debt / TTM EBITDA - Interest coverage = TTM EBITDA / TTM interest - DSCR = TTM CFADS / TTM debt service - Status: On track / Watch / Breach against three pairs of user thresholds ### Dashboard Headline metrics with covenant status and per-tranche composition. - Peak total debt and month of peak - M60 closing debt - Average blended rate and total interest paid over 60 months - Total debt service over 60 months - Average DSCR with On track / Watch / Breach status - Peak leverage with status and month of peak - Leverage, coverage, and DSCR breach counters - Weighted-average maturity - Per-tranche composition: face, all-in rate, maturity, type, M60 balance ### Cover Workbook overview, sheet legend, and tab-colour key for navigation. - Title and scope framing - Sheet-by-sheet purpose summary - Tab-colour legend ### Assumptions Every driver in one sheet: tranche panel, operating baseline, and covenant thresholds. - Five tranches with face, draw month, maturity, cash rate, PIK rate, amortisation type, initial draw, sweep, commitment fee - Monthly EBITDA at M1 with a per-month growth rate - CFADS / EBITDA conversion factor for DSCR - Three pairs of covenant thresholds: leverage, interest coverage, DSCR ### Debt Schedule Per-tranche monthly roll-forward across six rows and 60 months plus panel totals. - Opening balance rolls from prior-month closing; first month opens at zero - Draw at draw_month for term tranches; revolver draws initial_draw * face at month 1 - Scheduled principal resolves by type code: bullet at maturity, linear over (maturity - draw), zero for revolver - Cash sweep on revolver: sweep_rate * opening, capped at opening - PIK accrual: opening * PIK_rate / 12, only while loan is outstanding - Closing balance: opening + draw - scheduled - sweep + PIK, floored at zero - Panel totals block at the bottom for all six rows ### Interest Schedule Per-tranche cash interest, PIK interest, and commitment fee plus panel totals. - Cash interest = opening * cash_rate / 12 from Debt Schedule - PIK interest mirrors the PIK accrual row on Debt Schedule - Commitment fee = (face - opening) * fee_rate / 12 for revolver tranches - Panel total rows: cash interest, PIK interest, commitment fees, total interest expense ### Debt Summary Panel monthly summary on one sheet. - Total debt outstanding = panel closing balance - Blended interest rate = total interest * 12 / panel opening (well-defined at maturity) - Cash interest, PIK interest, total interest expense - Scheduled principal, cash sweep, commitment fees, total debt service - Monthly EBITDA path (M1 * (1 + growth)^t) and CFADS (EBITDA * factor) ### Covenants TTM leverage, interest coverage, and DSCR with pass-fail status. - Debt outstanding pulled from Debt Summary - TTM EBITDA, interest, debt service, CFADS - all annualised for periods < 12 months - Leverage = debt / TTM EBITDA - Interest coverage = TTM EBITDA / TTM interest - DSCR = TTM CFADS / TTM debt service - Status: On track / Watch / Breach against three pairs of user thresholds ### Dashboard Headline metrics with covenant status and per-tranche composition. - Peak total debt and month of peak - M60 closing debt - Average blended rate and total interest paid over 60 months - Total debt service over 60 months - Average DSCR with On track / Watch / Breach status - Peak leverage with status and month of peak - Leverage, coverage, and DSCR breach counters - Weighted-average maturity - Per-tranche composition: face, all-in rate, maturity, type, M60 balance ## Features - **Three amortisation profiles in one schedule:** A single tranche type code (1 = bullet, 2 = linear, 3 = sweep) drives the scheduled-principal formula. Drop in any debt instrument by setting the type and the model handles the bullet at maturity, the equal-amortisation period, or the revolver sweep mechanics without rewiring formulas. - **Cash and PIK interest tracked separately:** Each tranche has independent cash and PIK rates. PIK accrues to opening balance and compounds; cash interest flows through the income-statement line. The mezzanine tranche shows both side by side for late-stage and second-lien structures. - **TTM covenant tests with annualisation for short windows:** Leverage, interest coverage, and DSCR all use a trailing 12-month window that annualises for periods under 12 months (sum * 12 / months). Status flags resolve on track / watch / breach against three user-set thresholds and feed the dashboard breach counter. ## Use cases - **LBO and refinancing debt structuring:** Sizes a multi-tranche debt stack against a target EBITDA, blends senior, sub, and PIK to optimise blended cost subject to leverage and DSCR covenants, and confirms the bullet maturity is serviceable from terminal-year CFADS. - **Quarterly credit reporting:** Treasurers and CFOs pull leverage, interest coverage, and DSCR off the Covenants sheet for lender reporting. The status flags pre-validate compliance against the credit-agreement thresholds before the certificate goes out. - **Refinancing-window stress tests:** Flex the bullet-maturity month, the cash sweep rate, the PIK rate, or the EBITDA growth assumption to test refinancing risk. The dashboard breach counter highlights which quarters fall out of compliance and which tranche drives the issue. ## Frequently asked questions ### What is a debt schedule? A debt schedule is a roll-forward of every debt instrument on the balance sheet: opening balance, new draws, scheduled principal repayments, voluntary sweeps, interest accrual (cash and PIK), and closing balance. It is the single source of truth for the debt line on the balance sheet, the interest expense line on the income statement, and the financing-activities section of the cash flow statement. ### How are the three amortisation types handled? A type code per tranche drives the scheduled-principal formula. Bullet (1) repays the full opening balance plus the final PIK accrual at the maturity month. Linear (2) amortises Face / (Maturity - Draw_Month) every month between draw and maturity. Sweep (3) is the revolver: an initial utilisation at month 1, a per-month sweep on the opening balance, and an undrawn commitment fee that earns on face minus opening. ### How does PIK interest work in this template? Each tranche has an independent PIK rate. PIK accrues each month to the opening balance and compounds (next month's opening is this month's closing). At a bullet maturity, the scheduled principal repays opening plus the final PIK accrual together. ### How are the covenants computed? Leverage is closing debt divided by TTM EBITDA. Interest coverage is TTM EBITDA divided by TTM cash plus PIK interest. DSCR is TTM CFADS (EBITDA times a conversion factor) divided by TTM debt service (cash interest plus scheduled principal plus sweep plus commitment fee). For periods under 12 months the TTM windows annualise as sum * 12 / months. ### Can I extend it beyond 60 months? Yes. The builder is parameterised by N_MONTHS - bump it and rerun. The Debt Schedule, Interest Schedule, Debt Summary, and Covenants all use a column-position trick (COLUMN() - COLUMN($B)) so the formulas adapt to a wider horizon. The dashboard MAX / AVERAGE / COUNTIF formulas pick up the longer range automatically. ## Related templates - [Bank Loan Analysis Model](https://finamodel.com/templates/bank-loan-model) - [Syndicated Loan Underwriting](https://finamodel.com/templates/syndicated-loan-model) - [3 Statement Model](https://finamodel.com/templates/3-statement-model)