• 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...

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

Automatic Goal Seek

M

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

  • Health Care Costs Sandbox v2.xlsx
    29.5 KB · Views: 12
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

  • Health Care Costs Sandbox v2.xlsx
    29.6 KB · Views: 4
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.
 
Mark Carver
This helps with Start year,
but You gotta do some steps manually eg to use Goal Seek with correct end cell.
 

Attachments

  • Health Care Costs Sandbox v2.xlsx
    29.8 KB · Views: 4
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
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

  • Health Care Costs Sandbox v2.xlsb
    32.1 KB · Views: 10
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).
 
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.
 
vletm said:
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.
Click to expand...

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?
 
@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.
 
@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.
 
Peter Bartholomew said:
@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.
Click to expand...

I did figure out another solution but I will test this on a separate sheet. Thanks for the suggestion; I'm learning!
 
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

  • Assets Needed to Cover Healthcare Costs.xlsm
    39.7 KB · Views: 12
You must log in or register to reply here.
Back
Top