I’d like to share an apartment acquisition model I built that includes a Monte Carlo Simulation module. This method of analysis, a type of stochastic modeling rarely used in real estate, allows the analyst to better understanding the risk-adjusted returns of the investment. The genesis of this project, is a thesis I read this week shared with me by one of our readers. The thesis, Beyond DCF Analysis in Real Estate Financial Modeling: Probabilistic Evaluation of Real Estate Ventures, was written by Keith Chin-Kee Leung as part of his Master’s in Real Estate studies at MIT. I highly recommend you take the time to read the paper, as it makes an excellent case for using probabilistic modeling in real estate analysis.
Basics of The Model
This model takes one of my earlier apartment acquisition models, and layers in probability over nine variables – rent growth rate, other income growth rate, operating expense growth rate, capital expenditures growth rate, releasing costs growth rate, terminal cap rate, days vacant between leases, and renewal probability. With the addition of probability, the model is capable of using Monte Carlo simulations to better assess the variability of the expected returns. Returns are tested on a net present value basis as well as on an unlevered internal rate of return basis over 10,000 simulations.
Check out my post on How to Run Monte Carlo Simulations in Excel
I used Excel’s Data Table feature to run 10,000 simulations for net present value and 10,000 simulations for internal rate of return. The result is an analysis that gives a more complete picture of the expected risk and returns of a proposed investment compared to the deterministic method (single, best-guess assumptions) most prevalently used in real estate analysis today.
The Macro Conundrum
As many of you know, I don’t like using Macros in my models. They easily break, require an understanding of VBA to build and maintain, and open the user up to security concerns. However, given the processing power required of this type of analysis and to make the process simpler to manage, I’ve added one Macro to this model.
On the ‘Monte Carlo’ tab (blue) below the probability inputs, you’ll find a button for running the simulations. I’ve added a few prompts to tell you whether the Macro is running, if the Macro was run successfully, and when the last time the Macro was run. I’ve also turned off, via VBA, the autocalculate on open, close, and save to avoid running the simulation without notice.
A Few Tips on Using the Model
I’ll follow this post up with a tutorial on using the model when I have time in the coming days. In the meantime, here are a few tips before you begin to use the model:
- Set ‘Workbook Calculation’ to “Automatic except data tables” to help avoid the Monte Carlo Simulations running without notice.
- Turn ‘Stochastic Modeling’ on and off using the drop-down menu at the upper-right hand corner of the ‘Monte Carlo’ tab.
- The growth rates and and change in terminal cap rate probabilities on the ‘Monte Carlo’ tab adapt Leung’s Random Walk concept, where each year a new probability is run. I’ve also modeled in the idea of momentum, where the rate in one year is tied to the previous year and moves in a step fashion.
- I allow for two probability distribution methods, uniform and normal. I recommend normal, as I think it is more accurate. You will need to set a mean (average) change in rate and a standard deviation for the change in rate. Be sure to review the concepts surrounding normal distributions and the 68-95-99 rule to understand the probability inputs.
- When you save the model for the first time, it seems to want to run the Monte Carlo simulations without warning. This make Excel appear to be frozen. Just hit ESC to cancel the calculation, and Excel should go back to normal.
Again, I’ll get a tutorial up when I have time, but in the interim, if you have questions please don’t hesitate to reach out.
Download The Model
To make this model accessible to everyone, it is offered on a "Pay What You're Able" basis with no minimum (enter $0 if you'd like) or maximum (your support helps keep the content coming - typical real estate models sell for $100 - $300 each). Just enter a price together with an email address to send the download link to, and then click 'Continue'. If you have any questions about our "Pay What You're Able" program or why we offer our models on this basis, please reach out to either Mike or Spencer.
Note (especially for Gmail users): The model is sent via email and occasionally is blocked by spam filters. If you don't see the email arrive within five minutes, check your spam folder.
Apartment Acquisition Model with Monte Carlo Simulation Module
- Apartment acquisition model with built-in Monte Carlo Simulation module
- Macro included that runs the Monte Carlo Simulations
- Variability using either a uniform or normal distribution
- Built for apartments but could be used for senior living or other for-rent residential product
- Complex DCF module
- Property and investor level returns
- Inflation calculation
This Excel file (the "software") is distributed as is, completely without warranty or service support. www.AdventuresinCRE.com and its authors are not liable for the condition or performance of the software. www.AdventuresinCRE.com owns the copyright and grants users a perpetual, irrevocable, worldwide, non-exclusive, royalty-free license with respect to the software as set forth below.
www.AdventuresinCRE.com and its authors hereby disclaim all implied warranties. www.AdventuresinCRE.com grants the users the right to modify, copy, and redistribute the software and documentation, both within the user's organization and externally, subject to the following restrictions:
1. The users agree not to charge others for use of the software, regardless of whether users have altered and/or improved the software in anyway.
2. In any use of the software, the users agree to acknowledge the www.AdventuresinCRE.com authors that developed the software.
3. The users agree to obey all government restrictions governing redistribution or export of the software.
4. The users agree to reproduce any copyright notice which appears on the software and documentation on any copy or modification of such made available to others.
About the Author: Born and raised in the Northwest United States, Spencer Burton has over 15 years of real estate investment and development experience. In his current position, Spencer assesses new acquisition, development, and debt opportunities for a $45bn real estate fund. He resides in Dallas, TX.