How to create sensitivity data table based off input created from goal seek value

[MR06]

Hi all,

I am trying to create a sensitivity table that tells me what amount of equity I need to include in a deal in order to get a fixed IRR as the year 3 sales price changes. I have created a simplified version of my problem in the attached spreadsheet.

Assuming I want the IRR to be 25%, I can easily figure out what equity I need by using goal seek for the existing inputs (currently ~$437k). What I want to do is see how that changes as the year 3 price changes, but when I use the what-if data table, it spits out the same $437k number for each value. Can someone provide a solution as to how I can create a data table with varying year 3 prices, and the corresponding equity required to achieve 25% IRR?

Thanks!!

MR06

As a side note, I've also noticed that if I manually adjust one of the inputs slightly, the IRR dynamically updates (as it should). However, if I then re-run goal seek, if the dynamically updated IRR is close enough to what I put in goal seek, goal seek doesn't run. I left my example showing this (IRR is at 24.977, if you run goal seek at 25% nothing happens). Anyone have a solution to this?

 

[Eloise T]

XIRR can be finicky. You may want to check all your cells to be certain they are formatted correctly.

 

[MR06]

Hi Eloise,

The issue isn't calculating the IRR, that's working fine.

The problem is currently I need to manually adjust the unit price in order to goal seek the equity that yields a 25% IRR, and repeat that for each different unit price. I am looking for a way to automate that.

Thanks!

 

[Hui]

MR06

Goal Seek is a wonderful function but is not dynamic

You can setup a Dynamic Solution using a Data Table and a Forecast Function


See your file attached:

to learn more about data table please read my post at:
http://chandoo.org/wp/2010/05/06/data-tables-monte-carlo-simulations-in-excel-a-comprehensive-guide/

 

[MR06]

Hi Hui,

Thank you for this.

I think I must be misleading in my explanation. What I am looking to do is set up a table that says "if year 3 price is X, what amount of equity do I need to input in order to yield a 25% IRR." The manual process is 1) hard code the price in year 3, which then dynamically updates the rest of the numbers in the table 2) run goal seek changing the equity input cell to achieve a 25% IRR, which then hard codes the equity and gives me the answer I need for that specific price 3) hard code a new year 3 price and repeat.

What I would like is to create a table that shows varying year 3 prices as rows, and the necessary equity input to achieve 25% IRR as the column.

Thanks!

 

[Hui]

The great thing about Data Tables is that they add flexibility to models
To fix this revised problem I
1. Changed the values in the data table to 50,000 to 500,000
2. Relink the data table to B10 instead of G6
3. Voila


see attached file

 

[Hui]

Ok I misread the revised question
here is the Two Way lookup solution

 

[MR06]

Hi Hui,

Thanks for your continued help.

I need the 25% IRR to be fixed, so I'm not looking for a sensitivity analysis on how slightly differing equity levels impact IRR. What I'm looking for is how pricing levels impact the equity required for a 25% IRR. In other words, your data table is solving for 1 specific price (or equity) input. What I need is for the data table to run a loop that solves the equity input at 25% IRR for differing pricing inputs.

I just did this manually, and here is the output that I got. The issue is it takes like 4 minutes and a ton of clicking, and I have to run this analysis on hundreds of different data sets.

Equity @ 25% IRR
9 $450,015
10 $447,223
11 $444,439
12 $441,662
13 $438,893
14 $436,132
15 $433,379


Thanks,

Matty

 

[Hui]

Or with a Table of Solutions


also Change Cell D31 to 25%

 

[MR06]

Hi Hui,

Thank you for continuing to help.

This last little bit, the table of solutions, is exactly what I need. Can you let me know the steps to recreate this?

Thanks so much!

 

[Hui]

Examine the formulas in the Table
It is simply doing the same calculations for each Column with a different price

If You are literally doing hundreds of these I would approach the whole thing differently

I would establish a list of parameters eg: 1 to 300 with columns for each field
Then use a Data Table to set the inputs and retrieve the outputs and tabulate them

Can you post a small list of say 10 inputs and I will setup a dummy file for you

 

[MR06]

Hi Hui,

The example I provided is very simplified from the real thing. My actual models have much more complicated models that don't all operate the same way, although they do all have a price input, an equity input, and an IRR output. What I'm really looking for is a way to cut and paste the table of solutions in each different spreadsheet such that I can update the links within the formulas and quickly have this table as an output.

Thanks!

 

[Hui]

Try the following
1. Copy the Blue Bordered area to another file


2. In the new file, Select the red area
3. Goto the Data, What If Analysis, Data Table command
4. Link the Data Table to the new locations in the new model
5. Link the Upper Left cell in the Red Area Cell C16 to the new IRR Calculation in the new Model


6. If the Upper Left cell of the Blue area is not B15 you may have to adjust the formulas in Row 31, so study them in this model first

 

[Hui]

To better understand the above please read:
http://chandoo.org/wp/2017/07/04/how-to-add-a-lot-of-goal-seeking-to-a-model/

 

[MR06]

Hi Hui,

I have followed your steps and am experiencing 2 issues:

1) this is not a linear function but I'm 100% positive that the higher the equity, the higher the resulting IRR. However, as you'll see in the attached image, for whatever reason the data table is incorrectly spitting out results where sometimes more equity = lower IRR



2) if I change the equity input cell (B10 in the example) the forecast results change drastically, however in reality there is only 1 answer. How am I supposed to know if I'm getting the correct answer?

Thanks!

MR06

 

[MR06]

Hi Hui,

One other thing: the data table doesn't match my model's output for IRR. In the image I posted, for example, the data table shows $3M equity and price of $179 yield a 10.58% IRR, but my model shows an 11.8% (hence the upper left cell shows 11.8%). Any thoughts on this?

Thanks!

MR06

 

[Hui]

I realise that this isn't the model I posted above
Can you send me the actual model via email or a Conversation