Question

Your client just turned 75 years old and plans on retiring in 10 years on her 85th birthday. She is saving money today for her retirement and is establishing a retirement account with your office. She would like to withdraw money from her retirement account on her birthday each year until she dies. She would ideally like to withdraw $50,000 on her 85th birthday, and increase her withdrawals 10 percent a year through her 89th birthday (i.e., she would like to withdraw $73,205 on her 89th birthday). She plans to die on her 90th birthday, at which time she would like to leave $200,000 to her descendants. Your client currently has $100,000. You estimate that the money in the retirement account will earn 8 percent a year over the next 15 years. Your client plans to contribute an equal amount of money each year until her retirement. Her first contribution will come in one year; her tenth and final contribution will come in ten years (on her 85th birthday). How much should she contribute each year in order to meet her objectives?

Answer 1

Amount required on 85th birthday towards yearly payments during retirement is the present value of a growing annuity due, arrived at $ 259,432.32 as follows:

A B C D 1 Present Value of Annuity Payments at the beginningof each period 2 3 Present value of growing annuity is calculated using the formula PVA=[P/(r-g)]*{1-[(1+g)/(1+r)]^n]} 4 Where P= First payments, 5 n= Number of payments. Eg. if number of years=N, then n=N*2 for semi annual payments 6 r= Rate of interest per period in decimals and g= Growth rateper period in decimals 7 All amounts in $ 8 Given that 9 First payment (P)= 50000 No. of years (N)= 10 Rate of interest (% p.a)=R= 8 No. of payments a year (t)= 11 Growth rate (% p.a.)=G= 10 12 13 Calculations: 14 Formula Cell reference Value 15 Interest rate per period in decimals (r) R/(100*t) D10/(100*G10) 16 No. of periods (n)= N*t G9*G10 17 Growth rate (g) G/(100*t) D11/(100*G10) 0.1 [(1+g)/(1+r)]^n ((1+G17)/(1+G15))^G16 1.09608604 {1-[(1+g)/(1+r)]^n}*(1+r) (1-G18)*(1+G15) -0.10377293 r-g G15-G17 -0.02 21 P/(r-g) D9/G20 -2500000 22 Present value of growing annuity PV=[P/(r-g)]*(1-[(1+g)/(1+r)]^n]}*(1+r) G21*G19 $259,432.32 0.08 18 19 20

Value as on the 85th birthday, of the amount intended for descendants to be paid on 90th birthday is the discounted value of $200,000 arrived at $ 136,116.64 as follows:

1 A B C D E F 3 Discounted value of an amount is calculated using the formula V=F/(1+r)^n 4 Where F= Amount after the specified period (Future Value), 5 n= Number of compounbding periods. 6 r= Rate of interest per period in decimals All amounts in $ 8 Given that 9 Future value F= 200000 No. of years (N)= 10 Discount Rate (% p.a)=R= 8 No. of times compounded a year 11 12 Calculations: 13 Formula Cell reference Value 14 Discount rate per period in decimals (r )= R/(100*t) D10/(100*G10) 0.08 15 No. of periods (n)= G9*G10 16 (1+r)= 1+G14 1.08 17 (1+r)^n= G164615 1.469328077 18 Discounted value = F/(1+r)^n D9/G17 136116.6394 19 Rounded to $136,116.64 N*t

Therefore, total amount required as on 85th birthday= $259,432.32 +$136,116.64. = $395,548.96

Value of the current savings of $100,000 as on 85th birthday is the future value arrived at $215,892.50 as follows:

B C D G H A 1 Future Value 3 Future value of an amount is calculated using the formula FV=P(1+r)^n 4 Where P= Principal (Presen Value), 5 n= Number of payments. 6 r= Rate of interest per period in decimals All amounts in $ Given that 9 Present Value (P)= 100000 No. of years (N)= 10 Rate of interest (% p.a)= R= 8 No. of times compounded a year (t): 101 1 13 Value 0.08 12 Calculations: Formula 14 Interest rate per period in decimals (r) R/(100*t) 15 No. of periods (n)= N*t 16 (1+r)= (1+r)^n= 18 Future value FV= P*(1+r)^n 10 Cell reference D10/(100*G10) G9*G10 1+G14 G164G15 D9*G17 Rounded to 17 1.08 2.158924997 215892.4997 $215,892.50 19 20

Therefore, value of net amount to be saved as 85th birthday= $3995,548.96- $215,892.50= $179,656.46

Amount required to be saved every year till retirement is the annuity, arrived at $12,401.59 as follows:

Ε A B C D 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= 179656.46 No. of years (N) 11 Rate of interest (% p.a)=R= 8 No. of payments a year (t) 12 13 Calculations: 14 Value 0.08 15 Interest rate per period in decimals (r = 16 No. of periods (n)= 17 18 19 Present Value of future amount (PV)= Formula Cell reference R/(100*t) D11/(100*G11) N*t O'G11*G11 (1+r)= 1+G15 (1+r)^n= G174G16 FV/(1+r)^n= D10/G18 (1+r)^-n= G17A-G16 [1-(1+r)^-n]= 1-G20 FV*r= G19*G15 (FV*r)/(1-(1+r)^-n] G22/G21 Rounded to 20 1.08 2.158924997 83215.70248 0.463193488 0.536806512 6657.256198 12401.59359 $12,401.59 21 22 23 Periodical payment required (PMT)= 24

Hence the answer is the first option give, ie., $12,401.59