Question

Today is your 40th birthday (this is beginning of period, i.e., time 0). You expect to retire at age 65 and actuarial tables suggest that you will live to be 85. You want to move to Hawaii when you retire (on your 65th birthday). You estimate that it will cost you $50,000 to make the move on your 65th birthday. Starting on your 65th birthday and ending on your 84th birthday (all withdrawals are at the beginning of the year), you will withdraw $40,000 for annual living expenses. Assume the interest rate is 4%. a. How much will you need to have saved by your retirement date? Use the timeline method to solve this. (Hint: the present value, on your 65th birthday, of those costs. Cost at time 65 should include $50,000 besides the annual cost of $40,000.) b. What is your savings calculated in (1) worth today? (Hint: Find the present value of the amount you will need at age 65. c. You start saving for this goal today till your 65th birthday. How much would you need to save each year? (Hint: Need to find the future value of an annuity when the payment starts at the beginning of the year.)

Please use excel and show all formulas

Answer 1

a]

Savings required by retirement date = $50,000 + (present value of 20 year annuity of  $40,000 at 4% rate of interest)

(present value of 20 year annuity of  $40,000 at 4% rate of interest) is calculated in Excel as below :

rate = 4%

nper = 20

pmt = 40,000

fv = 0 (there is nothing left in the savings account at the end of the 20 year annuity)

type = 1 (the annuity payment is at the beginning of each year)

PV is calculated to be $565,357.58

Savings required by retirement date = $50,000 + $565,357.58 = $615,357.58

b]

Savings worth today = present value of savings at 4% rate of interest =  $615,357.58 / (1 + 4%)²⁵ = $230,830.97

c]

Saving required each year is the amount required to be saved each year for 25 years, earning 4% so that the value at the end of 25 years is $615,357.58

This is calculated using PMT function in Excel as below :

rate = 4%

nper = 25

pv = 0 (current savings are zero)

fv = 615357.58

PMT is calculated to be $14,775.94

Annual saving required is $14,775.94