A free real estate DCF model in Excel: ten years of an office building, lease by lease
Expiries, voids, rent-free, fit-out, letting fees and an energy upgrade, quarter by quarter for each unit, then the exit, the present value and the IRR at the asking price: six sheets, one fictional building, no macros.
The file values a 12,000 m² office building with four tenants and one vacant floor. Each unit pays its contracted rent until its lease ends; then the fit-out is spent, the void runs, the rent-free period passes and a new lease starts at market, and the cycle repeats. Over ten years the building earns €25.75 million of net operating income and spends €13.44 million of capital: 52 cents of capital for every euro of income. Discounted at 7.75%, it is worth €32.55 million net of acquisition costs, €10.45 million below the €43.0 million asking price, and bought at the ask it returns 4.32% a year.
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.
Office_Real_Estate_DCF_Model.xlsx (41 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 Office Real Estate. 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: 10 paperbacks by Julian R. Sterling on real estate and housing, printed on demand and shipped worldwide. Browse the bookshop →
What is in the file
Six sheets. Inputs are blue text on a pale blue fill; a yellow fill marks the assumption to argue about first; everything else is a formula.
Read me
How a unit behaves through the re-letting cycle, the input to change first, and the conventions.
2. Inputs
Market rent, rental growth, fit-out cost and its inflation, void, rent-free, term certain on a new lease, letting fees, non-recoverable costs, void holding cost, the energy upgrade, discount rate, exit yield, acquisition and disposal costs and the asking price.
3. Leases
The tenancy schedule: area, passing rent and quarters to the earlier of expiry and break for each unit.
4. Cash flow
Forty quarters for each unit: contracted rent, void, rent-free, new rent, fit-out, letting fees and holding cost, summed into the building's net cash flow.
5. Value
Exit NOI and exit value, present value of ten years of cash and of the exit, value gross and net of acquisition costs, the share of value in the exit, the gap to the asking price and the IRR at the ask.
6. Checks
Every figure the book prints against the live formula: seven lines, all reproduced.
What the worked case returns
Measure
Value
Lettable area
12,000 m²
Ten-year net operating income
€25,754,151
Ten-year capital expenditure
€13,438,059
Capital as a share of income
52.2%
NOI at exit
€3,837,473
Exit value, net of disposal costs
€54,367,116
Value net of acquisition costs
€32,551,200
Share of value in the exit
74.3%
Asking price
€43,000,000
IRR at the asking price
4.32%
The gap is not a valuation error; it is the re-letting cycle. A yield on passing rent ignores that every lease ends, that the space then earns nothing for three quarters, gives away three more in rent-free and costs €420 a square metre to fit out again. Priced in full, those costs take more than half of the income, and three quarters of the remaining value sits in an exit ten years away.
How to use it on your own building
Replace the tenancy schedule on 3. Leases with your own units: area, rent and quarters to expiry or break.
Set the void on 2. Inputs first. On the delivered assumptions it is the single largest driver of value.
Check fit-out cost and rent-free against current letting evidence for your market.
Read 5. Value from the bottom: the IRR at your asking price, then the share of value sitting in the exit.
Keep 6. Checks as a pattern for your own tie-outs.
Questions people ask about it
Is this real estate DCF 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. Each unit's cycle is computed from its own row of counters, and the IRR is a plain IRR over the forty quarterly cash flows.
Why is the value so far below the asking price?
Because the asking price capitalises today's rent as if it lasted. The model spends what it costs to keep the building let: voids, rent-free, fit-out, letting fees and the energy upgrade. On this building that is 52% of income over ten years.
Can I use it for a real building?
You can reuse the structure. The building, the tenants and every figure in it are fictional, and nothing here is investment advice.
The rest of the files
This template is one of the five companion workbooks for Office Real Estate: the others cover the yield you are actually buying, headline to net effective rent, the refurbish, reposition, convert or sell decision, and an eighty-question acquisition checklist. 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.
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.