Real Estate Financial Modelling: Sensitivity Analysis

A sensitivity analysis, otherwise known as a what-if analysis, constitutes an important tool for decision making in finance. Sensitivity analysis allows financial analysts to foresee what the desired result of a financial model would be under different circumstances.  It is used extensively in real estate to determine how sensitive the outcome is to changes in variable assumptions. 

To illustrate, let's refer back to our IRR example from last week. A project that requires an initial investment of $1,500,000 and accumulates an annual rental income of $125,000. After 10 years, the project will be sold for 6,250,000. In this example, $1,000,000 is funded by debt while $500,000 is funded by equity. We find that the project has an IRR of 20% and an equity IRR of 29%. 

                                   
                                    

Here, we are trying to determine how sensitive our IRR analysis is to changes in initial costs or the annual rental revenue (our two main variables). To begin a sensitivity analysis we must first establish a base-case scenario. This is typically the IRR using assumptions we believe to be most accurate. Thereafter, we can change various assumptions we had initially made, based on other potential assumptions. IRR is then recalculated, and the sensitivity of the IRR based on the changes in assumptions is determined. This will be done for both the Project IRR and the Equity IRR.   
                           
To construct the matrix, follow the steps below. 

1. Reference your base assumption at 0% sensitivity level. This can be done by simply multiplying all cash flows in your initial IRR analysis by the cells that contain your sensitivity levels. In my preceding example, I added two cells C36 for the base revenue assumption and C37 for the base cost assumption- both at 0%. I then multiplied the initial cash outflow -1,500,00 *(1+C7); likewise, cells D8-M8 have all been multiplied by (1+C36). 

                                   

                                   

                                   

at this step, we are establishing that $1,500,00 is our base cost assumption with a 0% margin of error. Likewise,  $125,000 is our base revenue per year, assuming there is 0% variance in our predictions. 

2. In a cell on the worksheet, reference the formula that refers to the two input cells you want to sensitise. In the example, I referenced my project IRR in cell T66. 

3. Enter one list of input values in the same column, below the formula. In the example, I input a range of development cost possibilities i.e. I expect that the initial cost of construction will vary between -15% (best case) and +15% (worst case).

4. Enter the second list in the same row, to the right of the formula. I have input revenue growth assumptions, which again, I expect to fluctuate between -15% (worst case) and +15% (best case). 

5. Select the range of cells that contains the formula and both the row and column of values. In our example, I have selected T66:AA73.

6.  Click 'What-If analysis' tool under the 'Data Bar'. Select the 'Data table' from the options. Hit Alt-D-T on your keyboard for a shortcut on windows. 

7. In the row input cells box, enter the sensitivity level reference for the row. In my example, the rows represent the revenue income, hence, I selected cell C36 in the row input cell box. 

8. In the column input cells box, enter the sensitivity level reference for the column. In my example, the columns represent the cost of development, hence, I selected cell C37 in the column input cell box. 

9. Press OK! this will give us the different scenarios for the unleveraged IRR!

10. To compute the scenario analysis for leveraged IRR, repeat steps 2-9, just reference the formula for the leveraged IRR as the base cell you want to sensitise.