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
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! 🙂
Gal, you might consider using Excel’s “solver” (or “goal seek”) functions. They allow you do solve one square while adjusting others.
Hi there everyone! First off, Big ERN and family, congrats on your… well ER! It took a while to chew through all these SWR series blogs, but I made it out the other side and started using the spreadsheet. Curious what you think of this little exercise I did (aka did I just do some good, bad, or reeeaaally ugly math!). Let’s say I’m 33, want to retire at 40, and run out of money at 100:
* 60yr retirement, 0% end amount -> 3.25% WR at 75/25 S/B (per spreadsheet). $30k expenses -> $923k portfolio (this is in 2019 dollars, not at retirement age, so inflate both by 7 years).
But what if we think of our money as being separated into the 401k/IRA bucket and the taxable account bucket? Would it be valid to consider these as 2 different retirement scenarios? For example:
*20 year retirement (age 40 to 60), 0% end amount -> 4.56% WR at 64/36 S/B (per spreadsheet). $30k expenses -> $658k taxable account (again inflate both by 7 years for nominal amounts).
*Then 40 year retirement (age 60 to 100), 0% end amount -> 3.58% WR at 75/25 S/B (per spreadsheet). $30k expenses -> $838k tax advantaged account (inflate this by 27 years for nominal starting amounts).
So, is this a bunch of hooey, or did I just unwittingly do some form of glidepath?!?!
You can certainly separate the two. But the results should not be very different. Or they differ only because of different asset allocations in the two buckets.
I just want to double check something that seems unintuitive: When I use the default settings I see a 4% SWR with 10% failure rate (“All” data). But when I raised “stocks after that” returns way up to 20% returns the SWR only goes up to 4.05%. If I set “stocks after that” returns absurdly high 40% the SWR only goes up to 4.06%– in an 80/20 stock heavy portfolio how are returns of 40% for SP500 for 50 years of so little consequence compared to the default 5%?
Makes sense. Most failures of the 4% Rule occur outside of the window where you’re setting the projected future returns: 1929, 1965, 1973.
Hi,
please let me issue a warning for libreoffice v6.2.5.2 users.
Libreoffice has a known bug, persistent since some versions.
This bug results in NOT recalculating cells, in case of many cells referring to cells of following rows eg. A1=A2+1.
This style was used by ERN for calculating the “Cummulative Backward Return”, that depend on the Stock/Bonds values.
Even forcing recalculation via shift-ctrl-F9 dont work, only triggers a single cell recalc (visible as the SWR values change per trigger) but no complete all-cells all-sheet recalc.
These ugly changes patched the bug:
in tab “stockBond returns” replace column T “cummulative backward return” (left from C1) with 3 columns:
column T: =ZELLE(“contents”;VERSCHIEBUNG(S$2477;7-ZEILE(T8);0))
column U: =U7*(1+T8)
column V: =ZELLE(“contents”;VERSCHIEBUNG(U$2477;7-ZEILE(V8);0))
column W: is the former C1, using now col V data
(sorry for the german function names, dont know how to retrive the english style)
In addition, the recalculation is only triggered, if any cell in this sheet gets changed, anyhow, a single letter will do.
So, each time I change the Stocks/Bonds/Cash relations, I afterwards change a commentary cell in “stockBonds returns” sheet. (This is not required for some other changes like “retirement horizon”).
BTW Im using the google sheet save-as format .xlsx, loaded into libreoffice. (save-as .ods format dont work, one of the tabs get skipped from export).
PS. ID like to emphasise the commentary of GAL https://earlyretirementnow.com/2018/08/29/google-sheet-updates-swr-series-part-28/comment-page-2/#comment-16662 : if you add some pensions starting at some future month, you may get too high SWR (eg. add a silly huge single time pension, you may see a SWR over 100%). Im now checking two horizons, one till my planned death, the other till start of pensions, with some cash left. The lower SWR wins.
PPS. thank you ERN for this professional sheet. It boosts my confidence in my plannings.
Wow! I’ve never had problems when using the sheet in MS Excel. Good point, I don’t vouch for anything downstream that uses non-MS Excel. The English functions would be “CELL” instead of “ZELLE” and “OFFSET” instead of “VERSCHIEBUNG”
Re your PS: I am aware of this and it’s not an error. You can certainly get safe withdrawal rates >10% or even 20% of your INITIAL financial net worth if you’re expecting a large cash infusion during the first few years in retirement (cash settlement, etc.). Even really large amounts if you’re expecting a “gazillion” dollars. So, always do a smell-test and make sure that there isn’t a cash flow problem where you’d have to borrow funds before the large cash flow arrives.
BERNI, I like your method of checking 2 horizons and taking the lower SWR, as a way to overcome the issue I raised.
I think some of extra by work can be eliminated by considering the following analysis:
The only scenarios in which future cash flows “rescues” a depleted portfolio are when the future cash flows themselves are larger than the monthly withdrawal (as derived from the current net worth).
But these are the exact scenarios in which you only have to check a single horizon – the one until the start of the pension. The other one is already covered.
Hi ERN, thanks so much for a super helpful spreadsheet and blog! This is amazing work and makes us feel much more confident about early retirement. Is there a way for us to update the sequence of returns assumptions with the actual performance data for our portfolio as the first 10 years go by, so we can dynamically update our fail-safe withdrawal amounts to reflect actual performance over time? That would be a super helpful addition! Thank you.
There’s no need to tie actual/historical data.
A more elegant way:
As the first 10y go by you should adjust the length of the retirement horizon to reflect the shorter life expectancy and then move the future cash flows accordingly (e.g., a year ago you were expecting a $10,000 payment 20y into retirement, now make that 19y into retirement) and update the current financial net worth number.
Great resource – would we be able to use the custom column to include Shiller data on real estate prices – for those who want to consider that an asset class? Income is a separate component harder to deal with, but investment in real estate is missing as an asset class from pretty much every such simulation / tool.
I would very much advise against that. Multiple reasons:
1: the CS is a pure price index and doesn’t include the (net) rental income. It would eriously underreport the attractiveness of RE. See this post https://earlyretirementnow.com/2019/10/16/how-to-lie-with-personal-finance-part-2-homeownership/ especially lie #1
2: Your individual RE portfolio will likely have a much higher risk/standard deviation than the CS index!
ERN, if you want to not post the problem with my saving the Excel file, no problem. I found the “show older posts” button and read all the older posts and found your answer to a similar question. Have to admit, I have a significant pension, so the SWR was confusing me at first. I eventually figured out it was safe total spending and then saw the post confirming it was really being safe total spending. Thanks!
Correct: this is a safe spending rate, adjusted by the supplmental cash flows.
Are you familiar with the SWR calculator at https://portfoliocharts.com/portfolio/withdrawal-rates/ ? I was really surprised at how drastically different your results were for a 25% Stock, 25% Bond, 25% Cash, 25% Gold portfolio was. Their SWR was 4.7%, yours was 2.7%. Yours seems suspiciously low considering that even their Perpetual Withdrawal Rate was 3.8%.
Any thoughts on what could be causing this ENORMOUS difference?
The difference is due to the other calculator using only the 1970-current interval. If you remove the two worst episodes (1929 + 1965-68) you get higher SWRs.
But that’s not very comforting! It’s like estimating the probability that you’ll get into a traffic jam by looking at traffic patterns only between 1 and 3 am!
I thought I answered a similar question already.
Careful about that calculator. It starts only after 1970. So it misses the worst retirement cohorts (1929, 1965-66, 1968)!
Greetings Ern. And thanks again for this terrific sheet!
I am having trouble reconciling the result of Safe Withdrawal Rate as a function of CAPE; specifically, for, say, the 0% failure rate, the sheet returns values of 8.04% (CAPE 20), 9.6% (CAPE >30). I was expecting the SWR to be inversely correlated with a increase in CAPE. Can the above returned values be realistic?
As a background, My wife and I have $2.6M in a 60/40 stock/bond ratio, She will work and contribute $2850 to retirement savings for 1the next 144 months (so the Scaling of Withdrawals column is set to 0 for those months, and 1 for all months thereafter), while I will contribute $2850 for the next 24 months. I assume a collection of 1738/month Social Security for from month 60 though month 516; my wife would collect $2100/month from month 145 through month 516.
Thanks very much for your insights on this question. Hope all is well by you.
– Eric
Clarification: The returned values are 8.43% (CAPE 20), 10.13% (CAPE >30). As noted above, was expecting SWR to be inversely correlated with CAPE. Thanks.
Gotche. Please see comment I left on the original note.
Two observations to reconsile this:
a) the SWR is not a strictly monotone function of the CAPE when looking at historical data. Sometimes the worst failure happens for 1960s cohorts when the CAPE was “only” in the mid 20s and you had a better SWR in 1929 then the CAPE was 30+.
b) because you start your withdrawals 12 years into retirement, the CAPE that will decide over success and failure is not so much the CAPE now but the one 12 years from now!
Thanks Karsten. Your response makes perfect sense; i.e., historical data, vs a formula to be used for a current (at retirement of thereafter CAPE value. Much appreciated
Thanks for sharing this wonderful tool! I have 1 question: is the tool using historical inflation that actually occurred each calendar year, or is the tool using the inflation parameter on the cash flow tab? I thought it was only using the inflation entered in the cash flow tab for future years, but I am not sure, as when I change the value, SWR changes for all periods, even historical periods. Thanks again.
I am guessing that income in cash flow columns that are not adjusted for inflation are discounted by the inflation rate that you entered; i.e., with, say, a higher inflation rate, the real present value of contributions to your net worth is reduced accordingly, and it is the therefore reduced real value of your portfolio (in, say, this example) that is subjected to the forces of history.
Erics, thanks for the explanation. That makes perfect sense, and I would guess that you are correct.
The CPI input is purely to estimate how future nominal cash flows translate into real cash flows. All past simulations have used real returns with actual CPI numbers.
The fact that the inflation estimate impacts your SWR results must be due to using future nominal cash flows.
Hello ERN,
I’d like to read your opinion about the following withdrawal method:
– On the start of retirement, use the sheet to calculate the fail-safe withdrawal amount. Let this amount be x0. Withdraw x0.
– After 1 year, calculate the fail-safe withdrawal amount based on current portfolio data and shorter retirement horizon. Let this amount be x1. Withdraw max(x0,x1)
– Repeat the last step for each subsequent year: on year n, calculate xn and withdraw max(x0,x1,…xn)
All withdrawals CPI adjusted as usual.
1. The method retains the main constant-dollar advantage of guaranteeing the same real spending power every year during retirement.
2. It overcomes the main disadvantage of constant-dollar method of not taking advantage of portfolio growth which is very likely to happen.
3. On top of that, it keeps being fail-safe. Specifically, applying this trick to any withdrawal with positive failure rate will further increase the failure probability, but not such is the case if you go with the fail-safe withdrawals.
Thanks for the suggestion.
It’s the right solution for the wrong problem. This will guarantee that in a long bull market you adjust upward to make sure that you don’t have excess money left.
It’s not a good solution for long horizons if there’s a recession and bear market around the corner. You’ll guarantee that you’ll “grab” the peak and then expose yourself to Sequence Risk.
But I agree: you should adjust your withdrawals in response to changing economic realities: But that also involves reducing withdrawals sometimes. 🙂
But doesn’t being fail-safe mean that no matter what peak or sequence risk waits around the corner, the fail-safe property is being preserved?
I understand why such a method would increase the risk for non fail-safe widthrowals (conditional probability) but can’t see why it the case for fail-safe widthrowals (still conditional probability but zero chance).
That’s true if you pin it to the all-time fails-safe. If you’re comfortable that in the future nothing gets worse than the Great Depression or 1970s then you are indeed safe. Good point!
Question regarding consistency, because I’m trying to figure this out – with the most recent version of the above spreadsheet (downloaded today), if I go to the case study tab and put in a 4% SWR with retirement starting in January 2000 and a 60% static equity weight, it estimates that in January 2020 I’ll have a portfolio balance of $501.6k real.
If I put the same thing into Portfolio Visualizer – https://www.portfoliovisualizer.com/backtest-asset-class-allocation with 60% US large cap and 40% 10-year treasuries, identical withdrawal parameters (of an inflation adjusted $3333 monthly) – it says I end up with an inflation adjusted $725,907.
The only thing I can figure out is if the bond duration is different in one or the other simulator. Would you be able to assist?
Never mind. I’m an idiot. I didn’t realize the static allocation uses the numbers from the parameters tab. Math works out just fine when I fix that – within a couple percent, which is probably just due to order of operations regarding withdrawals.
Ah, good to know! I checked and found that they line up pretty well, too! 🙂
I get results that match better:
Portfolioviz.:
https://www.portfoliovisualizer.com/backtest-asset-class-allocation?s=y&mode=2&timePeriod=2&startYear=1999&firstMonth=12&endYear=2019&lastMonth=12&calendarAligned=true&initialAmount=1000000&annualOperation=2&annualAdjustment=3333&inflationAdjusted=true&annualPercentage=0.0&frequency=2&rebalanceType=4&absoluteDeviation=5.0&relativeDeviation=25.0&portfolio1=Custom&portfolio2=Custom&portfolio3=Custom&LargeCapBlend1=60&TreasuryNotes1=40
Final Value 701,219 on Dec 31 2019 (inflation adjusted)
My spreadhseet:
https://docs.google.com/spreadsheets/d/1MjWyWpM36dvr1p-cLd0lNBwfz4eygMT1XnWakZdmC2U/edit?usp=sharing
Value on Dec 31 2019: $700,980 (CPI-adjusted)
Note: in order for the dates to line up you’d need to start the PortViz sims in Dec 1999, but my sims on Jan 2000 (because I always assume that the first withdrawal happens on the last of the month before retirement starts).
Close enough for government work!
The residual may be explained by different expense ratio assumptions (0.05% for me, didn’t see any input for the E.R. in PortViz, so I assume zero)
hello big ern.
I read your entire “safe payout series”. I think this is a great job!
How can I use your Google spreadsheet tool for a non-US resident?
Thank.
I would be cautious about translating it directly to non-US investors. You could, of course, hold the same assets (US stocks, US bonds) in your non-US portfolio and have identical SWR sim results but you’d face the exchange rate risk. Using the same S/B allocation but with your home country assets means that you might face a very different return path, i.e., lower bond yields, different equity valuations, etc.
So, my platform is targeted at USD-based investors. Use with extreme caution when operating in a different country/currency!
HI Big Ern!
I am new to your blog. It is exactly what I have been looking for!! I also really enjoyed the podcast with Andrew Chen. Thanks for such an amazing resource. I have a question about your toolbox. I noticed that the inflation input does not affect the failsafe withdrawal amount. Shouldn’t the SWA increase each year with the inflation number calculated in? Sorry if this has been explained or is obvious.
It depends. If you have no futures nominal cash flows then the inflation expectation has no bearing on your SWR because everything is done in real dollars.
Even with Social Security, because it’s inflation adjusted it should have no impact.
Only if you have future nominal cash flows (corporate pension) the CPI estimate would impact the future (real) value of that flow.
Enjoying reading the SWR series. Quick question on the ERN SWR Toolbox spreadsheet. I have a government pension that I will begin receiving at age 60 (month 89 on our spreadsheet) of approx. $1,000 per month (nominal $). Once I begin drawing the pension at age 60, and not until then, the payment will be adjusted for inflation based on CPI going forward. In other words, Year 1=$1,000 per month, Year 2=$1,000 + CPI adjustment/month, etc.
So, do I want to enter this payment into the “adjusted for inflation” cash flows area starting in month 89, but discount the $1,000 based on the projected inflation rate and enter that value from month 89 on instead?
If your pension is reported in today’s dollars then enter it in the cash flow tab (columns B-F).
One caution: will the $1000 figure still grow in line with inflation over the next 89 months? Then, nothing further to do.
If the $1000 is in nominal dollars (but will eventually increase with CPI) then you’d need to “discount” by roughly 7.5 years of inflation, so about $1000/1.16
Thanks for such a great tool! I’m quickly catching up reading all your posts but have a question about the ‘Failsafe by Equity Drawdown’ data, how do I interpret that data? If the market drops by a certain % then my withdrawals can increase?
I’m looking to ER early 2023 when I’ll be 51 and I’m comparing your great toolbox sheet along with the VWP sheet from the Bogle people, your is more conservative but I assume because it is not a variable method? Great news is even using your more conservative numbers it seem like the withdrawal suggested is more than my theoretical ER budget!
Yes, if the S&P is below its all-time high you can and should factor that into your SWR.
Keep in mind though: the higher RATE is applied to a potentially lower portfolio value. So, you may still withdraw less money on a net basis.
VPW is a good tool. But keep in mind that your annual withdrawals are just as volatile as your portfolio. That’s the price you pay for a higher initial WR.
Hi Ern,
Thank you for all the great research, modeling, and content that you have been sharing.
After reading your blog posts, the paper you published, and working with your Google sheet, I have come to realize that I struggle with one fundamental question: What is the withdrawal method I should use in retirement?
In your posts where you review the constant withdrawal adjusted for inflation method (“4% rule”) you come to the conclusion that (per your blog post part 2) an annualized withdrawal rate of 3.25% of initial portfolio value, then adjusted for CPI for a 75/25 portfolio over 60 years has zero risk of failure. In other words constant withdrawal adjusted for inflation is a good method as long as we rachet down our withdrawal to 3.25%.
However, in the posts that follow you review dynamic (flexible) withdrawal rules, and come to the conclusion that the CAPE based withdrawal method is probably the best flexible method. The CAPE withdrawal method would start around 3.1% Net Worth withdrawal at this time, but we (according to your worst case analysis blog post) should be prepared to live off of 2.5% of present Net Worth (and maybe even less?) for extended periods of time using this method and predicting a bad run of poor successive returns.
I’m wondering why would I pick the flexible CAPE method when it has a lower withdrawal both now and possibly in the future compared to the constant withdrawal adjusted to inflation. If I can live off 3.25%, why not just draw at that rate since it makes budgeting so much easier, I never have to plan for less money? If you can explain why you would pick one method over the other I would appreciate that.
Finally, I am curious what you think of the Vanguard results, Revisiting the ‘4% spending rule, MA Bruno, et al. In this paper and using the Vanguard Capital Markets Model their results show only a 75% chance of success at 3.2% withdrawal rate over 40 years for the constant withdrawal method adjusted for inflation. This is quite different than your result of 3.25% withdrawal rate with 100% chance of success over 60 years. I realize there are large differences between your two models, but which do you think is more accurate and why?
I look forward to your response and hope it will help me untie my mental knot. Thank you Ern!
Good questions.
When you use the CAPE-based rule, keep in mind that it’s not written in stone. If you use parameters a=1.75% and b=0.5 then with a CAPE of 30 you get a dynamic SWR of 3.42%. You can further increase that by making as adjustment to account for depleting your money over time (comparable to the Bogleheads VPW), so you should be almost at 4%, which is a bit higher than the fixed 3.25%. ut you also face some more volatility.
The Vanguard study probably relies on Monte-Carlo simulations. I would discard those results.
Hi BigERN,
Thank you very much for your articles and the Toolbox spreadsheet. They really help me get more comfortable with the decisions I need to make.
I just had one quick question for clarification around the relationship between the derived SWR and the supplemental income. Should I assume that the “SWR” results in the total net cash amount for a year, and that any supplemental income for that year would then be subtracted from that amount to arrive at the actual amount one would need to generate from the portfolio?
For example: with 1MM net worth, and a calculated 4% SWR, we get 40k per year. If a given year also has 10k in supplemental income, would the amount I actually “withdraw” only be 30k? Or would I still withdraw the full 40k, and enjoy the 10k supplemental cherry on top for that year?
Thanks again!
That’s exactly how I interpret my results. The positive supplemental cash flows will reduce the withdrawal. Negative supplemental flows will increase the withdrawals!
Hey Karsten,
I have created a model of my retirement portfolio and cashflow requirements (Pre-tax 401Ks, SS, Pension, Roth IRAs, etc.) and the required annual retirement budget. I have also accounted for taxation of the Pre-tax, Pension and SS cashflows. Finally I have modeled Roth conversions, RMDs, and their taxation as well.
Taking the inflows and taxation burdens into account over time, as they change Y-O-Y, I have been able to derive the annual withdrawal rates. By delaying some of my cashflows like SS claiming to 70 for my wife and I, it requires us to take a larger withdrawal rate until those sources start cash flowing. For example retirement until 70 on average I will need to take 4%. Starting at age 70 we can push that withdrawal down to 1%. Another strategy that I can evaluate is deferring my pension which would require a higher initial withdrawal rate, but also increase the future cashflows from the pension depending on the length of deferral.
In my spreadsheet I have all of the inflows, outflows, taxes, reinvestment of Roth conversion and RMDs that are in excess, etc. My WR is calculated as a percent taking all of this into account. This allows me to simulate different strategies and understand their impacts.
I would like to use the data within this spreadsheet and put required WRs that I have calculated to see how that performs, since they will be significantly front loaded. Is there a provision here for this type of analysis where I can plug these data points in?
Love the data, love the blog, and thanks for the critical thinking that you do and share with everyone.
Wow, great job. I don’t exactly know what your model looks like. The big unknown: what kind of returns do you use? If you want to test this with historical returns, I post my Google sheet, including historical returns and inflation rates. Complete Transparancy in the sheet.
What you can do with my Google Sheet is you can certainly plug in the supplemental cash flows due to pensions, tax liabilities, etc.
I don’t currently offer a sheet to model Roth conversions.
Wonderful resource, Big ERN, as is the entire blog and the series on SWRs. Thank you for creating it and sharing it with all of us! I have read many of your posts multiple times and each time I learn something new.
Thanks! Glad you like the content here! 🙂
REALLY GOOD JOB. THANK YOU VERY MUCH
You bet!
Apologies if this is a basic question. If my equity allocation is split 50/50 between VTI Vanguard Total Stock Market and VBR Vanguard Small-Cap Value Index Fund what values would I enter for Small Stocks (SMB) Value Stocks (HML)?
Thanks very much for your spreadsheet
For the 50% SCV fund you’d set the small and value styles to 0.5 each. If you’re really admant, you can also add a little more to the Fama-French small style because the VTI is about 15% or so in small stocks. So, add another 0.5*0.15=0.075 to the small style.
Hi, will be nice to get updated version of this google sheet together with one full topic on how to use it. Or even better to include that instruction in the sheet.
Yeah, it’s on my to-do list! 🙂
Sorry if this has been asked before. If we want to update our own saved spreadsheets as you publish new market data can we simply add in the new rows for columns E-T on the Stock/Bond Returns tab or is there more that we need to do? Thanks!
Normally yes. But in the most recent update I added more columns in the Stock/Bond tab. You’d have to be really careful to make sure the columns stay consistent. It might be easier to use the new spreadsheet and simply transfer your parameters and cash flow numbers.
I’m having a difficult time understanding the logic behind this so perhaps one of you can help me visualize these implications. My current 0% fail SWR based on my inputs is 3.27% (I include an inheritance and social security). This is the box in the sheet that has a 0.00% for All, CAPE>30, S&P500 High and 2000s. For a $2,000,000 portfolio that is a withdrawal amount of $65,400. If over the next day, month or year the markets rallied 100% I don’t believe this 0.00% SWR would change and so it would still be 3.27% and my portfolio would double if I was 100% invested in equities. So my new failsafe withdrawal amount is $130,800. If again over the following day/month/year the markets dropped 50% my portfolio would be back to where we are today but now my 0.00% failsafe for CAPE>30 is 3.27% but for S&P Drdwn>30% is 3.83%. Those safe withdrawal amounts are 65,400 or 76,600. Either way it is significantly lower than the $130,800 from when we were higher.
I realize my example is extreme but does it seem realistic that I could continue to withdraw my initial $130k and adjust for inflation and make the money last 30-60 years? I’m trying to determine if a SWR at S&P500 High needs to be regularly faded as the highs become more extreme. Perhaps based on some measure of how much higher the current high is than a prior high before an X percent retracement. I’d appreciate any feedback.
Yeah, it’s an extreme example. You can certainly justify walking up your safe withdrawal amount when the market keeps rising.
But: If from today’s level (CAPE=34) the market were to go up by 100% on Monday (CAPE=68) you probably don’t want to double your withdrawal amount. And if the market drops again by 50% on Tuesday we’d still face a CAPE of 34 we’re still at an extremely elevated CAPE and you want to just keep withdrawing the exact same amount you initially planned to withdraw today. 🙂
Hello Big ERN. Great spreadsheet – please excuse my ignorance as I’m just learning to use it. If I have an expense that I anticipate will go away midway through retirement (e.g., my mortgage), can I accurately model it under cash flow assist by entering the amount of the foregone expense as a positive supplemental cash flow (presumably not inflated if I’m talking about my mortgage) for each month that I will no longer have to pay that expense? Put differently, can expenses that end at some point during retirement be accurately modeled as supplemental cash flows that commence at those points and persist through the end of the retirement period?
Yes. I would model the mortgage as a supplemental nominal cash flow (negative values) in that tab. Then your retirement safe spending amount would be spending OUTSIDE the mortgage.
Got it. Thank you!
Hello ERN, do you have plans to update the spreadsheet to fully support glidepath? i.e. glidepath not just for a single case study, but as an enhancement for the main tab.
The current, static only allocation, doesn’t support finding the optimal SWR.
Great question!
The way the sheet is built right now, the GP are not possible to simulate over all possible starting dates. I would have to use Matlab to run a big loop over all possible starting points.
So, as of right now, run with a static AA, then check where the lowest SWR occurs (usually 1929, 1965-1968) and run a few case studies and see how the GP changes the outcome.
Sorry! 🙂
Hello! So, possibly slightly odd question for you. On the Cash Flow Assist tab, our data gives us a safe consumption amount of approx. $60,000 at the 0% failure rate for a 40-year retirement. Our average expenses over the past 5 years have been around $40k/year, and I have no reason to think they’re going to go up for the foreseeable future. If my logic is correct, then, based on historical data, we would be “saving” $20k each year if we continued to spend $40k every year.
Is there any way to model (very roughly) the effect over time of spending $20k/year less than the safe consumption amount at the 0% failure rate? E.g., by adding $20k as a supplemental cash flow for the years in which we think our spending will remain about the same? In real time, I’ll just keep updating our net worth and reducing our length of retirement each year, but I thought it would be interesting to see how much impact remaining at $40k/year expenses would have in terms of possibly giving us an extra bit of safety if, say, we have some unexpected old-people expenses 30 years from now, like assisted living or something like that.
Hope that makes sense. Thanks for all the great information and tools on the blog!
Good question. You could check the tab “Distribution of Final Value” and see how much money you would have had if you retiremed in the historical cohorts and had withdrawn so much less than the SWR.
Or do a case study of one single retirement cohort (maybe 1929 or 1968) and see how your balance might have evolved over time.
You will notice that even in the cases where you don’t run out of money, you might have still had a very bumpy and scary ride (e.g. 9/1929, 12/1968)
I’ll give both of these a shot. Thanks!
It doesn’t look like the “cash flow assist” is being applied to case study. I have concerns about the timing of drawing down pre-tax accounts after age 60, and additional out-of-pocket healthcare, increasing my minimum “spend” rate so much that retiring in 1966 is even more hazardous than 1929! I’d like to see how glide paths play out with rising spending. Case Study column F is tied to Cash Flow Assist column P, but that column seems to be hard-coded to the number 1. Is that still a work in progress? Is there a quick formula I can plug into P to include that sheet in the numbers?
Disregard – I see it made it in through the back door on column B of the main page! The numbers didn’t line up because Case Study is using its own starting portfolio number, while Cash Flow Assist is providing a percentage based on its own starting portfolio, and the scale applied to a different number messes up what were supposed to be relatively-fixed costs. I think I’ve got it figured out now!
Great! 🙂
Column G in “Case Study” factors in the supplemental cash flows in the same % relative to the net worth as in the Cash Flow Assist sheet.
You can model rising spending by playing with the scaling parameters (column P in “Cash Flow Assist”)
Yes, currently column is hard-coded to 1.0 to model a flat spending target. If you like to change that to model higher taxes and/or spending increasing faster than CPI you can certainly change the scaling.
Hi Big Ern,
Thank you so much for sharing all of your research online! In particular I hadn’t thought that withdrawals strategy could make or break success of early retirement (and how much it differs from normal retirement!), so I count myself lucky to have come across your page.
I was going through the sheets and had 2 questions –
1) In the “Cash flow assist” sheet for the chart “Fail-safe withdrawal amounts”, why are the amounts lower as the supplementary cash flows kick in? Intuitively, I thought that since there is supplementary cash flow the fail-safe withdrawal amounts would be go up? Not sure what I’m missing here.
2) For returns on non-US stocks I had input it to the “parameters and main results sheet” an expected return. Is this affected by the current numbers in the “stock/bond return”, “world EX US equities”? If yes, what is the impact? Apologies if this was covered anywhere else, I’ve tried to review the different parts but was not able to find an answer.
Thank you!!
1: If you have $1000 per month of supplemental cash flows X months into retirement then your withdrawals from the portfolio are reduced by $1000 those months.
2: the expected returns only cover the years going forward. The historical returns start in 1970