Articles

How do you build a scenario analysis for a property IRR?

Four columns, six rows and one switch cell: how to set the scenarios up in Excel, and why the base case is not the expected case.

Julian R. Sterling · from Real Estate Financial Modeling

Build a property scenario analysis by giving each scenario its own column of inputs, moving rental growth, vacancy, costs, capex, exit yield and the interest rate together, and pointing the model at one column with a single CHOOSE switch. On an illustrative 20,000,000 office at 55 per cent loan to value, the levered IRR runs from −5.56 per cent in a severe case to 11.65 per cent in an upside, around a base case of 8.37 per cent. Weighted by probability the answer is 6.25 per cent: the base case is not the expected case.

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 →

The asset and the scenario inputs

The asset is an illustrative multi-let office bought for 20,000,000 plus 6.8 per cent purchaser's costs, 21,360,000 in total, with an estimated rental value of 1,300,000. It is held for ten years and sold at the exit yield on year-11 net operating income, less 2.0 per cent selling costs. The loan is 11,000,000, 55 per cent of price, interest only on a floating rate, with a 1.0 per cent fee; equity is 10,470,000.

Six inputs, four scenarios (illustrative).

Input	Severe	Downside	Base	Upside
Rental growth, per year	0.0%	1.0%	2.0%	3.0%
Vacancy allowance	12.0%	8.0%	5.0%	3.0%
Non-recoverable costs, % of income	6.0%	5.0%	4.0%	4.0%
Capex in year 5	600,000	300,000	0	0
Exit yield	7.25%	6.50%	6.00%	5.75%
Average all-in interest rate	6.50%	5.75%	5.00%	4.50%
Probability	10%	25%	45%	20%

The columns are internally consistent worlds, not independent shocks: a market where rents stall is also one where voids lengthen and buyers demand higher yields. That coherence is what separates a scenario from a sensitivity, which moves one input and holds the rest.

Setting it up in Excel

The rule that keeps the model honest: the cash flow sheet only ever reads the live column, and the scenario block is the only place where scenario numbers are typed. A model with downside numbers pasted directly into the cash flow has no base case any more.

The result

Outputs by scenario.

Output	Severe	Downside	Base	Upside
Year-1 NOI	1,075,360	1,136,200	1,185,600	1,210,560
Net sale proceeds, year 10	14,535,901	18,922,619	23,605,583	27,727,888
Unlevered IRR	1.81%	4.47%	6.78%	8.39%
Levered IRR	−5.56%	2.86%	8.37%	11.65%
Equity multiple	0.62x	1.26x	1.92x	2.45x
Minimum interest cover	1.50x	1.80x	2.16x	2.45x

The spread is asymmetric. The upside adds 3.28 points of levered IRR to the base; the downside takes away 5.51 and the severe case 13.93. Leverage does most of that: unlevered, the severe case still returns 1.81 per cent, but equity gets back only 0.62 of each unit invested because the 11,000,000 loan is repaid in full from a 14,535,901 sale. Interest cover stays at 1.50 times or more in every scenario, so the risk here is to value at exit, not to income.

What the probabilities add

With weights of 10, 25, 45 and 20 per cent, the scenarios give an expected outcome that a committee can set against the base case.

Base case against expected outcome.

Measure	Levered	Unlevered
Base case IRR	8.37%	6.78%
Probability-weighted average of IRRs	6.25%	6.03%
IRR of probability-weighted cash flows	6.95%	6.16%

Both expected measures sit well below the base case, by 2.11 points on the simple average, because the downside columns are further from the base than the upside column is and carry more probability, 35 against 20 per cent. The expected equity multiple is 1.73x against 1.92x in the base.

A base case that is also the most likely single scenario can still overstate the expected return. If the distribution is skewed, say so on the summary page rather than letting the base case IRR stand alone.

The common mistakes

• Averaging IRRs and calling it the expected IRR. The weighted average of the four IRRs, 6.25 per cent, is not the return on the expected cash flows, 6.95 per cent. A deeply negative IRR in a small-probability case drags the average harder than its cash flows warrant. Show the equity multiple too.
• Building scenarios as single-input flexes. Moving the six downside inputs one at a time costs 5.12 points of levered IRR in total; moving them together costs 5.51, because the shocks compound through the exit value and the debt. The interaction is examined in whether a combined downside is worse than the sum.
• Three symmetric columns. Base, plus and minus one notch hides the tail. The severe column is the one that tells the committee whether the equity can be lost.

Takeaway

Give each scenario its own column, drive the model through one switch cell, and run all four through a data table. Here that shows a levered IRR of −5.56 to 11.65 per cent around 8.37, and an expected outcome nearer 6.25 to 6.95 per cent. The book's rebuild of the Meridian House sensitivity page, with its scenario table, tornado and break-evens, is in the free workbook for this case; for the one-input view of the same risk, see how to build a tornado chart.

Questions readers ask

What is the difference between a scenario analysis and a sensitivity analysis?

A sensitivity moves one input and holds the rest; a scenario moves several inputs together because they tend to move together in the same world. In the illustrative office the downside scenario cuts levered IRR by 5.51 points, while the six inputs moved one at a time cut it by 5.12 points in total.

How many scenarios should a property model have?

Enough to show the shape of the risk: usually base, upside, downside and a severe case. Three symmetric cases hide the asymmetry that leverage creates. Here the upside adds 3.28 points of levered IRR to the base, while the severe case takes away 13.93, which only a fourth column shows.

Should you average the scenario IRRs?

Only as a rough guide. Averaging IRRs weighted by probability gives 6.25 per cent here, but the IRR of the probability-weighted cash flows is 6.95 per cent, because a deeply negative IRR in the severe case pulls the simple average down more than its cash flows do. Report the expected equity multiple, 1.73x, alongside either.

Also on this site

• How do you build a tornado chart for a property IRR?Flex each input alone and sort by IRR swing. Here the exit cap at +/-50 bp swings a 7.28% levered IRR by 6.10 points, ahead of rental growth at 4.43.
• How do you build a two-way data table for property IRR in Excel?Point the corner cell at the IRR, set row and column input cells, run What-If Analysis. Here the levered IRR runs from 0.29% to 14.17% around a 7.48% base.
• Is a combined downside worse than the sum of the individual moves?On value, no: the two moves together cost 5,906,035 against a sum of 6,220,557, so the combination is 314,523 less bad. On a levered return, yes.
• Direct capitalisation vs DCF: why do the property values differ?They agree only if cap rate = discount rate less growth on stable income: 18,181,818 both ways here. One lease event and a softer exit take the DCF to 16,792,845.
• Equity first or pro rata funding: which gives a higher IRR?Pro rata gives the higher IRR, equity first the higher profit: 33.7% against 24.9%, but 3,479,443 of profit against 3,886,625 on the same 8,400,000 of equity.
• Should the exit cap rate apply to forward or trailing NOI?Forward NOI, the year after the sale, as at entry. On a 5-year hold with 3% growth, capping trailing NOI cuts exit value by 3.0% and the IRR by 53 bp.

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.