Question

You are 40 years old and want to retire at age 60. Each​ year, starting one year from​ now, you will deposit an equal amount into an investment account that pays 4.6​% interest. The last deposit will be on your 60th birthday. On your 60th birthday, you will switch the accumulated savings into a safer bank account that pays only 4.2​% interest. You will withdraw your annual income of ​$130,000 at the end of that year​ (on your 61st ​birthday) and each subsequent year until your 85th birthday. On that birthday you want to give $450,000 to your children. How much do you have to save each year to make this retirement plan​ happen?

Answer 1

The question is solved in 3 parts -

Part 1: Calculating the present value of annual withdrawals after retirement

Annual withdrawal = $130,000

Time = 25 (85th year - 60th year)

Rate = 4,2%

Present value = Using excel PV function

Syntax: PV(rate, time, annual withdrawal, ,0)

PV(0.042, 25 , 130000, ,0) = 198861.15

So, present value of annual withdrawal = $1,98,861.15

Part 2: At the end of the 85th year, an amount of $450,000 is given to the children. Calculation of its present value

Present Value = 450000/(1+0.042)^25 = $160886.48

So, the retirement at the end of 60th year = Present values of [Part 1 + Part 2] =$1,98,861.15 + $160886.48 =$3,59,747.63

Part 3: Calculation of equal amount that need to be invested to achieve the retirement amount

Time required = 20 (60th year - 40th year)

Interest = 4.6%

Future value = $3,59,747.63

So, annuity required per year to achieve the future value = Using excel PMT function

PMT(rate, time, ,future value,0)

PMT(0.046,20, , 359747.63,0) = 11,347.78

So, annuity or equal amount required to deposit = $11,347.78