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.
Get the template
One Excel file, no macros, nothing locked. Free: enter your email address and the download opens at once, along with the other 129 free models.
Lender_Model.xlsx (18 KB)
You join the reader list: one email when a new free model or a new book comes out, and a few notes on using the models. Leave in one click. Already on the list? Enter the same address. How your address is used.
Need the full deal structure? The LBO Model, Pro Edition adds four debt tranches, covenants, a management ratchet and 31 checks, with no circular reference and no macros. $99 → · Watch the 60-second preview
The book behind this model
This file is one worked case from The Private Credit Investor. The book explains every assumption, every sheet and the decision the numbers support, chapter by chapter.
Eight modules on the lender's side of this exact kind of deal, an Excel workbook that checks your answers, a 160-question quiz, and the Direct Lending Model, Pro Edition included ($149). The Pro model alone is $99.
The Financial Model Library: all 130 models from this site in one download, organised by theme, plus two modelling books in DRM-free PDF (Financial Modeling from First Principles and Real Estate Financial Modeling).
LBO Model, Pro Edition: seven-year buyout model with four debt tranches, covenants, a management ratchet, 31 checks, a blank copy and an 18-page guide. $99 → · See what is inside · Watch the 5-minute demo
This free file stays free. Secure checkout by Stripe.
Not on Amazon: 11 paperbacks by Julian R. Sterling on banking, credit and payments, printed on demand and shipped worldwide. Browse the bookshop →
The base case, five years
Year 1
Year 5
Fixed charge coverage
1.70x
2.40x
Debt to EBITDA
5.69x
3.64x
Debt outstanding, closing
$132.7m
$107.2m
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. An email address opens the download.
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.
LCR and NSFR calculatorLiquidity coverage and net stable funding built line by line, with encumbrance and six defensible readings.
Risk-weighted assets (RWA) calculatorStandardised against internal-model RWA, the mortgage loan split, CET1 ratios and the year the output floor binds.
Credit scorecard modelA points scorecard built from counts: bins, weight of evidence, information value and the leaking variable.
Price waterfall analysisList price to pocket price for one account and a whole company: nine leakages, ranked and added up.
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.