How do you run a Monte Carlo simulation on a property IRR?

Q: How many Monte Carlo iterations does a property model need?

Enough that the standard error of the mean, the standard deviation divided by the square root of the number of paths, is small against the decisions being made. In the illustrative model, on a second random seed, 100 paths give a mean of 7.64 per cent with a 0.34-point standard error; 10,000 paths give 7.12 per cent with 0.04. Percentiles in the tails need more paths than the mean.

Q: How do you correlate two inputs in an Excel Monte Carlo?

Draw two independent standard normals with NORM.S.INV(RAND()), keep the first for rental growth and build the second as rho times the first plus the square root of one minus rho squared times a new draw. With rho of -0.5 between growth and exit yield, the illustrative 5th percentile IRR falls from 1.97 to 1.02 per cent.

Q: Why is the mean simulated IRR below the base case IRR?

Because the IRR is not a linear function of its inputs, the average of the outcomes is not the outcome at the average inputs. The effects do not all point the same way: exit value is convex in the yield, so a yield 0.50 point lower gains more than the same move higher costs, which on its own nudges the mean up. The growth draw pulls harder the other way, because compounding and leverage make the IRR fall more for bad growth than it rises for good. Net, 7.11 against 7.25 per cent here.

Distributions for three inputs, a correlation between two of them, and what the simulated IRR tells a committee that a single base case cannot.

Julian R. Sterling · from Real Estate Financial Modeling

To run a Monte Carlo simulation on a property IRR, replace each uncertain input with a random draw from a distribution, recalculate the cash flow for every draw, and read the result as a distribution rather than a single number. On an illustrative 30,000,000 logistics asset at 50 per cent loan to value, 10,000 draws of rental growth, exit yield and vacancy give a mean levered IRR of 7.11 per cent, a 5th percentile of 1.02 per cent and a 58.2 per cent probability of missing an 8 per cent target. The base case, at 7.25 per cent, shows none of that.

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 distributions

The asset is an illustrative single-let logistics warehouse bought for 30,000,000 plus 6.8 per cent purchaser's costs, 32,040,000 in total, with year-1 net operating income of 1,650,000 before vacancy. It is held ten years and sold at the exit yield on year-11 income, less 2.0 per cent costs. The loan is 15,000,000 at a fixed 5.0 per cent, interest only; equity is 17,040,000.

Uncertain inputs and their distributions (illustrative).
Input	Distribution	Centre	Spread
Rental growth, per year over the hold	Normal	2.0%	1.5% sd
Exit yield	Normal	5.25%	0.50% sd
Vacancy allowance	Triangular, 0% to 10.0%	3.0% mode	4.33% mean
Correlation, growth with exit yield	n/a	−0.5	n/a

The correlation is the input most models leave out. Markets where rents grow strongly are usually markets where buyers accept lower yields, so a high growth draw should tend to come with a low yield draw, and the reverse.

Building it in Excel

Two normal draws: z1 in C20: =NORM.S.INV(RAND()); z2 in C21: =-0.5*C20+SQRT(1-0.5^2)*NORM.S.INV(RAND())

Inputs: growth =2%+1.5%*C20; exit yield =5.25%+0.5%*C21; vacancy from the inverse of the triangular distribution on a third RAND()

Repetition: a one-variable data table with 1 to 10,000 down the side, a blank cell as the column input and the levered IRR at the top; every row forces a recalculation and records one path

Summary: =AVERAGE(), =PERCENTILE.INC(range,0.05) and =COUNTIF(range,"<8%")/10000

Set calculation to automatic except data tables while building, then press F9 once to run. The live model is untouched: the random cells feed the same assumption cells the base case uses, through a switch that is off by default.

The result

Levered IRR over 10,000 paths, with and without the correlation.
Statistic	Correlated (−0.5)	Independent
Mean	7.11%	7.16%
Median	7.29%	7.33%
Standard deviation	3.59%	3.02%
5th percentile	1.02%	1.97%
95th percentile	12.68%	11.81%
Probability of a negative IRR	3.5%	1.6%
Probability below the 8% target	58.2%	59.7%

The deterministic model at the mean inputs returns 7.25 per cent, which is close to the simulated mean of 7.11. What the simulation adds is the shape: one path in twenty returns less than 1.02 per cent, and more than half miss an 8 per cent hurdle. The correlation barely moves the centre but widens the spread, from a 3.02 to a 3.59-point standard deviation, and more than doubles the estimated chance of losing money, from 1.6 to 3.5 per cent.

Where the 10,000 correlated paths land.
Levered IRR	Share of paths
below 0%	3.5%
0% to 4%	14.7%
4% to 8%	40.1%
8% to 12%	34.4%
12% to 16%	7.1%
16% and above	0.2%

How many paths?

The standard error of the mean is the standard deviation divided by the square root of the number of paths. With a different random seed, 100 paths give a mean of 7.64 per cent with a standard error of 0.34 of a point; 1,000 paths give 7.12 per cent with 0.11; 10,000 give 7.12 per cent with 0.04. A hundred paths are enough to sketch the distribution and not enough to quote it. The tail percentiles converge more slowly than the mean, so if the committee's question is the 5th percentile, run the larger number.

The common mistakes

Drawing each input independently. Here independence understates the 5th percentile loss by almost a full point, 1.97 against 1.02 per cent, and halves the probability of a negative IRR.
Using the modal value as the base case. The vacancy distribution is skewed: its mode is 3.0 per cent but its mean is 4.33. A base case at the mode returns 7.57 per cent, 0.32 of a point above the deterministic result at the means, before any simulation.
Reading the base case as the mean. The IRR is not linear in its inputs, so the average outcome, 7.11 per cent, is not the outcome at the average inputs, 7.25. Exit value is convex in the yield, which on its own lifts the mean slightly; the growth draw, compounded and levered, pulls it down by more. Only the simulation says which effect wins.
Precision theatre. The output is only as good as the spreads typed in. A 0.50-point standard deviation on the exit yield is a judgement, and the result should be presented with it.

Takeaway

Put distributions on the two or three inputs that matter, correlate growth and yield, run 10,000 paths and report the mean, the 5th percentile and the probability of missing the target. Here that is 7.11 per cent, 1.02 per cent and 58.2 per cent, against a base case of 7.25. The property model template and the book's sensitivity workbook, into which a simulation block like this plugs, are in the free workbooks for this book; to find which inputs deserve a distribution in the first place, start with a tornado chart, and for the single most important threshold, the break-even exit yield.

Questions readers ask

How many Monte Carlo iterations does a property model need?

Enough that the standard error of the mean, the standard deviation divided by the square root of the number of paths, is small against the decisions being made. In the illustrative model, on a second random seed, 100 paths give a mean of 7.64 per cent with a 0.34-point standard error; 10,000 paths give 7.12 per cent with 0.04. Percentiles in the tails need more paths than the mean.

How do you correlate two inputs in an Excel Monte Carlo?

Draw two independent standard normals with NORM.S.INV(RAND()), keep the first for rental growth and build the second as rho times the first plus the square root of one minus rho squared times a new draw. With rho of -0.5 between growth and exit yield, the illustrative 5th percentile IRR falls from 1.97 to 1.02 per cent.

Why is the mean simulated IRR below the base case IRR?

Because the IRR is not a linear function of its inputs, the average of the outcomes is not the outcome at the average inputs. The effects do not all point the same way: exit value is convex in the yield, so a yield 0.50 point lower gains more than the same move higher costs, which on its own nudges the mean up. The growth draw pulls harder the other way, because compounding and leverage make the IRR fall more for bad growth than it rises for good. Net, 7.11 against 7.25 per cent here.

Read the whole case

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

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.
How do you calculate the break-even exit yield on a property?Goal Seek the IRR to the target by changing the exit yield. Here the 7 per cent break-even is 6.71 per cent, 21 bp of headroom, not the 10.25 money-back yield.
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.