Build a Debt Schedule & Covenants Model
This guide shows how to create a detailed debt schedule in Model Reef and monitor covenant-style metrics such as leverage and coverage ratios. Model Reef does not enforce covenants, but you can build drivers and charts that replicate lender covenant tests.
Before you start
You should have:
A model with an operational forecast.
At least one planned or existing loan.
Basic familiarity with how Liability variables work.
If you are new to debt modelling in Model Reef, start with Build a Capital Structure Model first.
What you will build
One or more loan variables with:
Principal.
Interest.
Amortisation.
Bullets if required.
A view of the debt balance and cash interest over time.
Custom metrics approximating common covenants:
Leverage ratio.
Interest coverage.
Debt service coverage.
Create detailed loan variables
For each loan or facility:
Create a Liability variable named for the instrument, for example:
Debt - Term Loan ADebt - Revolver
Configure:
Principal amount or drawn amount.
Interest rate:
Fixed.
Or linked to a driver (for example base rate plus margin).
Payment frequency.
Amortisation profile:
Level principal.
Custom schedule using an amortisation driver.
Interest only until a bullet repayment.
Set drawdown timing:
At model start if already in place.
At a future period for new debt.
Check the preview to confirm principal and interest patterns.
View the debt schedule implicitly
Model Reef does not show a separate Excel-style schedule, but the schedule is represented in:
The Balance Sheet:
Loan balance each period.
The P&L:
Interest Expense each period.
The Cashflow Statement:
Debt Drawdowns and Debt Repayments.
The Cash Waterfall:
Interest Paid.
Debt Movements.
Use these together to understand the implied schedule.
If you want an explicit schedule view, you can export loan time series to CSV and review them in a tabular tool.
Build coverage and leverage metrics
Covenant-style metrics are built using formulas in charts or derived series.
Common metrics:
Leverage Net debt divided by EBITDA.
Interest coverage EBITDA divided by cash interest paid.
Debt service coverage Cash available for debt service divided by interest plus principal repayments.
To create these:
Build custom chart series referencing:
Debt variables for principal and interest.
Cash balance from the Balance Sheet.
EBITDA from P&L or Cash Waterfall.
For example:
NetDebt = TotalDebt minus CashLeverage = NetDebt divided by EBITDA
Add these series to a Debt and Covenants dashboard for easy monitoring.
Compare against target covenant levels
If lenders have specific thresholds, for example:
Maximum leverage 3.0 times.
Minimum interest coverage 2.0 times.
You can:
Add horizontal reference lines in charts by creating constant drivers:
Covenant - Max LeverageCovenant - Min Interest Coverage
Plot both the actual metric and covenant threshold.
This visually indicates where the model breaches or approaches limits.
Use scenarios for covenant testing
To test resilience:
Create separate models for different operating scenarios, for example:
Base case
Downside case
Stress case
In each:
Modify revenue, margin and capex assumptions to reflect stress.
Leave loan terms unchanged to reflect real world obligations.
In each scenario model:
Review leverage and coverage metrics.
Identify periods where implicit covenants may be breached.
This gives a structured way to test covenant compliance under different conditions.
Check your work
Loan variables correctly represent drawdowns, repayments and interest.
Balance Sheet loan balances match expectations over time.
Coverage and leverage metrics are calculated using consistent definitions.
Stress scenario models show how robust the structure is to downside conditions.
Troubleshooting
Related articles
Last updated