Creating a Sensitivity Analysis - Real Estate Financial Modeling

Hi all!

when building a finanancial model for a potential real estate investment, future projections are typically based on assumptions. How do we figure out which key variables have the biggest input on the returns (KPIs?). That’s (among other use cases) where sensitivity analysis becomes a powerful tool. By testing key variables in the DCF model, sensitivity analysis allows to see different outcomes and better model worst- and best-case scenarios.

Let’s dive in!

What is Sensitivity Analysis?

Sensitivity analysis tests how sensitive an investment’s performance is to changes in key inputs, such as ERVs (estimated rental value), inflation, or interest rates. It shows how a small change in one or more variables can impact metrics like the IRR (Internal Rate of Return), Cash-On-Cash or the Money Multiple (MOIC). Essentially, it helps to understand which assumptions are critical the investment performance.

In a real estate model, some of the most commonly tested variables include:

• Interest rates

• ERVs (Estimated Rental Values)

• Cap Rate / Exit Multiple

• CapEx measures

• and many more ... (you can pretty much test any variable!)

Step 1: Choose the Key Input-Variables

Identify the input variables that could have the most significant impact on your investment. These are usually the inputs that are still uncertain or subject to change.

Let’s say you want to test effects of changes of the:

• Interest Rate

  : Can vary based on Monetary Policy, Risk-Premiums, etc...

Once you've identified the key variables, we can model different scenarios.

Step 2: Set Up The Base Case

Create a base version of your real estate model, with the best estimates for each input. (So think to yourself, what the most "realistic" version of the investment. It will serve as the baseline for the sensitivity analysis.

Step 3: Build a Sensitivity Table in Excel

To visualize the results changing variables, Excel’s Data Table function is incredibly powerful and useful. This feature allows you to create a sensitivity table that shows how changes in two variables affect a single output, such as the IRR.

Let’s say you want to see how the Interest Rate affects the IRR. Here’s how to set it up:

• Link the top-left cell

  to the formula containing the IRR in your model.

• List Interest Rate variations

  (e.g., 2.0%, 3.0%, 4.0%, 5.0%, 6.0%) across the row.

• Use Excel’s

  Data Table

  feature (found under "What-If Analysis" in the Data tab) to calculate the results for each change or the interest rate. This really saves a lot of time, as you do not have to type every change manually. In the "Row Input Cell", you then enter the input to your interest rate. If you would extend the Data Table to Columns (see a future tutorial), you should insert this in the "Column Input Cell".

Shortcut for Data Tables: Alt + A + W + T

This table will show you how sensitive your IRR is to changes in the interest rate (in this case), and it helps providing a clear picture of which variable has a more significant impact on your investment.

Step 5: Interpret the Results

Once your sensitivity table is complete, you can analyze the results. Ask yourself some questions, like

• Which variable has the most significant impact on the desired KPI (in our case - the IRR)? For example, if small changes in the interest rate lead to major shifts in the IRR, this suggests that the investment is highly sensitive to the interest rate - maybe due to high leverage? Go into detail! It is important that you understand.

Step 6: Stress Test Your Model

Sensitivity analysis doesn’t just help with moderate adjustments; it’s also useful for stress testing your model. This means testing extreme scenarios to see how the investment would perform under worst-case (or best-case) conditions.

Conclusion

Sensitivity analysis is an essential part of real estate financial modeling, providing valuable insights into how key variables affect any investment’s performance. By testing different scenarios and understanding how sensitive your model is to various inputs, you can make more informed decisions and better manage risk.

Now it’s your turn to build a sensitivity analysis into your own real estate model! If you need help setting up your Excel tables or interpreting the results, feel free to reach out—I’m happy to assist.

Happy modeling and until next time! 😊

– Aron