Question

Problem 2. Mr. and Mrs. Smith are planning for retirement which they hope to begin five years from today. They expect that retirement will last for 25 years. During each of the years in retirement the Smith's want to withdraw $90,000 annually from their retirement investment portfolio. When the Smith’s retire they will naturally become more cautious and the plan is to move the portfolio investments to less volatile investments with an average annual compounded expected rate of return of 3%. Prior to retirement the Smith's are using a 6% compounded annual rate of return on all invested monies as a projection. As of right now the Smiths’ have a portfolio that equals $1,000,000. The expectation is that this portfolio will continue to earn the compounded annual 6% return. Over the next five years the Smiths’ expect to add $25,000 annually to the retirement portfolio. These monies are expected to earn an average annual rate of 6%.

a. How many dollars do the Smith's need in their investment portfolio at the end of 5 years so that they will be able to withdraw the desired amount per year at the end of each of the retirement years noted above to supplement their cost of living?

b. How much is the current portfolio amount plus the additional expected investments and earnings projected to grow to be by the end of the remaining five years of the savings/investment period. In essence, how much is projected to be in the retirement portfolio just as retirement begins?

If the answer to part “b” is higher than the answer to part “a” then the Smith’s are comfortable with the existing plan. If “a” is higher than “b” then a (or several) changes are needed.

c. At the end of the day the Smith’s wanted to be able to withdraw the target amount identified above during each year (annually) of retirement. They are expecting to actually have the amount you calculated in part “b”. Given the part “b” amount you calculated above how much could the Smith’s withdraw annually during the retirement years so that the balance is fully used right after the final retirement withdrawal? ______________________

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

nper = 25 (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 $1,567,183.29

b]

Value of portfolio at end of 5 years is calculated using FV function in Excel :

rate = 6% (rate of return earned)

nper = 5 (number of years until retirement)

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

pv = -1000000 (Current value of portfolio. This is entered with a negative sign because it is like a cash outflow)

FV is calculated to be $1,479,152.90

c]

Yearly withdrawal is calculated using PMT function in Excel :

rate = 3% (rate of return earned)

nper = 25 (number of years in retirement)

pv =  1479152.90 (Value of portfolio at retirement)

PMT is calculated to be $84,944.60