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.

Julian R. Sterling · from Office Real Estate

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.

Download the template

One Excel file, no macros, nothing locked. No account, no email address.

Headline_to_Net_Effective_Rent.xlsx16 KB

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. There is no sign-up, no email address and no paid version.

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.

Open the companion files →

Other free templates

LBO modelSources and uses to sponsor IRR, twelve sheets, every figure checked.
Real estate property modelRent roll, cash flow, debt and returns for one asset, empty and ready to fill.
Residual land valuationDevelopment appraisal with the four downside tests and peak debt.
DCF with terminal valuePerpetuity growth and exit multiple side by side, with the TV share of value.
Distribution waterfall modelReturn of capital, pref, catch-up and carry, three catch-up readings, European vs deal-by-deal.
Loan sizing: LTV, LTC, DSCR, debt yieldThe four lender tests at once, the binding one, and a rate shock.
Direct lending credit modelUnitranche underwriting: coverage, leverage, sweep, base, downside and stress.
Data center financial modelTurnkey, powered shell and powered land on one campus, levered returns and delay.
13-week cash flow modelLiquidity on paper against usable liquidity, a springing covenant and six levers.
Small business acquisition model (SBA loan)SBA 7(a) loan sized in closed form, lender coverage against cash coverage, exit returns.
3-statement financial modelFive-year forecast feeding a DCF and an LBO on one case, with a blank build.
Project finance modelSolar and storage project, debt sculpted to a 1.35x DSCR, no circularity.
REIT valuation modelFFO, AFFO, NAV and the implied cap rate for one REIT, plus an empty template.
Self-storage financial modelCohort rent roll, break-even occupancy, ten-year hold and the bid for one store.
Tax lien certificate yield calculatorBid, minimum charge and redemption month turned into a real annual yield.
Sale and leaseback modelNine buildings, the price split five ways, rent cover on the entity that signs.
PME calculatorKaplan-Schoar PME, PME+, Direct Alpha, IRR and TVPI on one fund's cash flows.
Working capital peg modelFive peg definitions, the completion-date effect, locked box against completion accounts.
JV waterfall and promote modelSponsor against capital partner: pref, IRR hurdles and the promote in a two-party JV.
Student housing modelA 520-bed scheme from rate card to RevPAB, costs, capex cycle and exit value.
VaR and expected shortfall modelParametric VaR and expected shortfall step by step, crisis correlations and a backtest.
Corporate liquidity modelMinimum liquidity, revolver headroom and the covenant test that takes the revolver away.
Co-investment fee savings modelWhat an LP co-investment programme saves in fees, its break-even loss rate and deal count.