Question

Your best friend Dave just celebrated his 24th birthday and wants to start saving for his anticipated retirement. Dave plans to retire in 36 years and believes that he will have 25 good years of retirement and believes that if he can withdraw $125,000 at the end of each year, he can enjoy his retirement. Assume that a reasonable rate of interest for Dave for all scenarios presented below is 6.5% per year. This is an annual rate, review each individual question for more specifics on compounding periods per year. Because Dave is planning ahead, the first withdrawal will not take place until one year after he retires. He wants to make equal annual deposits into his account for his retirement fund.

D. We are now back to Dave starting his retirement investments one year from now (36 years to retirement). Suppose Dave's employer will contribute $2,500 to the account each year as part of the company's profit sharing plan. In addition, assume that Dave has a trust fund that will pay out $50,000 to him when he is 40 (16 years from now). What amount must he deposit annually under these assumptions to be able to make the desired withdrawals at retirement? To find the amount of the annual deposit now, it is easier to break down the components of the problem. Doing so for each of the following to find your friend's annual deposit, we get:

D1) Value of employer's contribution at retirement:

D2) Value of trust fund at retirement:

D3) Remaining amount that Dave needs at retirement:

D4) (Final answer) Amount to save each year under these assumptions:

Answer 1

D1]

Value of employer's contribution at retirement is calculated using FV function in Excel :

rate = 6.5% (rate of interest earned)

nper = 36 (number of annual contributions)

pmt = -2500 (annual contribution. This is entered with a negative sign because it is a cash outflow)

FV is calculated to be $332,742.36

А1 - о в f =FV(6.5%,36,-2500) с от | $332,742.36 І

D2]

Future value = present value * (1 + interest rate)^{number of years}

Value of trust fund at retirement = $50,000 * (1 + 6.5%)²⁰ (there are 20 years between the time the trust fund is received and retirement)

Value of trust fund at retirement = $176,182.25

D3]

Total amount required at retirement is calculated using PV function in Excel :

rate = 6.5% (rate of interest earned)

nper = 25 (25 years in retirement)

pmt = 125000 (annual withdrawal. This is entered with a negative sign because it is a withdrawal)

PV is calculated to be $1,524,734.59.

A3 fc с =PV(6.5%,25,-125000) D E в 3 $1,524,734.59!

Remaining amount Dave needs = $1,524,734.59 - $176,182.25 - $332,742.36

Remaining amount Dave needs = $1,015,809.97.

D4]

Amount to save each year is calculated using PMT function in Excel :

rate = 6.5%

nper = 36 (number of annual contributions)

pv = 0 (beginning amount is zero)

fv = 1015809.97 (Remaining amount Dave needs)

PMT is calculated to be $7,632.11

fx C =PMT(6.5%,36,0,1015809.97) D E F ($7,632.11)