Creating Sensitivity Tables in 3 Easy Steps

A sensitivity analysis is a useful method of assumption, one that many investors use before they purchase a property to determine if it is likely to meet their investment goals.

They are a visual what-if analysis of the unknown variables and how they can change throughout the life of the investment. These variables can include but are not limited to the price of the property, holding period, cap rate at sale, lease renewal probability, vacancy rates, interest rate, debt yield, and more.

The purpose of a sensitivity analysis is to quickly view how any two measures vary with a change in one of the assumptions. These tables can help you see a variety of outcomes and can also show your investors what happens to the financial metrics in different scenarios.

We created a 3-outcome sensitivity table with variable inputs on both axis.

In this example, we can easily see 3 different return metrics at once, and how they change based on the purchase price and exit year.

This table will show you how the IRR, Equity Multiple, and Cash on Cash return change based on your purchase price and exit year.

Here’s how to create a 3-outcome sensitivity table in 3 easy steps! It’s not as difficult as you may think.

Step 1

Determine which 2 different inputs you want to have on your sensitivity analysis. These will be the inputs on the horizontal and vertical axis.

In this example, we will use purchase price and the sale/exit year.

Important: You must create the sensitivity table on the same Excel tab where theses inputs are in your underwriting model.

Step 2

Determine which return metric(s) you want to see in your sensitivity table. This can be the Limited Partner IRR, Yield on Cost, etc.

In every sensitivity table, the top left cell is linked to the return metric you want to see different outcomes for.

In this case, since we want to measure 3 different return metrics at once (IRR / EM / CoC), we will use this formula:

=TEXT(IRR,”0.0%”)&” / “&TEXT(EM,”0.0x”)&” / “&TEXT(CoC,”0.0%”)

** With the highlighted text in red, be sure to link those cells to the actual returns in your model.

Step 3

Last step is highlight (shown in yellow) the entire sensitivity table area and add create the table.

In Excel, go to the Data Tab, then What-If Analysis, and click on Data Table.

Important: The ‘Row Input Cell’ will always be the cell that has whatever is on the rows in table. In this case, I would click on the purchase price cell in my underwriting model. The ‘Column Input Cell’ will always be the whatever is on the column of your table. In this case, I would click on the exit year in my underwriting model. Then click OK.

That’s how you can easily create a sensitivity table in your underwriting model! Commercial real estate underwriters use data tables to easily view different outcomes based on variable inputs.

We have a variety of sensitivity models like this created in the Next Level Value Add Model.

Here’s an example Excel file you can download for free with a few 3-outcome sensitivity tables!

Download Example