Question

You just graduated from college and are starting your new job. You realized the importance to save for the future and have figured out that you will save $1,000 per month for the next 15 years; and then increase to $7,000 per month for the following 4 years. The amount accumulated at the end of these investments will be your retirement egg nest. You plan to start retirement and start withdrawing monthly amounts the following month (you will be in retirement for 25 years). If your required rate of return is 12% compounded monthly, how much are your monthly withdrawals?

Answer 1

Please refer to below spreadsheet for calculation and answer. Cell reference also provided.

A B Annual Interest rate 2 12.00% Monthly interest rate 3 1.00% 4 For next 15 years Monthly deposit No. of monthly deposit Value of fund at end of 15 years $1,000.00 180 $499,580.20 8 Following 4 years Monthly deposit No. of deposit Value of fund on retirement 10 $7,000.00 11 48 $1,233,994.48 12 13 14 During retirement(25 years) No. of monthly withdrawal 15 16 300 $12,996.73 Amount of monthly withdrawals 17 18 19 6.

Cell reference -

A Annual Interest rate 2 0.12 |=C2/12 Monthly interest rate 3 4 For next 15 years Monthly deposit 1000 No. of monthly deposit Value of fund at end of 15 years =15*12 |=FV(C3,C7,-C6,0) 8 Following 4 years Monthly deposit No. of deposit Value of fund on retirement 10 11 7000 12 =12*4 =FV(C3,C12,-C11,-C8,0) 13 14 During retirement(25 years) No. of monthly withdrawal Amount of monthly withdrawals 15 16 =12*25 =PMT(C3,C16,-C13,,0) 17 18 19 20 LO

Hope this will help, please do comment if you need any further explanation. Your feedback would be highly appreciated.