A free WAULT calculator in Excel, to expiry and to break
WAULT by rent and by area, to lease expiry and to the first tenant break, with vacant space at its ERV and the share of rent that can end within one to ten years: a rent roll of up to twenty leases, dates in, years out, no macros.
WAULT, the weighted average unexpired lease term, is each lease’s years to expiry weighted by its rent. Measured to the first tenant break instead, it says when the income can actually stop. On an illustrative six-lease office with 1,778,000 of rent, the file returns 6.39 years to expiry and 3.82 years to break: two break clauses take 40 per cent off the headline term. Weighted by area, the figures are 6.17 and 3.77; counting the vacant unit at its ERV and zero years, 5.89 and 3.53. And 90.6% of the rent can end within five years if the tenants break. You type dates; the file does the years.
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.
WAULT_Calculator.xlsx (15 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 Asset Manager. 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).
This free file stays free. Secure checkout by Stripe.
Not on Amazon: 36 paperbacks by Julian R. Sterling on how to read the documents, printed on demand and shipped worldwide. Browse the bookshop →
The formulas
WAULT to expiry = Σ(rent × years to expiry) / Σ rent
WAULT to break = Σ(rent × years to first break, or to expiry if there is no break) / Σ rent
What is in the file
4 sheets. Blue type on a pale blue fill is an input; black type is a formula.
Read Me
What the file does, the worked case, how to enter your own rent roll, and its limits.
Rent Roll
The valuation date, then up to twenty leases: area, passing rent, expiry date and first break date in blue. Years to expiry and to break are formulas on a 30/360 basis; a lease without a break uses its expiry. Vacant space and its ERV go at the foot.
Results
WAULT to expiry and to break, rent-weighted, area-weighted and with the vacancy counted, the gap between expiry and break, and the share of rent that can end within 1, 2, 3, 5, 7 and 10 years on each basis.
Checks
Five identities that hold for any rent roll, from “no break later than its expiry” to “counting the vacancy shortens the WAULT”, then the worked case. The file ends ALL OK.
What the worked case returns
Measure
Value
WAULT to expiry, rent-weighted
6.39 years
WAULT to break, rent-weighted
3.82 years
WAULT to expiry, area-weighted
6.17 years
WAULT to break, area-weighted
3.77 years
With the vacant unit at ERV, to expiry / to break
5.89 / 3.53 years
Rent ending within 3 years, at expiry
16.5%
Rent ending within 5 years, at expiry / at break
39.3% / 90.6%
The two averages are 2.56 years apart because the two leases with breaks carry half the rent: the professional services firm on unit 1 can leave after 3.5 years of an 8.5-year lease, the software company after 5 of 10. The average to expiry looks like a long-let building; the expiry profile at break says 90.6% of the income can stop within five years. Read both, and read the profile behind them, before putting a WAULT in a loan covenant or a sale brochure.
How to use it on your own figures
On Rent Roll, type the valuation date, then each lease’s area, passing rent and expiry date over the blue cells. Add the first break date where there is one; leave it blank where there is none.
Use contracted rent as passing rent. If a lease is in a rent-free period, decide whether you want the rent it pays or the rent it will pay, and use the same basis for every lease.
Put vacant space at the foot with its ERV. It stays out of the standard WAULT and comes into the third measure at zero years.
Read Results. For a lender, the break figure and the profile are the ones that matter; for a valuer, both.
Keep the Checks sheet. It ends ALL OK on any rent roll except the worked-case rows, which stop matching once you enter your own leases.
Questions people ask about it
What is WAULT in real estate?
The weighted average unexpired lease term: the average number of years left on a building’s leases, weighted by the rent each lease pays. It tells a lender, valuer or buyer how long the contracted income lasts on average.
How do you calculate WAULT to break?
Use each lease’s years to its first tenant break, or to expiry where there is no break, and weight by rent. On the worked office it is 3.82 years against 6.39 to expiry. It is often written WAULB.
Should WAULT be weighted by rent or by area?
By rent, normally, because it measures how long the income lasts. Area weighting gives a different figure, 6.17 years here against 6.39, and is used for occupancy questions. Always state the basis.
Is this WAULT calculator really free?
Yes. It is a companion file to a book. You enter an email address, the download opens at once, and you can leave the reader list in one click. It has no macros and no circular reference.
Where do the figures come from?
From an illustrative rent roll in the book: a fictional multi-let office of 8,100 m² with six leases and one vacant unit. Nothing here is investment advice.
The rest of the files
This template sits alongside the companion files for The Real Estate Asset Manager: the workbooks behind every chapter, the worked cases and the checklists. Free, like this one.
How do you measure tenant concentration in a rent roll?Square each tenant's share of rent and add them up: 2,188 here, 4.6 effective tenants. Same expected loss, but a 10.2% chance of losing more than half a year's rent.
How do you calculate the EPRA vacancy rate?Divide the ERV of vacant space by the ERV of the whole completed portfolio: 740,000 of 2,400,000 gives 30.8 per cent, against 35.1 per cent by floor area.
How do you calculate a lease surrender premium?Compare keeping the tenant to expiry with re-letting now: on 2,400,000 of rent left, the landlord breaks even at a premium of 467,068, about 9.3 months' rent.
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.
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.