Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need a formula for my personal finance spreadsheet
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need a formula for my personal finance spreadsheet
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: ToolsGoal seek Excel 2007: Data Tab, What if Analysis, Goal seek -- Kind regards, Niek Otten Microsoft MVP - Excel "johndc43" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need a formula for my personal finance spreadsheet
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need a formula for my personal finance spreadsheet
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need a formula for my personal finance spreadsheet
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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need a formula for my personal finance spreadsheet
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 On Thu, 8 Oct 2009 08:32:01 -0700, 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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need a formula for my personal finance spreadsheet
<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. -- Kind regards, Niek Otten Microsoft MVP - Excel "Gord Dibben" <gorddibbATshawDOTca wrote in message ... 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 On Thu, 8 Oct 2009 08:32:01 -0700, 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. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need a formula for my personal finance spreadsheet
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. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need a formula for my personal finance spreadsheet
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). "Gord Dibben" wrote: 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 On Thu, 8 Oct 2009 08:32:01 -0700, 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. |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need a formula for my personal finance spreadsheet
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" wrote: 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). "Gord Dibben" wrote: 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 On Thu, 8 Oct 2009 08:32:01 -0700, 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. |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need a formula for my personal finance spreadsheet
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. |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need a formula for my personal finance spreadsheet
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. |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need a formula for my personal finance spreadsheet
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pulling stock quotes from Yahoo Finance into a spreadsheet | Excel Discussion (Misc queries) | |||
Finance Formula Help | Excel Worksheet Functions | |||
help with personal budget spreadsheet | Excel Worksheet Functions | |||
Excel finance formula | New Users to Excel | |||
Why doesn't the finance rate in MIRR impact the formula result? | Excel Worksheet Functions |