Question

~~~In Excel~~~

Question 1: Today is your 22nd birthday (this is beginning of period, i.e., time 0). You expect to retire at age of 60 and actuarial tables suggest that you will live to be 85. Starting on your 60th birthday and ending on your 84th birthday (all withdrawals are at the beginning of the year), you will withdraw $50,000 for annual living expenses. Assume the interest rate to be 5%.

Calculate the amount needed on your 60^th birthday to pay for your annual living expenses over your retirement using the timeline method. (14 points)

How much do you need to save per year if you start saving today (i.e, at the beginning of year) and save until you are 59 (i.e., you make the last payment on your 59^th birthday)? (6 points)

~~~In Excel~~~

Answer 1

Annual Withdrawals = $ 50000, Retirement Age = 60 and Withdrawal Limit Age = 84

Withdrawal Tenure = (84-60) + 1= 25 years

Interest Rate = 5 %

Amount Needed on 60th Birthday = PV of post-retirement Annual Withdrawals = 50000 x (1/0.05) x [1-{1/(1.05)^(25)}] x (1.05) = $ 739932.0897

Let his annual savings be $ K

The total future value of the annual savings should be equal to the PV of the post-retirement annual withdrawals.

Therefore, K x (1.05)^(28) + K x (1.05)^(27) + ...............+ K x (1.05) = $ 739932.0897

(1.05) x K x [(1.05)^(28) - 1 / (1.05 -1)] = 739932.0897

K = $ 12066.19973 approximately.