portfolio stress testing excel

Portfolio Stress Testing

Portfolio Stress Testing

A Portfolio Stress Test is an important consideration when considering the resiliency of your current assets in 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 market value or inflation changes is essential. Beyond your portfolio (as reflected by your asset allocation), you have a comprehensive retirement plan integrating other income sources such as social security and pensions. Retirement plan stress testing can help alleviate some anxiety 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 past results with Monte Carlo odds, you can see how different asset allocations perform given certain assumptions.

Stress testing a retirement plan is more complicated, as various additional inputs, such as social security, real estate, pensions, etc., 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.

Regarding 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 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.

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 simple variables 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 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.

Look at a retirement plan on professional planning software and see what we can learn about portfolio stress testing.

Portfolio Stress Testing

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 on Monte Carlo probability.

At baseline, this plan has about an 80% chance of success. 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 suggest where you need to work on your retirement plan.


Portfolio Stress Testing Calculator

With this software portfolio stress test calculator, you can increase or decrease the 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 crashes, 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.

What happens if we double the stress of 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 when you look at them. I like to simplify them a bit.


portfolio stress testing excel

Figure 5 (Portfolio Stress Test)

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

Using this fine-tuning table, a DIY investor can see how 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, you can create an Excel spreadsheet or use versions found online.

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 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.

Posted in Retirement Income Planning and tagged .