Question

Robin is planning for her retirement. She is currently 37 years old and plans to retire at age 62 and live until age 97. Robin currently earns $120,000 per year and anticipates needing 80% of her income during retirement. She anticipates Social Security will provide her with $15,000 per year at age 62, leaving her with required savings to provide $81,000 ($120,000 x 0.80 - $15,000) annually during retirement. She is willing to take some investment risk. Her pre-retirement portfolio will be 80% stocks and 20% bonds and their post retirement portfolio will be 60% stocks and 40% bonds. Large company stocks have averaged 9% annually, and intermediate term government bonds have averaged 5% (please calculate the weighted average expected return of your portfolios). She believes inflation will be 3% per year. How much must Robin save at the end of each year if she wants to make her last savings payment at age 62 to meet her retirement goal?

PLEASE SHOW ALL WORK, IF IN EXCEL PLEASE SHOW ALL EQUATIONS THANKS!

Answer 1

Rate of return will be as follows:

Pre-retirement: 80%*9% + 20%*5%= 8.2%

Post retirement: 60%*9% + 40%*5% = 7.4%

Amount required on retirement at the age of 62 is the Present Value of Annuity arrived at $925,184.30 as follows:

в со 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] 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)= 81000 No. of years (N)= 35 10 Rate of interest (% p.a)=R= 8.2 No. of payments a year (t)= 11 11 12 Calculations: 13 Formula Cell reference Value 14 Interest rate per period in decimals (r )= R/(100*t) D10/(100*G10) 0.082 15 No. of periods (n)= N*t G9*G10 35 (1+r)= 1+G14 1.082 (1+r)^-n= G164-G15 0.063393667 [1-(1+r)^-n]= 1-G17 0.936606333 [1-(1+r)^-n]/r= G18/614 11.42202845 20 Present value of annuity PV= P*{[1-(1+r)^-n]/r]} D9*G19 925184.3047 21 Rounded to $925,184.30 22 16 19

Amount required to be saved at the end of each year, before retirement is $13,808.47 calculated as follows:

B A 1 Annuity Payment C D Payments at the end of each period 2 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 $ 9 Given that 10 Future value required FV= 925184.305 No. of years (N) 25 11 Rate of interest (% p.a)= R= 7.4 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.074 16 No. of periods (n)= N*t O'G11*G11 25 17 (1+r)= 1+G15 1.074 18 (1+r)^n= G174G16 5.958089656 19 Present Value of future amount (PV)= FV/(1+r)^n= D10/G18 155282.0381 20 (1+r)^-n= G174-G16 0.167839032 21 [1-(1+r)^-n]= 1-G20 0.832160968 22 FV*r= G19*G15 11490.87082 23 Periodical payment required (PMT)= (FV*r)/(1-(1+r)^-n] G22/G21 13808.47127