A one-way sensitivity for every input, ranked, and why the ranges you choose decide the ranking.
Flex one input at a time to an adverse and a favourable value, record the IRR at each end, and sort the inputs by the width of the range: the widest bar goes on top. On a fictional office with a 7.28 per cent levered base IRR, the exit cap rate at plus or minus 50 basis points swings the IRR by 6.10 points, from 4.30 to 10.40 per cent, ahead of rental growth at 4.43 and vacancy at 3.46. The ranking is only as honest as the ranges, and mismatched ranges reverse it.
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 tornado chart answers the first question an investment committee asks of a model: which assumption is carrying the answer? It is a bar chart of one-way sensitivities, but the work is in the table behind it, and in one decision the chart cannot make for you: how far to move each input. This article builds the table, reads it, and shows how the ranking changes when the ranges are chosen carelessly.
| Input | Base |
|---|---|
| Purchase price, plus acquisition costs | 50,000,000 + 5.00% |
| Gross rent, year 1 | 3,500,000 |
| Rental growth | 2.50% |
| Vacancy and credit loss | 7.00% |
| Non-recoverable opex, growing 3.00% | 300,000 |
| Capex, each year | 150,000 |
| Exit cap rate on year-6 NOI, selling costs | 6.00%, 1.50% |
| Loan-to-value, interest-only rate | 55%, 5.50% |
Year-1 NOI is 2,955,000, a 5.91 per cent yield on the price. The loan is 27,500,000 and the equity 25,000,000 including costs. Year-6 NOI of 3,334,952 sells for 54,748,787 net, and the levered IRR is 7.28 per cent.
For each input k: IRRlow = IRR(base with k adverse), IRRhigh = IRR(base with k favourable)
Swing = IRRhigh − IRRlow; sort descending
In Excel, a one-variable data table does each input: put the two test values in a column, the IRR cell reference at the top of the next column, and use Data, What-If Analysis, Data Table with the input cell as the column input. Then plot the low and high results as a stacked bar centred on the base, longest bar first.
| Input and range | Adverse | Favourable | Swing, points |
|---|---|---|---|
| Exit cap rate, ±50 bp | 4.30% | 10.40% | 6.10 |
| Rental growth, ±1.00 point | 5.01% | 9.44% | 4.43 |
| Vacancy, ±3 points | 5.50% | 8.96% | 3.46 |
| Interest rate, ±100 bp | 6.22% | 8.34% | 2.12 |
| Capex, ±50% | 6.99% | 7.57% | 0.58 |
| Opex growth, ±1.00 point | 7.06% | 7.48% | 0.42 |
The exit cap rate dominates. Half a point either way is worth 6.10 points of IRR, because on a five-year hold with 55 per cent debt the sale proceeds are most of the equity's return. The bar is also lopsided: the favourable side adds 3.12 points while the adverse side takes 2.98, since a lower cap rate raises value by more than a higher one reduces it. Rental growth comes second at 4.43 points, and vacancy third. Interest rate, which dominates many conversations, is fourth: a full 100 basis points moves the levered IRR by 1.06 points each way on interest-only debt. Capex and opex growth barely register at the ranges chosen.
The top two bars are not additive. The exit cap at 6.50 per cent and growth at 1.50 per cent, taken separately, take 2.98 and 2.27 points off the IRR, 5.25 in all. Together they take 5.36 points, leaving 1.92 per cent. The tornado is a ranking, not a scenario; why a combined downside can be worse than the sum works through when and why.
A tornado ranks the ranges you give it. Move the exit cap by only ±25 basis points and rental growth by ±2 points, and the chart turns over: the exit cap swing shrinks to 3.05 points (5.77 to 8.82 per cent) and rental growth jumps to 8.90 points (2.63 to 11.52 per cent). Growth is now the dominant risk, not because the asset changed but because a 2-point growth miss over five years is a far more extreme event than a 25 basis point move in yields. A committee shown that chart would spend its time on the wrong assumption.
| Ranges used | Exit cap swing | Growth swing | Top bar |
|---|---|---|---|
| Exit ±50 bp, growth ±1.00 point | 6.10 | 4.43 | Exit cap |
| Exit ±25 bp, growth ±2 points | 3.05 | 8.90 | Growth |
The common mistake is to use percentage changes on every input, for example ±10 per cent of each base value. Ten per cent of a 6.00 per cent cap rate is 60 basis points, a large move; ten per cent of 2.50 per cent growth is 25 basis points, a trivial one; ten per cent of a 7.00 per cent vacancy is 0.7 points. The chart then ranks inputs by how big their base value happens to be. Ranges should be set input by input, from evidence: the dispersion of exit yields over past cycles, the range of growth in the submarket, the vacancy the building has actually experienced. The second mistake is to stop at the tornado. Its job is to choose the two axes of the two-way table, and to tell you which break-even numbers to compute, starting with the break-even exit yield.
Flex each input over an equally plausible range, record both ends, and sort by swing. On this deal the exit cap rate carries the answer at 6.10 points of IRR, and rental growth comes next at 4.43. Change the ranges carelessly and the ranking reverses, so the ranges deserve as much argument as the base case. The free workbook for this chapter builds the full page a committee reads: tornado, two-way table, scenario table and break-evens.
Build a one-variable data table for each input with its adverse and favourable values, read the IRR at each, and compute the swing. Sort descending and plot the low and high results as horizontal bars around the base. In the worked case the bars run from the exit cap rate, 6.10 points of IRR, down to opex growth at 0.42 points.
Ranges that are equally plausible for each input, set from evidence rather than a flat percentage. A flat 10 per cent move is 60 basis points on a 6.00 per cent cap rate but only 25 basis points on 2.50 per cent growth. With exit at plus or minus 25 basis points and growth at plus or minus 2 points, growth swings 8.90 points and appears to dominate.
No. Each bar holds every other input at base. In the example the exit cap at 6.50 per cent and growth at 1.50 per cent cost 2.98 and 2.27 points separately, 5.25 in total, but 5.36 points together, leaving a 1.92 per cent levered IRR. Combined cases need their own scenario.
Chapter 15 of Real Estate Financial Modeling specifies what a sensitivity section owes a committee: a tornado chart, a two-way table on the top two drivers, a scenario table and the break-even numbers. 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 453 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.