Revenue to free cash flow available for debt service, the debt schedule with amortisation and a cash sweep, fixed charge coverage, leverage, and a base, downside and stress case.
A unitranche lender puts $137.5 million, 6.25 times EBITDA, into a manufacturer with a stable 22% margin. In the base case fixed charge coverage is 1.70x in year one and leverage falls from 5.69x to 3.64x by year five. That is not the number that decides the loan. The downside is, and this model is built so that the downside is a coherent scenario rather than a haircut.
Download the template
One Excel file, no macros, nothing locked. No account, no email address.
Fixed charge coverage is EBITDA less capital expenditure and cash taxes, divided by cash interest plus scheduled amortisation.
Covenants are set at 9.5x leverage and 1.10x coverage. 50% of the excess cash flow sweeps to the lender each year.
The three cases
Case
Year-1 revenue
EBITDA margin
Base
6%
22%
Downside
-12%
17%
Stress
-20%
15%
Type Base, Downside or Stress on the Assumptions sheet and the whole model runs that case. Run each in turn, note its year-one coverage and leverage on the Cases sheet, and it sets the three side by side against both covenants:
whether the loan survives an ordinary bad year, and whether it survives several things going wrong at once. That second answer is what an investment committee should be shown.
What is in the file
1. Assumptions
The business, the structure (leverage, coupon, amortisation, sweep, revolver, opening cash), the covenants and the three cases.
2. Model
Five years from revenue to free cash flow before debt service, the debt schedule, coverage, leverage and cash.
3. Cases
Base, downside and stress against the covenants, with the headroom, and a check that the unseasoned part of the business is stressed separately.
4. Checks
The chapter's published ratios against what the file computes.
How to use it on your own credit
Scale revenue to the borrower and set the margin, growth, capex, tax and working capital.
Set the structure: leverage, coupon, amortisation, sweep.
Build the downside before you look at the base case: volume and margin together, sized to what the business has actually lived through.
Questions people ask about it
What is a unitranche?
A single senior loan that replaces a senior and a junior tranche, at one blended price. It is the standard product of direct lending funds.
Why does working capital matter?
A growing company can raise EBITDA and still consume the cash in receivables and inventory. The model takes a share of each revenue increase as a cash outflow.
Two inputs are marked calibrated. Why?
The book publishes the two coverage ratios without the model behind them, so the coupon and capex are set to reproduce them. The read-me says so.
Is it free?
Yes, no sign-up.
The rest of the files
This template is one of the companion files for The Private Credit Investor: the full set adds
the fund economics workbook, the underwriting and diligence checklist and the interview and memo preparation file. Free, like this one.
If this book helped — or didn’t — a few lines on Amazon are worth more than they look: they are what the next reader goes on. Write a review. The template stays free either way.