Hi, I'm working on a 10 year financial forecasting model for an investment
fund. The end result is to calculate the IRR. There are 4 variable inputs I
would like to change, and observe how the IRR changes as those inputs change.
In other words, I would like to play with changing two different fees, the
fund size, and the expected return, and see how changing those impacts the
IRR.

Is there a way to do this on one sheet without manually changing all these
inputs? I think Scenario or Data Tables might do it, but I'm not sure.

Thank you.

You might try this template, available free from the Microsoft Excel
Templates area:
http://office.microsoft.com/en-us/te...CT101444811033


"irrhelp" wrote:

Hi, I'm working on a 10 year financial forecasting model for an investment
fund. The end result is to calculate the IRR. There are 4 variable inputs I
would like to change, and observe how the IRR changes as those inputs change.
In other words, I would like to play with changing two different fees, the
fund size, and the expected return, and see how changing those impacts the
IRR.

Is there a way to do this on one sheet without manually changing all these
inputs? I think Scenario or Data Tables might do it, but I'm not sure.

Thank you.

irrhelp -

For single-factor sensitivity analysis, where usually you keep three inputs
at base case and vary the fourth, you could use a one-way data table.

For two-factor sensitivity analysis, where usually you keep two inputs at
base case and vary the other two, you could use a two-way data table.

The data table approach is described in my free chapter, Sensitivity
Analysis Using Excel, available from the Chapters page at www.treeplan.com.

An alternative is to use an add-in to automate similar analyses. My SensIt
add-in may be useful. Another add-in is Palisade's TopRank.

- Mike
http://www.MikeMiddleton.com



"irrhelp" wrote in message
...
Hi, I'm working on a 10 year financial forecasting model for an investment
fund. The end result is to calculate the IRR. There are 4 variable
inputs I
would like to change, and observe how the IRR changes as those inputs
change.
In other words, I would like to play with changing two different fees, the
fund size, and the expected return, and see how changing those impacts the
IRR.

Is there a way to do this on one sheet without manually changing all these
inputs? I think Scenario or Data Tables might do it, but I'm not sure.

Thank you.

Did you ever find the answer to this? I am trying it today and there doesn't seem to be an easy way to do it in excel! Chug and plug...

On Tuesday, December 08, 2009 5:06 PM irrhelp wrote:

Hi, I am working on a 10 year financial forecasting model for an investment
fund. The end result is to calculate the IRR. There are 4 variable inputs I
would like to change, and observe how the IRR changes as those inputs change.
In other words, I would like to play with changing two different fees, the
fund size, and the expected return, and see how changing those impacts the
IRR.

Is there a way to do this on one sheet without manually changing all these
inputs? I think Scenario or Data Tables might do it, but I am not sure.

Thank you.


On Tuesday, December 08, 2009 5:34 PM JLatham wrote:

You might try this template, available free from the Microsoft Excel
Templates area:
http://office.microsoft.com/en-us/te...CT101444811033


"irrhelp" wrote:


On Tuesday, December 08, 2009 10:07 PM Mike Middleton wrote:

irrhelp -

For single-factor sensitivity analysis, where usually you keep three inputs
at base case and vary the fourth, you could use a one-way data table.

For two-factor sensitivity analysis, where usually you keep two inputs at
base case and vary the other two, you could use a two-way data table.

The data table approach is described in my free chapter, Sensitivity
Analysis Using Excel, available from the Chapters page at www.treeplan.com.

An alternative is to use an add-in to automate similar analyses. My SensIt
add-in may be useful. Another add-in is Palisade's TopRank.

- Mike
http://www.MikeMiddleton.com

Have you tried the free Sensitivity Analysis Add-In Macro provided at http://www.life-cycle-costing.de/sensitivity_analysis/ ?
It allows for up to 20 input and up to 20 output values to be varied / observed at a time. Either being varied separately (one input at a time) or in all possible combinations of all inputs.

Regards
Thomas