Upgrade your modeling skills and your investor pitches
A Guide to Modeling and Overcoming Analysis Paralysis
I am a numbers person by nature and often consider excel to my second home for my 9 to 5 job and so I can relate to those who find themselves wanting to exhaustively model a potential property and the cashflows that could result. There is a temptation to model every possible scenario but, in truth, it should be balanced with reality. I feel those who get lost in analysis paralysis would benefit from my approach. I leave the modeling of catastrophic events like floods, terrorism, or the sequence of maintenance events to the insurance quants (I generally account for those major events when evaluating the macro market and would only do my analysis if the market was favorable. IE, I don’t model flood risk because I won’t buy in a flood plane.) but I model all the other scenarios exhaustively so that I have a very clear picture of what the probability of a profitable investment is.
Below is my approach to modeling a stabilized property in suburbia USA. I will give you a warning now that there is some high school level statistics referenced so you may want to refresh yourself.
Monte Carlo Simulations
Monte Carlo simulations are a way of modeling forecasts when there are random/uncertain variables introduced that cannot be accounted for using a single value. For example, utility bills, vacancy rates, interest rates etc are all random variables that we try to account for. Normally, we try to model a property using best guesses and usually consists of something along the lines of “what is best case scenario?” and plugging in favorable numbers into our model. Followed by “what is worst case scenario?” and repeating the process with worst case numbers.
The premise behind the Monte Carlo approach is to assign each variable in your model a probability distribution and then letting your PC run 10K scenarios where it picks a random value for each of your variables based on your probability distribution that you assigned and records the result. The output is something like this where I can see what the most likely annual cash flow is going to be.
For those of you who suffer from a certain level of analysis paralysis, you can gain reassurance that a property may or may not make money on an annual basis by taking a look at the thousands of scenarios and admitting that the thousands of scenarios your PC can run for you in the matter of minutes will be far more exhaustive than the manual what-if scenarios you try to generate yourself.
In the above screenshot, the property will most likely make 6.5K every year. If all the usual factors are against me (high utility bills, bad interest rates, vacancy, etc) then I will make 2K…if the stars align than I might hit 12K in a year.
In a completely fabricated example, the below screenshot indicates that I have a 33% chance of having a negative cashflow on an annual basis (small red text at the $0 mark).
Models like these are pretty simple to construct.
Simulation Instructions
To get started:
1.Install the excel add in
- I like ModelRisk, it is free but there is also @Risk by Palisade. Both are great programs and I am sure there are a few others out there as well. Some of them can get pretty pricey.
2.Prepare your excel model.
- I can’t stress this enough. You need to know excel and you need to have your model built in such a way that none of the values are hard coded into functions.
- Your model needs to account for everything you want include in your model. Mine accounts for monthly expenses, vacancy, mortgage rates (because it is pre purchase and I don’t have a rate lock yet).
- You can build your model up to be much more complicated, I use the BRRR method so mine accounts for a few extra variables that I'll mention later.
3.Prepare your data.
- Running lots of scenarios does not mean that you get to skip on the due diligence to understand your costs. If anything, it makes you understand your costs better because you now have to understand the distribution. IE, before you would ask what the average water bill was….now you have to ask what the average is, the MIN, the MAX and possibly a few other descriptors. Average is a dirty word in modeling….. and you can’t model using only the average.
- Take a look at your data and decide what distribution each variable should follow.
- Normal distributions, triangle distributions, binomial distributions, and Bernoulli distributions are the ones I find myself using the most often.
- Utility bills will generally follow a normal distribution (IE average water/sewage is 90$ with a 20$ standard deviation in my area)
- Interest rates are only going to go up from here (June 2018). I plan to lock in a rate within 3 weeks so I look at historical data and find the largest shift in interest rates over a 3 week time. I set it up as a triangle distribution.
Example:
Below is one of my excel models. The base cash flow sheet is pulled from a friend or maybe BP files section. It does a straightforward job calculating annual cash flow. For the sake of keeping on point, this is an example of how to use a Monte Carlo simulation….not the ins and outs of my proforma calculation
With ModelRisk installed you get a new tool bar.
Which has lots of great functionality that you can explore but here are the basics.
4)You need to identify your output variable. In this case, I am selecting the annual cash flow.
a.Select the annual cash flow cell
b.Click on the Output/Input button on the tool bar >> Mark as Output/Input
c.Fill in the Name of the variable….”Annual Cash Flow”
d.Make sure the “type” has output selected.
5)For each variable that you want to model.
a.Select the cell you want to add a distribution to >> Click the Select Distribution Button
b.Select which distribution you want to use. I will use the Normal dist in the example.
c.Populate the appropriate values to describe the distribution.
In my example, annual water bill will be on average $1000 with a standard deviation of $120
d.Press ok and repeat for each of your variables.
6)Once you have set all your variables up, set the model to run about 10,000 times.
a. 
b. Press the Start button and let it run for a few minutes. The results should automatically come up
Whoala… you now have a very comprehensive set of 10,000 scenarios and what the likelihood of profitability is for your property. Quick Tip: This analysis is very beneficial to include in your investor pitches. You can honestly tell them that you have considered 10K scenarios.
If there is interest then maybe I will put together a small webinar on how to do these analyses in more detail. If so, let me know in the comments.
A Few Notes.... My Brain Dump
When I start modeling a property, almost every variable is being modeled using a distribution. But, as due diligence is completed, as insurance quotes are received, and interest rates are locked in, I start to plug those numbers into the model since those variables are no longer uncertain. This reduced uncertainty will modify the results of the model and you start to hone in on your most likely result.
You can build very elaborate models and monte carlo simulations can help analyze the results of those. I have models that follow me through the full BRRRR process. They capture uncertainty around purchase price, the rehab costs, the rental cash flow (above), and the refinancing.
To get complicated, you can use Bernoulli variables to model the likelihood of a binary result (if a tenant will leave in a specific year, if the zoning board will approve a particular project etc) and account for that in the cost.
I mentioned above that I don’t model the SEQUENCE of major repair events and whether that major roof repair will happen in a given year (but you can using Bernoulli variables if you wanted)… Instead I account for that next major roof repair by setting aside reserves and maintenance funds. I do that because it is not a question of whether I will incur the cost but rather a question of when.
Interest rates are best modeled using a triangle distribution. I feel that I am not going to get a better rate than there is today (5% for commercial)… and that it COULD increase up to .5% before I lock in a rate. You can see what that looks like on a distribution below.
Utilities expesnes are great for normal distributions.
Wikipedia and the modelrisk help pages are fantastic for helping explain the different options for probability distributions.
You can track lots of different outputs. Cash on Cash returns, potential out of pocket costs (I use the BRRR method so out of pocket costs shouldn't be a thing but there is always the possibility of a bad appraisal or high rehab costs).
Hope that provides some ideas for everyone!