A free commercial loan and covenant model in Excel
A middle-market acquisition loan from the banker's side: sources and uses, leverage and covenant cushion at close, the borrowing base, quarterly leverage tests through a breach, fixed charge coverage, collateral coverage and the relationship's return on capital.
The bank finances a $41 million acquisition at 2.23x leverage, a 25.7% cushion against the covenant. Two years later leverage reaches 3.17x and fixed charge coverage falls to 1.20x: a breach. Collateral covers 64% of the senior exposure.
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.
Commercial_Loan_and_Covenant_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.
The book behind this model
This model goes with The Commercial Banker, which explains the method behind it, chapter by chapter.
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).
This free file stays free. Secure checkout by Stripe.
Not on Amazon: 24 paperbacks by Julian R. Sterling on insurance and retirement, printed on demand and shipped worldwide. Browse the bookshop →
What is in the file
7 sheets. Inputs can be changed; everything else is a formula, and the last sheet checks the figures against the book.
Inputs
Every assumption of the case (the acquisition, the facilities and pricing, covenants, operating figures, quarter-end balances, the amendment, ratings, collateral, relationship economics).
Deal
Sources and uses, leverage at close, covenant cushion, borrowing base, all-in rate and fees.
Covenants
The quarterly leverage test from Q2 2024 to Q4 2026, the covenant in force, cushion and breach flags.
Cash Flow
Operating figures FY2023 to FY2028, cash available for fixed charges, interest, fixed charge coverage by year, management and downside cases.
Collateral
Liquidation values at 31 December 2025 against the senior exposure, and the going-concern view.
Relationship
Pre-tax return on allocated capital at origination and in 2026.
Checks
The figures printed in the book beside the formulas that produce them. ALL CHECKS OK on the default inputs.
What the worked case returns
Measure
Value
Total uses ($m)
41
Pro forma EBITDA ($m)
18.4
Leverage at close
2.23x
Covenant cushion at close
25.7%
EBITDA room before a breach ($m)
4.7
All-in rate at close
6.50%
Borrowing base at close ($m)
19.25
Leverage, quarter of the breach
3.17x
FCCR in the breach year
1.20x
Collateral coverage
64.5%
Return on allocated capital, year 1
19.5%
The relationship earns 19.5% on allocated capital in its first year, 12.3% on the loan alone. After the breach the same relationship loses money, which is what a workout costs a bank before any credit loss.
How to use it on your own case
Enter the acquisition, facilities, pricing and covenants on Inputs.
Read sources and uses, leverage, cushion and borrowing base on Deal.
Enter quarter-end figures and read the covenant tests on Covenants and the FCCR on Cash Flow.
Read collateral coverage and the relationship's return on capital.
Questions people ask about it
What is a leverage covenant?
A limit on debt divided by EBITDA, tested each quarter. Breaching it gives the lender the right to act, usually leading to an amendment or waiver.
What is the fixed charge coverage ratio?
Cash available for fixed charges divided by those charges: interest, scheduled principal and other fixed obligations.
What is covenant cushion?
How far EBITDA can fall, or debt rise, before the covenant is breached, as a share of the current figure.
Is this template really free?
Yes. You enter an email address, the download opens at once, and you can leave the reader list in one click.
Go further
The free workbooks of The Commercial Banker go further: the full companion set, blank models and the worked cases of the book.
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.