A lease with open market reviews pays a flat rent between reviews, and a model that grows the rent every year quietly adds income the tenant never pays.
Model the rent as a step: it stays flat between reviews and resets to the higher of the passing rent and the ERV in force at each review date, with =IF(MOD(year-1,5)=0,MAX(prior rent,ERV),prior rent). Growing the rent every year instead adds income the lease does not pay: on an illustrative 500,000 lease with ERV growth of 2.5 per cent, it overstates ten years of rent by 273,170 and the unlevered IRR by 25 basis points, 7.66 per cent against 7.41.
Worked in full in Real Estate Financial Modeling by Julian R. Sterling, with every figure reproduced in a free workbook.See the book on Amazon →
A lease with five-yearly open market reviews pays a fixed rent for five years, then moves to market. The estimated rental value can grow every year; the rent the landlord receives cannot. A model that applies the growth rate to the rent line each year is modelling an indexed lease, not this one. The error is quiet because both versions reach the same rent at each review date, so the exit value often looks identical. The difference sits in the income between reviews, and in the exit whenever the sale falls between two reviews.
| Input | Value |
|---|---|
| Passing rent and ERV today | 500,000 |
| ERV growth a year | 2.5% |
| Review cycle, years (upward only) | 5 |
| Purchase price including costs | 9,000,000 |
| Exit yield | 5.75% |
| Selling costs | 1.5% |
The price gives an initial yield of 5.56 per cent on the gross outlay. The lease runs well beyond the hold, and all figures are fictional.
1. Build an ERV line: ERV in year t = 500,000 × 1.025^(t−1). In Excel, with the year number in row 3, the starting ERV in B10 and growth in B11: =$B$10*(1+$B$11)^(C3-1) in row 5.
2. Build a review flag in row 4: 1 in years 1, 6, 11, otherwise 0. =--(MOD(C3-1,5)=0).
3. Rent line in row 7: =IF(C4=1,MAX(B7,C5),B7). On a review the rent moves to the higher of the old rent and the ERV; otherwise it carries forward. The MAX is the upward-only clause.
4. The result: 500,000 in years 1 to 5, 565,704 in years 6 to 10, 640,042 from year 11. The annual-growth version reaches 551,906 in year 5 and 624,431 in year 10.
5. Ten years of rent: 5,328,521 stepped against 5,601,691 grown annually, a difference of 273,170 that the lease never pays.
| Year | ERV | Rent, stepped | Rent, annual growth |
|---|---|---|---|
| 1 | 500,000 | 500,000 | 500,000 |
| 5 | 551,906 | 500,000 | 551,906 |
| 6, review | 565,704 | 565,704 | 565,704 |
| 9 | 609,201 | 565,704 | 609,201 |
| 10 | 624,431 | 565,704 | 624,431 |
| 11, review | 640,042 | 640,042 | 640,042 |
With a ten-year hold, the sale falls on the eve of a review, so a buyer capitalises the reviewed rent of 640,042 in both versions: the exit is 11,131,170 either way. The whole difference is the interim income, worth 172,830 in present value, and it lifts the IRR from 7.41 to 7.66 per cent.
Sell between reviews and the exit is affected too. After eight years the stepped rent of 565,704 is below the current ERV of 609,201 for two more years. A buyer values that as term and reversion: 10,514,780 at a 5.75 per cent equivalent yield. The annual-growth model capitalises a 609,201 rent the building does not have and gets 10,594,808, 80,028 too much.
| Case | Stepped | Annual growth | Overstatement |
|---|---|---|---|
| 8-year hold, 2.5% ERV growth | 7.24% | 7.54% | 30 bp |
| 10-year hold, 2.5% ERV growth | 7.41% | 7.66% | 25 bp |
| 10-year hold, 1.0% ERV growth | 6.06% | 6.17% | 11 bp |
| 10-year hold, 4.0% ERV growth | 8.75% | 9.16% | 41 bp |
The overstatement scales with growth. At 4.0 per cent it is 41 basis points, enough to move a deal across a hurdle. It also scales with the review cycle: a lease with ten-yearly reviews loses more to the shortcut than one with three-yearly reviews.
Upward-only reviews add a second asymmetry. If ERV falls, the MAX keeps the rent where it is; a growth line applied to the rent would cut it. That is a separate value, priced in what an upward-only rent review is worth.
The property model template, with flag rows, rent roll and cash flow sheets already built, is in the free workbook for this case. For how renewals and voids are blended into the same rent roll, see how to blend renewal probability into leasing assumptions.
With a review flag in row 4, ERV in row 5 and rent in row 7: =IF(C4=1,MAX(B7,C5),B7). On a review date the rent moves to the higher of the previous rent and the ERV; in other years it carries forward. In the illustration the rent steps from 500,000 to 565,704 in year 6 and to 640,042 in year 11.
It depends on ERV growth and the review cycle. On an illustrative 10-year hold with five-yearly reviews, the overstatement is 11 bp at 1.0 per cent growth, 25 bp at 2.5 per cent and 41 bp at 4.0 per cent, because the model collects in every year the growth the lease only pays at reviews.
As term and reversion: capitalise the passing rent until the next review, then the current ERV from the review onwards. Selling the illustrative building after eight years, with 565,704 passing and 609,201 of ERV two years from review, gives 10,514,780 at 5.75 per cent, against 10,594,808 if the full ERV is capitalised now.
This article is one calculation from Real Estate Financial Modeling. The book takes the same case from first principles to the decision, chapter by chapter, and every figure it prints is a live formula in the free companion workbooks.
Get the book on Amazon →Free companion files
Also on Amazon UK · Amazon Germany · Amazon France · Amazon Canada
Reading guide: real estate investing, finance and fund management → · All 1340 articles →
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 workbook stays free either way.