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! 🙂

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

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!

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!

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!

### 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:

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!

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:

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

In the “green” column on the right, the program translates the different cash flows into percentages 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.:

### 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:

- The 80% S&P500 baseline
- 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%!** - 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!

### 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:

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.

### 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:

- What’s the change in real, inflation-adjusted withdrawal amounts over a 30-year horizon?
- 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
- And finally, what’s the average over the 30-year window relative to the initial amount.

See below for a numerical example:

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:

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!

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:

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!

### Please check out the other posts in this series and leave your comments and suggestions below!

- Part 1:
**Introduction** - Part 2: Some more research on
**capital preservation vs. capital depletion** - Part 3: Safe withdrawal rates in different
**equity valuation**regimes - Part 4: The impact of
**Social Security benefits** - Part 5: Changing the
**Cost-of-Living Adjustment**(COLA) assumptions - Part 6: A case study: 2000-2016
- Part 7: A
**DIY withdrawal rate toolbox**(via Google Sheets) - Part 8: A
**Technical Appendix** - Part 9:
**Dynamic**withdrawal rates (Guyton-Klinger) - Part 10: Debunking Guyton-Klinger some more
- Part 11: Six criteria to grade
**dynamic withdrawal rules** - Part 12: Six reasons to be suspicious about the “
**Cash Cushion**“ - Part 13: Dynamic Stock-Bond Allocation through
**Prime Harvesting** - Part 14:
**Sequence of Return Risk** - Part 15: More Thoughts on
**Sequence of Return Risk** - Part 16: Early Retirement in a
**low return environment**(The Bogle scenario!) - Part 17: Why we should call the 4% Rule the
**“4% Rule of Thumb”** - Part 18:
**Flexibility**and the Mechanics of**CAPE-Based Rules** - Part 19:
**Equity Glidepaths**in Retirement - Part 20: More thoughts on
**Equity Glidepaths** - Part 21:
**Mortgages**and Early Retirement don’t mix! - Part 22: Can the
**“Simple Math”**make retirement more difficult? - Part 23:
**Flexibility**and**Side Hustles!** - Part 24:
**Flexibility Myths**vs. Reality - Part 25: More
**Flexibility Myths** - Part 26: Ten things the “Makers” of the 4% Rule don’t want you to know
- Part 27: Why is
**Retirement Harder**than Saving for Retirement? - Part 28: An
**updated Google Sheet**DIY Withdrawal Rate Toolbox - Part 29: The
**Yield Illusion:**How Can a High-Dividend Portfolio Exacerbate Sequence Risk? - Part 30: The
**Yield Illusion**Follow-Up - Part 31: The Yield Illusion (or Delusion?): Another Follow-Up!

Also notice, all the usual disclaimers apply!

*Picture Credit: **Pixabay*

Hi Karsten. Not sure that I follow, since column L has no such verbiage as “Please do not change” (I do not see that anywhere on the sheet. Note that this sheet reflects the 80/29/2018 update, SW Series part 28). Also, the column of the results table titled “Failure Rates” begins in column 12, row 13.

Thank you very much for looking into this.

Eric

Oops – Seems I was not running the latest update. I see what you describe on the current sheet. Thanks again.

OK, great. You scared me for a second there! 🙂 Glad you found the right sheet!

Yes, seems to work well. Look forward to your upcoming post about this.

For a future update to the sheet (and, believe me, I am “happy as a clam” about this sheet, even if you opt to never update it further), seems to me that some cash flows should be discounted for inflation (pensions, mortgage payments, etc)l others, such as salary (assuming working for a few years prior to retirement) or Portfolio contributions (401k) need not be discounted as these are often a percent of wages/salary which can often be assumed to keep pace with inflation.

Many, many thanks for your hard work on this. I know I speak for so many when stating that you have expanded and educated minds on such and important and yet widely misunderstood subject as personal finance.

Ern – Pardon again… Noticed that you already have columns for inflation vs non discounted cash flows. Many thanks (yet again).

You bet! 🙂

ERN – I have been reading your entire site over the past couple of weeks. Planning on ERNing myself in August this year. Your SWR series is exactly what I was looking for – glad I finally found you!

I have a question on the Cash Flow Assist sheet and how it calculates the cash flow for a future social security income.

Here is my scenario:

– I am 42 now and plan on taking SS at 62.

– I used the detailed SSA benefits estimator to get a monthly SS Income cash flow.

– Based on what I have researched, SS benefits are not adjusted for inflation until I reach age 62. After age 62 the benefits are adjusted for inflation or COLA.

Should I enter my calculated SS estimate at month 240 (when I turn 62) on the Cash Flow Assist sheet or do I first adjust that amount for the 20 years of inflation between now and age 62?

I believe it is the latter, but hopefully you can confirm or correct me.

All the best and appreciate your writing!

Jeremy

You should do the former. The SS estimate is in 2019 dollars, so enter that number in one of the inflation adjusted columns (B, C or D) in the cash flow assist tab in month 240 and copy that same number all the way down. No need to do an inflation adjustment!

OK, knowing that you were correct, I had to dig a little deeper. I learned that the SSA applies an index factor to actual earnings which adjusts them for inflation. Now it makes perfect sense. Thanks for the help.

Thanks for confirming! 🙂

HI Karsten,

I love the SWR series and your spreadsheet – both have really helped to me solidify my FIRE calculations. I am still working but intend to quit in a few months to enjoy the beautiful UK summer.

I’ve used the spreadsheet to calc my SWR several times but coming back now and re-checking, I have a question on the cashflow assist tab. The cell for entering net worth is labelled “net worth today” but I think this should be “net worth at retirement” – am I correct ? I ask this because rows 11 onwards start deducting withdrawals from month 1 so if someone is not yet at retirement, they need to project the future value of their retirement pot first and enter in B5.

Or am I wrong and you enter the current net worth then adjust cashflows to start according to today?

Problem is there are withdrawals happening that I am not yet making as i am still working.

Thanks in advance!

Ian

True, it should be NW at retirement. There is a hack to still enter today’s NW, then keep contributing for a set period and then retire after, say, 18 months.

I addressed that issue here:

https://earlyretirementnow.com/2018/08/29/google-sheet-updates-swr-series-part-28/comment-page-1/#comment-13601

In that case, it’s truly today’s net worth, because the net worth 18 months down the road is not known.

I have spent quite a bit of time playing with this new spreadsheet and I have really enjoyed using it. I was a bit surprised to see you had added a field for gold investing. I have previously been resistant to using any gold in my portfolio because of the issues of poor long-term return and higher expenses associated with owning gold. However after playing around with the numbers it would appear that a modest holding in gold (perhaps 10%) does indeed significantly improve the risk characteristics of my portfolio (at least based on previous returns). I’m starting to think that reducing SoRR is more important than trying to maximizing long-term return or minimizing expenses. Love your SoRR series and I would be very interested to hear any thoughts you have about the use of gold in a portfolio.

You got that right! I’m amazed myself how a low-return asset can make such a big difference. Of course, you lower your average return, but it makes a big difference around the tail events.

So many things to do in future SWR posts! 🙂

Thanks Karsten! This SWR series has been filled with eye openers. You keep killing one sacred cow after another and you have forced me to reassess much of what I thought I knew. I am 50 years old and I retired at the beginning of 2018. My wife is 48 and is originally from China. If your daughter stood next to my two children, I think most people would assume they were all siblings. Keep enjoying your retirement!

Thanks, Stephen! Best of luck in your retirement and all the best to you and your family! 🙂

Hi ERN,

I have just been using your SWR spreadsheet as a check against my own calculations (Noddy compared to yours!). One aspect of the withdrawal amounts that I have ‘modelled’ in my spreadsheet is to vary the amount by my equivalent of the ‘cashflow assist’, as I want to model a constant income stream. To achieve the same effect in your ‘Case Study’ tab, I subtract the ‘Supplemental Cash Flow’ from the calculated ‘Withdrawal’ amount. So the formula for F22 becomes “=$B$14*$B$17*’Cash Flow Assist’!L11-G22”. Does this make sense, or am I missing the point?

Thanks,

Ashu

If you look at the formula for D22:

=(D21-F22+G22)*(1+H22)

You take last months portfolio value, subtract the consumption target and add the supplemental cash flow. Then add the return.

So the net withdrawal to satisfy the consumption target is indeed $B$14*$B$17*’Cash Flow Assist’!L11-G22 as you state, but in the formula for F22 I measure only the consumption amount. Don’t subtract G22 here again otherwise you’d be double-counting the supplemental cash flow.

Hope this helps!

Yes, thanks, that does make sense.

Could you please explain me why, if i reduce my initial portfolio value from ”$3 000 000” to ”$1 000 000”, the WR on the main tab goes UP? That sounds counter intuitive to me. I have less money so my SWR should be less, no?

Hi ERN,

Also, do I use the spread sheet correctly to switch all values to ”0” in the subtab ”Cash flow Assist”, if I want to assume that I will receive no funds whatsoever in the future?

If you expect no additional cash flows, then set that all to zero. Correct.

For positive fixed supplemental cash flows (as they are set in the default settings, see the post here) you spread them over a smaller net worth, so the SWR has to go up.

Notice that with zero supplemental cash flows, the portfolio value has no impact on the SWR. It’s all linear and scalable in that case.

Thank you for your quick reply. I have been struggling with many of these concepts, new to FIRE community (I am in medicine, so a bit different from economics ^^). I try to simulate the dutch tax system, which is different from yours, with higher ”expense ratio’s” p.a. The impact on the SWR are quite shocking.

Agree. Probbaly almost 1-for-1 reduction in the SWR due to higher ERs, ouch!

Best of luck!

Hi, first thanks a lot for this fantastic tool.

I’m using it to estimate my possible retirement year.

At first I only used the main tab + cash flow assist to calculate the fail-safe SWR (box E18).

But understanding that glidepath withdrawal strategy can improve the SWR, I now moved to the case study tab.

The thing with this tab is that to find the optimized fail-safe SWR, I have to “guess” the starting year, then “guess” the initial & final equity %, and finally “guess” the withdrawal % itself and make sure the portfolio does not deplete at any point.

In the static allocation strategy, I only had “guess” one parameter (that is, the stock\bond rate) to obtain the fail-safe SWR but here I have to “guess” 3 different parameters, making it hard to find the desired optimized result.

Is there any shortcut or trick to overcome this?

Good point. Not sure how to optimize the SWR along asset allocation and glidepath parameters in this simple spreadsheet. The research in Parts 19/20 in the Series was done with Matlab and some pretty intense compuations! I see no easy way replicating this with the Google Sheet.

Thanks,

Hopefully any fail-safe SWR is a result of a retirement in a set of up to like 10-20 specific very bad dates, so I guess I’ll have to assemble such a list to narrow down the options I have to play with.

One more thing I’ve noticed about the SWR results, correct me if I’m wrong please – I’ve seen scenarios where the portfolio gets depleted in the middle of the retirement but due to the supplement cash flows kicking in afterwards, it ends positively at the end of the retirement horizon.

Maybe in a spreadsheet it looks good, but I wouldn’t consider such a retirement a successful one in real life.

Do you agree and plan to deal with this issue in a future version of the tool?

That’s a possibility. I might look into how to prevent that in a future version.

But here’s a challenge: the way I calculate the SWR is really fast and avoids that long loop over dates (see SWR Series Part 8).

Checking for this cash flow issue would increase the complexity of the problem exponentially! 🙂