Debt Schedule Model

Debt Schedule Model

When to use

Use whenever a business carries term debt that needs a real amortization schedule, or when debt service coverage needs checking against a covenant threshold — especially replacing a model that treats debt as a flat annual interest expense with no actual principal paydown mechanics or DSCR test.

What it does

Builds a standard level-payment amortization schedule for each debt tranche via a bundled calculator (computing the interest and principal split of each year's payment as the balance pays down), sums total debt service across all tranches per year, and computes debt service coverage ratio (EBITDA divided by total debt service) against a stated minimum, flagging the specific year DSCR would breach if the projected EBITDA path doesn't keep pace with scheduled debt service.

Method

1. Build a separate amortization schedule per tranche, not one blended debt number — different tranches (a term loan, a revolver) often carry different rates and amortization periods, and blending them hides which specific tranche is driving the total debt service burden.
2. Run the bundled calculator (scripts/debt_schedule.py) to get the year-by-year interest, principal, total payment, and ending balance for each tranche using standard level-payment amortization mechanics.
3. Sum total debt service across tranches per year and compute DSCR as EBITDA divided by that total — using the projected EBITDA path, not a static current-year number, since debt service coverage needs to hold up across the life of the debt, not just today.
4. Set the minimum DSCR threshold to the actual covenant level, not a generic default, so the check reflects the real constraint the business has agreed to.
5. Read the breach-year flag as an early warning, the same discipline as covenant-headroom-monitor — a DSCR breach projected in year 3 is a problem to solve well before year 3 arrives, through refinancing, extending amortization, or improving EBITDA, not something to discover when the covenant is actually tested.
6. Check DSCR against the downside scenario, not just the base-case EBITDA path — a schedule that holds comfortably under base-case assumptions but breaches under a plausible downside is a real risk worth planning for now.
7. Revisit the schedule whenever the capital structure changes — a refinancing, a new tranche, or a prepayment all change the amortization mechanics and need to flow through a rebuilt schedule, not a patched version of the old one.

Inputs

• Each debt tranche's principal, annual interest rate, and amortization period
• Projected annual EBITDA for the schedule's horizon
• Minimum DSCR covenant threshold
• Config saved as JSON matching the format documented at the top of scripts/debt_schedule.py

Output format

Full amortization schedule per tranche (interest, principal, total payment, ending balance by year); combined total debt service per year; DSCR per year against the minimum threshold; explicit flag naming any year DSCR would breach.

Example

A $5M term loan and a $1M revolver combine to roughly $1.2M in annual debt service, held constant through level-payment amortization. Against a projected EBITDA path growing from $2.2M to $3.4M, DSCR stays comfortably above the 1.2x minimum throughout, ranging from 1.83x to 2.83x. If the EBITDA path had instead been flat or declining, the calculator would name the specific year DSCR first drops below 1.2x, giving a concrete trigger point instead of a vague sense that debt service "might get tight" at some point.

Common pitfalls

• Modeling debt as a flat interest expense line instead of a real amortization schedule, missing how principal paydown actually reduces future interest.
• Checking DSCR only against the base-case EBITDA path, missing a breach that would occur under a realistic downside scenario.
• Waiting to check DSCR until the covenant is actually tested, instead of flagging the breach year years in advance while there's still time to act.