A free loan sizing template: LTV, LTC, DSCR and debt yield in one place
A lender sizes to whichever test is tightest. This template runs all four, names the binding one, compares four lender types, then shocks the rate and ladders the maturities.
An asset with $6.4m of net operating income, appraised at $128m. At 65% loan-to-value it would support $83.2m. But the loan is $66.9m, because the debt service coverage test binds first. A lender never sizes to one metric; it sizes to the tightest of four, and which one binds changes with the market.
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.
Debt_Sizing_and_Covenants.xlsx (26 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 project or fund? The Pro Editions go further: Development (60 months, S-curves, senior and mezzanine loans, JV waterfall), $99 → · Fund (up to eight assets, European or American waterfall), $129 →
The book behind this model
This file is one worked case from Real Estate Fund Management. The book explains every assumption, every sheet and the decision the numbers support, chapter by chapter.
Eight modules on one logistics park: rent roll and NOI, cap rate and DCF, LTV, DSCR and debt yield, levered returns, a development with its construction loan, the JV waterfall and quarterly NAV. Excel exercises that check your answers, a 160-question quiz and the Real Estate Acquisition and Development Model, Pro Edition included ($149).
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).
Real Estate Fund Model, Pro Edition: a closed-end fund with up to eight assets, fees, European or American waterfall, 30 built-in checks, a blank copy and a 19-page guide. $129 → · See what is inside
REPE Interview Prep Pack: 18 real estate PE interview cases solved step by step, with three Excel models. $129 →
Real Estate Acquisition and Development Model, Pro Edition: monthly ground-up model over 60 months, S-curves, senior and mezzanine loans, bulk or unit sale, JV waterfall, 33 checks, a 14-page guide. $99 → · See what is inside
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 four tests on one asset
Test
Limit
Maximum proceeds
Loan to value
65%
$83.2m
Loan to cost
70%
$84.0m
Debt service coverage
1.25x
$66.9m
Debt yield
9.5%
$67.4m
Proceeds (binding: debt service coverage)
$66.9m
The template also prints the headroom to the next-tightest test, here $0.51m: how far the market has to move before a different test starts to drive the loan.
Run the same asset through four lender types and the answers range from $64.0m to $71.1m.
The rate shock
For a floating-rate loan, a coverage ratio that looks comfortable at closing can fall quickly. On the same loan the DSCR, after the cost of the rate cap, is 1.29x at closing and 1.02x after a 200 basis point rise.
The cash sweep is triggered at an index of 5.82%. The debt yield does not move at all, which is why lenders favour it when rates are volatile.
What is in the file
Loan Sizing
Proceeds under all four constraints, the binding test, the headroom, and the credit box of four lender types.
Rate Shock
DSCR and debt yield along a rate path, the rate cap as an economic cost, the covenant triggers and where the loan breaks.
Maturity Ladder
Maturities by year against a policy limit, floating exposure, hedge coverage and lender concentration.
Stressed Refi
A bridge loan whose refinancing proceeds fall short, the gap, and the paydown plan.
Debt Checklist
Fund-level, deal-level and portfolio-level questions.
Checks
Every figure the chapter prints against what the file computes.
How to use it
Type your NOI, value, total cost, rate and amortisation, then the lender's four limits.
Read the binding test and the headroom before the proceeds.
On Rate Shock, enter the index, the spread and the cap strike: check whether the cap actually prevents a cash sweep or only buys comfort.
Questions people ask about it
What is debt yield?
Net operating income divided by the loan amount. Unlike DSCR it ignores the interest rate and amortisation, so it measures the loan against the income alone.
Why is DSCR binding here and not LTV?
Because at this interest rate and amortisation the income supports less debt than the value would. When rates fall or values drop, the binding test changes.
Does it handle interest-only loans?
Yes: set amortisation to zero years.
Is it free?
Yes, with no macros. An email address opens the download.
The rest of the files
This template is one of the companion files for Real Estate Fund Management: the full set adds
the fund waterfall, underwriting and exit sensitivity, asset management and fund operations, and the fund definitions priced. Free, like this one.
Does an interest rate cap protect your DSCR?A cap fixes the worst rate the loan can pay. Whether that rate passes the covenant is a separate calculation, and the premium belongs in it.
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.