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

Save Your Own Copy

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!

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

Also notice, all the usual disclaimers apply!

Picture Credit: Pixabay

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

  1. Great summary. I’m using your toolbox very often. It’s quite an awesome resource. Thanks for making this.
    Here seems to be a missing text in the article: “I’d concede that today’s elevated CAPE ratio doesn’t quite “feel” like the”
    I also have a question around that, many have said that the CAPE is “out of wack” recently since the low interest rates and other factors. Have you hear of alternative approaches to correct this CAPE approach? E.g.: https://investornews.vanguard/valuing-the-stock-market-with-a-new-yardstick-the-fair-value-cape/ What do you think about that?

    1. Thanks for pointing that out. That sentence must have gotten cut off!
      The CAPE is out of whack in more than one way, but it’s still a decent measure.
      I’d first give the CAPE a bit of a haircut because we will soon roll out the poor EPS numbers from 2008-2010 and replace them with much better-looking earnings. Heck, even a mild to moderate recession today would still raise the 10-year rolling average.
      I also agree with the Vanguard point: today’s fair-value CAPE is no longer 15 and there are various reasons for it. But what is it? 20? 25? Hard to pin down!

      1. If anyone has more thoughts on CAPE, or links to other articles, it would be great. I seem to recall reading some justification that it ought to be CAPE /7(years). I thought maybe Kitces but can’t find it.

  2. Thank you for this great spreadsheet! I´m looking forward to playing with it. Even if I´m still far from retirement I feel that it is important to learn from history and consider as many variables as possible as it will help me be a bit more prepared.

  3. You said “I hope that nobody would even consider a strategy with a failure rate of 25 or even 50%”, but aren’t these useful for determining if you’re in for a bumpy ride or you should eject and get a job? Say you get a few years in and there’s a bad downturn (and your haven’t been bond glidepathing). Match your current portfolio up with your withdrawal numbers and bam!

    1. I had a similar question. If half the US Population lives entirely on Medicare, Medicaid and social security, doesn’t a failure just mean you are average? Although definitely not an ideal state, I don’t think it can be viewed as a total failure.

      1. Good point! You won’t starve, that’s a good news. Personally, I’d not have a comfortable retirement.
        I also don’t think that half of the population lives on public assistance ONLY. You’d be much lower down the ladder than the median…

    2. I’d be concerned that with a SWR too high you might go back to work almost for sure. The 50% failure plus maybe another 25% from false alarms (type 2 errors), so I wouldn’t even call that early retirement. Sounds more like a 2-3 year time off…

  4. Love this series. As an engineer I gobble up the math although honestly there are points that even I have to re-read several times.

    I was so excited to see this email. I cannot wait to use the new sheet tonight.

    Thank you!

  5. Hi, I love your SWR series and thanks for the updated spreadsheet, but i get an error when opening the downloaded spreadsheet in Excel, which causes all the graphs to be blank.
    Can you please check this ?
    Thanks
    Ian

    1. I checked this and found that Excel messes up some of the charts, but some are OK. Are you using the latest Excel version?
      In any case, not sure how to fix this. The numerical results seem OK, though. Maybe it’s just a matter of manually inserting the bad Excel Charts…

      1. Thanks for confirming the numbers are still ok. I’m using the latest excel on Mac and windows but it seems to be the graphs with dates on x-axis that don’t translate properly. I recreated them and all is well. Had no issue with v1.0 but that’s IT for you (I work in IT!).
        Thanks again for the superb work you’ve done on this and the whole SWR series – it has really helped me crystallise my retirement plans.
        Ian

  6. Thanks so much for updating this! As a data scientist, I had been having a lot of fun exploring scenarios with the old version and look forward to trying out the new one.

    I was noticing a strange result in the old spreadsheet. It seems the calculation used to get the results in “Parameters & Main Results” tab yield different results than in the “Case Study” tab. I used the same parameters in both tabs and, to get comparable results, ran the calculations in the Case Study tab for all valid Retirement Start Year and Month values (basically, brute-forcing it). I’m not sure if I have a bug somewhere, if it’s a rounding error, if there’s a small difference in your formulas from part 8 and the brute-force approach that the Case Study tab uses, or something else, so I thought I’d mention this. I noticed this discrepancy as I tried to extend your work to let me explore some of the withdraw strategies you discuss in the other parts of this series for my particular case.

    A minor bug I found when looking at the old spreadsheet that hasn’t been fixed: In the Stock/Bond Returns tab, the Year and Month columns are mislabeled. The same problem is on the new CAPE-based Rule tab. Not a big deal, but since you asked… 🙂

    Again, thanks so much for all your work on this! I’ve been loving this series!

    1. Thanks for the feedback.
      At least in the most recent version, I made sure that the case study file is consistent. So if I set the horizon to 30Y in the main sheet, then do a case study and set the initial WR to the value computed in the tab “SWR time series” (ideally formulaic, not just copy and paste the numbers to avoid rounding errors), then in the case study I reach exactly the final value target (e.g., $0 or 25% times initial, or whatever).
      If there was a bug in the old sheet it should now be corrected! 🙂

  7. Great work. Thanks for the updates. Can you post an Excel version for download. I downloaded the google.docs version as an Excel, but when opening in Excel it produced errors.

  8. Amazing spreadsheet, I’m close to experience an org… ahem, anyway, a minor bugfix: in the stock/bond returns sheet month and year are messed swapped (cells A5 and B5)

    Thank you for this incredible work Big ERN!

  9. Holy smoke. Hat’s off to you, Karsten, for this detailed and refined sheet, as well as the details in this post that links the sheet’s specific features to the relevant SWR postings for further reading. Can’t thank you enough!

    Just wondering… As a PhD economist, you must have published dissertation/papers for peer-review. How do you find the online/blog publishing experience/feedback as compared to publication for the academic community?

    So glad to learn, through your postings, the the Ern family is doing so well in its newest phase of life. Be well, and God Speed!

  10. Thanks so much for the updated spreadsheet and the rude awakening. I usually just mess around with cfiresim, even if it is a black box. But @#$%, adding those extra monthly figures in really messes with the withdrawal rate for 0% failure. Even assuming we get 75% of social security at 67, we are at a 2.71% withdrawal rate to have 0% failure for capital preservation after 50 years. Cfiresim is at 3.1%, largely because they exclude September 1929 and the other terrible scenario, January 1966, has a near market peak after 50 years. You are making me rethink my consistent belief that 3% withdrawal rate is always safe. Maybe we will get comfortable with the 1% failure rate, otherwise it looks like we just added another year.

    Thanks for another great thought-provoking post! I am so glad you are sticking with the blog after retirement.

  11. First off, as much as I enjoy your postings I’m glad to see they have slowed down a bit since you left employment. Glad you’re living the plan!

    As far as this sheet, I had added an extra sheet for what you call the “cash flow assist” on a downloaded copy of your original as well so thanks for including that this time around. This piece of work is exceptional, yes, but more than it’s really quite a gift. Thanks for continuing to put your work out there for everyone.

  12. Thank you for this update! I’ve been torn on whether or not to include my house equity in my total portfolio, since it’s not currently a productive (income-generating) asset but merely reduces expenses (I have no mortgage). I recall that past discussions of housing presented real estate as roughly tracking inflation. Since I have an oversized amount of my portfolio tied up in real estate, I would like to know how I can include an inflation-adjusted asset that could in future years be used for a reverse mortgage or liquidity by selling and switching to renting in a cheaper locale. Should I enter my house equity value as if it were bonds? Do your simulation of bonds only reflect the holding of bonds to maturity, or do they reflect the ups and downs of the bond market due to the change of bond prices and yields?

    Last, this approach will be useful if I wind up getting a rental property, since your v2.0 toolbox helps me track the income but I’ll need a separate way to track the equity. Thanks again!

    1. For rental properties you can use the supplemental cash flows to include them. But don’t include their value in the portfolio value. Only the financial assets.
      Your owner occudped house is a ‘dead’ asset and “only” pays you an in-kind dividend. But you can use it as a last resort asset and tap the equity through a reverse mortgage if things don’t go well. And you add it to your estate!
      Bonds: the bonds are 10y benchmark bonds so you don’t hold to maturity. It’s like a constant maturity bind ETF (like IEF).

      1. I initially made the mistake of thinking Cash Flow Assist was an optional second step of analysis. But since it’s pre-filled with the example you provide here in the blog, it’s important for users of the spreadsheet to double-check the Cash Flow Assist amounts, some of which don’t appear until many months into the analysis (e.g. medical expenses, social security income for spouse).

        This is a super useful tool for what-if analysis. Thank you!

  13. Good morning ERN, (or evening, depending on where you are on the globe!).

    Just love the updates you have made, especially the cash flow assist tab and glide-path. Lots of intuitive ways to change key parameters. An amazing and invaluable financial planning resource for the early retiree

    I hope ER is treating your family well. The world trip sounds wicked awesome.

    Sitting here on the deck looking over the mountains with an early morning coffee, 12 weeks into retirement, and wondering my god, how did i get here? Channeling my Talking Heads there…..I am not sure when we stop pinching ourselves…..

    All the best,

    Mr. PIE.

    1. Thanks, Dr. Pie! We are cruising right now visiting Ireland!
      The world trip is still a lot of fun (pinching ourselves every day, too!!!) but we also look forward to our back porch time once we settle down.
      Thanks for stopping by and stay in touch!
      Karsten

  14. Greetings Ern. For the Safe Withdrawal Rates/Amounts, might you mean Safe Spending Rates/Amounts?

    Example: The Google sheet reports a SWR under the various CAPE values, as well as providing a Safe Withdrawal Amount for these conditions based on our initial portfolio and inputted Social Security payments.

    Do the withdrawal percentages/values reflect our total annual safe spending (i.e., withdraw the outputted figures less Social Security), or is the Safe Withdrawal in addition to payments such as Social Security?

    Thank you for these great sheets! Many of we lay-people would never have (before your SWR series) seen personal finance and planning as so cool!

    1. Very important point! Thanks for this!
      I mean the safe initial spending rate. So, when you get Social Security you reduce the withdrawals. I will still use the phrase “SWR” because that’s what everybody uses but the withdrawals are supposed to be adjusted by the supplemental cash flows to keep the consumption amounts constant (after CPI inflation).

  15. Great work. A question. Lower half of first table indicates failsafe at CAPE>30 of 3.25% SWR. But upper half of table indicates a failure rate of 1.75% for the same 3.25% withdrawal rate. How to reconcile?

    1. Use that with a grain of salt: small sample! You can sometimes get odd probabilities when comparing the 20-30 CAPE vs. The 30+. Depending on how the Great Depression (CAPE above 30) vs. The mid60s (CAPE below 30) worked out.

      1. Sorry ERN but I think I have an easier explanation.
        The lower 3.25% is actually 3.2488% if you expand the digits. That is why the exact 3.2500% in the upper table has a small fail rate.
        I had exactly the same question but after downloading the spreadsheet, I understood it.

        This is my first post. I am an engineer (Math is easy for me) and I am doing my deep research on FIRE.
        I have achieved FI already but it is hard to convince my wife to do the “RE” part. One-more-year…

        I have read a lot of FIRE sites and academic papers and your SWR is the most accurate and safest.
        Congratulations!
        Or, I should say: Thank you!

  16. I’d love your thoughts on

    1. Quantifying political risk in your retirement models. Its possible with the Trump administration’s craziness that poicymaking regarding retirement may be suboptimal.

    2. Why not much on real estate? Most Americans own their own home and can factor that into their retirement planning.

    1. We’ve had political risk before. And financial markets priced it in correctly, e.g. Nixon resignation.
      For the record, Trump is good for the economy, at least on a net basis. Less regulation, lower taxes, more business-friendly environment. I think that 99% of the breathless reporting about Trump’s disasters is totally exaggerated by the media. Mind you, these are people who went to journalism school and never ran a business and who are too dishonest or too stupid to acknowledge that Obama was a disaster for the economy and a guy with an orange face and orange hair is able to fix the Obama mess.

      Real estate: if I had reliable returns on RE with a long enough time series I’d gladly include it. I factor in my owner-occupied RE quite easily: I will have a lower out of pocket expenditure because I don’t pay rent. I also can afford to leave a lower financial assets estate because of the house we will soon own.
      Real estate investments are harder to model in this spreadsheet. If you have a rental property with reliable cash flow, probably model it through the supplemental cash flow sheet.

      1. I’ve seen historical analysis of the efficiency of stock markets ability to price in political risk. I think their ability is overstated. Frankly, nobody knows what will happen with Trump’s administration – a war or low level civil conflict is highly unlikely, but not completely impossible.

        1. Well, this whole trade issue seems to be getting old. The other day they announced tarrifs on another $270b of Chinese imports. The S&P 500 dropped by 6 points. Not 6% but 6 points=0.2%. But if someone thinks not enough uncertainty is priced in, please go ahead and short the market. I would not take that risk.

      2. Well this is the first time we’re hearing your perspective on politics, and I suppose it’s a good way to re-bias your recommendations. You’re usually thoughtful and sober with your analytical claims, but these are pretty extreme statements backed with no nuance or data. Stating Trump is good for the economy without discussing the business planning uncertainty caused by tariffs is naive. Stating Obama created a mess without examining the Greenspan put and excessive credit injected into the market during Bush’s term is almost a hostile form of willful ignorance.

        1. No bias here; just the facts…

          – The “exsessive credit” injected into the market was in the last few months of the Bush Presidency (in the wake of the home mortgage crisis and Lehman Bros Bankruptcy). This additional liquidity should have been a tail wind to propel the Obama economy.

          – “Greenspan Put” – that policy ended in calendar year 2000.

          The tariffs are a drag and a possible catalyst for economic disaster but, thus far, have not been sufficient to counteract the positive effects of tax reduction and deregulation.

          In what universe do added regulation and taxes lead to economic growth? It is not a coincidence that growth under Trump has reached levels not seen in many years.

          Karsen is correct in his response. The original poster decided to inject a gratuitous political jab.. Too bad for that.

        2. Well, someone asked me for my opinion on political uncertainty. This was my opinion. I won’t paste in a 2,000 words with data tables and charts. Maybe I should do a blog post on it if I have more time again.
          But for the record, I’m a free-trader at heart. I’ve taken enough econ classes to understand that. I’ve also taken enough game theory classes to understand that it’s not a bad strategy for the U.S. to use trade policy as a stick to enforce compliance with bad actors.

  17. Such useful tool, thank you for updating and sharing. Even though I appreciate how results can be dramatically impacted over long periods of time, I still somehow seem to be amazed at how quickly the failure rates jump with just a 25 basis point increase in withdrawal rate.from 3.00% to 3.25% with CAPE at 30+. Wow – such a good reminder to not cut things too short or be too aggressive with assumptions.

    Question: The tool has the flexibility to adjust projected future returns for the next 10 years and then after 10 years. I am curious why you chose 10 years as the cutoff versus some other time frame (perhaps five years) given the sequence of return risk factor – especially in the early years after retirement. Would results be drastically different if the tool allowed a 0-5 year future return assumption, 5-10 year, and 10+?

    Thanks again for all your insightful work around SWRs – they have been instrumental in my personal financial planning!

    1. Thanks! Yes, small changes in withdrawals will mess around with the failure probabilities. Just like small changes in savings contributions make a big difference over the decades, they could also wipe out a fortune!
      Great question! Ten years is a sweet spot for both bonds and stocks. Say, the 10y yield is 3% today and inflation is forecast to be 2% then I’d use a 1% real return for bonds.
      For stocks it appears that today’s CAPE is mostly correlated with the next 10 years of returns. After that initial 10 year window it’s safe to assume that returns go back to their longer term averages.
      But nothing keeps you from “hacking” the sheet and using your own 0-5, 6-10 and 10+ window. 🙂
      Hope this helps!

  18. Glad I stumbled on these. All these people shouting about 4% always struck me as pretty irresponsible.

    Oddly enough once I add my withholding taxes to my expense ratios, the “0%” failure rate on 100% equities exactly matches my dividends over the last 4 quarters: 2.25%. I’m invested globally, though, so using CAPE30+ may be a little conservative, but then again maybe not.

    Still, living purely off dividends of a bog-standard portfolio at least for the first 10 years of my retirement doesn’t seem so crazy in this 30+ CAPE environment.

  19. Thanks Karsten for the updated spreadsheet, I like the CAPE based tab which help quantify estimated withdraw rate based on Equity valuation it is crucial particularly after the next market correction to re-evaluate the SWR.

  20. ERN getting some very odd results. If I put in 100% cash allocation (or 100% gold) then increase the real return to cash to 10% the SWR/failure rates don’t change?

    1. I can’t replicate that result. Not tha it makes much sense to have 100% cash, but if I use that the failure probs clearly change when changing the assumptions about the 2018+ return estiamates. Can you “share” your sheet with me? I’ll take a look! 🙂

  21. Hi, and first of all thanks a lot for the spreadsheet.
    Im quite new to all this and have a few problems in understanding it all. Can someone please explain why SWR go up when i reduce net worth?

    1. If you have supplemental cash flows that are mostly positive, then when the net worth goes down but the supplemental cash flows stay the same then the SWR goes up.
      If you were to cut all numbers exactly in half (net worth and all cash flows) it should have no impact on the SWR.

  22. I may be misunderstanding the cash flow sheet. Is inflation being factored correctly? Since medical expenses (used as example) is entered in today’s dollars, shouldn’t changing the inflation rate have an impact on SWR? For example, I removed all additional income in the example and only left the sample medical costs. Changing inflation rate from 2% to 10% make no difference.

    1. The inflation rate input makes a difference only if you enter cash flows that are NOT in today’s dollars. I.e., the three columns that are entered in nominal dollars. If all future cash flows are in today’s dollars it’s no surprise that you don’t see a change in the safe withdrawal amount.

  23. First, I want to sincerely thank you for all the works you have put in this blog and particularly the SWR series. I’m so happy right now because of this spreadsheet. We are near retirement right now and this week I decided to do some calculation that I wanted to do for quite some time but I was too lazy to do. We are Canadian and I wanted to see if we have the same return on the S/P 500, the emerging market and EAFE when we invest in the Canadian ETF in CAD as when American invest in similar funds but in USD. As I feared, for the SP 500 I went back 48 years and the difference is quite considerable. In USD the anualize return is 10,53% and in CAD it’s 6,65% ! I decided to get back to your blog to re-read the series to found out how this could affect my SWR and I found this spreadsheet. THANK YOU! We are targeting a 3% SWR. Do you think that I need to modify the returns in the Stock/bond result to see how this will affect my SWR or just playing with future Real returns fields will give me a good idea?

  24. I’ve just finished the series. It took me a while :-).
    It’s definitely the most comprehensive and mind-blowing work on RE post-retirement withdrawals I’ve come across.
    It is very much appreciated!

    Personally, I’m about your age. Not a US national or resident.
    My wife whose a couple of years older than me and I RE 3 years ago.
    I am quite conservative for a person who RE.
    We also face some more risks as non-US: currency exchange rate risk, political risk, and regulatory risk. We also pay higher expenses for financial instruments.
    We invest in a global portfolio, the equity part trying to track MSCI All country.
    Currently, the equity part is less than your suggestion of 80% of the portfolio (about 65%).
    Our bonds are local intermediate bonds and yielding even lower returns than in the US. Probably will lengthen their maturity when yields are higher, also thinking to change them to our local version of TIPS.
    Thinking of an equity glide path following this series(Actually did a rather rapid glide path the last few years as a form of DCA).
    We are kind of frugal by nature.
    Because of all of the above (plus the high CAPE) we have been withdrawing less than 2% of our portfolio (Actually even less taking into account some temporary benefits we got for the first few years post-RE and a tiny business gig I’m running).
    This also helps us to mitigate SRR at least in these first years of the retirement.
    This is definitely easier for us now to withdraw less than when we are older.
    Also, we have our own mortgage-free apartment that may serve for a fail scenario by moving to a less expensive one.
    Good luck to you and all!

    1. Hi TA!
      Thanks for your comment and your compliment!
      Yes, the situation is very different for non-US investors. Better go with a world equity portfolio, not too much exposure to the (potentially very small) own equity market.
      Great thinking on the Glidepath and the cautious initial WR!
      Best of luck to you as well! We should be able to weather whatever the market throws at us!

  25. Great stuff thanks for sharing. I want to continuously update the results but I haven’t found a reliable source of S&P 500 monthly returns with reinvested dividends. Where can I find the data that you used for the stock returns?

  26. Is the SWR on the main results page inflation adjusted? For example if it is 3.5% this year then the next year you can withdraw 3.53% because of the inflation.

    Also could you remove my previous comment (above this one because i’d rather not respond with my full name)?

  27. Hi Big Ern, Love your work and the google sheet. Interesting that you include a parameter for gold, which I have in my own portfolio at 3-4% as a stabilizer/insurance. Does your data show that historically with all its volatility gold has outpaced inflation by approximately 1%? I had always thought that it tracked inflation long term. The sheet appears to show that having some gold improves the SWRs.

  28. Thank you for posting all this for us . I learned a ton reading all this info. Im planning on leaving my remaining money to my grandson. Im 100 % in equities, have a 3.25% withdrawal rate set. Monthly withdrawals from vanguards admiral total stock market fund. My question is, instead of getting a monthly cola every month. He is only allowed to get the cola every other year. Not forgoing them, but catch up to the cola in year 2, then year 4 , then year 6 etc.. He would lose a mild spending power during that year, but made “whole ” by getting it all every other year. How much would that raise the swr in a 60 year payout? I cant figure out how much that would raise it. Thank you and happy new year.

    1. Good question. With a 2% CPI inflation rate this will make almost no difference. Every second year you’re reducing your real spending by 2%. 1% p.a. on average. So, you could potentially increase your SWR by a factor of 1.01. So about 3.28% instead of 3.25%.

  29. Love this sheet!! It has enabled me to run a lot of different simulations. I thank you tremendously!

    I am having trouble with one thing though. When I plug in the same scenarios in the SWR time series and the Glidepath Case Study tabs, I get different results on failure rates.

    For example, say I plug in a 60/40 equity split with 0% goal on final value on the Parameters & Main results tab. Then I plug in a 60% initial equity weight with 60% final weight with 0% slope on the Glidepath case study tab.

    The results of 2/1929 through 5/1929 show SWR (column E in SWR time series tab) of 4.09%, 4.12%, 4.12%, and 4.04%. However when I plug in a value much higher than this in the Glidepath tab, say even 5%, the minimum Portfolio size remains a positive number.

    Is the expense ratio a culprit? I don’t think that’s it. Sorry for what seems like a moot point, but the Glidepath tab enables me to do so many amazing calculations, so I am worried that it is too conservative somehow (at least that’s what this example is showing me)

    Thanks so much for your thoughts!

    1. Perhaps a simpler example is using a 100% equities:
      – The SWR time series says 3.28% as the SWR for 100% equities.
      – If you plug in 3.28% within the glidepath sheet (using 100% equities and 0% slope obviously), the lowest the portfolio gets is $228k. I think the SWR for the glidepath sheet is around 3.58% for portfolio depletion (which is the setting I used for the SWR time series sheet too).

    2. Not sure if you’re comparing the correct numbers. The Case Study numbers should be 100% consistent with the other calculations. For example if I do a case study starting in 9/1929 and use the exact same SWR calculated in the “SWR time series” sheet by setting the WR in the case study to:
      “=’SWR time series’!E711”
      Then I exactly deplete the portfolio (down to the penny) after 30 years. Of course if the main parameter sheet has a longer horizon than 30Y then you’ll not deplete your portfolio. Maybe extend the rows in the “Case Study” sheet to match your retirement horizon.

      https://docs.google.com/spreadsheets/d/1hqJBMhtYQHw_GIQtad3Quo08fL3EhgFPzOS2YzyrIFY/edit?usp=sharing

      But your comment made me find one small mistake in the sheet: The expense ratio wasn’t subtracted from the case study returns with the glidepath. I corrected that! Thanks for the pointer!

        1. I think the crux of the issue is the 30 year vs 60 year time horizon!

          I also somehow made the glidepath calculation around 0.01% higher than the static calculation through my tinkering. That really adds up over 30 / 60 years!

  30. Fantastic series and great spreadsheet resource. Sadly I am not gifted in math or maths as we say in the UK so struggle with some of the theory / explanation.s. Hopefully I can meet some one at a fire group or perhaps on skype one day who will be kind enough to run me through. I don’t know ( for reasons above ) if this is the same point raised above ( which was down to decimals ) but I have what seems to be an odd result with my numbers – that is for some percentiles a higher failure rate at CAPE >20 than CAPE > 30 eg: on one like CAPE > 20 is 4.34% and CAPE > 30 is 0%. Could be me misudertanding things but please let me know and thanks again

    1. The calculations depend on your personal circumstances, of course. While I can’t say with 100% certainty what’s going on, this sounds like something that I have encountered before:
      A withdrawal strategy would have succeeded in 1929 (when the CAPE was >30) but failed in 1965/66 (when the CAPE was at 22 to 23). Hence the “odd” failure rates by CAPE bucket.
      This is an important caveat! There isn’t a linear, monotone relationship between the CAPE and failure rates. Sometimes your particular situation fares better in a drastic bear market like 1929 and not so well during the very long, dragged out 1965-1982 malaise. Depends on your S/B allocation and your supplemental cash flow parameters.

  31. I really appreciate all of the work that you are doing on this site. I find the information incredibly valuable and want to share your SWR Toolbox spreadsheet (version 2.0) and findings with my peers.

    However, I had a couple of questions.

    You mentioned in part 7 of the Safe Withdrawal Rate series that “It is a toolkit to determine how different withdrawal strategies would have performed in the past. It’s not a forecast.”

    However, you have included the section for “Project Future Real Returns 2018 and forward.” The information added to that section adds what looks like an additional 60 years to the overall analysis of SWRs. My concern here is that the forward looking projection, if not accurate, could skew the SWR results negatively. How much of an impact do the forward looking projections affect the rest of the results?

    My peers are probably looking at 30 to 40 year retirement horizons at most.

    If my goal was to simply compare a single retirement scenario using “only the historical information” against the rest of the orange customizable fields on the first and second pages, how could I achieve this? Perhaps not populate the Projected fields on the first page?

    Again, I love what you have accomplished but simply want to understand how the first two tabs of the spreadsheets tie into each other as it relates to those future projections.

    1. I’ve addressed this in previous emails and comments, but here’s a quick reply I always give:

      The extrapolation of returns is to accommodate 60-year windows of the 1965/66 episode and 30-year windows of the 2000 retirement cohort. It turns out that the final few years of returns have very little impact on the SWR. Thanks to Sequence Risk. See here:
      https://earlyretirementnow.com/2017/05/24/the-ultimate-guide-to-safe-withdrawal-rates-part-15-sequence-of-return-risk-part2/
      Especially here: earlyretirementnow.com/wp-content/uploads/2017/05/srr-table033.png

      But if you don’t like my return assumptions, feel free to play around with different assumptions. You will notice that the 4% Rule fails (or succeeds) almost regardless of what you assume about the returns over the final 10 or so years of the retirement horizon. Whether you set the equity return to 0% real or 10% real. But if you insist on using purely historical data, then you should calculate SWRs only up to the point of 12/31/2018 minus your retirement horizon, say 30 years. Simply ignore the simulation results with starting points after 12/31/1988.
      Good luck!

      1. Thank you Karsten for the explanation. It makes more sense now to understand the need to complete the older cohort retirement horizons by generating the analysis past 2018 which, as you mentioned, wouldn’t impact the overall SWR’s much across their entire historical range.

        However, if I understand correctly, the cohort start dates would have to be all the way up to, essentially, mid 2018 in the overall analysis of all SWRs (no historical data more current than July, 2018 in the spreadsheet).

        As the retirement cohorts approach the August 2018 retirement “start months”, the 1989 (30 year) and 1959 (60 year) and more current cohorts start to slowly increasingly rely on using the “Project Future Real Returns 2018 and forward” section of the “Parameters and Main Results” tab.

        It’s the more recent retirement cohort with very little historical data that is more concerning to me. As these newer cohorts are included into the analysis, does this have the potential to negatively affect/skew the overall calculated SWR’s for all cohorts “if we are wrong” in our forward looking real return calculations?

        Or are the number of cohorts with known historical data so heavily outweighing those relying on unknown future real return data, that the unknown information either won’t or only minimally affect the “total” SWR outcomes against all of the data (1871-2076)?

        Also, based on your prior suggestion, are there any specific steps I need to take in the spreadsheet to be able to ignore/remove the starting points after 12/31/1988 (or earlier if longer retirement horizon) and still retain the rest of the SWR information accurately for all remaining time periods back to 1871? This again is assuming that I didn’t want to include any future projected data past 2018.

        I apologize for any misunderstanding I may have with the spreadsheet. I hope that I’m not asking the same question as before in a different way or am looking at this completely wrong. I appreciate any clarity you can provide.

        Thank you,

        David

        1. First of all, the final cohort to be considered in the SWR study is December 2015. This has not been expanded. The only thing that has been expanded is the window of live returns (currently going to Dec 2018).
          If you want to “kick out” the cohorts that are too mich impacted by extrapolated returns I’d recommendthe following “hack:”
          in the tab “SWR time series” the time series of SWRs goes until the final cohort, 12/2015. Simply delete the rows you don’t want. And not just write over them and clear the formula, but mark the rows, right click and pick “delete rows”
          That will now calculate all SWR conditional on only counting the cohorts you like.

          I can look into editing the clean file to do this via user input but that will take some testing before I can deploy the update. Not sure what else is impacted downstream when I make a major change like this. Hope you understand.

          Also, including the recent cohorts will not really “skew” anything. If anything, the most recent SWRs with too much “made up” data are likely oversetimating the SWR because you’re assuming zero-volatiliy returns going forward. Also, the worst case scenarios like 1929 are not impacted by what you add after 12/2018. And even the 1966 cohort, which relies on a few more years of made up data at the end is not very sensitive to what you assume about returns for the next few years.

          Hope this helps! Good luck with your presentation!

          1. Karsten,

            Thank you so much for the information and quick reply. After posting earlier I noticed that the SWRs only went up to 2015. I think an option (if possible) to be able to choose whether to use future projected returns and historical data versus historical data only, would be a great feature.

            I will certainly pass on this latest update to the group. The information that you have provided on this blog is fantastic, not only for the FIRE community but for all future retirees.

            Thanks again!

  32. Could you possibly explain this in a different way? What you’ve written doesn’t make sense to me.

    Let’s say I have $2k/mo in rentals. Net worth $1M. If my net worth drops to $500k, why would i be able to withdraw a higher percentage of my investment?

    1. This sheet is not about real estate investments. It’s about paper assets. If you want to add income from business/real estate you’d do that through the supplemental cash flows. But keep in mind that in a recession bad enough your other income will suffer as well!

    1. OK, now I understand! Here’s your question again:

      “Could you possibly explain this in a different way? What you’ve written doesn’t make sense to me.
      Let’s say I have $2k/mo in rentals. Net worth $1M. If my net worth drops to $500k, why would i be able to withdraw a higher percentage of my investment?”

      This was in response to someone who had earlier asked this:
      “Hi, and first of all thanks a lot for the spreadsheet.
      Im quite new to all this and have a few problems in understanding it all. Can someone please explain why SWR go up when i reduce net worth?”

      OK, so assume that there are no supplemetnal cash flows. If you change the initial net worth, it has no bearing on the safe withdrawal rate. The SWRs stay exactly the same. And the safe withdrawal AMOUNTS change exactly linearly with the net worth.

      Now assume you have some (positive) cash flows, e.g., pension, Social Security, etc. If you lower the initial net worth by half while the supplemental income stays the same, then these cash flows are now bigger as a % of the Net Worth. That has a positive impact o the withdrawal RATE. Now, the overall impact is still that a lower net worth necessitates a lower withdrwawal amount. But the SWR may be higher. Think of that as the Net Worth being cut in half but the Safe Withdawal amount dropping by only 45% (because the supplmenetal cash flow stayed the same the drop was a bit less than 50%).

      Hope this helps!

      1. one more question (and thanks for your response above; it makes sense now):
        why would expenses, under the cash flow assist tab, be included here? isn’t this something that would be captured in one’s budgeting spreadsheet and would fall under your required safe withdrawal amount which is essentially SWR?

        1. These are the expenses over and on top of the flat spending profile. The program solves for one single number: the safe flat spending rate. Everything in addition to that has to be accounted for through the supplemental cash flow tab! 🙂

  33. Hi Karsten. For a simulation of a future retirement (say, some years off), is it possible to input the expected contribution/year to one’s existing portfolio value (so the Net Worth today section would be assumed to increase). This sheet has been so useful, and I think that the results I am seeing for SWR are conservative in that the current steady cash flow input (or some percentage of it) is not interpreted as as expanding the current net worth (or is this occurring, but I am not realizing it)?

    Many thanks!

    Eric

    1. Good point!
      There is but it’s a little bit of a hack (and I plan to write a future blog post about it).
      Imagine your retirement is still 18 months off. In the “Cash Flow Assist” tab, column L (where is says “Please do not change!!!”) set the first 18 numbers to zero (from 1). Then add your planned contributions (401k, taxable savings, etc.) as positive numbers in the cash flow columns (probably as nominal numbers in columns E-G).
      And you can estimate how much you should withdraw (in real terms) as a % of your current Net Worth taking into account zero withdrawals for the first 18 months and actually adding to the Net Worth in between.
      Hope this works!
      K.

      1. I am also using this to try and simulate future retirement possibilities.

        One thing I’d like to confirm is: When adding current income to the “cashflow” column the amount entered should be how much is left after expenses (and taxes)? Because as far as I can tell putting zero for “scaling of withdrawals” is like saying “I don’t need to withdraw money from my net worth because I already paid for my expenses with my income”, correct?

        Thanks, this spreadsheet is amazing!

        (I made a few updates/modifications to your “Cash flow assist” that make this forecasting easier that I’d be happy to share with you!….)

Leave a Reply

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