A long-established benchmark for retirement financial security is a $1,000,000-portfolio. Let’s consider age 65 retirees who have accumulated $1,000,000-portfolios.

We will make a few other simplifying assumptions in order to make the assessment below as simple to execute as possible:

• Annual portfolio returns are compounded once annually at the end of each year.
• Annual portfolio returns are normally distributed with a mean of 11.8% and a standard deviation of 18.1%.
• Withdrawals from a portfolio are made once a year at the beginning of each year.
• The retiree is no longer making any additional contributions to the portfolio.

You are to compare the results of 50 randomly generated retirees’ portfolio histories for 65-year-old retirees who start with a million dollars in their retirement account and who use two different withdrawal strategies. Simulate and record what happens each year from age 65 through age 95 (30 years for each retiree) of earnings and withdrawals, assuming a withdrawal strategy that pulls out 5% of the remaining portfolio value at the beginning of each year. Then randomly generate another 50 histories, assuming a withdrawal strategy that takes out 4% of the remaining portfolio value if the previous year’s return was negative, 6% if the previous year’s return was more than 20%, and 5% otherwise. Your output for each history should include columns each year for Age, Withdrawal Amount, Remaining Portfolio Balance, Random Return and Final Portfolio Balance for each year for each retiree.

(a) Use your output to estimate the Average Final Balance of such portfolios after 30 years when using these two strategies.

(b) Use your output to estimate the Proportion of Exhausted (no balance left) Portfolios within 30 years when using these two strategies.

(c) Statistically compare the two withdrawal strategies (i.e. withdrawing 5% each year regardless versus the layered strategy of withdrawing 4%, 5% or 6% depending on the previous year’s return) by completing an appropriate T-Test in Excel to determine if there is any significant difference in the mean Final Balances between these withdrawal strategies. (If so, indicate which strategy is significantly better than the other.)