portfolio stress testing excel

Portfolio Stress Testing Excel

Portfolio Stress Testing

A Portfolio Stress Test is an important consideration when considering the resiliency of your current assets in the light of future potential risks.

Future risks include known risks—market crashes, for one—and potential risks such as inflation and longevity. In addition, we live in an intermittently democratic society, and legislative risk (changes in taxes or social security) is inevitable. And what about spending shocks such as health care and long-term care costs?

Stress testing current asset allocation against changes in market value or inflation is essential. Beyond just your portfolio (as reflected by your asset allocation), you have a comprehensive retirement plan that integrates other income sources such as social security and pensions. Retirement plan stress testing can help alleviate some of the anxiety that results from wondering if your money will run out in retirement.

Will you have enough? Let’s stress test your retirement plan and find out!

What is a Portfolio Stress Test?

Stress testing is a technique that models a portfolio’s response to various hypothetical scenarios. Using either past results with Monte Carlo odds or straight historical returns, you can see how different asset allocations perform given certain assumptions. Stress testing a portfolio looks at diversification.

Stress testing a retirement plan is more complicated, as there are various additional inputs, such as social security, real estate, pensions, etc., that also have built-in assumptions. Retirement plan stress testing is defined as calculating the odds of success of the entire plan given certain possible futures.

Certain possible futures imply different future risks.

What Retirement Risks should be Considered?

There are many retirement risks.

If we look at common Retirement-Specific Risks, some risks are more straightforward to ponder than others. For instance, if you have inadvertent spending shocks or a local real estate crash, everything about your retirement plan can change. These, along with social risks such as cognitive decline and elder financial abuse, are difficult to plan for.

Other risks, such as sequence of return risk and market crashes, occur with regularity (but not predictability) and must be accounted for.

There are at least 18 retirement risks to think about if you plan to retire. Which of these can we stress test, and how difficult is it?


Stress Testing for Specific Retirement Risks


Risks and considerations for portfolio stress testing

Figure 1 (Risks and considerations for retirement plan stress testing)

Above, you can see different risks, how easy they are to simulate with stress testing, and suggested mechanisms for stress testing.

Those that are easy to model have variables simple to modulate on financial planning software.

Moderately complex simulations include those with not only variable time frames but also variable effects. So, for instance, it is difficult to plan for unknown costs at a time uncertain.

Difficult-to-plan for risks include public policy risk as there is no way to predict the future. In addition, there are social risks, such as frailty and financial elder abuse risk, which take multimodal planning to address. Finally, Long-Term Care risk is perhaps the most intractable future risk present today for a segment of retirees with inadequate resources to self-pay but who don’t desire to spend down to Medicaid.

Let’s look at a retirement plan on professional planning software and see what we can learn about portfolio stress testing.

Portfolio Stress Testing Excel

Professional planning software can be used to stress test a portfolio.


Portfolio stress test software

Figure 2 (Example of a retirement plan stress test)

Above, you can see a stress test based upon Monte Carlo probability.

At baseline, this plan has about an 80% chance of success. You can see it is most susceptible to inflation and taxes, and less susceptible to market crashes, social security changes, longevity, and increased health care costs. Portfolio stress testing software can give you suggestions of where you need to work on your retirement plan.


Portfolio Stress Testing Calculator

For this software portfolio stress test calculator, you are able to increase or decrease the amount of stress you place on the portfolio.


portfolio stress testing excel

Figure 3 (Portfolio stress testing stress levels)

As you can see above, you can modulate the percentage of equity crash, tax expenses, social security income, and health care costs. In addition, you can change the severity of inflation from the given assumption and how much longer you and your spouse will live. You can set up a program on excel to stress test your portfolio in a similar way.

What happens if we double the stress to the portfolio?

Double Portfolio Stress Test

Double Stress Test

Figure 4 (double stress test)

Above, we have doubled all the stress test stresses from figure 3. As you can see, the baseline is unchanged.

Doubling the stress levels helps uncover inflation risk. Taxes and sequence of return risk (via market crash) are also highlighted as issues of concern.


How to Stress Test your Portfolio

DIY will want to know how to stress test their portfolio. I think Merriman has an excellent resource for this. You can use his fine-tuning table to see what different asset allocations do historically. Calculate the stress your portfolio faces depending on your asset allocation. Plan for a bit of a stress test of your own when you look at them. I like to simplify them a bit.


portfolio stress testing excel

Figure 5 (Portfolio Stress Test excel)

For your selected portfolio’s asset allocation, you can see the annualized return and standard deviation. Also, since 1970, see the worst 3-, 6-, and 12-month returns, as well as the worst 3- and 5-year annualized returns.

Using this fine-tuning table, a DIY investor can see how well diversified his or her asset allocation is. This is a stress test for your portfolio you can do without software.


Portfolio Stress Testing Excel

To find portfolio stress testing software, your options are to create an excel spreadsheet or to use versions that can be found on line.

Portfolio stress testing software can be found here, otherwise ask around on sites for portfolio stress testing excel spreadsheet, and you might find someone who will share.


How to Stress Test your Retirement Plan

Stress testing is important in medicine and for the banks. Why not stress test your portfolio? Stress testing your portfolio can help determine if your asset allocation is well diversified for your risk tolerance. You can fund professional software or use portfolio stress testing excel spreadsheets.

Or, when you are closer to retirement, consider stress testing your retirement plan. Retirement plan stress testing is important to help anticipate any possible future gaps in the plan, and to ensure your plan will hold water no matter the risk.

Posted in Retirement Income Planning and tagged .