📈 Creating Sensitivity Tables in 3 Easy Steps

Numbers Don't Lie: The only newsletter helping you become an expert commercial real estate underwriter every Saturday 11am EST/10am CST.

Author

Justin Goodin
April 15, 2023

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.

Recently, I 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.

If you're a passive investor, be sure the sponsor has a variety of sensitivity tables for you to view. This will allow you to see how your returns could change based on different outcomes.

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

📈 CRE & Market News

📈 Stay Up-To-Date on Rates

📈 Chart of The Week

Apartment Markets with the Lowest Rents

Among the nation’s largest 150 apartment markets, only a handful are still commanding average monthly rents below the $1,000 mark.

Among the nation’s largest 150 apartment markets, the metro with the biggest bargain is Wichita. With rents averaging $850, prices here come in nearly $1,000 short of the U.S. norm of $1,792.

👋 Did you enjoy this newsletter? The best compliment would be for you to share this on social media or with a friend.

📈 Modeling Your Success,