A free net effective rent calculator in Excel, with a worked case
Headline rent to net effective rent in both directions, the break that decides the term certain, total occupancy cost and the price of an indexation cap: five sheets, one fictional building, no macros.
The file takes a €300/m² headline rent on a five-year term certain, with a nine-month rent-free period, a €420/m² fit-out contribution and letting fees of 12% of the first year's rent, and turns it into the €163.8/m² the owner actually banks: a 45.4% erosion against the headline. Run the same lease to a ten-year break instead and the net effective rent rises to €231.9/m², 68.1 euros a year more per square metre, 817,200 euros a year across a 12,000 m² building. Every one of those numbers is a formula you can move.
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.
Headline_to_Net_Effective_Rent.xlsx (16 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 Office Real Estate. The book explains every assumption, every sheet and the decision the numbers support, chapter by chapter.
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: 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
Five sheets, in the order the calculation runs. Blue text on a pale blue fill is an input; black type is a formula.
Read me
What the workbook does, the number that moves the answer most, and the conventions behind the colours.
2. Headline to net effective
The headline rent, the rent-free period, the fit-out, the letting fees and the term certain, netted down to the net effective rent and the erosion it represents against the headline.
3. Net effective to headline
The same arithmetic run backwards: the net effective rent you are willing to underwrite, turned into the headline rent you must achieve to deliver it, with a check that feeds the answer back through sheet 2.
4. Checks
Every figure the book prints set against what the model computes, with a verdict on each line: 22 lines, all reproduced.
5. Break, occupancy, index
Three extensions: what a five-year break costs against a ten-year term, total occupancy cost against rent, and the present-value cost of capping indexation on a five-year lease.
What the worked case shows
Measure
Value
Headline rent quoted
€300/m²
Net effective rent, five-year term certain
€163.8/m²
Erosion against the headline
45.4%
Net effective rent, ten-year term certain
€231.9/m²
Difference across a 12,000 m² building
€817,200 a year
Headline rent needed to deliver €220 net effective
€368.0/m²
Total occupancy cost (rent, service charge, taxes)
€460/m²
Capital value destroyed by a €15/m² service-charge gap
€2,506,667
Read the first four lines together. The same headline rent, on the same incentive package, produces a different net effective rent depending only on whether the tenant can walk in year five or year ten: €163.8/m² against €231.9/m², a gap of €817,200 a year across the building. That is the number a rent roll built on lease length rather than the break will get wrong, and sheet 5 prints it live alongside total occupancy cost and the cost of capping indexation.
How to use it on your own letting
Open sheet 2 and overwrite the blue cells: headline rent, rent-free period, fit-out, letting fees, capital contribution and the term certain.
Set the term certain to the break date, not the lease length, if the lease has a tenant break.
Use sheet 3 when you are underwriting to a target net effective rent and need to know the headline you must ask for.
Read sheet 5 for the break comparison, total occupancy cost against competing buildings, and the cost of an indexation cap.
Keep the Checks sheet: once you change inputs it stops matching the book, which is expected. Use its structure for your own tie-outs.
Questions people ask about it
Is this net effective rent calculator 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: every incentive is annualised over an input you set, not computed from its own result. The file recalculates in Excel, LibreOffice or Google Sheets without iterative calculation.
Why does the tenant break matter more than the lease length?
A ten-year lease with a break at year five is a five-year lease for this purpose: the fit-out, the fees and the rent-free period are all recovered, or not, inside the term certain. Amortising the incentives over the full ten years instead of the five-year term certain is the single most common overstatement in an office model, worth 68.1 euros per square metre a year on this building.
Can I use it for a real letting?
You can reuse the structure. The building, the tenant and every figure in it are fictional, and nothing here is investment advice.
The rest of the files
This template is one of the companion files for Office Real Estate: the full set adds
the yield you are actually buying, the ten-year cash flow, the refurbish-reposition-convert-or-sell comparison
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.