A free commercial real estate underwriting model in Excel, with a complete worked acquisition
Rent roll to levered IRR for a value-add multifamily deal, five sensitivities rebuilt in full, the maximum bid solved in closed form, an industrial LOI and retrade, and a fund pipeline: nine linked sheets, no macros.
The file underwrites Larkspur Commons, a fictional 312-unit apartment complex bought for $62.4 million, or $200,000 a unit. The plan renovates 200 units at $14,000 each for a rent premium of $190 a month: a 16.3% annual return on every renovation dollar. The deal opens at a 5.59% going-in cap rate, reaches a 6.85% cap rate on price by year three, and returns a 9.62% unlevered IRR and a 14.10% levered IRR on 60% debt. The highest price that still earns the 14% target is $62.53 million: $131,700 of headroom, which is the number an investment committee should read first.
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.
CRE_Underwriting_Model.xlsx (37 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 file is one worked case from The Real Estate Acquisitions Analyst. 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).
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
Nine sheets, in the order the underwriting runs. Inputs sit on one sheet in blue on a light yellow fill; every other cell is a formula.
Inputs
Every assumption in one place: units, in-place rent, the renovation programme, rent growth, vacancy, operating costs, reserves, price, closing costs, the loan and the exit cap rate.
Larkspur_CF
Annual cash flows from closing to sale: units renovated, gross potential rent, vacancy, other income, expenses, NOI, capex, debt service and the levered cash flow.
Larkspur_Returns
Price per unit, the return on each renovation dollar, going-in and year-three cap rates, yield on cost, DSCR, debt yield, unlevered and levered IRR, equity multiple and profit.
Sensitivities
Five cases, each rebuilt as a full cash flow in its own block rather than flexed in a data table: exit cap rate up and down 50 basis points, a lower premium, late renovations and a higher price on the same loan.
MaxBid
The highest price that earns the target levered IRR, solved in closed form because every levered cash flow is linear in the price, with the proof that the IRR at that price equals the target.
Route9_CF and Route9_Prices
An industrial asset with a single tenant: probability-weighted rollover at lease expiry, then the ask, the LOI, the LOI with the roof, and the retrade.
Pipeline
A fund pipeline from 240 deals screened to three closed, and the equity deployed against the fund.
Checks
Every figure the book prints against the formula that computes it: 66 lines, all OK.
What the worked case returns
Measure
Value
Price, $ per unit
$200,000
Return on each renovation dollar
16.3%
Going-in cap rate
5.59%
Year-3 cap rate on price
6.85%
Yield on total cost
6.46%
DSCR, year 1
1.62x
Debt yield, year 1
9.31%
Unlevered IRR
9.62%
Levered IRR
14.10%
Equity multiple
1.90x
Maximum bid at a 14% levered IRR
$62.53m
Read the sensitivities against the target. Fifty basis points on the exit cap rate move the levered IRR from 14.10% to 10.91%; a $50 lower rent premium takes it to 12.77%; renovations that start a year late barely matter, at 14.03%. The deal is an exit-yield bet first and a renovation bet second, and the headroom of $131,700 over the maximum bid says the price left almost nothing for being wrong on either.
How to use it on your own deal
Overwrite the yellow cells on Inputs with your own property: units, rent, renovation cost and premium first, then price and the loan.
Read Larkspur_Returns before the IRR: DSCR and debt yield in year one tell you whether the loan is sized on the asset or on the plan.
Open Sensitivities and change the five cases to the risks of your own deal. Each block is a full cash flow, so nothing is approximated.
Set your own target IRR on MaxBid. The maximum bid is the number to carry into the LOI, not the asking price.
Keep Checks as a pattern: once you change the inputs it stops matching the worked case, which is expected.
Questions people ask about it
Is this commercial real estate underwriting model really free?
Yes. It is a companion file to a book. The file costs nothing. You enter an email address, the download opens at once, and you can leave the reader list in one click.
Does it use macros or circular references?
No macros and no circular reference. The maximum bid is solved in closed form, not by goal seek, because with a loan sized at a fixed share of price every levered cash flow is linear in the price.
How is the maximum bid calculated?
It is the present value at the target IRR of the unlevered cash flows, less what the loan contributes, divided by one plus closing costs less the loan share. The sheet shows each term and then proves the result: the levered cash flows at the maximum bid return exactly 14.00%.
Can I use it for a real acquisition?
You can reuse the structure. The properties, the fund and every figure in them are fictional, and nothing here is investment advice.
The rest of the files
This template is the companion workbook for The Real Estate Acquisitions Analyst; the companion page also carries the toolkit of working documents that goes with it. 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.
This template comes from The Real Estate Acquisitions Analyst. The book is on Apple Books and Google Play.
If this book helped, or didn’t, a rating where you bought it is worth more than it looks: it is what the next reader goes on. The template stays free either way.