Actually, I solved this by setting up an auto_open macro so each time I open
the workbook the What-if/goal seek will execute. Seems to work fine. Thanks
everyone for all the help.

"Niek Otten" wrote:

Not sure what you mean, but..
If you mean you want to change the "To value" part:

You have a cell in which the result appears. Let's say that is cell A10.
Have a cell where you put in the desired result. Let's say it is cell B10.
Have a third cell (let's say C10), where you have the formula:
=A10-B10
Now you can always goalseek for zero for C10

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"johndc43" wrote in message
...
Hooray! Thanks Rick, I got it to work.

Now my problem is how can I get the "what if/goal seek" to adjust
automatically whenever the spreadsheet changes, e.g. when the investment
pot
changes. The investment numbers change daily and I'd like to avoid
manually
reentering the what if daily.

"BSc Chem Eng Rick" wrote:

OK, the whole point of this analysis is clearly to not have a negative
over
the specified time line. What I would do is have a cell which sets "max
allowable annual expenditure" which obviosuly already use in each of your
rows. The value of this cell is found using What If? anaylsis as
suggested by
Niek. This is how you do it.

Click on the investment cell of the last year in your time line. Then
Data
-- Data Tools -- What-if Analysis -- Goal Seek...

In the window that pops up you will see the address of your last year's
cell
in "Set cell:". You want this cell set "To Value:" of zero because that
will
give you the most money you can spend before it goes negative. Finally
"By
changing cell:" now click on the "max allowable annual expenditure" we
made
above, then OK.

The calc procedes numerically and the result is naturally the limit of
what
you can spend annually.

If this helps please click "Yes"
<<<<<<<<<<

"johndc43" wrote:

Thanks Rick
Successive years are in rows. The first year is plugged and then each
succeeding year is simply adjusted by a constant inflation percentage
(one of
several variables). Income sources are adjusted each year for earnings
and
taxes and then applied to the spend in the appropriate order. A MIN
function
pulls the first year that investments go negative


"BSc Chem Eng Rick" wrote:

John,

This is definitely doable and there are a number of ways to
accomplish it.
Could you give a little more detail on how you calculate annual
spending? Do
you have successive years in consecutive columns with calcs for time
value of
money or is it only one column to give you the current status?

"johndc43" wrote:

My knowledge of Excel is fairly basic, but I have developed a
somewhat
complex personal finance spreadsheet for retirement planning. The
spreadsheet applies various income sources (pensions, soc Sec,
401(k), other
investments, etc. all adjusted for earnings and taxes) to meet cash
requirements. If a set a yearly cash requirement, say 100, 000,
then the
spreadsheet adjusts this each year for inflation and reveals in
what year my
assets will be depleted. I can use trial and error to determine
the annual
spend that will not deplete my assets until age 100. What I would
like to do
instead is have the spreadsheet deliver the annual spend that will
deplete my
investment assets at age 100.

It seems to me that this should be doable, but I have no idea how
to go
about setting up a formula that will accomplish this. Can someone
point me
in the right direction? Thanks.