Thanks Rick, but I warned you that I was a novice. When I did what you
suggested it returned a negative number that seemed somewhat less (about 15%)
than the inflation adjusted spend for age 100. What I need is the today
spend before inflation adjustment.

I should also note that I have two investment cells -- 401(k) and Other
Investments. Both go negative in the same year. I doubt if it matters but
some of the spend is always covered through pensions.

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