An Updated Google Sheet DIY Withdrawal Rate Toolbox (SWR Series Part 28)

Since I first published Part 7 of the SWR Series with the accompanying Google Sheet in early 2017, I’ve made several changes and enhancements. Sometimes without much explanation or documentation. So, it would be nice to do a quick update and itemize the changes since then. Whether this is the first time using the toolbox or you check it out again after more than a year, I hope you all find the new features useful…

Here again is the Google Sheet Link:

Link to the EarlyRetirementNow SWR Toolbox v2.0

As always, please save your own copy because the current (clean) version posted on Google Sheets has to be write-protected so visitors don’t mess around with my formulas! You will be asked to create your own personal copy, which you can edit! 🙂

Main Tab: more detailed results

As before, you enter almost all parameters in the main tab “Parameters & Main Results.” All fields in Orange are user inputs, such as:

  • Equity/Bond/Cash/Gold share. To make sure the weights sum up to 100%, the Gold share is set to 100% minus the sum of the rest.
  • Fama-French style factors. This is new, see item #3 below for more details.
  • Assumed projected asset returns for the asset classes going forward. Why do this? I’d like to be able to simulate 60-year windows of the 1960s and 1970s retirement cohorts and don’t want to constrained by a slightly shorter than 60-year data availability. Since the SWR is overwhelmingly determined by the first 10-15 years of the retirement horizon (Sequence Risk, see SWR series Part 15), the last few years of “made up” returns during retirement don’t have much of an impact on the SWR estimates. Example: the failsafe WR is 3.46% during the 1960s with our assumed 3.75% p.a. equity return. If we increase the equity expected return to 20% p.a., the failsafe increases to only 3.52%. But, of course, the 2000 and especially 2007 fail-safe numbers would be significantly impacted by the return forecasts. So, use those figures with a grain of salt!
  • The retirement horizon in months and the target final value (e.g. for bequests) as a percentage of the initial value.
  • Notice that the supplemental cash flows are no longer inputted here in this main tab, but in a separate tab, see item #2 below.
SWR-Part28-Table01
Main Parameters. Same as before but I added the Fama-French Small Cap Style (SMB) and Value Style (HML)!

The main results table is pretty large so I split it in two, the first part is below:

  • As before, I display the failure rates of different initial withdrawal rates in the top half of the table.
  • The bottom portion of the table is new and presents the calculation the other way around: Pick a desired failure rate and look up the withdrawal rates to match it. The first row in that portion at 0.00%, i.e., this is the fail-safe initial withdrawal rate. But you can also look for other failure rates, 1%, 2%, 5% and 10% as well and see how much extra initial withdrawal you could have sustained in historical simulations. And I hope that nobody would even consider a strategy with a failure rate of 25 or even 50%, but I display those just for reference.
  • The columns calculate the corresponding stats for all retirement starting months and years, since 1926 (the start of the Fama-French database and also the starting point for the Trinity Study) and 1950 and also for different Shiller CAPE Ratio regimes (under 20 vs. 20 to 30 vs. 30+). Notice that as of late August 2018 we’re at over 32! As conservative as I am normally, though, I’d concede that today’s elevated CAPE ratio doesn’t quite “feel” like the 30+ CAPEs of the past. Maybe I’d still consider the current CAPE a high 20s, not really a low 30s!
  • Notice how sensitive the failure rates can be when changing the WR in 0.25% steps, especially when the CAPE is high!
SWR-Part28-Table02
Main Results: The top panel shows the failure rates of specific initial withdrawal rates. The bottom panel goes the other way around: Specify a certain failure rate and find the initial withdrawal rate that would have generated that failure rate.

And Part 2, see below. The table has the same format as the first part, but now the columns are conditional on how far the S&P 500 index is away from its most recent high. Why does this matter? The stock market is a Random Walk and past returns have no bearing on future returns, right? Wrong! As is well-known in finance and as I pointed out in a post a few months ago, stocks have the tendency to mean-revert. So, expected returns tend to be higher after a steep drop and lower after a long run-up in stock prices. That’s reflected in the failure probabilities of the 4% Rule below. The unconditional failure probability was just under 10%, but it’s 18.55% when we were at the market peak. So, failure probabilities are indeed impacted by past returns. File this as another piece of evidence that the stock market isn’t exactly a Random Walk!

Why is this relevant? Well, on August 22, 2018, the market just became the longest running bull market in history (though not everyone agrees on the definition of the longest bull market, see this article on Bloomberg). If history is any guide the retiree in this numerical example will be wise to be more cautious with the initial withdrawal rate and not just blindly apply the 4% Rule mantra. Maybe withdraw a little bit less, 3.25-3.50% to have a bit a cushion. In other words, after a potential drop in the portfolio the effective withdrawal rate might reach 4% right around the time when the S&P had a ~20% drop, which is when, historically, the 4% becomes very safe again!

SWR-Part28-Table03
Same as Part 1 of the results table but conditional on the equity drawdown

I also added a small table with the fail-safe withdrawal rates in five important time intervals around stock market peaks. That’s because I usually like to see during what period the all-time fail-safe occurred. Most of the time it’s either 1929 or the 1960s, depending on the stock vs. bond. vs. cash allocation! It’s always amazing to see that the 1970s and early 80s recessions did an even worse trick on the mid-60s retirement cohorts than the ones that retired around the 1973 market peak!

SWR-Part28-Table04
Fail-safe Withdrawal Rates in five different bear markets.

Supplemental cash flows: now easier to input!

One of the biggest challenges for folks trying to use this spreadsheet used to be how to correctly enter the supplemental cash flows. I tried to make this a little bit easier and intuitive, so I created a separate tab, appropriately named “Cash Flow Assist” to help with that. At the top, we can input the initial portfolio value and a projected inflation rate to discount the value of any non-inflation-adjusted future cash flows, e.g., corporate pensions etc. in the orange fields below. As always, it’s best to go through a simple example:

  • The portfolio is worth $3m at the beginning of retirement.
  • We expect 2% inflation going forward.
  • Spouse 1 expects Social Security after 25 years (=month 301) worth $2,000 per month.
  • Spouse 2 expects Social Security after 26 years (=month 313) worth $800 per month.
  • Spouse 1 expects a small corporate pension of $300 (not inflation adjusted) 11 years into retirement.
  • We expect $1,000 in additional monthly expenses (e.g. medical) 30 years into retirement (in today’s dollars, adjusted for inflation) and that amount rises to $2,000 after 40 years. This corresponds to the two spouses reaching a certain age where they may scale back other expenses (travel) but face increase medical, in-home care expenses, etc. that will case in a net increase in expenses.

How do we input all of this? At the top of the page we input the net worth and inflation figure and leave the other orange fields empty (=$0) for now because there are no supplemental cash flows during the first year, see below:

SWR-Part28-Table05
Enter the Net Worth today and the inflation assumption. Cash flows don’t start until later, so the other orange fields are left blank (=$0).

Next, we input the corporate pension, starting after 11 years (month 133), see below. Notice that we input this in the fifth column where the non-COLA cash flows reside. So, this will be discounted to make sure it’s comparable to today’s real dollars!

SWR-Part28-Table06a

Next, the Social Security payments starting 25 and 26 years into retirement, see below. The benefits are inflation-adjusted so they are entered in the corresponding columns with COLA cash flows:

SWR-Part28-Table06b

And, finally, the additional budget for medical expenses, care, nursing homes, etc., see below. This is now inputted as a negative cash flow!

SWR-Part28-Table06c

In the “green” column on the right, the program translates the different cash flows into percentages of the initial portfolio:

SWR-Part28-Chart05
Monthly supplemental cash flows as a % of the initial portfolio.

Also in the same tab is the same table that showed up in the main tab but the percentages are translated into withdrawal amounts in dollars p.a.:

SWR-Part28-Table12
Translate withdrawal percentages into dollars p.a. for this numerical example.

Fama-French Style Factors (since 1926)

I added that feature pretty early in the spring of 2017, mostly out of curiosity about how much of a difference some of the widely cited style premia such as value and size would have made. See Ken French’s site for the data and more documentation on the construction of the Fama-French factors. For example, let’s simulate the following scenarios:

  1. The 80% S&P500 baseline
  2. Keep the overall equity share at 80%, but use a 25% small caps and 25% value stocks tilt: Set the SMB and HML allocation to 25% each. Make sure you keep the overall stock allocation at 80%!
  3. Keep the overall equity share at 80%, but use 50% growth stocks. Set the HML allocation to -50%. Again, make sure you keep the overall stock allocation at 80%!

The small-cap plus value bias would have easily lifted the SWR to above 4%, see table below. But I’d probably not get too excited about this result. There’s no guarantee that the size and value premium will persist forever and continue to help future retirees. All this could just be backward-looking bias. Certainly, in 1926 nobody would have known about the work by Fama and French. In fact, if you had been wrong and bet on the wrong style, for example, “growth” instead of value you would have totally ruined your safe withdrawal rates. Fail-safe withdrawal rates are now in the low 2% range. Ouch!

SWR-Part28-Table13
Fail-safe initial withdrawal rates for different equity style premia.

Case Study: glide path simulation

In the tab “Case Study” where you can simulate one single time series of the portfolio values for a specific starting date and initial withdrawal rate, I also added a simple glidepath simulation for comparison (see Part 19 and Part 20 of the SWR Series). This is the simplest possible version with just the static glidepath going between two different equity weights in fixed steps (e.g. 0.3% per month) and investing the residual in bonds, see below:

SWR-Part28-Table09

Compared to an 80/20 static stock/bond allocation, the glidepath would have made a huge difference during the Great Depression! But glidepaths were not a panacea because, during the 1970s, bonds offered much less diversification.

SWR-Part28-Chart02
A portfolio with a 4% initial WR and 80/20 fixed allocation would have run out of money after less than 25 years. A Glidepath would have performed much better!

Simulate CAPE-based Withdrawal Rules

I added another tab to simulate CAPE Rules with different parameters. Just as a recap, the CAPE-based rule, in its simplest form, expresses the annualized target withdrawal rate as a+b times the inverse of the Shiller CAPE (=CAEY = Shiller Earnings yield). See Part 18 for more details and why I like this approach. My preferred rule would be to set the intercept to around 1.5-1.75% and the slope to one half. In the example below I use 1.75%/0.50. Notice that a constant percentage rule would be a special case if we set a=4% (or whatever rate you like) and b=0, i.e., withdraw 4% p.a., regardless of equity valuations.

Running out of money is no longer an issue with the CAPE-based rules. Failure comes in the form of deep and extended cuts to consumption. So, to compare how much (or how little) I like different CAPE I tend to look for 3 key stats:

  1. What’s the change in real, inflation-adjusted withdrawal amounts over a 30-year horizon?
  2. Notice that the point-to-point comparison over 30 years can deep cuts in spending, so I also like to know the lowest withdrawal amount relative to the initial withdrawal amount
  3. And finally, what’s the average over the 30-year window relative to the initial amount.

See below for a numerical example:

SWR-Part28-Chart06
January 1970 cohort: Time series of 12-month rolling withdrawals and the three measures I calculate.

I’m interested in how the three measures would have evolved in the worst case scenarios, so I calculate them for different time intervals (all months vs. 1926 onward) as well as the worst case scenarios in the five different “troublemaker” retirement cohorts (Great Depression, 1960s, early 1973, Dot-Com bust and Great Recession), see the table below. I also add the volatility of year-over-year withdrawals and some stats on the withdrawal rates implied by this CAPE rule:

SWR-Part28-Table10
CAPE parameters and main results.

A little side note: I frequently get questions and comments about the CAPE parameters just like recently when a reader wondered why I assign a weight of 0.5 on the CAEY. Shouldn’t the withdrawal rate be less volatile with a higher intercept and lower slope? Yes, but as a retiree, I’m less worried about volatility in withdrawal rates and more worried about volatility in withdrawal amounts. So if I were to set the intercept to 3% and the slope to 0.3 I’d have a more volatile stream of withdrawals, see below. And the more you reduce the slope parameter the closer you get to the good old constant percentage rule where your withdrawals become just as volatile as the portfolio itself!

SWR-Part28-Table11
A higher intercept and lower slope. The withdrawal rates are less volatile but the withdrawal amounts become more volatile!

I guess it’s up to you and what you feel comfortable with but I like the way the CAPE rule cushions the volatility in withdrawal amounts, see below:

SWR-Part18-Formula03
From the SWR Series, Part 18: Under the constant percentage rule, the withdrawals will move in sync with the portfolio value. In contrast, tying the withdrawals to economic fundamentals has the potential to soften the fall in withdrawals in case of a bear market!

OK, so much for today! I hope you enjoy the new features! Please let me know if you find bugs or like to suggest more features!

Thanks for stopping by today! Please leave your comments and suggestions below! Also, make sure you check out the other parts of the series, see here for a guide to the different parts so far!

Also notice, all the usual disclaimers apply!

Picture Credit: Pixabay

272 thoughts on “An Updated Google Sheet DIY Withdrawal Rate Toolbox (SWR Series Part 28)

  1. Hello Big ERN,

    In running my numbers on the DIY SWR spreadsheet, I came up with a couple of questions.

    1. In looking at the Cash Flow Assist tab, that shows how much we can safely withdraw each month in Column Q. I can see that the pension and eventually social security totals (Column M) are discounted as the months go by to account for inflation. Also, the withdraw rate goes up by the same amount to keep the annual cash flow at our failsafe rate.

    So, many years down the line, we’re still showing the same annual consumption amount (withdrawals + cash flow), but I assume that this is because it’s in today’s dollars. All along, our withdrawals have been adjusted for inflation and SS amounts will actually increase with inflation. So, do I understand correctly that because of SS increases, the actual amount 20 years from now will be greater than our starting failsafe consumption amount, but the purchasing power will be about the same (provided our inflation estimates are reasonably close)?

    2. Reducing the future real returns of stocks on the Parameters sheet seems to have no effect on our failsafe withdrawal and safe consumption amounts. Why is that?

    1. All results are in CPI-adjusted dollars to make comparisons easier.
      And keep in mind that this is a safe consumption rate, because withdrawals will be reduced 1-for-1 when you draw Social Security and pensions.

      2: the future “expected return” figures are merely used to forward-fill some of the return for cohorts that would otherwise fall out of the sample. Using higher returns here may not impact the fail-safe because if the fail-safe is based on 1929, no future expected return data are used for that number.

    2. #2 One thing you could do is put in very low future returns such as near 0% equity returns and you’ll probably start triggering 2000 as the worst year to retire instead of 1929/1968.

      1. Good point! But it depends on the horizon. Looks like the 30-year retirement starting in 2000 will likely be safe considering how well the market recovered recently. But for longer horizons, yeah, why not enter negative expected returns for the medium-term and then only 4-5% long-term for stocks, and 1% fore bonds Should do the trick. 🙂

  2. Love the sheet and thank you for doing it. Why do cash flows from other retirement income such as SSI impact my safe withdrawl rate?

    1. If you look closely then you will see that the idea is that a larger withdrawal rate is supported early on because the amount you withdraw later is reduced by the guaranteed income streams that are external to your investments. In other words your income is evened out giving a more sensible profile.

      I find it more comprehensible if I look at the Safe Consumption Amounts to target different Failure Rates on the Cash Flow Assist tab where the absolute amounts are listed rather than the percentage as it is more natural to think of the total income received as being a mixture of drawdown and other income streams than to think of the percentage as being a mixture.

      1. Sorry, UK-centric language used. When I say “drawdown” I don’t mean fall in the stock market I mean “withdrawal from your pot of wealth”.

  3. Thanks for all your great work on this. I am particularly interested in using your CAPE-based strategy as I start to plan out my drawdown plans for my retirement.

    One thing I noticed (assuming I’m using it correctly) was that the data entered in the Cash-Flow Assist tab, like adding in income from Social Security, does not impact the calculation of SWR when using the CAPE-based Rule, like it does on your Parameters & Main Results tab. Is this something you are planning to add to the CAPE-based Rule tab? I think it would be tremendously useful.

    Keep up the great work!

    1. Correct.
      The cash flows are not (yet) considered in the CAPE sheet. It’s likely too complicated to implement into a simple spreadsheet and would probably take a Matlab code to loop over the different starting dates. 🙂

      1. Thanks Big ERN! Fair enough. I think I’ve found a way to factor in using the tables you gave in the case study of Mr. Corporate (in the Adjustments for Social Security Section). I think I can just look up my own specific scenario in the tables (80% stocks, 40y retirement length, 15 years to collect) and get around .339% and multiply it with the percentage benefit I get (yearly benefit / portfolio value). Basically just do it as a lookup as you did in that example.

        The other factor I’m not sure how to incorporate is fees. I’m paying 0.81% to a financial advisor to manage our portfolio. I know, its higher than generally recommended, but it is an advisor we trust and we have some complexity in our portfolio that makes me feel better knowing a professional is on the case. Not sure how to factor that into the CAPE formula to generate a percentage impact based on fees. Any hints on how to adapt the formula to take into account fees so the resulting percentage is net of fees? I don’t think it is a straight subtraction; at least that is not the relationship when I play with the Expense Ratio in the main tab.

        Again thanks so much for your work. I’m learning a ton :).

  4. Karsten, thanks again for all the great work you have done! A quick tech question on the Google spreadsheet – how do you model withdrawals with COLA+X% adjustments? I cannot find an obvious input for that, and, as any IB analyst knows, it is better to ask the model author then try to decompile it independently 🙂

    1. I presume I should use the scaling factor on the cash flow assist sheet and set it to (1+X%/12)^(Ty-T0) for month y. Correct?

    2. Tab: “Cash Flow Assist”
      Column P. If you grow the values at a rate of, say 1% annually (e.g. P12 = P11*1.01^(1/12)) and then copy down that formula you are now growing the real withdrawals at that rate.

  5. I’m confused, because when I increase the Portfolio Value Today value the failure rates go UP, not down. Leaving all the other numbers the same, if Portfolio Value Today is $3M to target a 0% failure rate SWR is 3.27%. If I change the Portfolio Value Today to $1M, to target a 0% failure rate SWR is 3.51%. So I can withdraw a higher percentage safely with a lower starting portfolio value? Either there’s a bug or I still really don’t get it after reading this whole page.

    1. There is no bug.
      If you have positive cash flows built in tab “Cash Flow Assist” then the added benefit of those cash flows is spread over a larger initial net worth and the SWR has to fall.
      If you set those cash flows to zero, then the SWR in % should be independent of the initial net worth.

  6. I have a remedial question about the input parameters. If I select a stock/bond allocation of 80/20 I get a higher SWR than if I select 100/0. If I’m planning on getting to 100% equities via a glidepath, should I assume the input parameter should be 80/20 and I use the corresponding SWR for that as I plan for retirement?

  7. Q: are the calculations of amount below S&P high in real or nominal dollars?

    Hoping it’s the former (to be more meaningful during the high-inflation 1960s and 1970s) but guessing it might be the latter because that’s simpler to calculate. And nominal probably doesn’t include reinvested dividends either.

    1. That’s conditional on real drawdown of the total return index. As you stated, it’s easier to calculate. It’s also the only sensible thing to do, otherwise numbers wouldn’t be comparable across different decades with different inflation regimes.

  8. Officer, officer! I need to report a problem!

    Seriously, with the current version of the Google sheet (whether downloaded to excel or just using a copy in Google), the numbers no longer match those of the version from last year.

    In particular, using the new sheet, with 65% stocks, 35% bonds, 360 months, 0% final value, its shows a failsafe WR of 3.94%

    Last year’s version (which I downloaded in the spring) shows the same 3.83% that your Part 20 post shows.

    The new version shows consistently higher SWRs than the old one did.

    With the initial values that come with the latest sheet (75/25, 720 months, 25% final value target) it shows a SWR of 3.37%. Plugging those same parameters into last year’s version shows 3.20%

    I checked and rechecked and am pretty positive that it’s not user error.

    While it would be good news for all retirees if the new numbers are correct, it seems unlikely the ERN we all know and love had a) been publishing incorrect data all these years, and b) would without announcing it slip the newer numbers into a spreadsheet 🙂

      1. Thank you! Problem solved. The latest version has many entries in the cash flow section. I assumed it was blank like the prior.

        Clearing those out does indeed fix the problem!

        Apologies for the false alarm

    1. That’s possible because if your fail-safe is 1968 and you have a 60-year horizon, you’re filling new data with very strong equity returns over the last few years. It should make a noticeable difference in the SWR.

  9. Thanks for leaving the comments on this older thread open. I think the SWR Toolbox is possibly the most valuable resource on the site. I am writing up our personal financial plan (for my spouse) and started really looking at how I was interpreting the “SWR” toolbox output. Browsing the comments above, it is important to note that what the tool is outputting is a Safe Consumption Rate (SCR), not a SWR! I recommend you change the terminology on the workbook as I had been thinking of the output as an SWR, which it is not, since the value is modified by supplemental cash flows.

    1. I’ve added the following explanation to my copy of the workbook:
      “SWR has been changed to SCR throughout this workbook. The reason is that the sheet includes entry of supplemental cash flows, such as social security and pensions and it modifies the rate based on those. Obviously, supplemental cash flows do not affect the depletion of a portfolio at a given withdrawal rate. The SCR reduces to a SWR only when all supplemental cash flows are zero (no values entered).”

    2. True. I changed the verbeage to “initial consumption rates” and “Safe Consumption Amounts”.in some of the places.
      I sill carry a few referedfens to SWR here and there and assume that the educated reader will understand the subtle difference.
      But I will look into ways to make this more obvious. 🙂
      Thanks!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.