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~~~

what are the inputs used to solve this in excel

Answer 1

a). PVAD = P + P[{1 - (1 + r)^-(n-1)} / r]

= $50,000 + $50,000[{1 - 1.05^{-(25 - 1)}} / 0.05]

= $50,000 + $50,000[0.6899 / 0.05]

= $50,000 + $50,000[13.7986]

= $50,000 + $689,932.09 = $739,932.09

b). PVAD = P + P[{1 - (1 + r)^-(n-1)} / r]

$739,932.09 = P + P[{1 - 1.05^{-(38 - 1)}} / 0.05]

$739,932.09 = P + P[0.8356 / 0.05]

$739,932.09 = P + P[16.7113]

P[16.7113] = $739,932.09 - P

P = [$739,932.09/16.7113] - [P/16.7113]

P = $44,277.38 - P[0.05984]

P + P[0.05984] = $44,277.38

P = $44,277.38/1.05984 = $41,777.43

In Excel,

The function that we use for present value of an annuity due on an Excel spreadsheet is:
=PV(rate,N,pmt,fv,type) OR.
=PV(0.05,24,-50,000,1)
Type 0 is for an ordinary annuity while Type 1 is for an annuity due.