Question

Derek plans to retire on his 65th birthday. However, he plans to work part-time until he turns 75.00. During these years of part-time work, he will neither make deposits to nor take withdrawals from his retirement account. Exactly one year after the day he turns 75.0 when he fully retires, he will begin to make annual withdrawals of $128,885.00 from his retirement account until he turns 94.00. He he will make contributions to his retirement account from his 26th birthday to his 65th birthday. To reach his goal, what must the contributions be? Assume a 5.00% interest rate.

Answer 1

Derek would make contributions from 26 year to 65 year. All at the beginning of year. There are 40 periods in between.

From the year 66 to year 75, he will nether draw nor add any amount. There are 10 periods.

Derek would draw $128885 from year 76 to 94, all at the end of year. There are 19 periods

We will use PV function in excel -

We start by calculating the PV of his retirement account at the start of year 76

Here, rate = 5%, NPER = 19, PMT = Drawings = -128885, FV =0, Type = 0 (Drawings at the end of period)

1. PV of his retirement account at the start of year 76 = PV(5%,19,-128885,0,0)= $1,557,616.58

This is same value as at end of year 75

2. We now calculate the Present value of his retirement fund at the start of year 66

From the year 66 to year 75, he will nether draw nor add any amount. There are 10 periods.

Here, rate = 5%, NPER = time = 10 years, PMT = 0, FV = 1557616.58, Type = 1 or 0 ( It wouldn't matter as no drawings or deposits)

Present value of his retirement fund at the start of year 66 = PV(5%,10,0,1557616.58,0)= -$956,241.60 (The negative sign is here, because in excel Fv & PV have different sign)

This value is same as the value at the end of year 65

3. We now compute the equal annual deposits to be made from year 26 to year 65.

Derek would make contributions from 26 year to 65 year. All at the beginning of year. There are 40 periods in between.

We will make use of PMT function to calculate the value.

Here, rate = 5%, NPER = time = 40 years, PV = 0, FV = 956241.60, Type =1 (Deposits done at the start of year)

Equal annual contributions to be made from year 26 to year 65

= PMT(5%,40,0,956241.60,1) = $7,538.97

Deposits at the start for 40 period No drawings/ deposits for 10yr Drawings at end for 19 yrs

26 ----------------------------------------------- 65--66------------------------------------------ 75---76 ----------------------------------94