Update 8/29/2018: Check out Part 28 as well with some enhancements to the Google Sheet!
Update: We posted the results from parts 1 through 8 as a Social Science Research Network (SSRN) working paper in pdf format:
Safe Withdrawal Rates: A Guide for Early Retirees (SSRN WP#2920322)
One commenter the other day had a good suggestion: Publish the Excel spreadsheet that we use in our safe withdrawal rate research. Great idea! There is only one problem: we didn’t use Excel to calculate any of the SWRs. We did use Excel to create some tables, but the computation and most charts were all done using GNU Octave, a free number-crunching programming language, similar to Matlab.
But we still liked the idea of creating a tool to run some quick SWR calculations. In Octave, we can calculate a large number of simulations and calculate safe withdrawal rates over a wide range of parameter value assumptions. Millions and millions of SWRs over many different combinations of parameter values (retirement horizons, final asset value target, equity shares, other withdrawal assumptions). That would have been cumbersome, probably even impossible to implement in Excel. But a quick snapshot on how one single set of SWR parameters would have performed over time? That’s actually quite easy to do, even though there are 1,700+ different retirement cohorts between 1871 and 2015.
Here’s the Google Sheet Link:
Link to the EarlyRetirementNow SWR Toolbox v1.0
For obvious reasons, the baseline Google Sheet can only be edited by us. If you like to run your own calculations you have to download your own copy. There are at least two ways to do so:
- (recommended) Click on Menu, then “Make a Copy” or “Add to MyDrive” to get a local copy of the spreadsheet in your own GoogleDrive. You can then edit the sheet and use your own assumptions.
- Click on Menu, then “Download as” then “Microsoft Excel (.xlsx)” to get a copy as an Excel file to store on your own hard drive. It’s not really recommended because most of the formatting will get lost. But if you care only about the computations you should be fine.
Update February/March 2017: Gold and cash returns
- Gold returns are only completely trustworthy after 1968 when I got the London Fixing time series via Quandl. Before that, I had to rely on annual data from OnlyGold.com. If someone has a better (monthly) time series for 1871-1967 please let me know!
- For cash returns I use:
- 3-month T-bill interest rates from the Federal Reserve starting in 1934. Monthly data.
- I have annual data going back to 1928 from NYU-Stern. Data gathered via Quandl.
- For 1871-1927 I use annual data on 1-year T-bill yields from Prof. Rober Shiller. It’s not exactly ideal to splice it this way but it’s the best I can right now. If someone has better data, please let me know!
How our tool is different from cFIREsim
- We use monthly data, while cFIREsim uses only annual data.
- We project forward return forecasts beyond 2016 year-end so we can calculate SWR for more starting dates. For example, the January 2000 cohort is already far underwater, as we showed a case study last week. Even aggressive return assumptions will still wipe out the portfolio before too long and we like to count those cohorts as 4% SWR failures even before the utter failure is actually confirmed.
- cFIREsim asks you for a specific withdrawal rate and then simulates how that rate would have performed over time for each of the different starting dates. We go the opposite route: We specify a final value target and our spreadsheet calculates the exact initial withdrawal rate that would have precisely matched the final value target. For every retirement cohort between February 1871 and December 2015 (=1,739 months). The advantage of this procedure is that we can then easily calculate the failure rates of different initial SWR without calculating any new simulations. The failure rate of the 4% rate? Simply calculate the share of ERN-SWRs that are greater than 4%. And redo the same for all rates between 3.00% and 5.00% without ever calculating any new set of simulations as would have been required in cFIREsim.
Enter Parameters
Fields with the orange shading are asking for user inputs:
- The Equity share. We are aggressive and set this to 90%. The residual is invested in 10-year U.S. Treasury Benchmark Bonds.
- The expense ratio: We currently set it to 0.05% p.a. One-twelfth of this is subtracted from each month’s return.
- Equity projected returns post 12/31/2016. These are real annualized return assumptions. In our SWR simulations we set this to 6.6% but here we are a bit more cautious and set this to a more conservative 5.0%.
- Bond returns: for the near-term (notice how low current 10Y yields are) and then longer-term. Short-term we use only 0.5% over the next ten years, then going a bit higher to 2.0% real return after that.
- Same for Cash: We expect pretty low cash returns over the next 10 years (0% real) and then a bit of a bump after that (+1% real).
- Expected future real return for Gold: We set this to +1%. Historically, gold has returned only about 1.5% p.a. after inflation.
- The length of the retirement horizon in months (e.g. 60 years = 720 months)
- The target final asset value as % of the initial portfolio. We set this to 50%.
Below the main parameters, you can also set an entire time series of additional cash flow needs (all monthly numbers as % of the initial portfolio value). For example, we predict to get a pension and Social Security worth about 1% of the initial net worth (in 2018 dollars) 25 years into retirement. So, starting in month 301 we set this value to 1%/12=0.0833%.
That’s all you need. The computer does the rest for you. It calculates the safe withdrawal rates for each month starting in February 1871 to December 2015 that would have exactly matched the final value targeting the last month of the retirement horizon.
Results
Some summary tables are in the first tab “Parameters & Main Results.” Be patient, depending on the internet connection and computer speed it may take a few seconds to recompute all results!
In the table on the left, we calculate the failsafe safe withdrawal rate both over the entire sample and for retirement cohorts post-1950 as well as the 1st, 5th, and 10th percentile. For example, since 1950, a 3.61% withdrawal rate would have failed 5% of the time and succeeded 95% of the time.
On the right, we calculate the failure probabilities of specific rates between 3% and 5% in 0.25% steps, again over the entire sample and since 1950, but also in the three CAPE regimes (<20, 20 to 30 and 30+). In the CAPE 20-30 regime, notice the big jump in the failure rates once you go beyond 3.5%!
We also throw in a chart with the data in the right table:
Distribution of the final portfolio value
In the tab “Distribution of Final Value” we can also specify a withdrawal rate and see the distribution of final asset values (real, CPI-adjusted, as multiples of initial). In the example below, we use the 4% rule. We are mostly worried about the left side of the distribution, so final values between the minimum and median. Note how for the median retirement cohort the investor would have grown the portfolio to 8 times (!) its initial real value. The maximum final value would have been a staggering 62-times the initial value. But at the same time, almost 10% of the retirement cohorts ran out of money!
More results
Also make sure you check out the tab “SWR time series,” which includes the SWR for all 1,700+ months in the simulation. For a quick look, there’s a time series chart as well. Notice how there are quite a few times when the SWR is quite substantially below 4%!
How the hell do we calculate so many SWRs?
Or, in other words, where’s the mega-spreadsheet that has 1,700 rows and 720 columns to iterate over the 60 years worth of portfolio values for the 1,700+ cohorts? We don’t need any of that! The withdrawal rate arithmetic is much easier than that. Stay tuned for next week’s post: our technical appendix with some of the background on the withdrawal rate arithmetic we developed. But if you’re interested, check out how the SWR tab “SWR time series” in Column E are calculated through some pretty trivial calculations from just four auxiliary variables in the tab “Stock/Bond Returns,” columns L through O. Likewise, we calculate the final asset values for the fixed withdrawal rate (column F in that same tab) without ever iterating over 720 months of returns each time we change the withdrawal rate. Much more elegant than the brute-force method in cFIREsim!
Case Study:
As we just mentioned, in calculating the SWR we never even go through the cFIREsim-style exercise of iterating over months and years and plotting the portfolio value time series. That would be too cumbersome for all 1,739 retirement cohorts and several decades of retirement horizon. But if you were wondering how any particular withdrawal rate would have performed over time for one specific retirement cohort, here’s the way to do it. Check out the tab “Case Study” where we can add the parameter values, again the orange shaded fields: The retirement start date (year/month), initial portfolio value and the withdrawal rate. And the computer does the rest!
The time series of portfolio values is in column D and also in the time series chart. This will use the same portfolio allocation and also the same supplemental income/expenses as in the main parameter tab. As we already noted last week, January 2000 would have been a pretty bad starting date for retirees. Not just early retirees!
Disclaimers
Please read the disclaimers here on the website and in the Google Sheet. We gladly grant the right for others to utilize our work but please make sure to credit us and quote us properly. We do own the copyright to everything we post here!
Also, note what this toolkit is and what it isn’t: It is a toolkit to determine how different withdrawal strategies would have performed in the past. It’s not a forecast. Past results are no guarantee of future results! But we can still learn from the past.
Great job ERN! This Google sheet you created will become an inevitable plannng tool for many DIY investors. Herculean effort, elegantly presented.
Thanks TFR! Let me know if you see anything amiss! Cheers!
More beautiful things here, ERN!
This post seems like it ought to be the core of a business school case competition: Ok, here’s the basic analytical structure…now build your own database and run 6 million simulations!
Wait – you did it for us! Phew. I really didn’t wanna have to work on this all weekend!
Thanks for this series, ERN – a great contribution that is sure to benefit many people!
Thanks FL! I hope this helps other FIRE researchers run their own sims. Have fun!
Very helpful information and an elegant analysis. Thank you for presenting these data.
Awesome! Glad you like it!
Woohoo, thank you so much again ERN! I can now geek out on this and run a couple of scenarios.
Indeed, I initially thought that you had done your analysis entirely with Excel, using a database format with 1700*720 rows or so (or through VBA). I look forward to seeing the next post, and in the meantime I will go through this spreadsheet. Thanks!
Thanks Johan! Yup there is an easier route than the brute force. Now, if someone insists on plotting the entire time series we’d still have to run the cumbersome calculations. But purely for the SWR and/or final value calculations it’s more elegant the way we propose. Stay tuned…
🙂
Cheers
Thank you for sharing the spreadsheet. I’m really enjoying the series. I’m particularly looking forward to your post on variable withdrawal rates. But I’d also like to make a request for a post that examines variable asset allocation based on CAPE (or some simple trend following approach) – is there an advantage to modifying allocations during different valuation regimes.
Thanks!
Actually, the variable (tactical) asset allocation problem is part of what I do for a living in my day job. I don’t want to get into legal trouble with my employer by revealing even the tiniest detail. So, I might hold back on that until I can find something pretty rudimentary without spilling anything proprietary.
Doing the stock/bond/cash allocation right and reliably requires some pretty advanced methods.
Cheers!
Dr. ERN thank you so much for the continued education of the FIRE community and awesome help as we map out scenarios for FIRE. I will be plugging our info into the sheet over the weekend once I get some breathing room from work. Again, a big big thanks. Fine stuff!!
Thanks, Dr. PIE! I made sure to add that feature to account for future pension benefits as requested by lots of visitors and I’m sure that’s what you need too! Let me know if it works!
Cheers!
What a great tool, thank you thank you!!
OK, so this is sort of a clarification question related to older series content, but your post above made me second-guess my approach here.
Regarding the impact of a future benefit (Pension) on SWR%:
Assumptions:
1) FIRE date begins 2020, pension benefit begins 2045
2) Future pension benefit (non-CPI adjusted) of $20k/yr
3) Initial FIRE nest egg of $1,000,000 (in 2020 dollars)
Question:
1) In the Google doc, do I enter “0.02/12” = 0.1667%
OR
2) Should I first discount the $20k/yr pension over the 25-year period, since it is not CPI-adjusted? Or is this adjustment already taken care of in the model?
Thank you SO MUCH for doing this!
Great question! This requires some “hacking”. If the $20k pension is in today’s dollars (initial benefit level grows with CPI until 2045, then no more CPI adjustments), then indeed I would set the value for month 301 (cell B318) to 0.02/12. But then you’d have to start discounting the subsequent months, so set cell B319 to “=B318/1.02^(1/12)” if you assume 2% inflation going forward. So your real benefits melt away at a rate of 2% p.a.
If the $20k is in year 2045 dollars you’d even have to discount the initial benefit by 25Y worth of inflation: 0.02/12/1.02^25.
Cheers!
Regarding this comment: “=B318/1.02^(1/12)”
And I meant to add: Copy down that formula all the way to the end. So that nominal pension loses value in real terms over time…
Cheers!
Thanks, will do. So I will discount my nominal Pension value (in 2020$) by inflation, to a lower Future Value 25-years down the road. Just wanted to triple check I wasn’t double-dipping the model assumptions.
I still wasn’t 100% sure if the pension in 2045 ($20k) is in year 2045 or year 2020 dollars.
I would set the supplemental income parameters to this
One column is for discounting the first value and one is the 0.02/12=0.00167
What they both have in common is that they both decay at a rate of 2% p.a. inflation:
https://earlyretirementnowdotcom.files.wordpress.com/2017/01/swr-part7-chart9.png
Thanks for checking up ERN. This was harder to clearly explain than I thought! 🙂
For our example, at FIRE date of 2020, the pension value of $20k is in 2020 dollars, with distribution of those non-CPI adjusted dollars beginning in 2045. So I used your 1st column approach above ($20,000 in 2045 dollars), discounting the 2020 value (by 2% p.a. inflation) to a lower, future value in 2045 dollars.
Make sense? Again, thanks so much for following up.
If the $20k is in year 2020 dollars then you want to use the second column, the one that starts with 0.001666 in the first month, then decaying at the rate of 2% p.a.
Cheers!
This is awesome. Seriously. I esp. appreciate the open source approach.
You use the “Project Future Real Returns after 12/31/2016” to allow running scenarios with data that is “younger” than 2016 less the retirement horizon (otherwise the last sequence you could run for a 60-year data set would be the 1956 cohort), right?
The final target value tripped me up a bit. A WR that ends up with a balance below the final target but above zero still is a “failure” in your model, correct? I would rather prefer to model something that yields me the best mean terminal value (i.e. closest to my target) but then tells me what the risk distribution around that target terminal value is.
Also it would be really cool to propose an ideal apriori asset allocation to minimize risk. Maybe show a curve that shows optimal (constant) asset allocation for different duration/WR combinations that minimize risk. Or does that run afoul with your employer?
Keen to see your math hacks in the next post.
Thanks, JK!
Yes, without projecting post 2016 returns we wouldn’t be able to check any of the post 1956 retirement cohorts. Which means we’d miss the really bad 1965/66 and 1999-2001 cohorts.
About the final value: You can set the final value target. I set it to 0.50*initial, but you can also use 0.00*initial. I would advise against using the mean final value plus risk distribution. The whole SWR issue is always about tail events never about the mean.
About the minimum risk issue: I would check online if there are any tools for “efficient frontier”
We have a post:
https://earlyretirementnow.com/2016/07/20/lower-risk-through-leverage/
and a Google Sheets with the calculations of the efficient frontier:
https://drive.google.com/open?id=1ZMdnOv-QOSoJ4wwLjWInM7lJdU4J-NJl35oMfBN_lJg
A caution: To compute the efficient frontier you need the pertinent inputs: expected returns, risk and correlations. Otherwise it’s garbage in, garbage out.
Another caution: Oftentimes the min-variance portfolio and the tangency portfolio (different from min-variance!!!) have an unpleasantly low expected return. You’d have to use leverage (gasp!) to scale up the return. Not something the average retail investor wants to do.
Awesome work publishing this! I’m a bit disappointed that more researchers/hobbyist aren’t publishing this kind of thing since it is so easy to do nowadays.
For all the blogs I’ve written on Medium https://medium.com/@justusjp I’ve put the source code up in Github: https://github.com/hoostus/prime-harvesting.
Hopefully more people follow your lead!
Thanks! It’s crucial to not just post some black box results. I put out my calculations for others to check. I hope there’s nothing wrong in there but if anybody finds a bug, please let me know!
Why do bond returns quadruple in 2027? Going from ~0.5% real return to ~2% real return? Is that just “well bonds will eventually revert to long-term trends and 10 years from now seems like a good guess”?
Correct. Given today’s extraordinarily bond yields I assume that the investment in 10Y Treasuries yields only today’s real expected yield (actual nominal yield minus inflation forecast). After that we revert to more attractive bond yields (hopfully!!!)
Thank you for putting this together. I will have to check it out and see how my plan fares with your calculations.
Great! Hope you like it. Let me know if you see anything that doesn’t look right!
This is awesome. Is there a way to model the rising equity glide path as published in the paper by Pfau and Kitces?
Adding glide paths in retirement would be a good addition to a later version. But it’s not yet set up for that.
Sorry!
Thank you for sharing this tool. This is really fantastic.
To help get the most benefit can you confirm or correct this understanding of the methodology.
This analyses 1,739 possible historical scenarios for starting to drawdown from a portfolio in any of 1,739 months starting 1871 to 2015. So scenario 1 measures how a SWR would have performed for a retiree taking the first withdrawal in February 1871, scenario 2 measures taking the first withdrawal in March 1871, scenario 3 in April 1871 and so on up to scenario 1,739 taking the first (and so far only) withdrawal in December 2015. All results finish with the final withdrawal in December 2015.
So the longest horizon tested is 144 years, the shortest the single month of December 2015?
If so, then this model is tests SWRs against all historical sequence of returns experienced in the last 144 years. A tough course to complete! Because the periods tested range from 1 month to 144 years can we imply an average period just over 70 years; so for retirees wishing to stress test, say, a 50 year horizon, can we assume the results here pass that test and are actually more demanding?
From your previous posts I gather you may soon test Guyton Klinger Decision Rules. The over 1% SWR boost which that strategy claims to deliver would certainly benefit from being put to the test. If your findings confirm that the approach does increase SWRs significantly then if a tab for the GKD Rules could be incorporated into this tool for testing that would be great. When the original research was published the paper gave plenty of results but of course there is no way for the FIRE community to verify the results other than through the type of work you are doing.
Another suggestion for research if I may. Higher returns can be achieved with portfolios which diverse beyond the S&P500 and hold not just US large cap, but also some international large caps, and return boosters such as international small caps, international value or high yield, and emerging markets. Data may be harder to get but if higher SWRs result it could be worth exploring.
Correct, there are 1,739 possible retirement cohorts.
But: They all have the exact same retirement length. 60 years, or whatever length you prefer. The Dec 2015 cohort has only 13 months of actual live returns and the remainder is specified by you in the return parameter assumptions. So you don’t have the problem of mixing cohorts with different length. 🙂
Also: I will write something on the GK rule and using other asset classes and equity indexes at a later time. Still gathering my notes on that one.
Thanks for stopping by!
Wrote something about Guyton-Klinger today in Part 9. Enjoy!
BIG ERN!! You never cease to amaze. An amazing tool, saved to my Retirement Planning folder on Google Drive, and a great addition to the more “traditional” planning sheets I have. You’re a Rockstar, and are helping a lot of folks with this series (including me!!). Great work!
Awesome! Thanks, Fritz! Always here to help and hack new tools!
Cheers!
As the 21st Century has afforded the investor / manager with high level computing power towards portfolio research science and many low expense ETF vehicles towards smart portfolio construction, a well designed quantitative investment strategy that exploits these products, based on academics and evidence based approach, may help investors the optimal and maximal efficient compounding of assets while in the “accumulation” phase * and production of income while growing assets in the retirement phase **.
….
* tinyurl.com/jnl6se8 ( paste in to browser address bar )
**tinyurl.com/znnqxdw
Some very interesting research. Do you have a blog where you present more results?
Thanks for stopping by!
How do you deal with different performances in stocks vs bonds? Do you just assume no rebalancing ever? And how are withdrawals managed… at the same ratio as initial asset composition?
Can it be extended to include taking withdrawals from the overweight portion of the portfolio + rebalancing at some (configurable) interval if such withdrawals cannot keep the asset ratio in check?
Thanks for the questions.
I assume monthly rebalancing.
Initial withdrawal is adjusted by inflation every month.
There will never be an over/under-weight, so I can’t really implement the “withdraw from the overweight” scenario. The only time I looked at varying Stock/Bond ratios is in part 13 (Prime Harvesting).
Ah, OK. Monthly rebalancing could be more expensive than is necessary, fees-wise, which is why Vanguard recommends yearly rebalancing. But the difference should be small and obviously it helps with the calculations a lot 🙂
Yes, exactly! I picked monthly rebalancing mostly for computational simplicity. Then the portfolio returns are simply the weighted Stock/Bond returns. If you want to rebalance less frequently (quarterly), results will not change that much, though.
Also one can do the rebalancing with mutual funds with same day rebalancing at zero cost. 🙂
Cheers!
Love the data! How about a short tutorial on Octave and some of your data from that app?
Minor issue, On your sheets that use Year and Month columns it looks like they are reversed with the data.
I think if you google “Octave tutorial” you’ll find something on the web. We don’t need a FIRE blog to do that. 🙂
And: all the return data used in the Octave calculations is also in the Google Spreadsheet in part 7!
On the “Case Study” page, why would you ADD the supplemental income to the value of your portfolio every month as if it is being invested in stocks/bonds and making market returns?
In your example, you start receiving Social Security in 2025. Wouldn’t this be used to pay for your current living expenses and not invested in the portfolio? That’s how I would use this money. I would take all my supplemental income like pensions, rental income, Social Security, etc and use this income to pay my living expenses. Whatever amount I am “short” I would withdrawal from my portfolio.
Can someone explain this? Thanks.
Thanks for your comment. Very important question!
Why do I add the SocSec income? Imagine in month 300 I withdraw $4,000 from the portfolio and consume it. In month 301, I start getting $1,000 per month in Social Security. I now start withdrawing only $3,000 from the portfolio. The way I model this in the computations is to add the SocSec to the portfolio and then keep withdrawing the same $4,000 for the required $3,000 net withdrawal. You wouldn’t literally do this in real life but the result is the same. Money is fungible!
I don’t get the same results. I created two spreadsheets – one where I have supplemental income, and one where I do not. The withdrawal rate from my portfolio was the same amount for each scenario. Since you are adding supplemental income to the portfolio calculation, in that spreadsheet it shows that my portfolio is growing much faster than it would be in real life. I will not invest my SS and pension checks each month so the calculation is overly optimistic. Unless I’m doing it wrong, the spreadsheet that has no supplemental income is more accurate for me.
Let me assure you, you are doing it wrong and my calculations are right. But since I can’t see your spreadsheet I can’t tell you where the mistake is. My suspicion is that you are double-counting the supplemental income. I don’t double-count the Social Security income and thus get the correct answer.
Let’s look at an example: You try to fund a consumption level of $1,000 per month (real) every month. After 10 years you receive $600 in Social Security. There are three ways to calculate this. Two are correct, one is incorrect:
Case 1: Withdraw $1,000 from the portfolio for 10 years. Then $400 after that. The Social Security income reduces your portfolio withdrawals dollar for dollar.
Case 2: Withdraw $1,000 from your portfolio forever, but “invest” the Social Security income in the portfolio after 10 years. Same outcome as Case 1, because the $600 inflow and $1,000 outflow exactly net each other out to the same $400 outflow as in case 1. Money is fungible.
Case 3: Withdraw $1,000 from the portfolio for 10 years. Then $400 after that. And on top of that invest the Social Security income. That’s incorrect because you double-count the Social Security income. You can’t eat the Social Security income and invest it at the same time.
I suspect you compared Case 1 and Case 3 and obviously got the wrong result with case3. But I use Case 2. For more clarification on how to calculate this the right way (and the elegant way!) please consult the post here:
https://earlyretirementnow.com/2017/02/01/the-ultimate-guide-to-safe-withdrawal-rates-part-8-technical-appendix/
Cheers!
I think I see my mistake. In the spreadsheet with supplemental income I need to enter my FULL yearly expenses as my withdrawal figure (including SS, pensions, etc). In the spreadsheet without supplemental income I enter only the amount that I am short. The portfolio numbers are still different but they are much, much closer.
The downside of including supplemental income is that the withdrawal % can be a very large number. For my situation it’s 14% because I have a high supplemental income. In reality I’m only withdrawing between 2-3% of my portfolio.
Wow, how do you get 14%? Are you sure you are using the percentages of supplemental income in monthly terms? Example: Net worth today = $1,000,000, Social Security income $20,000, the the supplemental income is 2%/12=0.1667%. A big difference between 2% and 0.1667%!
I will take a closer look. Clearly I am doing something wrong. Thank you for your help.
hi ERN congrats on the blog, i really enjoy your contributions. can you show me how i could adapt your google sheet for a different country ? i was looking around and could not figure out how to enter the
returns of my stock market, bonds, etc. Also I have data only from 1970 on, not sure if that´s a dealbreaker for the calculation. thanks.
If you go to my Google Sheet and go to tab “Stock/Bond Returns” and look at columns F/G, these are cumulative nominal Stock/Bond returns. Columns K/L are the month/month real returns (adjusted for CPI inflation).
If you want to provide your own returns and inflation numbers that’s the place where you can “hack” that.
But again, your simulations would assume U.S. returns up to 1969 and your country afterward. But you can still look at a case study post-1970.
Best of luck!
Link to Part 7:
https://earlyretirementnow.com/2017/01/25/the-ultimate-guide-to-safe-withdrawal-rates-part-7-toolbox/
Link to Google Sheet:
https://docs.google.com/spreadsheets/d/1QGrMm6XSGWBVLI8I_DOAeJV5whoCnSdmaR8toQB2Jz8/edit?usp=sharing
I was working with your spreadsheet to figure out my swr and came across some concerns.
As i was changing my stock/bond allocation to more bonds starting at 10% and climbing to 50% with a corresponding stock allocation to make up 100% i found that the swr kept getting higher and higher with cap > 30 failure rates equalling 0% but the 20-30 cape increased as you would think. Any ideas on whats happening or if this is a glitch in the spreadsheet??
It’s not an error. With a higher and higher bond allocation, the worst-case SWR no longer occur when equities are wildly overvalued but only midlly overvalued.
I guess im not quite understanding this. In my instance i plugged in my numbers with a 50/50 bond/stock split and received a swr chart that showed a 4% swr with a 28% failure rate from 20-30 cape but a 0% failure rate with a 4% swr when the cape is >30 ? So would you recommend the 4% rate with a 50/50 split since i get a 0% failure rate over doing a more traditional 20/80 bond/stock split that shows a swr of 3.75% with failure rates of 3.51% with a cape >30?
If and when I retire should i be using this cape swr chart to be changing my bond/stock allocation to correlate with the changing cape values?
With a 30Y horizon and 50/50 and 0% final target value, the 4% SWR will likely succeed for the 1999-2000 cohorts (CAPE>30) because bonds had such a strong performance during the 2000s recessions. But 50/50 and 4% SWR would have failed for the 1965/66 cohorts because bonds got hammered during the 1970s. So, a CAPE at 20-30 had a higher failure rate than the CAPE>30.
But two big warnings:
1: Don’t expect another bull run for bonds now. Yields are much lower now than they were 1999/2000. I doubt a 50/50 portfolio will do very well with a 4% WR going forward.
2: This only works because of the short horizon. Change the horizon to 600 months (50 years) and the 50/50 portfolio is pretty universally awful. Regardless of the CAPE.
Thanks Big ERN for your effort and for sharing this excellent spreadsheet , I ran multiple assets allocation scenarios and discovered that the most optimum assets allocation for maximum SWR is
70% Stock, 15% Cash and 15% Gold which lead to 3.34% SWR which is more than 80/20 Stock/Bond which lead to 3.28 ..
Thanks! Wow,15% gold is pretty high. For an asset that has low expected returns (essentially 0% real). But it is quite intriguing how a small amount of gold can hedge some of the bad equity returns. I wrote about this feature in one of the inflation hedging posts:
https://earlyretirementnow.com/2018/03/28/inflation-risk-for-early-retirees-part-4-hedging/