Question

This is a classic retirement problem. A friend is celebrating her birthday and wants to start saving for her anticipates retirement. She has the following years to retirement and retirement spending goals:

Years until retirement: 30

Amount to withdraw each year: 90,000

Years to withdraw in retirement: 20

Interest Rate: 8%

Because your friend is planning ahead, the first withdraw will not take place until one year after she retires. She wants to make an equal annual deposit into her account for her retirement fund.

a) if she starts making these deposits in one year and makes her last deposit on the day she retired, what amount must she deposit annually to be able to make the desired withdrawals at retirement?

b) Suppose your friend inherited a large sum of money. Rather than making equal annual payments, she has decided to make one lump sum deposit today to cover her retirement needs. What amount does she have to deposit today?

c) Suppose your friend's employer will contribute to the account each year as part of the company's profit-sharing plan. In addition, your friend expects a distribution from a family trust several years from now. What amount must she deposit annually now to be able to make the desired withdrawals at retirement?

Employer's annual contribution: $1,500

Years until trust fund distribution: 20

amount of trust fund distribution: 25,000

In order to answer any of these questions, first, we need to know how much your friend will need when she is ready to retire. Since this amount will be the same for each of the parts of the problem, we will solve for this amount now, which will be:

a) amount needed at retirement: ?

b) amount to save each year: ?

c) lump sum deposited today: ?

d) value of employers' contribution at retirement: ?

e) value of trust fund at retirement: ?

f) amount to save each year now: ?

Answer 1

a]

We calculate the amount required at retirement to enable the yearly withdrawals during retirement. The amount required at retirement is calculated using PV function in Excel :

rate = 8% (rate of return earned)

nper = 20 (number of years in retirement)

pmt = -90000 (yearly withdrawal. This is entered with a negative sign because it is a withdrawal)

PV is calculated to be $883,633.27

b]

We calculate the yearly saving required to accumulate the required amount at retirement. The yearly saving required is calculated using PMT function in Excel :

rate = 8% (rate of return earned)

nper = 30 (number of years until retirement)

pv = 0 (amount currently saved is zero)

fv = 883633.27 (required amount at retirement)

PMT is calculated to be $7,800.21

c]

Lumpsum to deposit today is calculated using PV function in Excel :

rate = 8% (rate of return earned)

nper = 30 (number of years until retirement)

pmt = -0 (yearly payment is zero)

fv = 883633.27 (required amount at retirement)

PV is calculated to be $87,813.12

d]

value of employers' contribution at retirement is calculated using FV function in Excel :

rate = 8% (rate of return earned)

nper = 30 (number of years until retirement)

pmt = -1500 (Annual contribution. This is entered with a negative sign because it is a deposit)

FV is calculated to be $169,924.82

e]

value of trust fund at retirement is calculated using FV function in Excel :

rate = 8% (rate of return earned)

nper = 10 (number of years from distribution until retirement)

pmt = 0 (Annual contribution is zero)

pv = -25000 (amount of trust fund distribution)

FV is calculated to be $53,973.12

f]

Total of employers' contribution and trust fund at retirement =  $169,924.82 + $53,973.12 = $223,897.94

Remaining amount to accumulate =  amount required at retirement - Total of employers' contribution and trust fund at retirement

Remaining amount to accumulate =  $883,633.27 - $223,897.94 = $659,735.33

We calculate the yearly saving required to accumulate the required amount at retirement. The yearly saving required is calculated using PMT function in Excel :

rate = 8% (rate of return earned)

nper = 30 (number of years until retirement)

pv = 0 (amount currently saved is zero)

fv = 659,735.33 (remaining amount to accumulate)

PMT is calculated to be $5,823.77