Question

Case narrative:

A young couple, both 25 years old, are planning to retire in 40 years at the age of 65. After they retire, they expect to live for an additional 20 years, until age 85. They plan to begin saving for retirement today and based on information from their financial planner, they think they will earn 8% on their investment compounded annually. They think they will earn 5% on their retirement savings after they retire.

1. If they begin at age 25 to save $5,000 each year for the next 40 years, how much will this couple have saved in their retirement account at age 65?

Answer: _____________________ (10 points)

2. If they begin at age 25 to save $5,000 per year for the next 40 years. Assume they will be selling their business in 5 years for $100,000, which will be deposit into their retirement account and leave there until age 65. How much will this couple have saved in their retirement account at age 65?

Answer _____________________ (10 points)

3. Referring to question # 1, how much will this couple have saved at age 65, if their earnings are compounded quarterly?

Answer: _____________________ (10 points)

4. Using the answer from question 1, if this couple is now age 65 and in good health and plans to travel and enjoy life. Since they are in good health chances of living until age 85 are quite good. If thy live until age 85 and are able to earn 5% on their retirement savings, what is the maximum amount they can withdraw each year, assuming they plan to spend all of their savings?

Answer_______________________ (10 points)

5. Using the answer from question #1, this couple expects to earn 5% on their retirement funds beginning at age 65. If they live for another 20 years, how much can they withdraw each year, if they decide to leave $200,000 in their retirement account for their heirs?

​Answer_______________________ (10 points)

Answer 1

1]

This is calculated using FV function in Excel with these inputs :

rate = 8%

nper = 40

pmt = -5000

FV is calculated to be $1,295,283

2]

This is calculated in two stages : first find the FV of the retirement account at the end of 5 years , then add $100,000 to the account, and find the FV of the retirement account at the end of 40 years

FV at end of year 5 is calculated using FV function in Excel with these inputs :

rate = 8%

nper = 5

pmt = -5000

FV is calculated to be $29,333

After selling the business for $100,000 the retirement account is worth $129,333. This earns 8% per annum compounded for 35 years. The ending value is calculated as $129,333 * (1 + 8%) ^ 35 ==> $1,912,333

3]

This is calculated using FV function in Excel with these inputs :

rate = 8% / 4 (converting annual rate into quarterly)

nper = 40 * 4 (40 years * 4 quarters per year)

pmt = -5000 / 4 (here it is assumed that the annual deposit is converted into 4 quarterly deposits)

FV is calculated to be $1,423,119

4]

This is calculated using the PMT function in Excel with these inputs :

rate = 5%

nper = 20 (20 years expected remaining life)

pv = $1,295,283

PMT is calculated to be $103,937

5]

This is calculated using the PMT function in Excel with these inputs :

rate = 5%

nper = 20 (20 years expected remaining life)

pv = 1,295,283

fv = -200,000 (amount required to be left at end of 20 years)

PMT is calculated to be $97,888