Question

Cesar plans to retire at age 57, and estimates that $62,400 a year in retirement for 28 years will be needed. That annual amount will be paid out in monthly income checks. To save for retirement, Cesar plans to put money every month into a 401k plan at work. If Cesar is currently 25, how much will need to be saved each month to meet the retirement goal, assuming the retirement accounts earn an average of 6.6% per year.

Please show your work.

Answer 1

Amount needed a year in retirement= $62,400

Therefore, amount needed a month= $62,400/12 = $ 5,200

Amount needed at the time of retirement (age 57) is the present value of annuity as on that date, calculated at $795,740.18 as follows:

A B C D 1 Present Value of Annuity Payments at the end of each period 2 3 Present value of annuity is calculated using the formula PVA= P*{[1-(1+r)^-n]/r]} 4 Where P= Periodical payments, 5 n= Number of payments. 6 r= Rate of interest per period in decimals 7 All amounts in $ 8 Given that 9 Periodical payments (P)= 5200 No. of years (N)= 10 Rate of interest (% p.a)=R= 6.6 No. of payments a year (t)= 11 12 Calculations: 13 Formula Cell reference Value 14 Interest rate per period in decimals (r )= R/(100*t) D10/(100*G10) 0.0055 15 No. of periods (n)= N*t G9*G10 336 16 (1+r)= 1+G14 1.0055 17 (1+r)^-n= G16^-G15 0.158351734 [1-(1+r)^-n]= 1-G17 0.841648266 19 [1-(1+r)^-n]/r= G18/614 153.0269575 20 Present value of annuity PV= P*{[1-(1+r)^-n]/r]} D9*G19 795740.179 Rounded to $795,740.18 22 18 | 21

Period left till age 57 = 57-25= 22 years

Amount needed to be saved each month in order to accumulate this amount is $1,344.70 calculated as follows:

22 A B C D E 3 Amount of periodical payments is calculated using the formula PMT= (PV*r)/(1-(1+r)^-n] 4 Where PMT= Periodical payments required, 5 PV= Present value of future sum which is FV/(1+r)^n 6 n= Number of payments. 7 r= Rate of interest per period in decimals All amounts in $ Given that 10 Future value required FV= 795740.18 No. of years (N) 11 Rate of interest (% p.a)= R= 6.6 No. of payments a year (t) 12 13 Calculations: 14 Formula Cell reference Value 15 Interest rate per period in decimals (r )= R/(100*t) D11/(100*G11) 0.0055 16 No. of periods (n)= N*t O'G11*G11 264 17 (1+r)= 1+G15 1.0055 18 (1+r)^n= G174G16 4.254688626 19 Present Value of future amount (PV)= FV/(1+r)^n= D10/G18 187026.6544 (1+r)^-n= G174-G16 0.235034826 21 [1-(1+r)^-n]= 1-G20 0.764965174 FV*r= G19*615 1028.646599 23 Periodical payment required (PMT)= (FV*r)/(1-(1+r)^-n] G22/G21 1344.697293 24 Rounded to $1,344.70 20 22