Goal Seek help needed

[FrankJM]

Hello,

New to the forum and have limited VBA / Macro experience.

What I would like to do is use Goal Seek or some variation of it solve for an amount required to achieve a certain IRR calculation (using XIRR) from a series of cash flows by being able to select the targeted IRR from a drop down menu.

I know that Goal Seek function generally solves as follows:

Set Cell =("B10") where my resultant Preferred Return (XIRR) is calculated, with the "to value" set at say 10%, by changing cell "F10". The value in F10 changes until the value in cell B10 = 10%. With Goal Seek, if I wanted to solve F10 for an IRR of say 12%, I would have to manually go in and modify the "to value" amount, then the calculation would be completed.

However, I would like to know if there is a way that would automatically change the "to value" in Goal Seek to match the selection from a drop down menu, say between 10% - 15% without having to manually change the "to value" in the Goal Seek function each time I make a change to the preferred return.

Again, you are dealing with a newbie here when it comes to VBA and Macros so be gentle.

Any help would be greatly appreciated.

Frank

 

[JoeMo]

Here's a change event macro that will run automatically whenever you change the desired IRR result in cell F10 using the data validation dropdown in F10. The goal seek will change the value in J13 (change cell address to match your sheet), which is one of the values in the XIRR range of values, such that cell B10 will return an IRR value that matches F10 as closely as Goal Seek can. Note that XIRR will error if you do not use reasonable values in your dropdown. Install the macro following the steps below.

To install the code:
1. Right-click the worksheet tab you want to apply it to and choose 'View Code'. This will open the VBE window.
2. Copy the code below from your browser window and paste it into the white space in the VBE window.
3. Close the VBE window and Save the workbook. If you are using Excel 2007 or a later version do a SaveAs and save it as a macro-enabled workbook (.xlsm file extension).
4. Make sure you have enabled macros whenever you open the file or the code will not run.

Private Sub Worksheet_Change(ByVal Target As Range)
Set Target = Target(1)
If Not Intersect(Target, Range("F10")) Is Nothing Then
       Application.EnableEvents = False
       Range("B10").GoalSeek Target.Value, [J13]
       Application.EnableEvents = True
End If
End Sub

 

[FrankJM]

Hello JoeMo,

Thanks for your response. Unfortunately the suggested code did not work. I even tried various combinations by moving the cell references around. I may not have clearly explained what I was trying to do with my cell references. Hopefully the following will help as I am now using actual cell references this time.

My drop down menu is cell B50, where I can select a desired IRR. Cell C50 is where the IRR is calculated based on a series of cash flows. Would like to automatically solve for the amount needed in cell BD49 (the "to value" in regular Goal Seek function) such that the resulting IRR calculation in cell C50 = the desired result in cell B50.

Hopefully this is a little clearer as to what I am after.

Thanks again for your help. It is much appreciated.

Frank

 

[JoeMo]

Hello JoeMo,

Thanks for your response. Unfortunately the suggested code did not work. I even tried various combinations by moving the cell references around. I may not have clearly explained what I was trying to do with my cell references. Hopefully the following will help as I am now using actual cell references this time.

My drop down menu is cell B50, where I can select a desired IRR. Cell C50 is where the IRR is calculated based on a series of cash flows. Would like to automatically solve for the amount needed in cell BD49 (the "to value" in regular Goal Seek function) such that the resulting IRR calculation in cell C50 = the desired result in cell B50.

Hopefully this is a little clearer as to what I am after.

Thanks again for your help. It is much appreciated.

Frank

Why not use the actual cell references in your initial post and save us all some time?

Try the following changes in the code I posted:
Replace F10 with B50
Replace B10 with C50
Replace J13 with BD49

If that doesn't work, please don't reply with something like "Unfortunately the suggested code did not work". That's like telling your automobile mechanic that your car doesn't work w/o providing any other details. Be specific as to what happens or doesn't happen when you use the drop down.

 

[FrankJM]

Hi JoeMo,

Sorry, about the "unfortunately"...did not want to be so forward to say it did not work, and sorry for not using the cell references in my original post. Was going to include a screen shot to help clarify what I was trying to achieve but ran into some problems, hence tried to explain, but obviously I did not do a good job of it. As I mentioned, I am new the forum.

The revised code still does not work. Made the suggested changes, saved the file again, opened it and enabled Macros and nothing happens when I make a change to worksheet or select a different desired IRR. I don't know what else to say that might help.

Thanks again for your help.

Frank

 

[JoeMo]

Hi JoeMo,

Sorry, about the "unfortunately"...did not want to be so forward to say it did not work, and sorry for not using the cell references in my original post. Was going to include a screen shot to help clarify what I was trying to achieve but ran into some problems, hence tried to explain, but obviously I did not do a good job of it. As I mentioned, I am new the forum.

The revised code still does not work. Made the suggested changes, saved the file again, opened it and enabled Macros and nothing happens when I make a change to worksheet or select a different desired IRR. I don't know what else to say that might help.

Thanks again for your help.

Frank

Just knowing nothing happens is valuable information. Here are the possible issues I can think of:

1. You did not install the code following the procedure I posted in post #2.
2. Your drop down is not a data validation drop down. Could it be a control combo box or list box?
3. You have inadvertently disabled events. To rectify this use the following procedure:

press alt+F11 keys to open the VBE, then press ctrl + g keys to open the Immediate Window. In the Immediate Window type (w/o the quote marks) "Application.EnableEvents = True" and press Enter. Now try again to change the drop down cell (assuming it's a data validation drop down).

 

[FrankJM]

Hi JoeMo,

I did install the code as suggested. My data validation was a list, so I simply deleted it and tried a simple input cell. Added the language to the Immediate Window and it still did not work. So I tried the code in another small model I created and the code works!!! Now I just have to figure out why it is not working in the model where I need it.

Thanks for all of your help. Consider the matter solved.

Frank

 

[JoeMo]

Hi JoeMo,

I did install the code as suggested. My data validation was a list, so I simply deleted it and tried a simple input cell. Added the language to the Immediate Window and it still did not work. So I tried the code in another small model I created and the code works!!! Now I just have to figure out why it is not working in the model where I need it.

Thanks for all of your help. Consider the matter solved.

Frank

You are welcome. Note that the changing cell (BD49) must be a manual input cell, not a formula cell in order to employ Goal Seek.

 

[FrankJM]

Good morning and Happy New Year Joe!

You solved the above problem for me back in November, so I thought I would simply reach out to you with a follow up question...hope you don't mind. How would I change the VBA code to solve for a second Goal Seek? Would I simply repeat the code or portion thereof with revised cell references to solve for the second Goal Seek variable? Your insight is appreciated.

Regards,
Frank

 

[JoeMo]

Good morning and Happy New Year Joe!

You solved the above problem for me back in November, so I thought I would simply reach out to you with a follow up question...hope you don't mind. How would I change the VBA code to solve for a second Goal Seek? Would I simply repeat the code or portion thereof with revised cell references to solve for the second Goal Seek variable? Your insight is appreciated.

Regards,
Frank

Just repeat this block with appropriate references:

If Not Intersect(Target, Range("F10")) Is Nothing Then
       Application.EnableEvents = False
       Range("B10").GoalSeek Target.Value, [J13]
       Application.EnableEvents = True
End If