Spreadsheets and SOR Risk

People planning retirement sometimes insert a minimum successful portfolio rate of return into a spreadsheet. The thought process goes like this. "If my spreadsheet of retirement finances works when I plug in say, a 2% average expected rate of return for my portfolio, then I know my plan is safe if I earn at least that much. Surely I can earn 2%."

But that strategy won't work when sequence of returns (SOR) risk is involved. Here's why.

The terminal value of a retirement portfolio (it's balance at the end of retirement) that we spend down using a sustainable withdrawals (SW) strategy isn't solely a function of the rate of portfolio return. It is a function of the withdrawal rate, investment returns, and the sequence of those returns.

For every average rate of portfolio return, there is some probability that the portfolio will be depleted prematurely and some probability that it will fund at least thirty years depending on the sequence of the returns. If the portfolio enjoys a high average rate of return over the 30-year period, the probability that it will be derailed by SOR risk is quite small. Likewise, if the average return is quite low over that period, the portfolio will probably fail, perhaps even without the nudge of a poor sequence of returns.

But in the range of average returns that you are most likely to experience, say between about 2% a year and 6%, SOR risk will often determine failure or success.

To illustrate, let's look at historical returns using the Robert Shiller data and the spreadsheet from the Retire Early Home Page and see what the historical results would have been for 2% real rates of return over past 30-year periods with a 4% withdrawal rate.

Historical stock market data is very limited. Shiller's data back to 1871 provides 142 years of data, but that is less than five unique 30-year periods. We try to stretch this number in a somewhat-flawed statistical manner by using rolling 30-year periods of historical data, but there are still only 112 of those. That is a relatively small sample for our purposes and no periods experienced 2% rates of return. Nonetheless, the terminal portfolio values for a $1,000 portfolio of 50% equities and using a 4.5% withdrawal rate with historical returns data can be shown as follows.

There were no periods with real 50% equity portfolio returns of only 2%, the rate of return I was hoping to investigate. There were so few periods in the sample, in fact, that I had to increase the withdrawal rate from 4% (the one I actually wanted to investigate) to 4.5% just to show a few more failures. Regardless, you can see that some portfolios historically failed with 4.5% rates of return while some successfully funded 30 years of retirement with only a 3.5% average return due to sequence of returns (SOR) risk. In fact, during this period of historical data, portfolios would have failed with real rates of return as high as 4.4% a year while others succeeded with returns as low as 2.8%.

Because there is such a small sample of historical data to work with, we sometimes use Monte Carlo simulation to test hypotheses. (A reader recently complained that I should use historical data more often, a strange complaint given that I very rarely use anything else, but this is an example of when we really need simulation to explain a point because the historical data is inadequate.)

I used the simulation from The Implications of Sequence of Return Risk to generate a similar graph. This simulation provided not only several scenarios with 2% portfolio returns, but produced a number of failed scenarios with a 4% withdrawal rate. The simulation provided 10,000 unique 30-year scenarios.

Notice in this graph that I rounded rates of return along the x-axis to the nearest one percent. Instead of producing a cloud of outcomes as in my previous post, this chart displays a vertical bar (actually a cluster of points) of the terminal portfolio values (TPV's) that demonstrate the range of outcomes for each rounded rate or return. (In effect, I scrunched all the outcomes from portfolio returns of 1.5% to 2.5% into a vertical bar above "2%", for example.)

Also note the yellow marker inside each vertical bar (double-click the chart to enlarge). That point marks the terminal portfolio value that would have resulted from a 30-year sequence of identical returns – in other words, it's the expected TPV with no SOR risk. This is the highly unrealistic scenario that would be generated by a spreadsheet or consumption-smoothing models that don't randomize returns.

Using spreadsheets and other tools that don't randomize returns, the yellow markers would seem to indicate that any return of 2% or greater would result in a retirement plan using the SW strategy successfully funding 30 years. But in reality, only 66% of the simulated scenarios with returns from 1.5% to 2.5% succeeded. The spreadsheet looks fine, but there is actually about a one in three chance of failure with this rate of return. And as we saw above, sometimes a lower return would have succeeded and sometimes a higher return would have failed.

What if I plug in 1% instead of 2% for my portfolio's rate of return and my spreadsheet still works? That's gotta be a good sign, right?

You've actually made the outcome less predictable.  Scenarios with 1% average returns in the simulation had about double the SOR risk of 2% returns. If you're looking for the lowest rate of return for your spreadsheet that will very likely be successful, insert a higher rate of return, not a lower one.

That's why we can't plug a low average rate of return into a spreadsheet or other planning tool that doesn't randomize returns and gain confidence from the results that our plan will definitely work.

Often it will. Sometimes it won't.

If you plan to implement a SW strategy, be aware that unless you randomize the returns in your spreadsheet, you won't see SOR risk. I'm not suggesting that you shouldn't plan for retirement using a spreadsheet or E$Planner, only that you should do so carefully.

The Implications of Sequence of Returns Risk

Some time ago, I wrote a series of posts on Sequence of Returns (SOR) risk. The focus of those posts was to explain what SOR risk is mathematically and where it comes from (periodically buying or selling from a volatile portfolio of stocks and bonds).

If you plan to fund retirement by selling off assets from such a portfolio, it is vital that you understand what SOR risk is, its source, and how it impacts your finances after you retire. I won't repeat the first two points about definition and source here, so you might want to review my earlier posts before proceeding, but I do want to expand the discussion regarding the implications of SOR risk.

As I mentioned, SOR risk is a result of periodically buying or selling from a volatile portfolio of stocks and bonds, as retirees do who implement a systematic withdrawals (SW) strategy. They're going to sell stocks every year for perhaps 30 years or more and with no idea what future stock prices will be when they eventually sell. That uncertainty is SOR risk.

In contrast, if they bought the exact same portfolio and held it intact for thirty years, the sequence of returns would make no difference in the portfolio's terminal value, whatsoever.

SOR risk is also present in the accumulation or "saving" phase of financing retirement, but its impact appears to be less severe than during the spending phase and it is more controllable. We sell assets in retirement because we have an urgent need to spend the money, even if we are forced to accept a low price, but we can easily postpone purchasing stocks during the accumulation phase when prices are high by leaving our savings in cash for a while.

Selling a constant dollar amount every year exacerbates SOR risk because it means that we will sell more shares when prices are low, which is, of course, the opposite of what we would prefer. Selling a percentage of remaining portfolio balance every year instead would help us sell fewer shares when prices are lower, but would leave us with an unpredictable income stream. (I still think that is the preferable approach. When you have less wealth, you should spend less.)

Bonds held to maturity, annuities, pensions and Social Security benefits, on the other hand, are not subject to SOR risk. The portion of our retirement spending provided by those sources is not affected by stock market price variance. In fact, even bond funds that do not hold their bonds to maturity but have low volatility will have only a little SOR risk.

So, the first way that SOR risk impacts retirement finances is that it is only present when we buy or sell from a volatile portfolio. Retirees with little or no stock exposure will have little or no SOR risk. The second way it impacts retirement finances is that it has a greater impact during spending than during accumulation. A poor sequence of returns when we are saving reduces our accumulated wealth, but during the spending phase a poor sequence can take us out of the game entirely.

In finance, risk is often defined as the uncertainty of outcomes. Unlike the everyday connotation of the term "risk", uncertainty can be both good and bad. A risky portfolio of stocks and bonds has greater potential for both gains and losses than a less-risky portfolio. Risk is where we make our money on investments. Risk-free investments do well to cover inflation.

In the same sense, sequence of returns risk can leave you with more wealth or less wealth. The most wealth during the spending phase comes when a sequence of returns is ordered from the best return first to the lowest return last. The least wealth comes when that same sequence is ordered from worst return first to best last.

In the saving phase, the opposite is true. We want the best returns later in life when our portfolios are larger, and we mind losing money less when we are young and have little to lose.

SOR risk is completely unpredictable. Diversifying your stock assets won't reduce this risk and the market cannot compensate you for it. In that sense, it's a "bad risk". A good risk would be one that we can be compensated for taking.

Much is made, and rightly so, of the fact that big portfolio losses early in retirement can devastate your portfolio. Wade Pfau estimates that your returns in the first decade of retirement explain about 80% of portfolio survival for thirty years. I have seen simulated scenarios with correlations near 90%. This is a result of SOR risk and is a direct result of my previous statement that "the least wealth comes when that same sequence is ordered from worst return first to best last."

An important consequence of sequence of returns risk is that our terminal portfolio value, what we have remaining to leave to heirs, can be dramatically different even with the same portfolio returns.

Following is a scatter plot of terminal portfolio values (TPV) versus portfolio returns. I created this from a simulation of 10,000 scenarios of annually spending 4% of initial portfolio value for 30 years from a 50% stock portfolio with a geometric mean return of 5.6% and a standard deviation of 11%. Each blue dot on the chart represents a scenario's TPV when the average return equalled amounts along the x-axis. (I let the portfolio values run negative, even though your broker won't, because cutting off the portfolio values at zero graphically hides some information.)

Notice that the very bottom of the blue crescent area of terminal portfolio values drops below the zero portfolio value line (x-axis). In the following chart, I zoomed in on the area between 1% and 6%, where portfolios both succeed and fail.

If you look directly above 4%, for example, you will see that many portfolios had TPV's greater than zero and many showed negative TPV's even though they all experienced 4% average growth annually without spending.

Again, SOR risk results in a broad range of outcomes even with the same market return.

SOR risk can exacerbate other risks, as well.

Imagine that you are retired and have a mortgage, so you have foreclosure risk. If your mortgage payment is paid from the sales of stocks in your retirement savings portfolio, then a poor sequence of returns could jeopardize your ability to make those payments. Your home is now subject to a greater risk of foreclosure than if your entire mortgage were paid from pension benefits or an annuity, for example.

The last thing I want to point out about SOR risk in this post is that, as the chart above shows, some portfolios failed with a 6% portfolio return, while others funded thirty years with portfolio returns of only 1% or 2%. The former had a poor sequence of annual returns while the latter had a fortunate sequence.

So, those are eight things you need to know about sequence of returns risk (I italicized them), and here's one more.

If stock investing weren't risky enough, once we start spending from a portfolio and exposing ourselves to sequence of returns risk, our financial success in retirement is not solely a function of the rate of return we earn. It also depends on the sequence of returns. An SW portfolio can fail when average returns from the market do relatively well and survive when average market returns are relatively modest.

I'll pick up there in my next post.