In: Finance
Beginning one month from today, you plan to save $250 each month for the next ten years. After that, you plan to increase the amount you set aside each month from $250 to $450, remaining at this level for another ten years. In the next ten years, you plan to save $650 each month. Finally, for the last ten years of your investment program, you plan to save $850 each month. The rate of return on your invested funds is 7.5% APR, compounded monthly. Your starting balance at age 25 is zero. Ignore inflation and taxes.
If you withdraw nothing from your account along the way, what will be your account balance 40 years from today?
What constant amount should you set aside each month to reach the same ending balance you computed in part 1?
Once you reach age 65 (and the end of your 40-year investment horizon), suppose you decide to stop accumulating funds and start spending. Your accumulated investment account is your only source of income. The rate of return on invested funds is 6% APR, compounded monthly. Assume you expect to live for another 20 years. How much could you afford to spend each month?
n=120
rate =0.075/12
PMT=250,450,650,850
Period | Ammount accumulated | Formula |
At end of 10 years | $ 44,482.5855 | FV(0.075/12,120,250) |
At end of 20 years | $ 174,018.75 | FV(0.075/12,120,450,44482.5855) |
At end of 30 years | $ 483,193.57 | FV(0.075/12,120,650,174018.7497) |
At end of 40 years | $ 1,171,776.84 | FV(0.075/12,120,850,412021.4329) |
Formulas for excel are provided.Notice how we have used the FV of previous period as present value of next period.
For same amount each month:
n=12*40=480
FV=1,171,776.84
rate =0.075/12
Excel formula=PMT(0.075/12,480,0,1171776.84)
=$387.52
For spending each month:
rate =0.06/12=0.005
n=240
PV=1,171,776.84
FV=0
Excel formula=PMT(0.005,240,-1171776.84)
=$8,394.97