Need a formula for my personal finance spreadsheet

[johndc43]

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.

 

[Niek Otten]

Your question is rather general, so I can't give you a formula.
But this type of investigation is generally solved quite well with the Goal
Seek functionality.
Before Excel 2007:
Tools>Goal seek
Excel 2007:
Data Tab, What if Analysis, Goal seek

 

[BSc Chem Eng Rick]

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]

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]

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

 

[Gord Dibben]

I like your optimism...........using age 100 as an endpoint.

I'm spending mine as fast as I can so there will be nothing left for my
heirs except some unpaid bills to look after.


Gord Dibben MS Excel MVP

 

[Niek Otten]

<I'm spending mine as fast as I can so there will be nothing left for my
heirs except some unpaid bills to look after.>

Great!!

My dad always told me he'd be doing just that and he kept his word.

 

[johndc43]

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.

 

[johndc43]

Hey guys -- it pays to be conservative (also in earnings assumptions)

I present valued back the number I got and it gave a number that comes out
age 116 rather than age 100 (15%+ off again).

 

[johndc43]

Playing with this a bit more, I see that it always returns a constant number
regardless of the "set cell" chosen in the spreadsheet. Obviously I'm doing
something way off.

 

[johndc43]

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.

 

[Niek Otten]

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

 

[johndc43]

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.