Monday, August 25, 2014

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.


  1. Hi Dirk. It would seem reasonable then that one way to reduce sequence of return risk (SOR) would be to allocate less on one's portfolio to stocks. For example, instead a a 50/50 allocation maybe 30/70. I realize that this would require one to have more assets for a given income need in retirement, but maybe better to accumulate more assets (something one has at least some control over) than less and hope for a fortunate sequence of returns (something one has no control over). Does this logic seem sound? Of course determining the right asset allocation to both meet income needs and minimize sequence of returns risk for a given level of assets is beyond my knowledge base, but maybe not yours. Thanks for post. Brad

    1. Brad, as I mentioned in my last response, Bengen found that SWR increases as equity increases from zero to 45% of the portfolio, flattens out, then drops off at 60% equity. So, portfolio allocation will affect SOR Risk, but the biggest impact by far is the withdrawal rate. Lowering the withdrawal rate, of course, is quite expensive. A 4% withdrawal rate requires that you accumulate 25x your annual retirement shortfall. Lowering it to 3.5% requires that you accumulate 29x that amount. A very safe 2% withdrawal requires that you accumulate 50x your annual shortfall.

    2. This comment has been removed by the author.

  2. I feel like I'm missing something here. Putting 1% in your spreadsheet does not actually affect reality. So how and why would putting that into your spreadsheet increase your risk of running out of money?

    It seems like the simplest thing to do would be to model the worst case. At least that's something Excel would be capable of.

    1. Well, it is certainly true that putting anything into your spreadsheet does not actually affect reality and changing numbers in your spreadsheet won't increase your risk of running out of money -- unless you act inappropriately on the results of the spreadsheet. That isn't, however, what I said.

      When you plug 2% into your spreadsheet you are showing that a 2% ROR will work (or won't) with no sequence of returns risk. Of course, if you are spending from a portfolio, there is SOR risk. Due to SOR risk, your portfolio will actually fail about 30% of the time with 2% returns and 4% withdrawals.

      If you change that to 1%, even though your spreadsheet shows that it will still work "on average", it actually fails about 60% of the time, due to SOR risk.

      The point being that Excel isn't capable of modeling the worst case unless you randomize returns. Otherwise, you're ignoring SOR risk.

      Hope that helps. Thanks for writing!

  3. Steve, perhaps this is a better way to explain?

    Which would you consider the worse case, experiencing a 4% rate of return on your portfolio and running out of money in retirement, or experiencing a 3% rate of return and successfully funding 30 years? I'm assuming you would consider running out of money to be the worse case, even though your portfolio return was higher in that scenario.

    So, which rate of return would you plug into a spreadsheet as the worst case, 4% or 3%?

    You might be tempted to say 4% now, but there are 3% scenarios that also fail.

    The point is that the worst case isn't a rate of return, it's depleting your savings. And that can happen across a fairly broad range of returns if the sequence of those returns is poor enough.

    Portfolio failure is usually a combination of poor to average portfolio rates of return and an unfortunate sequence of returns, meaning the worst returns arrive in the first decade of retirement. That isn't something you can plug into a spreadsheet without randomizing returns.

    Does that help?

  4. thanks Dirk
    it would be interesting to see how those charts change if one was to have 10 years of TIPS ladder to begin retirement (something you suggest considering) and not touching stocks for 10 years if poor returns. I wonder how much the less than 1/3 failure rate would occur with 2% returns. It seems all studies I have seen showing SOR risks use all nominal bonds

  5. Derek, you wouldn't have any SOR risk unless and until you started selling down the stock portfolio. And a spreadsheet calculation would be fine for that part of the plan. Even if you started selling the stock portfolio regularly after 10 years, you would have reduced lifetime SOR Risk significantly. You would then be looking at perhaps a 20-year remaining retirement, and as the SWR studies show, less time to spend means less SOR risk.

  6. I also am likely missing the point. If one does a Monte Carlo simulation with 10,000 scenarios, wouldn't lots of those scenarios reflect both poor return and poor SOR?

    If one has a 95% probability with a Monte Carlo simulation that one wouldn't run out of money, how does the 5% chance that you might run out relate to your discussion of poor returns & poor SOR?

    Can you be more concrete about how to think about this? What should feel 'safe' enough?


    1. The answer to your first question is “yes, lots would”. And the answer to your second question is that the 95% prior probability that you will run out of money is unaffected by this discussion. 95% is the estimate of prior probability before retirement begins and expecting your returns will be something like historical returns were; this post is about the conditional probability of failure at the end of retirement after you know that your personal returns were well below those expectations. We’re looking for a worst case.

      This is a discussion, as the title says, about SOR risk and spreadsheets, and specifically about attempting to find a single rate of return (a “hurdle rate”) to plug into the spreadsheet above which all scenarios always successfully fund 30 years of retirement and below which all scenarios fail to. In other words, it’s about trying to mathematically identify a worst-case scenario in a spreadsheet in terms of the portfolio’s rate of return.

      There are average rates of portfolio return below which you are unlikely to succeed even with a very favorable sequence of returns. There are rates of return above which you are likely to succeed even with a poor sequence of returns. But in between, there is a large range of rates of return that may succeed and may fail, depending on the (unpredictable) sequence of returns.

      In a concrete example, a client told me that his spreadsheet worked if he only earned 2% a year. His spreadsheet assumed a constant 2% return every year for 30 years, meaning no SOR risk. I ran the numbers to show there is a one in three chance that his spreadsheet is misleading him. There are lots of possible outcomes with an average rate of return of 2%, some good, some not so much. Without randomizing returns in some way, a spreadsheet won’t show this risk. (This is the post’s entire and sole point.)

      Since SOR risk is unpredictable, it is not possible to pick a rate of return (like 2%) in that middle range of returns and say, “Here’s where failure starts.” The edge of portfolio failure isn’t a sharp line of portfolio returns, it’s a blurry cloud of probability.

      I believe the answer to your last question, when should you feel safe enough, doesn’t have an answer in the form of a non-randomizing spreadsheet or a single hurdle rate of portfolio return. But you’re also asking how to think about this. The way to think about it is that there isn’t a single rate of return that separates success from failure. SOR risk muddies the line.

      I think you should feel safer with a monte carlo simulation, which would tell you the probability of succeeding, or using a worst-case historical series of returns, though future returns might be worse, or (and I prefer this one) ensuring that you have a floor of safe income in case you lose the future-rate-of-return bet.

      Thanks for writing, and please respond if I’m still being unclear.

    2. Great article. So there is really no such thing as a 100% SWR. We just take our best shot at a SWR and hope that Lady Luck smiles on us.

    3. James, there is an important kernel of truth in your first statement, but we can do more than hope.

      It is true that, in theory, there is no 100% safe withdrawal rate. No one can promise you with certainty that your stock portfolio won't lose most or all of its value. People go broke in the stock market. That's why we call stocks and many bonds "risky assets".

      This is also why most economists recommend that you build a floor of safe assets like annuities, TIPS bonds, Social Security benefits and pensions, for example, to meet your basic needs in the unlikely but possible event that the market and spending depletes your stock portfolio. (I wrote about floor and upside strategies at

      A major determinant of SWR is your spending rate. If you choose to spend 3% to 4% of your remaining balance annually, you cannot achieve a 100% level of safety over 20 to 30 years. 90% to 95% is about the best case you can argue. I personally find a 1-in-5 to 1-in-10 chance of going broke unacceptable, but that's my risk tolerance level and may not be yours.

      If you were to spend 1% a year, on the other hand, you could come quite close to 100%, but again, a stock portfolio will never be 100% safe. It would be expensive, too. If you needed to spend $10,000 a year from your portfolio in retirement and chose to spend 4%, you would need to save $250,000. At 3%, which is becoming the consensus SWR, you would need to save $333,333. To spend only 1%, you need to save a million dollars.

      In a very real sense, we do "just take our best shot at a SWR and hope that Lady Luck smiles on us" when we spend down a volatile retirement portfolio. But I recommend that you take several steps to reduce the odds of being unlucky, like diversifying into both risky and riskless assets, building a floor of safe income before you invest in a risky portfolio, and taking the smallest withdrawal you can.

      Thanks for writing, James.

  7. Dirk,
    great article.
    According to Kitces and Pfau I was under the impression that SOR risk could be virtually eliminated by picking a WR of say 3.5% and applying it to the previous year end balance (in other words not inflation adjsted) overlaid with a glide path strategy of starting retirement w/ a 25% equity portfolio that would increase 2% a year for the first 10 years of retirement.
    would love to get your opinion..

    1. Ooh, I love this question!

      You have to start by understanding what SOR risk is. If you buy and sell stocks to save for and fund retirement annually for 30 years, there are 30 stock sales in your future when you retire. You don't know what stock prices will be in any of those years. Those unknown prices at which you will sell are SOR risk. So, the way to reduce SOR risk after retirement is by doing anything you can to reduce the amount of stocks you need to sell.

      You could do this by selling less stock each year by reducing your spending, by decreasing your equity allocation so you sell fewer stocks and more bonds, by building a floor of non-equity income generating assets, or by any other method -- if you sell less stock in the future at prices you can't predict, you will have less SOR risk.

      Does reducing your WR from 4% to 3.5% reduce SOR risk as Michael and Wade suggest? Of course. Will it reduce it enough to save your portfolio? Time will tell.

      Does skipping inflation adjustments lower SOR as they suggest? Yes. Lowering spending of any kind will lower SOR whether you skip an inflation adjustment of skip a latte. As I wrote sometime back, withdrawing a percentage of current portfolio value instead of initial value also helps because it updates the information in your decision process with your new balance. Never ignore new information.

      Will a rising glidepath reduce SOR risk? Of course, because you will be selling fewer stocks early in retirement. (Raising your equity allocation later in life to make up for the lower early allocation only works, I would point out, if you live long enough for that to happen. For retirees with an average life span or less, a rising glidepath will simply mean "invest less in stocks".)

      By the way, a 10-year retirement has less SOR risk than a 30-year retirement, which is why Bengen said you could have a higher WR. You'll be selling less stock (a third) at unpredictable prices. So, not living long reduces SOR risk, though you might prefer to go with the lower equity allocation for non-financial reasons.

      But does it "virtually eliminate" SOR risk? That's a leap of faith. To virtually eliminate SOR risk, you need to virtually eliminate stock sales.

      Thanks for writing!

  8. Thanks for the reply Dirk.
    Very "crystalizing".
    Kitces also suggestions a valuation approach to controlling SOR risk where equity allocation in retirement is a function of the then current Shiller PE Ratio. Undervalued 65% equity weighting, fairly valued 45% equity weighting, overvalued (like today) 30% equity weighting. He believes that Shiller PE ratio is a lousy short term indicator but a pretty good long term (10 years+) indicator. Do you see any value with this strategy vis a vis controlling SOR risk?

  9. Not a lot. I do agree with Michael (and Shiller and Bernstein) that CAPE is a long-term valuation measure and not a timing mechanism. To quote Bernstein, once you have a reasonable asset allocation, tweaking it in one direction or the other doesn't make a lot of difference. (That's why he talks about asset allocation in dollops and smidges and not percentages.) SOR Risk is fairly constant, all other factors being equal, as a function of asset allocation from about 35% to 65% equities. Michael's is a theory. And one that would be very hard to prove going forward.

  10. I learned so much from your articles.

    1. Thanks, Tom! It's a win-win. I learn a lot from writing them.

  11. I am not one of the DivGrowth die hards, but I do agree with them that taking your cash draws from dividends (instead of/also from capital gains) is a pretty sure bet you never run out of money - regardless of sequence of returns.

    I have modeled the retiree who takes a wd%rate = the S&P's dividend yield in year one, and increase the $w/d by inflation. That never failed.

    I have also modeled the retiree who takes a wd%rate higher than the index's yield in yr 1, assigning him the yearly return of the yield-decile that would have yielded the necessary $$. This also rarely failed. The only failures was for retirees of the late 1960's. And the failure was caused by inflation, not sequence of returns.

    1. I'm not a fan of the strategy, either.

      I don't doubt your findings, though it is always possible to look at historical data and find some strategy that would have worked. That doesn't mean it will work in the future. Our historical data on stock returns is extremely limited.

      SOR Risk is primarily a function of the percentage of your current wealth that you spend. It doesn't matter if you spend less because you set a lower spending rate, because you spend a percent of remaining portfolio value instead of a constant dollar amount, because you reduce taxes, because you've paid off the mortgage, or because spending only dividends results in lower overall spending. A buck not spent is a buck not spent.

      I can't see any reason that spending the same amount from dividends as capital gains would reduce SOR Risk. I would think that any reduction in SOR Risk you observed would be because spending only dividends resulted in lower overall spending when your portfolio balance fell, which is the same reason selling a percent of remaining portfolio balance reduces SOR Risk. (Spending less when you have less money is a wise decsion.)

      Both swap SOR Risk for variable income.

      Now, is spending only dividends the best way to ensure that you spend less when your savings dwindle? I don't think so, because that strategy will increase dividend-paying stocks in your portfolio disproportionately.

      It's an interesting argument, though. Thanks for writing!