Question

You are planning to save for retirement over the next 30 years. To save for retirement, you will invest $1,850 per month in a stock account in real dollars and $610 per month in a bond account in real dollars. The effective annual return of the stock account is expected to be 10 percent, and the bond account will earn 6 percent. When you retire, you will combine your money into an account with an effective return of 8 percent. The returns are stated in nominal terms. The inflation rate over this period is expected to be 4 percent.

a. How much can you withdraw each month from your account in real terms assuming a 25-year withdrawal period?

b. What is the nominal dollar amount of your last withdrawal?

Please use excel if possible!

Answer 1

a). First, we need to find the real required return

Real Return = [(1 + Nominal Rate) / (1 + Inflation Rate)] - 1

For Bonds;

Real Return = [1.06 / 1.04] - 1 = 1.0192 - 1 = 1.92%

For Stock;

Real Return = [1.10 / 1.04] - 1 = 1.0577 - 1 = 5.77%

For Retirement Account;

Real Return = [1.08 / 1.04] - 1 = 1.0385 - 1 = 3.85%

We need to find the annuity payment in retirement. Our retirement savings ends and the retirement withdrawals begin, so the PV of the retirement withdrawals will be the FV of the retirement savings. So, we find the FV of the stock account and the FV of the bond account and add the two FVs.

FVA = Annuity x [{(1 + r)ⁿ - 1} / r]

Stock Account;

FVA = $1,850 x [{(1 + 0.0577 / 12)^30*12 - 1} / (0.0577 / 12)]

= $1,850 x [4.6216 / 0.0048] = $1,850 x 961.30 = $1,778,404

Bond Account;

FVA = $575 x [{(1 + 0.0192 / 12)^30*12 - 1} / (0.0192 / 12)]

= $575 x [0.7797 / 0.0016] = $575 x 486.55 = $296,796.17

So, the total amount saved at retirement is:

$1,778,404 + $296,796.17 = $2,075,200.17

Solving for the withdrawal amount in retirement using the PVA equation gives us:

Annuity = [PVA x r] / [1 - (1 + r)^-n]

= [$2,075,200.17 x (0.0385 / 12)] / [1 - {1 + (0.0385 / 12)}^-(25*12)]

= $6,651.28 / 0.6171 = $10,778.16

b). This is the real dollar amount of the monthly withdrawals. The nominal monthly withdrawals will increase by the inflation rate each month. To find the nominal dollar amount of the last withdrawal, we can increase the real dollar withdrawal by the inflation rate. We can increase the real withdrawal by the effective annual inflation rate since we are only interested in the nominal amount of the last withdrawal. So, the last withdrawal in nominal terms will be:

FV = PV(1 + r)^t

= $10,778.16 x (1 + 0.04)^{(30 + 25)}

= $10,778.16 x 8.6464 = $93,191.93