How to Model a Commercial Lease in Excel (Step by Step)
How to Model a Commercial Lease in Excel (Step by Step)
To model a commercial lease in Excel, set up a tenancy schedule with the key dates and terms, build a timeline with on and off flags, then calculate the rent month by month, allowing for rent reviews, rent-free periods, breaks and expiry. Let's go through each step, with example formulas.
The quick version
Start from the heads of terms or the lease, and list every date and term you need.
Use flags (1 or 0) on a timeline so each event switches on and off cleanly.
Model reviews, rent-free periods, breaks and reletting as separate steps.
Check the result with WAULT and occupancy measures.
What do you need before you start?
Lease start date and expiry date
Passing rent, and how often it is paid (in the UK, usually quarterly in advance)
Rent review dates and the review type (open market, fixed or index-linked)
Break dates and any break penalties
Rent-free periods and other incentives, such as capital contributions
Estimated rental value (ERV), void period and reletting assumptions
Service charge, business rates and other costs
How do you model it, step by step?
Build the tenancy schedule. One row per tenant, one column per term. This is your inputs area.
Set up the timeline and flags. A lease flag is 1 while the lease runs and 0 otherwise. For example: =IF(AND(Date>=LeaseStart, Date<=LeaseEnd), 1, 0)
Calculate passing rent. Monthly rent = lease flag × annual rent ÷ 12.
Add rent reviews. For an upward-only open market review, the new rent is the higher of the passing rent and the ERV at the review date. For example: =IF(Date=ReviewDate, MAX(PassingRent, ERV), PassingRent)
Add rent-free periods. A rent-free flag is 1 from the lease start for the number of months agreed. Rent received = rent × (1 − rent-free flag).
Add breaks and expiry. Decide what happens at a break or expiry: the tenant stays, or leaves and you have a void, then relet at ERV.
Add costs. Include service charge shortfalls and business rates on empty space, plus marketing and letting fees on reletting.
Create outputs and checks. Total rent per year, rent roll, WAULT and occupancy. Check that rent stops when the lease stops.
How do you model rent indexation with a cap and a collar?
Some leases link rent reviews to inflation, with a minimum increase (the collar) and a maximum (the cap). For each year, the growth applied is the inflation rate, limited by the collar and the cap: =MIN(MAX(Inflation, Collar), Cap). With a 1% collar and a 4% cap, on a £100,000 rent reviewed after five years:
Inflation each year | Growth applied | Rent after 5 years |
|---|---|---|
6.0% | 4.0% (cap) | £121,665 |
2.5% | 2.5% | £113,141 |
0.5% | 1.0% (collar) | £105,101 |
How do you work out effective rent with a rent-free period?
Effective rent spreads the rent-free period across the lease. Take a 10-year lease at £120,000 a year with 12 months rent-free. Total rent is 9 × £120,000 = £1,080,000. Divide by 10 years and the effective rent is £108,000 a year.
What is WAULT, and how do you calculate it?
WAULT is the weighted average unexpired lease term. It tells you how long the income is secure, weighted by rent. Calculate it to expiry, and to the first break.
Tenant | Rent | Years to break | Years to expiry |
|---|---|---|---|
A | £100,000 | 3 | 5 |
B | £60,000 | 2 | 2 |
C | £40,000 | 8 | 8 |
WAULT | 3.7 years | 4.7 years |
The formula is =SUMPRODUCT(rent, years)/SUM(rent). For expiry: (100,000 × 5 + 60,000 × 2 + 40,000 × 8) ÷ 200,000 = 4.7 years.
Physical or economic occupancy?
Physical occupancy is the occupied area as a share of the total area. Economic occupancy is the rent actually being paid as a share of the rent that could be paid. A building can be fully let (100% physical occupancy) but have lower economic occupancy while tenants are in rent-free periods.
How do you model a break clause?
A break clause lets the tenant (or landlord) end the lease early on a set date. You need to decide what your model assumes. Three common approaches are to assume the tenant breaks (the cautious case), assume they stay to expiry (the optimistic case), or weight the two by a probability.
For example, take a £100,000 lease with a tenant break in year 5. If the tenant leaves, assume 12 months void (£100,000 of lost rent), six months rent-free for the next tenant (£50,000) and a letting fee of 10% of the new rent (£10,000). The cost of the break is £160,000, plus business rates and service charge on the empty space. If you think there is a 50% chance the tenant leaves, the expected cost is £80,000.
If the lease has a break penalty, such as six months' rent paid to the landlord, include it in the break scenario.
How do you model tenant incentives?
Rent-free: set the rent to zero for the agreed months.
Half rent: reduce the rent by 50% for the agreed period.
Capital contribution: a one-off cash payment to the tenant, shown as a cost in the month it is paid.
Always show incentives on their own lines, so the reader can see both the headline rent and the effective rent.
Common mistakes
Typing dates inside formulas instead of using flags and inputs.
Ignoring the review mechanism, for example treating an upward-only review as if the rent could fall.
Counting a rent-free period twice, or not at all.
Using monthly timing when rent is paid quarterly in advance, which changes cash flow timing.
Not stress-testing void and reletting assumptions.
Frequently asked questions
What is a tenancy schedule?
A table listing each tenant with their rent, key dates and lease terms. It is the starting point for modelling income.
How do you model a rent review?
Set the review date and review type as inputs, then use a formula that picks the new rent at that date. For an upward-only open market review, take the higher of the passing rent and ERV.
What does WAULT tell you?
It shows how long, on average, the rent is contractually secure, weighted by how much each tenant pays.
Want to learn how to do this properly?
The How to Model a Commercial Lease course covers rent reviews, void periods, rent-free periods, breaks, incentives and WAULT, with two practice cases to test your skills.
Join the EiP Academy for all nine courses
