# Automatic Goal Seek

#### Mark Carver

##### Member
See attached file, cell P48. I developed a spreadsheet for my personal use so it was easy to hardwire it for my own information. But, several friends want copies so I am trying to automate it. My primary problem is how to automate Goal Seek. Basically, I am trying to determine how much money to set aside for Medicare and Long Term Care costs. I do this by listing all the withdrawals, and an investment return on the balances, etc. The actual amount is the result of a Goal Seek. I have reached an impasse in my thinking. So, all help is appreciated. THANKS!

#### Attachments

• 29.5 KB Views: 11

#### vletm

##### Excel Ninja
Mark Carver
Your Q9 ( from Q9 to Q12) would 'normally' use fixed value 500 (for some reason)...
If You could do it 'my way' ( solve needed value to cell Q3 ) then You could use 'Goal Seek' to get ZERO to cell S38.
... if Q9 value ... hmm? is huge, then there should raise eg 'Medical Costs' to get smaller 'Balance'

#### Attachments

• 29.6 KB Views: 3

#### Mark Carver

##### Member
Yes, I had only put the "500" formula in the first few cells as a test. I see what you did by putting the value in Q3. However, the start cell in column Q is variable as a result of when Medicare starts. So, if it starts in 2023 instead of 2020, how do you automate the Goal Seek?
Sorry, I am not great at this and your patience is very appreciated.

#### vletm

##### Excel Ninja
Mark Carver
This helps with Start year,
but You gotta do some steps manually eg to use Goal Seek with correct end cell.

#### Attachments

• 29.8 KB Views: 4

#### Peter Bartholomew

##### Well-Known Member
To automate Goal Seek would require the use of VBA procedure containing a code line of the form
Code:
``Range("S38").GoalSeek Goal:=0, ChangingCell:=Range("Q9")``
The cell to change is overwritten so a formula is not allowed.
You could provide a button to run the macro or it could run in response to some worksheet change.

#### vletm

##### Excel Ninja
vletm
Your the original wish was to get formula with basic Excel.
But if VBA is okay then You could test this...
As You have written Your the 1st year could be 'whatever' and ... how about the last year?
You change values to Summary-sheet ...
and then You'll wish to see Your Tables-sheet
... select it and You'll get there results 'Automatic' with GoalSeek.

#### Attachments

• 32.1 KB Views: 9

#### Mark Carver

##### Member
You are definitely a Ninja. This does work. I will study them and learn. THANKS!!!

#### Peter Bartholomew

##### Well-Known Member
Just another thought if you would like a non-macro-enabled workbook..

Instead of building the data table forward in time, you could build the formulae from the latest date backwards in time. You know the required end condition of zero remaining assets at the expected date of death and, from there, you add payments to determine the prior balance and subtract the interest received (dividing by 1.05 to calculate the interest payment from the final rather than initial value).

#### vletm

##### Excel Ninja
Peter Bartholomew
#4 sample works with formulas ... except the end date.
Hmm? ...from the expected date of ... hum? Of course, nobody cannot live for ever ... and that day would be challenge to forecast.
I would start from the first year and plan some years forward ... and later add more years as many as possible.

#### Mark Carver

##### Member
vletm
Your the original wish was to get formula with basic Excel.
But if VBA is okay then You could test this...
As You have written Your the 1st year could be 'whatever' and ... how about the last year?
You change values to Summary-sheet ...
and then You'll wish to see Your Tables-sheet
... select it and You'll get there results 'Automatic' with GoalSeek.
I have not use VBA but I'd like to learn. I poked around a little in the spreadsheet you sent but could not find the VBA code. I went to Developer, Visual Basic but didn't see anything in any of the project windows. Sorry to be so lame but could you point me in the right direction?

#### vletm

##### Excel Ninja
Mark Carver
... the right direction?
Did You read and do as has written in #10 reply?
Did my sample works as it should work?
Questions waits answers.

#### Peter Bartholomew

##### Well-Known Member
@Mark Carver
We now appear to have a discussion distributed over multiple forums.
Don't forget to inform each group of any solution you adopt.

As for finding the code, it is within the sheet module but preceded by blank lines so that you need to scroll down to find the event handler.

#### Peter Bartholomew

##### Well-Known Member
@vletm
Assumptions relating to ages at the start of the plan and at death are already built into the model, so my suggestion is merely to reverse the direction of the calculation so that it starts with the target end condition (of a zero balance) and works backwards in time.

#### Mark Carver

##### Member
@vletm
Assumptions relating to ages at the start of the plan and at death are already built into the model, so my suggestion is merely to reverse the direction of the calculation so that it starts with the target end condition (of a zero balance) and works backwards in time.
I did figure out another solution but I will test this on a separate sheet. Thanks for the suggestion; I'm learning!

#### Mark Carver

##### Member
Thanks to all of you who helped. I did finally get it working to where I needed it. It may be a little kludgy, but it works (attached). Also, my apologies for using two unrelated forums, now I know. Again, thanks.

#### Attachments

• 39.7 KB Views: 9