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

 

[FrankJM]

Hi Joe,

Thanks for your quick response. It is much appreciated!!!

Have a great 2017,
Frank

 

[FrankJM]

Hi Joe,

One quick follow up question. How would I amend code to ensure goal seek runs with any change to the spreadsheet? Right now it appears that it will only run if I change alter the cell with the desired IRR.

Thanks,
Frank

 

[JoeMo]

Hi Joe,

One quick follow up question. How would I amend code to ensure goal seek runs with any change to the spreadsheet? Right now it appears that it will only run if I change alter the cell with the desired IRR.

Thanks,
Frank

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Range("B10").GoalSeek Target.Value, [J13]
Application.EnableEvents = True
End Sub

Repeat the goal seek block for each cell you want to seek a goal using appropriate addresses.

 

[FrankJM]

Hi Joe,

Again, thanks for the quick response. You only included two variables in your revised code. What happens to cell [F10] in your original code? I would still like to have the option of changing [F10] which is the desired IRR. Sorry, this stuff really is not my cup of tea.

Here is what you originally had:
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 = TrueEnd IfEnd Sub
Cheers,
Frank

 

[JoeMo]

You asked that the code be revised to run with any change on the sheet, not just cell F10, but the code will run if F10 is changed (and again each time any other cell is changed.

 

[FrankJM]

Hi Joe,

thanks for your response. The reason I was asking about cell F10, which is where I have my desired IRR value, is without it, the the code does not know what to solve to. When I run it as is, I am getting an extremely large number in my cell B10, where I have my XIRR formula.

The original code worked when I changed F10, but when I changed another input cell, I would have to go back to F10, re-enter the desired IRR, and only then would the VBA calculate the new values. I need to be able to solve to F10 when that value is changed, and I also need the code to run when I make other input changes on my spreadsheet.

Hope this explains things a little better.

Regards,
Frank

 

[JoeMo]

Hi Joe,

thanks for your response. The reason I was asking about cell F10, which is where I have my desired IRR value, is without it, the the code does not know what to solve to. When I run it as is, I am getting an extremely large number in my cell B10, where I have my XIRR formula.

The original code worked when I changed F10, but when I changed another input cell, I would have to go back to F10, re-enter the desired IRR, and only then would the VBA calculate the new values. I need to be able to solve to F10 when that value is changed, and I also need the code to run when I make other input changes on my spreadsheet.

Hope this explains things a little better.

Regards,
Frank

Thanks for clarifying. Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
'enter all the goal seek cells, with goal cells and changing cells you desire
Range("B10").GoalSeek [F10], [J13]
'Next goal seek line
'next goal seek line
Application.EnableEvents = True
End Sub

 

[FrankJM]

Hi Joe,

I tried the revised code and still not working. For some reason, my XIRR formula in cell B10, is not calculating to my desired IRR Target cell F10 because cell J13, the changing cell is not changing.

Not sure what else I can tell you to help, other than thanks for trying.

Frank

 

[JoeMo]

Hi Joe,

I tried the revised code and still not working. For some reason, my XIRR formula in cell B10, is not calculating to my desired IRR Target cell F10 because cell J13, the changing cell is not changing.

Not sure what else I can tell you to help, other than thanks for trying.

Frank

Works fine for me so I have no way of diagnosing your problem.

 

[FrankJM]

Hi Joe,

Thanks again for getting back to me so quickly.

I did manage to get it to work, but I had to clear the other VBA code on another tab in my model, and then I had to close and restart Excel. No sure what was going on there, but we appear to be good at the moment.

By the way, how do I get the VBA to work again after the Debugger has been stopped? When I was entering the info again...for the hundredth time, I made a type, the Debugger came up, so I corrected it, then got message when I closed the VBA window that Debugger will be stopped. Nothing happened when I made a change, so I had to save file, close Excel and then restart the program, and enable the macros again. Seems strange.

Anyway it is now working and I have to say, I have learned a little through this process, emphasis on little.

I do appreciate all of your help.

Take care,
Frank