Risk is EVERYWHERE in real estate. Given the amount of variables that affect the sale, cash flow, or acquisition of any property or development, you’re bound to have one or multiple variables greatly affect your deal (in a positive or negative way).
Our industry is governed by many “standard” metrics that we use to understand the relative riskiness of a deal. For example:
- 50 percent of gross rent should be attributed to expenses for any given small cash-flowing property;
- The cap rate on a large stabilized market rate rental property should be around 7.5 percent;
- A commercial lender should require a 1.2 percent debt coverage ratio on an acquisition, etc.
While these metrics (and many others) are rough industry standards, they are never truly accurate for any one specific deal because the metrics always deviate plus or minus a few degrees. When you add up enough minor deviations from the standard assumptions, you could have what once appeared to be a homerun quickly turn into a strike out.
So how can we understand the ramifications of the fluctuations around these standard, assumed metrics?
Enter Sensitivity Tables:
Sensitivity tables are a financial and business modeling technique that allows one to understand an output (think NOI, ROI, IRR, acquisition price, etc.) based on the changes in one or two variables. Typical questions answered by a sensitivity table include:
- What will my debt service be based on different acquisition prices and interest rates?
- What is the NOI based on different acquisition prices and rent levels?
- What is the lowest rent I can charge to still generate at least X dollars in monthly income for this property if I acquire it at Y price?
- What cap rate and NOI do I have to have to acquire this property for X dollars?
- At what gross rent and vacancy levels do I start losing money on this property?
Modeling these variables can quickly provide a tremendous amount of information on the relative risk of a project. To build a sensitivity table, model your deal with a spreadsheet as you would any other deal. Once finished, pick several variables you want to “test” the sensitivity on. To do this, you’ll have to pick three things.
1) Your outcome. What result to do you want to see from this analysis (common outcomes include net operative income, internal rate of return, net present value, and acquisition price)?
2) Input number one. What input do you want to test the sensitivity on (common inputs include the cap rate, lender interest rate, rent levels, and vacancy rates)?
3) Input number two.
Then use your spreadsheet to build the sensitivity table. The inputs will be in a column and row while the outputs will populate the rest of the table. A typical, blank sensitivity table will look like this:
Here’s a very quick example using a hypothetical investment.
Let’s say a broker calls me up with an intriguing multi-family acquisition. It’s in an area I know very well and I’m confident in my assumptions on rent, expenses, vacancies, and tenant turnover. After modeling my cash flows and returns, I want to test out the riskiness of vacancy rates and rent levels. Based on past projects, I’m pretty sure I can achieve a gross monthly rent of $7,000 and have a vacancy and credit loss of about five percent. By keeping my other variables constant (operating expenses, debt service, etc.) I can use a sensitivity table to see how high or low these variables can be before I start incurring a negative cash flow.
Holding my assumptions like expenses and debt service constant (among others), I can see that I need to be somewhat careful about both vacancy and gross rents because, if they deviate in the wrong direction simultaneously, I could quickly be in a bad situation. Looking at the results of the sensitivity analysis, if I think my general assumptions are sound, I may think this is too risky of a venture for my capital.
Every acquisition or development has a mountain of risks involved. While you’ll never truly know the exact performance of a property or new project, sensitivity tables can help get a sense for how tolerant you can be when the actual numbers deviate from your initial projections.
P.S.
If you’re interested in an available and formatted sensitivity table, check out the BiggerPockets FilePlace (within the Resources section of BiggerPockets). I used a modified version of the sensitivity table found in the “Pro Forma Template – Small Apartments” Excel file for the example above.
Photo: llamnudds
{ 4 comments… read them below or add one }
WOAH. Just stumbled upon this and it is AWESOME!
Thanks Kyle! Can’t wait to play around with this thing on my future deal analyses.
Really solid resource hee Kyle, thanks for sharing with us all!
Kyle
Great job. You are using the Data Table function within Excel for this sensitivity table? As that can do the same thing and utilize conditional formatting to highlight the turning points of where cash flow turns negative.
Ankit
Glenn and Mike, glad you found it useful!
Ankit, good call on the data tables. I created the example manually, but definitely true with the effectiveness of the Data Table function in Excel (and all the “what-if analysis” functions).