Excel Sensitivity (What If) analysis

[jordanpcpre]

The analysis below (chart #1) shows what the IRR is at a 6.75% Cap Rate and a
$4.60 Rental Rate. The IRR was derived from a long formula of inputs (note
Cap Rate and Rental Rate were both one of the inputs). I need to create a
sensitivity (what if) analysis table showing what happens to the IRR if the
Cap Rate and/or Rental Rate is increased (see chart #2). Any recommendations
on how I can do this?


#1) Cap Rate 6.750%
_________
Rental |IRR
$4.60 |29.71%


#2) Cap Rate 6.750% 7.000%
_____________________________
Rental |IRR IRR
$4.60 |29.71% 25.14%
$4.70 |32.44% 27.86%

 

[Del Cotter]

I need to create a sensitivity (what if) analysis table showing what
happens to the IRR if the Cap Rate and/or Rental Rate is increased (see
chart #2). Any recommendations on how I can do this?

#2) Cap Rate 6.750% 7.000%
_____________________________
Rental |IRR IRR
$4.60 |29.71% 25.14%
$4.70 |32.44% 27.86%

Perhaps a Surface Chart? It should be "2D" rather than 3D; that is, it
should be a plan view looking down on the data. The trick to making such
a graph elegant and informative is choosing the coloring and formatting.

Alternatively, the benefits of a surface chart and more can be achieved
using the most versatile of Excel chart types, the scatter chart. This
is a similar problem to the recent thread "Need help graphing an
equation" where the equation turned out to be the equation of a surface,
not a line:

http://www.branta.demon.co.uk/excel/eqsurf.xls