Question

A couple will retire in 40 years; they plan to spend about $31,000 a year in retirement, which should last about 20 years. They believe that they can earn 7% interest on retirement savings. a. If they make annual payments into a savings plan, how much will they need to save each year? Assume the first payment comes in 1 year. (Do not round intermediate calculations. Round your answer to 2 decimal places.) b. How would the answer to part (a) change if the couple also realize that in 15 years they will need to spend $61,000 on their child’s college education? (Do not round intermediate calculations. Round your answer to 2 decimal places.)

Answer 1

Part (a):

Value of the yearly retirement income, at the time of retirement is the present value of annuity calculated at $328,414.44 as follows:

C D A B 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. r= Rate of interest per period in decimals All amounts in $ Given that 9 Periodical payments (P)= 31000 No. of years (N)= 200 10 Rate of interest (% p.a)=R= 7 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.07 15 No. of periods (n)= N*t G9*G10 20 (1+r)= 1+G14 1.07 17 (1+r)^-n= G164-615 0.258419003 18 [1-(1+r)^-n]= 1-G17 0.741580997 19 [1-(1+r)^-n]/r= G18/614 10.59401425 20 Present value of annuity PV= P*{[1-(1+r)^-n]/r]} D9*G19 328414.4416 21 Rounded to $328,414.44 22 16

Amount required to be paid into savings plan every year till retirement is $ 1,645.07 as follows:

9 A B C D 1 Annuity Payment 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 8 All amounts in $ Given that 10 Future value required FV= 328414.44 No. of years (N) 11 Rate of interest (% p.a)=R= 7 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.07 16 No. of periods (n)= N*t O'G11*G11 40 17 (1+r)= 1+G15 1.07 (1+r)^n= G174616 14.97445784 19 Present Value of future amount (PV)= FV/(1+r)^n= D10/G18 21931.64143 (1+r)^-n= G17A-G16 0.066780381 21 [1-(1+r)^-n]= 1-G20 0.933219619 22 FV*r= G19*G15 1535.2149 23 Periodical payment required (PMT)= (FV*r)/(1-(1+r)^-n] G22/G21 $ 1,645.07 18 20

Part (b):

In order to spend $61,000 for college education in 15 years, they have to pay additional amount of         $2,427.47 into the Savings plan, during the first 15 years, 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= 61000 No. of years (N) 11 Rate of interest (% p.a)= R= 7 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.07 16 No. of periods (n)= N*t O'G11*G11 17 (1+r)= 1+G15 1.07 (1+r)^n= G174616 2.759031541 19 Present Value of future amount (PV)= FV/(1+r)^n= D10/G18 22109.2072 (1+r)^-n= G17A-G16 0.36244602 21 [1-(1+r)^-n]= 1-G20 0.63755398 22 FV*r= G19*615 1547.644504 23 Periodical payment required (PMT)= (FV*r)/(1-(1+r)^-n] G22/G21 $ 2,427.47 18 20