Free Excel templates

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.

Get the book on Apple Books → Or on Google Play

Want every model at once?

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).

Get the Library, $79 →

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

MeasureValue
Total uses ($m)41
Pro forma EBITDA ($m)18.4
Leverage at close2.23x
Covenant cushion at close25.7%
EBITDA room before a breach ($m)4.7
All-in rate at close6.50%
Borrowing base at close ($m)19.25
Leverage, quarter of the breach3.17x
FCCR in the breach year1.20x
Collateral coverage64.5%
Return on allocated capital, year 119.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

  1. Enter the acquisition, facilities, pricing and covenants on Inputs.
  2. Read sources and uses, leverage, cushion and borrowing base on Deal.
  3. Enter quarter-end figures and read the covenant tests on Covenants and the FCCR on Cash Flow.
  4. 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.

See the book and its free files →

Other free templates

This template goes with The Commercial Banker. See the book.