Question

Complete the following Complex Time Value of Money problem. Show your work. You are doing some long-range retirement planning. On the day you retire (28 years from now) you want to be able to withdraw $180,000. Then, you want to withdraw the following amounts at the end of each year after that (during your retirement period). Years 1-6 $140,000 Years 7-10 $160,000 Year 11 $250,000 Years 12-22 $145,000 At the end of the 22nd year in retirement, you’d like to have $400,000 remaining in your retirement account available for withdraw (Note that this is equivalent to a $545,000 withdraw in year 22). During your retirement years, you anticipate earning a 4.7% rate of return. You currently have $90,000 that you are going to use to start your retirement savings today. In addition, you plan to save $350 at the end of every other week for the next 14 years. At that point (14 years from today) you will add another $125,000 to your retirement fund. Then, over the remaining 14 years, how much must you save at the end of every other week to reach your goal if you earn 9.3% as a rate of return during the first 14 years and 7.8% over the final 14 years in which you are saving for retirement? (Hint – Your final answer should somewhere between $300 and $450 if you did this correctly).

Answer 1

Solution: We have to divide this problem into Pre Retirement and Post Retirement Stages. We will first start with Post Retirement Stage and treat Retirement day as T=0. So what ever values we are planning to withdraw we would chart them on a 22 years timeline and will Discount these by 4.7% to know the exact corpus we need at T=0 for Retirement Spending. The Workings are as under:

Post Retirement Cash Flow Present Value					Post Retirement Cash Flow Present Value
Year	Cash Flows	DF @0.047	PV		Year	Cash Flows	DF @0.047	PV
0	180000	1.00	180000.00		12	145000	0.58	83561.81
1	140000	0.96	133715.38		13	145000	0.55	79810.70
2	140000	0.91	127712.87		14	145000	0.53	76227.99
3	140000	0.87	121979.82		15	145000	0.50	72806.10
4	140000	0.83	116504.13		16	145000	0.48	69537.82
5	140000	0.79	111274.24		17	145000	0.46	66416.26
6	140000	0.76	106279.12		18	145000	0.44	63434.82
7	160000	0.73	116009.41		19	145000	0.42	60587.22
8	160000	0.69	110801.73		20	145000	0.40	57867.45
9	160000	0.66	105827.82		21	145000	0.38	55269.77
10	160000	0.63	101077.19		22	545000	0.36	198412.72
11	250000	0.60	150843.47		Fund needed on day of retirement			2365957.84

Now we know that we need a corpus of $23,65,957.84 at the time of retirement that is at T 28. Now we will move forward to know how much we need to save every other week in last 14 years to reach to our goal. The Workings are as under

Our Next Level on Timeline is T=14: Where will find out how much corpus we have and what is required shortfall.

For that we would find Future Value of 90000 deposited at T0 = FV(rate,nper,,-PV,0) where rate= 0.093; nper =14; PV= -90000 we will get by computing the values in the formula = 312553.80 at T14

Similarly every other week deposit of $350.00 = 350*26 = $9100 at end of Year 1 corpus thus at FV of this 9100 ordinary annuity at T14 =FV(rate,nper,-PMT,,0) where rate= 0.093; nper =13; PMT= -9100 we will get by computing the values in the formula = 213050.41 at T14

At T14 we also have $125000 deposited thus valuing the same at T14. Thus our total fund value at T14= $312553.80 + $213050.41 + $125000 = $650604.21

We see the shortfall is $17,15,353.63 this could be make up first by knowing the future value of $312553.80 at T28 we would use the Future Value formula once again: = FV(rate,nper,,-PV,0) here rate = 0.078; nper =14; PV=-312553.80 we will get by computing the values in the formula = $894514.59 thus an appreciation of $8,94,514.59 - $312553.80 = $5,81,960.80 at T28

We will now grow $125000 at T14 by using Future Value formula: = FV(rate,nper,,-PV,0) here rate = 0.078; nper =14; PV=-125000 we will get by computing the values in the formula = $357744.25 thus an appreciation of $3,57,744.25 - $125000 = $2,32,744.25 at T28

So now our net shortfall at T28 will be $17,15,353.63- $5,81,960.80- $2,32,744.25 = $9,00,648.58 (this value is nothing but the Future Value of Our Ordinary Annuities at every other week)

We would use PMT function in excel to find out the value =PMT(Rate,Nper,,-FV,0) here Rate = 0.078; Nper=13 (as accumulation will be over after Year 15); FV= - 900648.58. By substituting the values in the Formula we would get Annual Ordinary as $42,450.72 when divided by 26 other weeks in the year we will get $1632.72 as every other week savings for the remaining 14 years.