Automated goal-seek issues

[Elliottgpjones]

Hi all - i am running a simple automated goal-seek in order to find what the price of a product has to be in order to return a 10% IRR for a project. it works fine, however if i change some of my inputs by too large-an increment, it seems to fail as the IRR function shows "#NUM". For example, changing my cost input from 50 to 25 works fine, but then changing it back up to 50 results in an error message. Could someone kindly help with this?

Furthermore, i am trying to post my results in a data table. for example with a range of one cost on the row of the data table and the range of another cost on the column of the data table. however, for some reason the data table will not return any difference in numbers.

thanks in advance for the help in both questions!

 

[Jon Peltier]

If you showed what your model looks like, it would be much easier to figure it out.

 

[BSALV]

is the sum of the cashflows perhaps negative or the irr close to 0%.
Sometimes using another estimated % can help.

 

[Elliottgpjones]

is the sum of the cashflows perhaps negative or the irr close to 0%.
Sometimes using another estimated % can help.

Thank you for this! This was very helpful - i instead linked the goalseek to NPV rather than IRR % and that worked. However, i am still having an issue regarding making the sensitivity table. Unfortunately when i create the data table, it just comes up with one answer. Would you happen to know of a solution to this?

 

[BSALV]

Is VBA allowed ?
How does your data look like for the moment ?
Sensitivity, what are you changing ?

 

[Elliottgpjones]

Is VBA allowed ?
How does your data look like for the moment ?
Sensitivity, what are you changing ?

Thanks a lot for the help! Unfortunately i cannot share the data, however i basically have a simply goalseek running, in order to get the revenue needed for a product to get the npv = 0. what i would like to do is then show the different answers of that revenue if i change two different raw material costs. However, when i try to do this in a data table, it just comes up with the same answer. I am sure it is because of the goal seek nature of this that the data table sensitivity isnt working, but im not sure at all how do fix it. Hope this all makes sense

 

[BSALV]

Unfortunately i cannot share the data, ...

you can create a lookalike ?
lookalike

Sub sens()

     Set lo = ActiveSheet.ListObjects(1)
     If lo.ListRows.Count Then lo.DataBodyRange.Delete
     
     For i = -300000 To -10000 Step 10000
          Range("B2").Value = i
          lo.ListRows.Add.Range.Range("A1").Resize(, 2).Value = Array(i, Range("C1").Value)
     Next

For i = -10000 To 0 Step 1000
          Range("B2").Value = i
          lo.ListRows.Add.Range.Range("A1").Resize(, 2).Value = Array(i, Range("C1").Value)
     Next   
End Sub