1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

Discussion in 'Ask an Excel Question' started by MR06, Jun 30, 2017.

  1. MR06

    MR06 New Member

    Messages:
    8
    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?

    Attached Files:

  2. Eloise T

    Eloise T Active Member

    Messages:
    641
    XIRR can be finicky. You may want to check all your cells to be certain they are formatted correctly.
  3. MR06

    MR06 New Member

    Messages:
    8
    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!
  4. Hui

    Hui Excel Ninja Staff Member

    Messages:
    10,700

    Attached Files:

    Thomas Kuriakose likes this.
  5. MR06

    MR06 New Member

    Messages:
    8
    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!
  6. Hui

    Hui Excel Ninja Staff Member

    Messages:
    10,700
    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
    upload_2017-7-3_21-34-3.png

    see attached file

    Attached Files:

  7. Hui

    Hui Excel Ninja Staff Member

    Messages:
    10,700
    Ok I misread the revised question
    here is the Two Way lookup solution
    upload_2017-7-3_21-42-6.png

    Attached Files:

  8. MR06

    MR06 New Member

    Messages:
    8
    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
  9. Hui

    Hui Excel Ninja Staff Member

    Messages:
    10,700
    Or with a Table of Solutions
    upload_2017-7-3_21-46-32.png

    also Change Cell D31 to 25%

    Attached Files:

  10. MR06

    MR06 New Member

    Messages:
    8
    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!
  11. Hui

    Hui Excel Ninja Staff Member

    Messages:
    10,700
    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
  12. MR06

    MR06 New Member

    Messages:
    8
    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!
  13. Hui

    Hui Excel Ninja Staff Member

    Messages:
    10,700
    Try the following
    1. Copy the Blue Bordered area to another file
    upload_2017-7-3_22-24-39.png

    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
    upload_2017-7-3_22-26-5.png

    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

    Attached Files:

    Thomas Kuriakose likes this.
  14. Hui

    Hui Excel Ninja Staff Member

    Messages:
    10,700
  15. MR06

    MR06 New Member

    Messages:
    8
    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

    upload_2017-7-12_8-47-26.png

    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
  16. MR06

    MR06 New Member

    Messages:
    8
    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
  17. Hui

    Hui Excel Ninja Staff Member

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

Share This Page