Question

Your friend is celebrating her 30th birthday today and wants to start saving for her anticipated retirement at age 65. She wants to be able to withdraw $80,000 from her savings account on each birthday for 25 years following her retirement; the first withdrawal will be on her 66th birthday. Your friend intends to invest her money in the local credit union, which offers 4.5 percent interest per year. She wants to make equal annual payments on each birthday into the account established at the credit union for her retirement fund.

a. If she starts making these deposits on her 31st birthday and continues to make deposits until she is 65 (the last deposit will be on her 65th birthday; use 35 years), what amount must she deposit annually to be able to make the desired withdrawals at retirement?

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

c. Suppose your friend’s employer will contribute $12,000 to the account every year as part of the company’s profit-sharing plan. In addition, your friend expects a $100,000 distribution from a family trust on her 55th birthday, which she will also put into the retirement account. What amount must she deposit annually now to be able to make the desired withdrawals at retirement?

Complete all parts of the problem in Excel using financial functions. (In other words, do not input the numbers to calculate the answers.) You may want to complete the problem on paper first, then input and re-calculate in Excel. Note that Excel has a built-in financial calculator so PV, PMT, FV, etc. all work to calculate the amount.

Answer 1

a]

First, 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 = 4.5% (rate of return earned)

nper = 25 (number of years in retirement)

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

PV is calculated to be $1,186,256.72

Next, 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 = 4.5% (rate of return earned)

nper = 35 (number of years until retirement)

pv = 0 (amount currently saved is zero)

fv = 1186256.72 (required amount at retirement)

PMT is calculated to be $14,555.90

Amount to deposit annually = $14,555.90

b]

Amount to deposit is calculated using PV function in Excel :

rate = 4.5% (rate of return earned)

nper = 35 (number of years until retirement)

pmt = 0

fv = 1186256.72 (required amount at retirement)

PV is calculated to be $254,160.77

c]

Future value of employer's contributions at retirement is calculated using FV function in Excel :

rate = 4.5% (rate of return earned)

nper = 35 (number of years until retirement)

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

FV is calculated to be $977,959.42

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

rate = 4.5% (rate of return earned)

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

pmt = 0

pv = -100000

FV is calculated to be $155,296.94

Total future value of employer's contributions + trust distribution = $977,959.42 + $155,296.94 = $1,133,256.36

Balance amount to accumulate at retirement = $1,186,256.72 - $1,133,256.36 = $53,000.36

Next, 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 = 4.5% (rate of return earned)

nper = 35 (number of years until retirement)

pv = 0 (amount currently saved is zero)

fv = 53000.36 (Balance amount to accumulate at retirement)

PMT is calculated to be $650.34