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! 🙂
Also, if the Google Sheet and all the options seem intimidating, my friends Jason and Eric at Two Sides of FI put together a video tutorial on YouTube explaining how to get started with this toolkit.
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 be 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 was 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 more 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 an increase in medical, in-home care expenses, etc. which 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 an 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 start 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, the 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!
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
Used your sheets for the first time this weekend. I’ve used other calculators before and could kind of strong arm the information into them to get results. Yours definitely offers more specific areas to enter things like future incomes Etc.
Even in our second year of retirement you never stop rethinking and rethinking your financial choices.
Thanks for the feedback. Yes, we very much think alike: I used different tools before but then wanted to build my own to serve all the specific needs I have. And you certainly want to revisit your plan occasionally. Shouldn’t be too much of a burden. We’re all spreadsheet nerds, right?
How can I calculate my SWR if I’m a high earner using tax-exempt muni bond funds instead of treasuries?
Is it safe to assume that muni funds with a high credit rating would have similar volatility and correlation effects as treasuries, so I can just add the tax savings to my post-tax SWR I calculated using the spreadsheet and my tax rates? Or is there a better way to do it?
I considered replacing the bond data in the spreadsheet with historical muni returns but the available data is a lot less than the treasury data.
It’s a decent assumption. I will likely publish an update at some point detailing how to use combinations of the existing asset classes to replicate other exotic ETFs, like MUB or the like. It might boil down to just 10y treasuries but with a little bit of extra equity exposure to account for the slight credit spread.
Thank you for the deep dive!
Since so many in the FIRE community use VTSAX as the main stock mutual fund, is there any value in seeing how the SW rates stack up if not using an S&P fund?
Additionally, is it possible to see the implications of not reinvesting dividends?
Great site with a lot of very helpful information.
The correlation between VTSAX and S&P500 is > 0.99.
I’m using the S&P 500 total return index. It includes dividends. How you withdraw from your equity holdings, i.e., whether you take the dividends or the capital gains has no effect on the simulation results.
If you want to see how a 100% equity portfolio retirement would look like when only withdrawing dividends, please see Part 40: https://earlyretirementnow.com/2020/10/14/dividends-only-swr-series-part-40/
Thank you for the reply. I’m considering a “coast FI” type pathway. We will have enough quite shorty to meet most of our goals, but would not plan on withdrawing for another 10 or so years. I read your post about taking dividends and not reinvesting (as to avoid tax on dividend and cap gains). So would want to keep reinvesting during that horizon. Helpful to know that does not affect simulations for SWR though.
Then you set your SWR equal to today’s dividend yield during that time.
My only fear is you are going to stop updating the asset returns and i wont be able to use the sheet anymore 🙁 what an amazing resource!
Haha! Then someone else, someone smarter and harder-working will take over for me! 🙂
OK i have been using this spreasheet since you made it and I still love fooling around with it but I have a question. I am a federal employee who wants to retire at 57 but the pension will not become inflation-adjusted until 62. Do I put the initial withdrawal rates in the not-inflation adjusted column and then once it gets down to age 62, transfer whatever the discounted value of the pension is at that point into the inflation-adjusted columns?
You could put everything into the real column, just deflate the numbers for the first few years by, say 2-2.5% p.a., then keep fixed at that level.
OK i have been using this spreasheet since you made it and I still love fooling around with it but I have a question. I am a federal employee who wants to retire at 57 but the pension will not become inflation-adjusted until 62. Do I put the initial withdrawal rates in the not-inflation adjusted column and then once it gets down to age 62, transfer whatever the discounted value of the pension is at that point into the inflation-adjusted columns?
duplicate. see reply to the other comment.
Perhaps it’s covered somewhere else in the mountains of dialog and description, so sorry if that’s the case..when you enter “REAL” returns in the assumptions, what does “real” refer to?
Real= inflation adjusted
Nominal= unadjusted
As inflation is almost always positive the nominal number is numerically larger but the real number gives you a today’s money equivalent figure.
For example if the nominal return on equities was six percent but inflation was two percent then the real return on equities would be 4%.
Thanks, if real = inflation adjusted, so a 9% return is actually 6% with 3% inflation.. just stating the obvious so the concept “real” is not glossed over.
Then wouldn’t it make sense to add that inflation % number–where it’s stated on tab #2 in Cash Flow Assist–so that inflation number is an integrated formula, calculated uniformly into whatever nominal return one typically tracks?
I don’t know about you, but when i think of any return from a stock or bond, I don’t do math in my head that subtracts 3.25% or whatever the # is.
Personally I think in real terms and get annoyed when people talk about returns without specifying real or nominal. I also get frustrated by optimistic expectations set when nominal figures are banded about.
When it comes to using the model bear in mind that you are inputting a value to use over a decade or more so it isn’t just the current value. I would leave the details unless you have a strong view based on sound reasoning that justifies deviating. Even then I wouldn’t expect the decision to be large.
Yeah, I find that annoying, too. i wrote a blog post dealing with issues like that one! See items 2 and 3 in https://earlyretirementnow.com/2019/05/22/how-to-lie-with-personal-finance/
My sheet is set up to model real spending and track real portfolio values. To transfers future expected nominal values into real values, you need to discount them by a future expected inflation rate, which is what I do in the tab “Cash Flow Assist” in columns J-N.
What you suggest in your comment after “Then wouldn’t it make sense…” doesn’t make sense. I vouch for my sheet to do what it’s advertised to do. If you mess with the formulas you will likely get nonsensical results.
Not challenging your work, unvouching it in any way. hopefully it didn’t ding your dopamine levels. I’m actually quite enjoying how it’s well done. Bravo, I think you’ve done humanity a great service.
As I’m not suggesting a fix, I’m stating how easily it would be for a leagues of financial novices, of which we all once were, to think they’re entering a market return for their return. I assumed it was that -and I’d hazard many more here have, because when anyone speaks of stock or bond RETURNS, the convention is not stated by calculating fractions of inflation on the fly.
We look at our returns on financial sites. Luckily saw the term “real” which is missing on the current spreadsheet..but was on a 2022 one awhile back?
So again if inflation is 5% and you have 5% returns onbonds, ENTER “0%” returns!
No offense taken.
Also: you don’t enter any returns in my sheet. You use historical returns provided by me. The only exception is the “Project Future Real Returns” feature which I now explicitly urge people to not change. See cells A752:B768 in Tab “Parameters & Main Results”
Just to be sure, I added a disclosure at the top of the main page: “Returns and withdrawals are real, CPI-adjusted”
Also if you want to look up historical nominal returns, please check out the tab “Asset Returns” and columns E-M. Those are nominal cumulative returns, so x(t)/x(t-1)-1 will give you nominal monthly returns.
Good answer!
Real vs. nominal: inflation-adjusted vs. not adjusted, raw returns
To automatically include inflation into the expected return, this was the formula I used.
=sum(5%-‘Cash Flow Assist’!E6)
The “%” in the above formula is the commonly regarded everyday nominal return used in colloquial discussion when describing a return.
Where and why would you use that formula?
Hi Karsten, your marvelous Sheet has become an essential part of our monthly financial planning during our retirement. I open it at the end of each month and update it with our latest data. Doing this has made me arrive at a couple changes I would love you to make.
#1 Retirement Horizon. Each month my time horizon shrinks :-(. Rather than a number of months, it would be nice if this parameter was an age or date. I have hacked the sheet for myself by inserting the formula ‘=datedif(now(), “6/1/2067”, “m”)’ which is easy enough but I think others would appreciate this change and the more hacks I make the harder it is becoming to move to new versions of your Sheet.
#2 The Cashflow Assist sheet has a nice feature of trimming off old months but since it doesn’t also trim off my cashflow entries for the old months it essentially rolls them forward. My SS which I plan on taking at 70 now starts in the sheet at 70 plus one month. This means that every month when I open it I have to move all the numbers around on that sheet. Am I doing something wrong?
#3 I recently made a mistake of not sync’ing my time horizon with by cashflow partly due to the Cashflow Assist sheet having dates that extend well beyond my horizon. This is what we call in the software development world a priority 3 (only get to it time if you’re bored), but it would be nice if the Cashflow Assist sheet ended at the end of my horizon.
Thanks again for all your work! Jason
Thanks for the feedback.
These are all interesting add-ons, but they are not a high priority for me right now.
1: I use this sheet as my research tool and I enter some exact number of months for the horizon, like 360 or 480 or 600, etc. I’m not going to automate the horizon.
2: Yes, you’d have to shift the cash flows back one month. Another option would be to have another tab with the cash flows linked to precise dates. Then, use vlookup to read those cash flows into the appropriate row in the Cash Flow Assist tab.
3: If cash flows extend over your horizon they will be ignored in the calculations.
Totally bonehead question but I am trying to account for differing cost of health insurance before and after 65 for spouse and I. If i enter, lets say, “-1800” in the inflation adjusted column, is the SCR that is solved under the CAPE-Based Rule tab NET of my health insurance, i.e. when I plan a retirement budget, the budget does not have to contain health insurance because I already accounted for it in the cash flow tab? Thanks for all you do, you genius man you
2 ways to accomplish this:
1: model the expected health care expenses as negative cash flows. Then the sheet calculates the safe consumption amount ABOVE the health expenses.
2: Keep 0 in the supplemental cash flow columns until age 65. Then model the jump in premium as a negative amount equal to the premium jump.
Now your safe consumption amount is the retirement budget including the lower of the two premiums.
Do you have a guide, or can you point to one, on how to adjust the Fama-French factors (the %s for HML and SMB) when we input our porfolios? I have a portfolio for USA equities that is primarily index funds (like VBK, VTI) but I also have some single-stock positions (e.g., GOOG) and a variety of bond (IAGG, BND) and international-stock funds (e.g., EEM, VEA, VEU). Personal Capital/Empower breaks down my USA allocations into categories of Value / Core / Growth for Large, Mid, and Small cap stocks. Not sure how to calculate the %s to plug into the model for the Fama-French factors.
You’d have to run a factor model to see how much growth/value or small/big bias you have.
I added a tab in my sheet to include a bunch of widely popular ETFs and how they would be replicated through a combination of the 8 return factors (SPX, 10y, 30y, cash, Intl Stocks, Gold, SMB, HML).
I plan to automate this and eventually add more return series, including some individual stocks. For now, you might have to replicate the GOOG with QQQ. Or get your hands dirty and run a factor model yourself.
Thank you for adding the tab that has the ETFs etc. At the risk of asking too much — and giving away my naïveté — how exactly would I apply the %s shown to the ETFs I have, when looking to fit them into the Parameters (main tab)? For example if you look at say VNQ that shows, among other things, 78.5% (correlation) to SPX-TR, 65.53% to 30yr-BM, 27.88% to SMB factor and 30.33% to HML factor, how would I look at allocating say $100K of VNQ on the Parameters tab.
—>I think on the SMB/HML side I’d apply the $ values in those shown %s when I try to figure out how much of my porfolio total value has either SMB or HML weighting, to try to calculate overall %s of SMB/HML for my portfolio?
—>But for allocating that $100K of VNQ to the other buckets, between SPX-TR or 30yr-BM etc, as the %s don’t sum to 100%, should i just pick the most correlated category (highest %) or try some allocations spread across categories depending on the correlation %s (at least where there is positive correlation)?
–> In more simple terms, if you had say $1MM of EEM, how would you use the %s now shown on the ETF correlation tab to allocate that $1MM of EEM on the Parameters tab.
I appreciate your taking the time to respond and I apologize in advance for not being that sophisticated here. Maybe the answer will help other folks besides me, especially now that you have the ETF tab.
I added a sample calculation in that same tab. And how to implement that in the portfolio allocation.
I’ve used the sheet in the past, but taking another run at if after ~1.5 years away from it. Have bought a house just after my last go around with it, and am trying to think through the application of the mortgage cash flow. I’m expecting my initial portfolio to cover those costs in my initial Safe consumption rate, so wouldn’t I think about the ending of that mortgage as a positive cash flow in ~28 years?
Also, to note, that I’m discounting the principal & interest payments between now & the retirement date, which is then the figure I’m including in the undiscounted CF model at the time my mortgage ends.
Am I backwards here?
I’m backwards here. Feel free to ignore. Thanks!
Whew, thanks! 🙂
I wound up downloading the Google sheet again, and was scanning through the posting and noticed that the top line of the second part of the table located https://i0.wp.com/earlyretirementnow.com/wp-content/uploads/2018/08/swr-part28-table02.png?resize=798%2C554&ssl=1 has 3 entries of 3.25% SWR to achieve 0% failure rate, but the first part of the table shows for “All” 3.25% with 0.06% failure rate, so it can’t be 0 in the second part, “Since 1926” 3.25% resulting in 0.09% failure rate, and “CAPE >20” 3.25% with 1.75% failure rate, so looks like a copy/paste error?
Also, the 1% failure rate entry for the last column looks odd as well, since it’s listed as 3.32%, but the first part of the table shows 3.25% WR resulting in 1.75% failure rate, so the second part’s entry should be less than 3.25% and not 3.32%
TTFN
1: Rounding error? The actual failsafe might be 3.249. And at 3.250 you have one single failure.
2: That’s odd indeed. You can’t have a 1% failure rate at 3.32 and a 1.75% failure rate at 3.25. But I only see a screenshot. In my own table where I can check formulas there is no such inconsistency.
Too bad there’s no way to add files or pictures; the first two values seem like they should be closer to 3.15% WR to get to zero, based on graphing the data in the table. The last column seems to have both WRs for 0% and 1% too far off; the 1% FR seems like it should have around 3.275% WR and the 0% FR should correspond to maybe 3.05% WR
TTFN
you could create a copy of the file and share the file with me to take a look. Otherwise there’s no way for me to see what’s going on.