The world doesn't actually unfold that neatly, of course. We get returns that can vary widely each year, and if you're buying and selling along the way, it isn't nearly the same thing. This variance of returns introduces sequence of returns risk when we are accumulating or spending from a volatile portfolio and the spreadsheet doesn't capture that.
Market crashes can cause wealth-destroying panic selling and high portfolio variance can increase longevity risk. The typical spreadsheet analysis doesn't account for any of that.
Most importantly, half the time your return will be more than the average and half the time it will be less, so if you assume an average return of 5%, for example, and need at least 5%, you will fail half the time. (Technically, that's the median and not the average, but I'm assuming they're pretty close in this situation.)
This chart shows a probability density function of portfolio returns with a mean of 5% and standard deviation of 11%. Your actual returns can fall anywhere along the x-axis, but most will fall somewhere close to the average return of 5% in the middle.
To address these problems, many planners and nearly all academics prefer to use Monte Carlo simulation, instead. Monte Carlo generates many outcomes and, unlike the spreadsheet approach, shows the distribution of outcomes, like this graph, and the probabilities of each occurring. It also simulates sequence of returns risk and creates some "market crash" scenarios.
Instead of simply using an average that we will under-perform half the time, Monte Carlo can also tell us what rates we are likely to outperform 80% or 90% of the time, for example.
Is there a way to use a spreadsheet and incorporate this volatility risk that would typically remain hidden? Wade Pfau recently published a column in Advisor Perspectives entitled "New Research on How to Choose Portfolio Return Assumptions" attempting to answer that question. I won't repeat his findings in detail — I hope you will read his analysis — but I will summarize the results.
In simplest terms, Wade performed a Monte Carlo analysis to generate a random variable with a median return of 5% (the same as the spreadsheet answer that is exceeded 50% of the time) and standard deviation of 11% and then calculated what the returns would be with more conservative assumptions, like the return that would be exceeded 90% of the time, also known as the 10th percentile.
Wade confirmed some guidelines suggested by financial planner, Daniel Flanscha, who noted that when he uses a fixed return assumption (a spreadsheet analysis), he subtracts 1.0% to 2.5% from the return during the accumulation phase and 2.5% to 4.0% from the return during the distribution phase to account for the effects of randomness.
Wade found that compared to performing Monte Carlo simulation of a retirement spending scenario with a median geometric return of 5% (arithmetic mean of 5.6%) and volatility of 11%, a return you could expect to meet or exceed 50% of the time, you could provide a reasonably conservative result, one that might be met or exceeded in 90% of cases, with a spreadsheet by using an expected return of 1.9%.
Here's our portfolio returns probability density function chart with the dotted line now showing the more conservative 10th-percentile figure of 1.9%.
Second, it shows that sequence of returns risk is greater during the spending phase than during the accumulation phase. In Wade's example scenario, you would subtract 2% during the accumulation phase, but 3.7% during the retirement spending phase. I think I showed that with my discussion of sequence of returns risk, too.
Lastly, as an anonymous commenter to the column noted, conservative stock returns start to look a lot like bond returns:
"Given the 1.9-2.5% real projected returns at the 10th percentile that you use for the example, by the time you subtract any management fees, transaction costs, and/or fund expenses, the real returns would be in the range that's quite attainable with a carefully laddered long bond-only income portfolio where the bonds are held to maturity. Even if the 50/50 portfolio at the 10th percentile might do slightly better than the bond ladder, the difference in real return might not be worth the risk. . ."To which Wade added:
"this is an important point about how a conservative rate of return assumption starts getting close to the internal rate of return from a bond ladder. There is one important difference, however: the bond ladder will not have upside potential, while the diversified portfolio could perform better. Remember, this is a conservative return assumption."The diversified portfolio could also perform worse; there's a 10% chance of that happening. In fact, Wade's analysis assumes that the individual earns market returns, which is a big assumption. Most don't. Underperform those stock market index returns with your own investments and you do have bond returns.
Still, there is a much better chance that you will do better than worse at the 10th percentile.
Be careful with tools other than your own spreadsheets, as well. E$Planner, for instance, used to use spreadsheet-like calculations until it began to incorporate a Monte Carlo function. Monte Carlo is still optional. (You can implement Monte Carlo simulation in a spreadsheet, just know whether or not you are.)
It was spreadsheet-like thinking that led Peter Lynch to infamously suggest that 7% should be a sustainable withdrawal rate and many people to think you can always make a profit earning an average market return that is higher than your mortgage cost.
It's half right.