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.
I have been looking for a cape based spreadsheet forever. This is great. My only question is that when add my social security and pension in to the second sheet, mu withdrawal rate goes from 3.25 up to 8 %. Perhaps i don’t understand the safe consumption rate concept. Can you explain why the rate goes up so high? Best regards and Merry Christmas and Happy New Year
Yes, it’s the safe consumption rate. You subtract the supplemental benefits and then withdraw the rest from the portfolio.
Merry Christmas and a Great New Year to you, too!
Hello Karsten,
Thank you for updating the sheet and updating the change log in particular, it really helps to know whether to port personal copies/changes to your newer versions. Such an amazing resource.
Thanks for the feedback! Glad this helps! 🙂
I’m getting an error in the CAPE-rules sheet, cell W18, and the green cells in columns D show #N/A, so it seems that the latest changes to import the latest CAPE data don’t work for me?
Yeah, it’s frustrating: Google Sheets doesn’t like my CSV file. You can always download it by hand and then look up the recent CAPE estimates:
https://drive.google.com/uc?export=download&id=1ugtRN3TaAVwQi-20mjt4DctF-glppSMD
Hello Karsten,
I have used your SWR worksheet for the last few years. Thank you for your work.
In preparation for 2024, I downloaded the version with the comment “Last Change: 12/23/2023” in the P&MR tab. On the latest version, when I set the Final Value Target (% of Initial) to be anything greater than 0% I am getting negative numbers in the “WR” column of the Results table.
If I modify an older version of the SWR worksheet – e.g. such as the version I set-up in January 2023 – I do not get this type of result. The negative WRs occur whether there are cash flows in the Cash Flow Assist tab or not.
I reviewed the Change Log tab but do not see any changes that would cause the results I am seeing.
Any suggestions?
Respectfully,
CNR
Never encountered that myself. Do you mind sharing access to that sheet?
If you forget the “%” in that field I noticed that the values will go negative. Made that mistake a few times…
Good point! That might be the solution!
Thank you both for your responses. It pointed me in the correct direction. It was an input error on my part in that I forgot to input the “%” in the Expense Ratio field. Without the “%” in the ER field, the WR percentages were positive but seemed inaccurate. Once I started increasing the Final Value percentage, the WR values went wildly negative. Everything seems to be working now. Thank you again.
Respectfully,
CNR
Whew! That’s good to know!
Hi Karsten,
– Is the “Equity Drawdown” column in the “Asset Returns” sheet representative of how far down the S&P is from an all time high? In other words, could I use this figure to determine which column of the equity drawdown related results table I’m in?
– The loading of your CAPE CSV doesn’t always work for some reason within the spreadsheet, but because that data is available in Asset Returns, I’ve used this formula to populate the latest CAPE value on the CAPE-based sheet:
INDEX(‘Asset Returns’!O6:O, COUNT(‘Asset Returns’!O6:O))
– Your formula for N(ret) [Cell AE1 on Asset Returns] can be simplified to use the built-in COUNT function: COUNT(F6:F). I’m not sure if there’s any real benefit to that, other than simplicity.
Thanks, as always, for providing this great resource
1: Correct, that’s the drawdown of the real S&P500, total return.
2: Yes, Google Sheets doesn’t like accessing external CSV files. I have updated the formulas for the CAPEs to use the asset return values, if the CSV file data didn’t load properly.
3: Thanks for the suggestion. i changed the formula on my end, too.
I noticed some comments about decreasing the retirement horizon each month to account for the passage of time, and adjusting your cash flow as well each month to line up.
Why do that? Wouldn’t you just set your retirement horizon once, and set your retirement start date once, and just update your portfolio value each month and be done with it? Horizon (generally speaking) and start date shouldn’t really change right?
When you have a set date for a future cash flow, i.e., you expect a company pension or Social Security at a certain age, then when you rerun your SWR analysis a year later, you should move the starting date in the SWR supplemental cash flow sheet to reflect the passing of time.
Horizon: it’s likely that if you redo your SWR in 12 months that your retirement horizon has shortened by just about 12 months. Maye a little bit less reflecting the actuarial realities, but still pretty close.
Greetings!
First, a heartfelt thank you for sharing so much helpful work, Karsten. I am really enjoying understanding SCRs more clearly!
A question: I notice that if I change my Start Date (G5 in the Cash Flow Assist sheet) as you suggest above that all my entries for cash flows (e.g., Social Security income) keep their Month # rather than their date. For example, say I have a start date of 6/1/2024 and SS in the column Cash Flow 1 starting on 6/1/2025. If I make my start date a year later, 6/1/2025, my SS entries now start on 6/1/2026. I guess that’s the same number of months from the new start date but it’s now incorrect, as I still start SS on the same date, not one year later.
Thus, it seems I have to adjust the dates for SS and any other supplemental income “by hand” after changing the start date, which seems inconvenient, especially if I intend to do so regularly.
I hope my description makes sense, as it seems like I must be missing something. Am I?
Thanks again!
Knowing that I would lose data each time I copied a new Google sheet from Karsten, I created an excel sheet to mimic the “Cash Flow Assist” sheet cells A5:G730. I could have added columns H – O but I did not need these.
I have other cells in the my excel sheet that contain these variables:
My SS start age
Spouse SS start age
My SS monthly amount
Spouse SS monthly amount
My retirement date
Spouse retirement date
My age at death
Spouse age at death
Medical Expense pre Medicare
I created formulas in cells B11:G730 to generate the dates, ages, and the cash flow amounts based on their start/stop dates from the variables above. (Hint: I use Excel Tables to make formula creation and propagation very easy).
Important for next step: Do not use Paste (Ctrl-v) in Google sheets but instead use Paste By Value (Ctrl-Shift-V).
I copy my excel values from E11:G730 and paste by value into my copy of Karsten’s Google sheet at E11.
This allows me to change my varables to perform “what if” and copy and paste into the Google sheet.
As bonus, my Excel sheet is in my Workbook that contains all my financial assets thus I can snag other information such as my Portfolio Value, Asset Mix, Expense Ratio, Retirement Horizon that are needed on the Parameters & Main Results sheet.
That’s very helpful. I would do the implementation just like that.
One caveat: for the inflation-adjusted numbers (e.g., Social Security) you’d have to adjust the future numbers by COLA.
Thanks so much for the work you have done with the Toolbox. It is becoming my primary tool for my Safe Consumption Rate.
I need clarity on the input of my Social Security. Sample data:
Current Age: 65
Take Social Security at age 70 (5 years or 60 months from now)
Social Security Statement today indicates at age 70, my benefit is $1,000
Inflation p.a. (COLA). : 2%
What I am looking for is the value to enter in month 60 and all future months until death in the Cash Flow Assist sheet column E (Cash Flow 1 (e.g. SocSec Spouse1)).
Is it $1,000?
or COLA adjusted $1,000, which would be $1,000 x (1 + 2%) ^ 5 = $1,104?
You’d enter 1000.
Yes, you’d have to shift the future cash flows by hand. Sorry about the inconvenience, but in this Google Sheet it’s easier for me to implement.
Great spreadsheet!
How do I add Cash Flow columns? I have a couple of pensions on top of Social Security and it would be useful to add them without needing to lump them all together.
Thanks
You could select/right-click the column and add another. The totals use the sum function and if you add another column in the middle, the formula should adjust.
I use your Google sheet constantly to manage my ER. Thanks so much for all the work. Something I’ve noticed is that when looking at my 50 year horizon the failsafe by decades is always worst for 2000s followed by 1990s (both are worse than the 1920s). Is this because it is using projections for the next roughly 25 years for the 2000s scenario and 15 years for the 90s scenario? So basically when using acutal historical data the failsafe is actually better than when using a small subset of historical combined with the projections in the sheet?
If I’m correct it would probalby imply that the future projections are actually a bit conservative as compared to historical levels. Not necessarily a bad thing but important to know when planning what a reasonable SCR is. Thanks again.
Normally, 1929 and 1968 are the worst-case scenarios, but I can’t guess what causes your specific results without seeing your inputs.
My inputs are:
Stocks-50%, Bonds(10y)-20%, Cash-21%, Non-US-7%, Gold-2%, Retirement-600, Final Target-50%, CAPE-2. I have an inheritance planned in the cash flow assist for roughly 15% of my starting portfolio about 17 years in.
The worst failsafe peak is for 1999-2000 by far as it’s 3.34% vs. 3.47% for 1929. I need to drop cash to below 5% for 99/00 to not be the worst. I’m keeping a lot of money in cash currently due to the inverted yield curve but I’m now thinking that is why the 99/00 is always the worst. While I probably won’t have 20% in cash for the full retirement horizon I think it is likely I can have at least 10% in cash. It’s interesting that the 2008 peak jumps to a 3.93% failsafe for my parameters so the large cash probably isn’t the only thing going on. That does imply that my original theory wasn’t accurate about future projections being too conservative.
All this gets at the impossible question to answer of what should be an acceptable failure rate? If it is 0s across the board I’m looking at a 3.34% SCR. If however I discount that scenario I can go up to 3.5%. All this also ignores that the true safety net is the 50% final target. I think as long as somebody has that number at 25% or higher you can probably accept a 1-2% or maybe even 5% failure rate for your SCR. A debate for another day…
OK noted. It;’s possible that the dot-com crash generates the worst-case scenario, which we should take with a grain of salt becasue the post-2023 returns in the simulation will rely on “calibrted/assumed/average” returns.
I always think that that the failure probability should be zero. I don’t accept failure in many other aspects of life. I was never late for a job interview. Or wedding. Or funeral. I don’t want to have positive failure probability for something as profound as retirement safety. But different folks have different preferences.
I agree that failure needs to be zero as far as ensuring that you don’t run out of money. But I have my portfolio final target at 50%. I’d probably be willing to accept a 1-2% chance of not being able to leave my kids with anything if the sh@t really hit the fan. Dropping my final portfolio target to 0 raises the SCR from 3.29 to 3.64. 3.64 is roughly a 2.1% chance of failure with a 50% final target value. Not sure why it’s 3.29 now vs. 3.34 two weeks ago but whatever.
So basically if I were to use 3.64 SCR then I would have a 98% chance of leaving 50% of my portfolio to my heirs, a 2% chance of leaving between 0-50% of my portfolio to my heirs, and a 0% chance of running out of money in my lifetime (assuming of course that the future is no worse than the worst scenario of the past).
The flip side is to stick with a 3.29% SCR and esentially guaranteeing that 50% will be left over but almost certainly leaving far more than 50% of the final portfolio to my kids. Obviously this all assumes a flat SCR for all of retirement but after 2-10 years you will know if you avoided the sequence of return risk and can afford to elevate the SCR.
My guess is that most people who are considering early retirement don’t worry too much about how much they will be able to leave to their kids. I’m already basically retired (early) and more use the sheet to see what I consider a reasonable amount of expenses for my wife and I to spend. Most of the time we are below the 3.3% SCR level but I find it helpful to know what a safe expense level is for our portfolio and time horizon. If my expenses were to go above 3.3% but stay below 3.64% that tells me that we are still safe to have enough money before we die but a very small chance that our portfolio won’t hold its value (sorry kids).
That’s why everyone has to do their own customized analysis. I agree that running out of funds is not acceptable at any probability, but the heir(s) can certainly take a 2% risk. Good point!
Thank you so much for all your SWR work, including this great tool! Quick question. When referring to the % drawdown from the all-time high, I assume that it is inflation-adjusted. Although the market is back to an “all-time high,” would I still look at the 5% drawdown SWR since if you factor in inflation, it’s about 6.5% off the inflation-adjusted all-time high?
I hit my number last year and switched to a lighter schedule in Aug with an agreement with my employer to continue through April this year to assist with my transition out. As the end approaches and with the recent increase in the market, I’m just trying to wrap my mind around this a little bit better.
Sorry if I missed that answer to this somewhere else; I looked through as much as I could.
Thanks! Casey
Correct: I use the real S&P500 total return. We’re at an all-time-high, but still a bit below the CPI-adjusted previous peak.
Excellent work. I do wonder however why the SWR don’t change when I adjust the inflation rate. I would assume there must be an impact, especially if some investments are made to cash/gold/treasury, all of which may not provide good real returns in higher inflation environment.
thanks
All returns are in real, CPI-adjusted format.
The CPI forecast parameter is only used to transform your (optional) nominal future payments into real sums. If you have no nominal supplmental cash flows there is no impact from that parameter.
I’ve noticed that the CAPE values in your CAPEoutput4blog.csv differ from what is in your Asset Returns sheet. Same with the CPI. The differences are small, but I wondered if there was a reason. The CPI in the CSV matches what’s in the SWR Toolbox until May 2019, after which they diverge as much as 0.349.
There might have been a CPI revision that was already picked up in the CAPE CSV, but hasn’t been pushed into the SWR Google Sheet yet.
CPI are now aligned: 308.742 in 12/2023.
CAPE are 31.8190/26.3342 (Shiller vs. ERN) in 12/2023.
Thanks for giving us access to the toolbox! I am just trying to follow along and replicate the results for 65% stocks/25% bonds/10% gold.
SWR at 0% failure rate for CAPE>20 = 3.32%
SWR at 0% failure rate for CAPE>20 and at all time high = 3.38%
Lowest fail safe by decade = 3.97%
Does this sound right? Maybe I am not using it correctly.
Why SWR at all time high is 3.38%, which is higher than 3.32%?
Why is lowest fail safe by decade substantially higher at 3.97%?
It sounds right, but I can’t vouch for any other mistakes unless I see your sheet.
The SWR at the S&P 500 all-time-high can be higher because the all-time-high of the portfolio with gold can occur in a different month.
Dear ERN firstly thank you for creating this amazing Toolbox 2.0. It is no doubt the most comprehensive tool on the internet for retirement planning. My question has to do with inputing values for Cash Flow Assist.
To try and isolate the average annual dollar impact of say social security or expenses I first see what the safe consumption amount in dollars (per the Cash Flow Assist tab) on my combined investment portfolio and the sum of all my cash flow assists.
Then I zero out all of the numbers associated with my expenses in column G on the Cash Flow Assist tab. I compare this revised safe consumption amount to the orignial scenario and the difference between these two numbers should be the annual dollar impact for my expenses. In this case at 5% failure rate it is $168k. When I do the exact same analysis at a 20% failure rate my annual expenses decrease to $165k.
For Income and social security the impact is reversed whereby at higher failure rates I get more income. Can you tell me why the Toolbox would show expenses and income in the Cash Flow Assist tab varying with different Failure Rates. I would think that cashflows in the Cash Flow Assist tab would have 100% probability and therefore would never vary based on differing failure rates. Many thanks
The supplemental cash flows in that tab are considered 100% certain. Like Social Security. They are your inputs and have nothing to do with different failure rates. The remainder of your retirement budget is subject to different success rates.
Thank you for your reply. When I compare the NPV of Sociial Security for 30 years at a 5% failure vs. a 20% failure my result between the 2 numbers differs by around 3.5%. I would expect the numbers to be identical. I am observing this by looking at the ‘Safe Consumption Amounts to Target Different Failure Rates’. Any idea on why different failure rates result in changes to the 100% certain Supplemental Cash Flows? Thank you
I still don’t understand. A WR targeting 5% failure rate must differ from one with a 20% failure rate.
And I have no idea how you compare NPV of social Security. That’s not anything I do with this sheet.
And there is no change in the supplemental cash flows. That’s an input provided by the user. The sheet does not change a thing. We seem to speak different languages.
But if you like you can share copy of the sheet and I can take a look
Good time of day. It’s strange, isn’t the cape withdrawal rate supposed to change when % Stocks/Bonds is changed on the Parameters & Main Results tab. For example, by changing % Stocks/Bonds from 80/20 to 0/100, the rate remains unchanged. “thanks.
Ответить
Yes, correct. You should change the rule. I provide all the stats and charts, so you can change the parameters as you change the asset allocation. For most folks who use a portfolio between 60/40 and 80/20, there isn’t much need for changing the parameters. Certainly not the slope; I would leave that at close to 0.5. But maybe the intercept should be a bit lower for the 60/40 portfolio.
For a 0% equity, 100% bond portfolio I don’t recommend using the CAPE. You’d use the real bond yield as an input instead.