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.
Download the template
One Excel file, no macros, nothing locked. No account, no email address.
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, no sign-up, no macros.
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.
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.