Key Takeaways
- Three distributions cover most variables: Normal (symmetric, like rent growth), Triangular (bounded, like cap rates), Uniform (equal likelihood).
- Excel Monte Carlo uses RAND(), NORM.INV(), and Data Tables; Python uses NumPy for faster, more robust simulations.
- Key outputs: mean, median, percentile bounds, and probability of exceeding target thresholds.
- Monte Carlo provides probability-of-achievement metrics that scenario analysis cannot—e.g., 68% probability of 15%+ IRR.
This lesson moves from Monte Carlo theory to implementation. You will learn to define probability distributions for key assumptions, build a simulation engine in a spreadsheet or Python, run 1,000+ iterations, and interpret the output to make risk-informed investment decisions. The implementation uses the 20-unit example as a demonstration vehicle.
Defining Input Probability Distributions
Each uncertain variable needs a probability distribution that reflects your belief about its range and most likely value. Three distribution types cover most real estate variables. Normal Distribution: use for variables that cluster symmetrically around a mean (rent growth, general inflation). Specify mean and standard deviation. Example: rent growth ~ Normal(2.5%, 1.5%). Triangular Distribution: use for variables with a defined floor, ceiling, and most likely value (exit cap rate, vacancy). Specify min, mode, max. Example: exit cap rate ~ Triangular(6.0%, 7.25%, 9.0%). Uniform Distribution: use when all values in a range are equally likely (renovation cost per unit, months to lease-up). Specify min and max. Example: renovation cost ~ Uniform($10,000, $18,000/unit). Calibrate distributions using historical data, market research, and expert judgment.
| Variable | Distribution | Parameters | Rationale |
|---|---|---|---|
| Rent Growth | Normal | Mean=2.5%, SD=1.5% | Historical submarket data shows 2-3% average with occasional swings |
| Exit Cap Rate | Triangular | Min=6.0%, Mode=7.25%, Max=9.0% | Cap rates have a floor (strong markets) but can expand significantly |
| Vacancy Rate | Triangular | Min=3%, Mode=5%, Max=12% | Vacancy cannot go below 3% frictional, but can spike in downturns |
| Expense Growth | Normal | Mean=3%, SD=1% | Relatively stable, centered around inflation |
| Interest Rate | Triangular | Min=5.5%, Mode=6.5%, Max=8.5% | Has a near-term floor but can rise significantly |
Input probability distributions for the 20-unit Monte Carlo simulation
Building the Simulation Engine
In Excel, build the simulation using a combination of RAND(), NORM.INV(), and Data Tables. For each iteration: (1) generate random numbers for each variable using RAND(), (2) transform them into the appropriate distribution using NORM.INV (for normal) or custom formulas (for triangular), (3) feed them into the model assumptions, and (4) record the outputs (IRR, CoC, DSCR, equity multiple). Use a two-column Data Table with 1,000 rows: column 1 is the iteration number (1-1000), column 2 links to the IRR output. Each time the table recalculates, RAND() produces new values and the table captures 1,000 IRR results. In Python, use NumPy for random number generation and a loop that runs the cash flow calculations 10,000 times, storing results in an array for analysis.
Interpreting and Presenting Monte Carlo Results
With 1,000+ IRR values, create a histogram showing the distribution of outcomes. Calculate the key statistics: mean IRR (central tendency), median IRR (50th percentile, often more meaningful than mean), standard deviation (spread of outcomes), 10th percentile (downside—you do better than this 90% of the time), 90th percentile (upside—you only do this well 10% of the time), and probability of exceeding your hurdle rate (P(IRR > 15%)). For our 20-unit example, Monte Carlo might show: mean IRR = 17.8%, median = 17.5%, 10th percentile = 9.2%, 90th percentile = 26.1%, P(IRR > 15%) = 68%, P(IRR > 10%) = 87%, P(loss) = 3%. This rich output far exceeds what any scenario analysis can provide.
Watch Out For
Treating all variables as independent when they are correlated
Understates tail risk because adverse events are correlated (recession causes both vacancy increase AND cap rate expansion)
Fix: Use correlation matrices or copulas to model dependencies—at minimum, link rent growth and vacancy inversely
Running too few iterations for reliable statistics
Results vary significantly between runs, producing unreliable probability estimates
Fix: Run at least 1,000 iterations for basic analysis, 5,000-10,000 for publication-quality results
Key Takeaways
- ✓Three distributions cover most variables: Normal (symmetric, like rent growth), Triangular (bounded, like cap rates), Uniform (equal likelihood).
- ✓Excel Monte Carlo uses RAND(), NORM.INV(), and Data Tables; Python uses NumPy for faster, more robust simulations.
- ✓Key outputs: mean, median, percentile bounds, and probability of exceeding target thresholds.
- ✓Monte Carlo provides probability-of-achievement metrics that scenario analysis cannot—e.g., 68% probability of 15%+ IRR.
Sources
- NCREIF — Probabilistic Modeling for Real Estate(2025-01-15)
- Argus Software — Monte Carlo Implementation Guide(2025-01-15)
Common Mistakes to Avoid
Treating all variables as independent when they are correlated
Consequence: Understates tail risk because adverse events are correlated (recession causes both vacancy increase AND cap rate expansion)
Correction: Use correlation matrices or copulas to model dependencies—at minimum, link rent growth and vacancy inversely
Running too few iterations for reliable statistics
Consequence: Results vary significantly between runs, producing unreliable probability estimates
Correction: Run at least 1,000 iterations for basic analysis, 5,000-10,000 for publication-quality results
"Advanced Modeling: Development Pro Formas, Funds & Attribution" is a Pro track
Upgrade to access all lessons in this track and the entire curriculum.
Immediate access to the rest of this content
1,746+ structured curriculum lessons
All 33+ real estate calculators
Metro-level data across 50+ regions
Test Your Knowledge
1.What is the minimum recommended number of iterations for a Monte Carlo simulation?
2.How should correlation between input variables be handled in Monte Carlo simulations?
3.What key output from Monte Carlo simulation helps set investment committee hurdle decisions?