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.
New in 2025: a web-based SWR simulation tool:
If you prefer a slightly less intimidating web-based simulation tool (no spreadsheet!), then please check out this project I started with a friend of mine:
With this tool, you can replicate most of the functionality of the Google Sheet, but it’s all web-based. You can also save your parameters for future use and upload them again when you run the toolkit at a future date!
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!
Update (April 2025):
There’s been a lot of confusion about the Fama-French factors and their role in simulations. Small-cap stocks, value stocks, and small-cap-value (SCV) stocks outperformed until about the early 2000s. After that, those style factors became widely known, and there is no significant outperformance since. Just like any other alpha source, the profit opportunity eventually gets arbitraged away in an efficient market. Even worse, value stocks have done quite poorly recently, as I pointed out in a 2024 post. You’d overestimate the return potential by assuming that the outsized alpha from SCV will again present itself to future retirement cohorts. So, how would I model SCV now in the SWR sheet? I’d like to propose the following methodology. First, I generated an alternative series for the Fama-French SMB and HML factors that take out the trend alpha (both positive in the early years and the recent negative alpha). To this end, I ran a Hodrick Prescott (HP) Filter to split the cumulative SMB and HML series into trend and cycle. I removed the likely non-replicable alpha while keeping only the cycle component, i.e., I’d keep the correlation with other asset classes largely intact. If you still believe that there is some additional alpha from SMB and HML going forward, you can also enter those values in the designated fields. By default, the parameters are set to the HP-filtered Fama-French returns (though you can always revert to the Raw SMB and HML return series for comparison) and zero alpha.
And again, I would like to emphasize this severe and dire warning:
Anybody who claims you can significantly raise the SWR when using SCV is performing financial malpractice.
Please don’t take the Fama-French return series in the 1920s and 1930s at face value and propose that SCV can again outperform the market equity portfolio by as much as it did historically because a) hardly anybody was aware of this alpha source and b) even people who were aware probably wouldn’t have been able to harvest a premium that large due to trading costs and other impediments, like data delays, etc.!
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
I am using your SWR toolkit and have filled in the Parameters tab, the Cash Flow Assist tab, and the CAPE-based Rule tab. I am also planning on using a glidepath.
1) Let say I retire today. This first month, I withdraw amount in the “Target Withdraw ($/month) cell. Next month, what fields (and in what tab) do I have to update to get an accurate Target Withdraw? If you have already covered this, just point me to the post and I’ll go from there. I did watch the 2SidesOfFi videos which helped some.
I expect that on the “Parameters” tab I need to reduce the “Retirement Horizon” by 1 month each month, and, since I’m doing glidepath I need to update the “Portfolio” with the new percentages. I also think I need to update the “Portfolio Today” in the “Cash Flow Assist” tab but I don’t know if I need to update the “Start Date” or if I have to do anything about entries in the cashflow assist table. Anything else I’m missing?
2) If I put my mortgage in Cash Flow Assist, does that mean I pay it and take my target withdraw in addition? or does the target withdraw include the mortgage?
3) As time passes and you update the Asset Returns in your copy of the spreadsheet are they automatically updated in mine as well? Or do I have to get a new copy?
1) If you want to check your progress through retirement, I think your best bet is to make a new copy of the spreadsheet every time you want to re-evaluate your situation. Look at it like “re-retiring” every time you want to do a new analysis. 🙂 Your retirement horizon shifts, your cash flow additions/subtractions shift, and you would need to update your portfolio value and percentages, etc.
2) The amount in the withdrawal amount column (Col T in Cash Flow Assist) is net of all of your cash flows for that month. So if it says you can withdrawal $10k, and you’ve added a $2k mortgage for that month, then $2k of that $10k withdrawal would go to that mortgage payment.
3) The asset returns do not auto update. You can copy them in manually from the latest version of the sheet (or just grab a copy of the latest spreadsheet and start over).
Best piece of non Karsten advice on here
I do this once a year however I don’t reset my withdrawal rate each year based on the result as this would result in always being at the start of a new sequence of returns, and not in keeping with the concept of having certainty about future income ($X adjusted for inflation). I view it as a sanity check but if the market is running hot or has just had a correction, I would look carefully at the valuation/drawdown contingent consumption rates.
In terms of updating the sheet. I have a version linked to my portfolio Google Sheets document taking the live value from there so I don’t need to adjust my portfolio.
In general, do what makes sense for your approach to retirement but personally I favour not tinkering and not overfitting.
Thanks! Great explanation!
Yes, you reduce the length by 1 month and shift the supplemental cash flows 1 month up because now everything happens one month closer.
You might have to update the asset returns occasionally. Or download a new sheet and fill in your parameters again.
If you have supplemental cash flows you adjust your withdrawals by that amount. If you have a mortgage you withdraw more from the portfolio to cover that. If you have modeled a pension you withdraw less from the portfolio
Thanks to everyone who responded. I was headed down the path of tinkering and overfitting. I think I’ll just update it at the same time I rebalance.
ERN: I think this would be a good topic for an article. There is a lot on how to use the SWR toolbox before you retire but not much on how to use it after you retire in order to make sure you are still on-track.
Yes, I should write a post on just that topic. Good idea! Something for me to do in 2025!
Karsten… PLEASE do!
We just retired 17 Jan 2025. As I started to update the spreadsheet, the very same questions Edward is asking, I too have the same questions. Although, I’m using the CAPE-based rule I likely confused matters even more for myself.
Edward’s question is also my focus. “… but I don’t know if I need to update the “Start Date” or if I have to do anything about entries in the cashflow assist table.”
I noted that when I decrease the ‘Retirement Horizon’ value by 1 month (i.e. 420 –> 419), the Cash Flow Assist tab retains the original 420 records, and doesn’t update to remove Month 1.
This made me wonder if I needed to manually delete the Cash Flow inputs for Month 1 (“shift the supplemental cash flows 1 month up”) ; the entire record for that first month; or just start over with an entirely new spreadsheet where the my start date is the current month?
When I update the ‘Start Date’, all of the Cash Flow values from the previous months remain, but the Year-Month, Age 1, Age 2 are automatically updated. This requires that I manually delete those values (which isn’t a big deal)… just something that I happened to catch when my wife’s one time retirement payout remained for the new month.
I’m sure I’m overthinking this whole thing and probably expecting too much from an already awesome tool. But… Edward’s suggestion of a doing a post on the process of using/updating this toolbox IN retirement would be welcomed with open arms (and the sooner the better)!
For now when reviewing status, I’m updating the Time Horizon, Portfolio Value, Start Date value, then manually deleting the Cash Flow values in the last month (420 in this example) and any Cash Flow values that remained from the previous month(s).
Apologies if this is too much detail! Starting a new spreadsheet may just be the way to go.
Yes, as a quick-fix, you should just copy/paste all supplemental flows x rows up after x months have passed and then adjust the time horizon accordingly.
Thanks. As a follow on… can you confirm that I should also update the “Start Date” value to the current month as well?
If you have supplemental flows lined up with specific dates (social security, for example), and you start moving those around, it probably makes sense to also move your start date by the same number of months just to keep things consistent.
The “Start Date” affects what you see in the “Year-Month” column (Col B), and then that date is used for age calculations in the next 2 columns. Those calculated ages are also used on the “Life Tables” tab. By itself, just changing the start date doesn’t affect the SCR results.
Correct!
Yes, that goes without saying!
Hello and thank you for this wonderful tool!
I’ve been playing around with it and there’s one thing I’d like to confirm on the results (couldn’t get an answer from the YT video either).
Why is it that SWRs drop (often precipitously) when the time horizon (cell B36) is lowered from 720?
My results often show a SWR of ~3.5% for 720 months, but when I adjust for 360 or even 120, they drop to 2% or even wildly negative. Is this because the 720 horizon allows for the portfolio to fluctuate below the final target end value on bear markets, so long as it recovers by the end date? This would justify why a 10 year retirement window could yield -20% SWR; as it could be entirely comprised of losses that require additional contributions to reach the target end value.
Is this accurate and, if so, is there any way to check which periods have the highest drawdown/volatility besides running individual case studies?
Any thoughts on how to use the tool to plan for this portfolio total value volatility, other than adjusting SWR based on CAPE metrics?
Thank you!
With a final value target of 0, the SWR should go up if you lower the time horizon.
With a very high final value target it’s possible that you catch some bad 360-month windows that would have necessitated a lower WR over that window length, even though the subsequent 360 months saw a nice recovery that affords you a higher WR.
This may be a dumb question, but in parameters where do I put my portfolio value? I see all the percentages but nowhere to put the portfolio amount.
Tab: “Cash Flow Assist”
Cell: E5
The idea is that most of the time, all this just scaled linearly. So if your SWR is 4.25% and portfolio is $1,000,000 then you can safely withdraw $42,500. If your portfolio is twice that you withdraw twice that. i.e., $85,000.
That changes if you have supplemental flows, hence you enter the portfolio value today in that tab.
I probably tell you this way too much but idc…this sheet is the greatest free gift to the world of finance…no hyperbole intended, just the truth. To be able to see just how powerful my pension is and giving my wife and I confidence in our FIRE choices because it will be based on math/scientific rigor…thx Dr Jeske
JD
Thanks, Jon! Feedback like this is always great to read! Have a Great New Year!
Firstly thanks so much for this amazing tool!
I wanted to ask how often you update the “Asset Returns” Tab, or where I can find the updated SPX-TR and CPI values (relative to 1871).
I updated the real returns to October 2024. That’s pretty good. I will update the numbers to Dec 2024, once I have the time later this month. 🙂
Hi – I have been doing a bunch of my own modeling over the last few weeks and was excited to find my way to your blog and the wonderful toolkit you’ve created. Thank you for all the free content you put out there for all of us to enjoy and learn from!
I downloaded your EarlyRetirementNow SWR Toolbox v2.0 and it looks like a few areas need formulas pulled down, but I want to make sure I’m not screwing anything up if these were left blank intentionally. Here is the list of tweaks I’m making – could you please let me know if this is unnecessary? I’ve seen you mention in a few places that we can update the file with the latest returns data, and I’m thinking some of these may just be legacy ranges based on a prior rollforward:
– CAPE-Based Rule tab – should we pull row 1837 down to 1864 since there is returns data through October 2024? Biggest things this will impact are columns J and N
– Asset Returns tab – should cells AD:AF 1745 be pulled down to 1851?
– SCR Time Series tab – should everything be pulled from 1746 down to 1852? Seems like all the time series data is stiping in December 2015. Would also adjust the formulas for the N values on row 4
– Distribution of Final Value tab – similar to above, looks like the data stops at December 2015. Should I pull it down to grab the SCR Time Series data through Octoboer 2024 that’d be produced in the bullet above this one?
– Parameters & Main Results – would just need to adjust the outputs to capture the additional data on the SCR Time Series tab
Thank you very much again – you are an amazing mentor and resource to us all!
Hello, I’m so glad and grateful for having bounced in your spreadsheet. That’s a wonderful free tool. Thank you! I have played a bit with it and there is something that I have not understood. Why are the SWR/SCR lower when I increase the initial portfolio value (E5 in the Chash Flow Assist sheet)? Is it because, the relative weight of the (in my case positive future cash flows) is reduced?
Precisely! That’s the reason: you water down the future cash flows over a larger initial net worth!
I would like to add my thanks for this amazing free tool. The capabilities are very impressive. I apologize if you have already answered this, but in respect of the net worth amount. I note that you say it should be limited to financial assets. I have about 21% of my portfolio in private equity, and I am unclear how I should think about that for the net worth/asset allocation cells. Thank you.
One could model this as separate cash flows, i.e., you place the repayment of those assets as future positive cash flows.
Another option: designate PE as equity.
I have a money in private equity as well (commercial real estate). Turns out ERN has commercial real estate as well (mentioned in one of the posts). He did a posting on how model regular real estate but has never talked about how to model commercial real estate/private equity. I submitted some questions for him a couple years ago on how to model it into the spreadsheet. What I ended up doing is adding it in as a set of cash flows in the cash flow assist tab. In other words, I ignore that money in the “Porfolio Today” and my allocation percentages. In the cash flow assist tab I’ve put together estimates of the monthly cash on cash payments as well as sales events.
That’s what I would do! Correct!
Our portfolio’s “bond” allocation is entirely in the TSP G Fund. Although it does not have an equivalent fund outside of the TSP, it is described as having similar returns as the 10-yr US Treasury bond without the risk of principal loss. Given that description (please confirm if that is accurate), should that allocation be represented in your google sheet as a “10 yr Treasury bond” portfolio parameter for defining the allocation percentage? Would that be conservative since the gains may be similar but the losses of the 10-yr Treasury bond would not apply? Or should the G Fund be represented as a “Custom Series” in the google sheet? If this is the case, do you know where someone can get the historical G Fund data?
Since there are many federal employees who likely take advantage of the TSP G Fund, it would be greatly appreciated if you would recommend how to represent this unique fund in your spreadsheet.
Thanks for all of your hard work and willingness to share your research and this tool with others – you change lives!
I haven’t seen the exact TSP returns. It looks like a mic between cash and 10Y, potentially with a slight additional alphs (to be modeled as a negative expense ratio). I would probably set it to 50% cash and 50% 10y bond.
Hi, the spreadsheet is not opening. The following message appears: “The file could not be opened. Check the address and try again.” Can someone help me please? or send to my email
I can’t replicate that error.
Thank you for all this effort. I’ve got two separate questions to ensure I am getting the correct information. I will share some background, in case it helps.
We are 63 and started on our retirement journey a couple years ago (too old to retire young :)). We have recently laddered TIPS to complement our social security for the next 20 years. This amount should keep us in a good position for necessary living expenses, plus a decent cushion. Also I am keeping additional fixed income in short and intermediate vehicles in case of a hit to SS, which I will then convert to additional TIPS. The remaining amount over living expenses is for “fun”. I’m currently at 61% equities and working on getting down to a 50/50 or 55/45 mix and then plan on an increasing equity glide path.
Question number one is regarding the intercept and slope, I have kept the intercept 1.75 and slope of 0.50, but I note in your writing that this was set for 80/20 mix. Is it recommended I adjust this (at to what) given our lower equity percentage and age?
Question number two is related to inconsistencies I get in the spreadsheet results (likely due to my error or lack of understanding).
On the main results page my zero failure SCR is at 4.39% (box T20). Multiplying it out I get a number essentially the same as the “Failsafe Withdrawal Amounts (net of all supplemental flows)” (box T11 on the cash assist sheet) plus the supplemental cash flows. So that is reassuring. However, on the cape-based rule sheet, for the target withdrawal amount I get an amount which is nearly 50% greater than the previously mentioned failsafe amounts. This is 5.77% target withdrawal rate. If it helpful to know, coincidently the PV of supplemental flow is within a few percent of the desired FV. Obviously, a 50% difference is a concern. Any thoughts to this inconsistency, if I may have made an error, and what course of action I should take, ie which amount to use?
Thank you greatly!
The CAPE-based withdrawal amounts can be higher because they are not guaranteed to be flat over time. They will drop if there’s a bear market.
It’s crucial to not include the value of the TIPS ladder in your CAPE-based calculation, i.e., multiply the CAPE SWR by the non-TIPS portfolio only, otherwise you’d double-count.
wow, I am glad you pointed out about TIPS. I am not sure why that is double counting but will take it as truth.
Because of some prior purchases of treasuries and CDs, not all of the assets that mature in specific years are TIPS (currently it is 62% TIPS). It seems I could pull out these amount from the portfolio values, and add them back into cash flow assist, like I do with social security. Then modify the portfolio parameters and final value accordingly. then use that withdrawal amount.
Or is this getting way too complicated?
My other question was the slope and intercept. Is there any compelling reason, based on age or equity mix to change it ?(I have seen others post that they have, and I am not sure why)
Thank you
If the TIPS generate the supplemental cash flows, then you can’t count them in the portfolio value (Cell E5 in “Cash Flow Assist”). That would be double counting, yes.
I’d keep the slope at 0.5, because that’s the historical slope parameter for most CAPE vs. SWR regression estimations I’ve used.
You can play around with the intercept. Higher intercept means you get more initial, but very large correlation between the market performance and withdrawals.
This is the best retirement calculator online. Thank you for all your work on this. May I ask what you are using for the world ex US equities prior to 1970. I’m guessing after 1970 is MSCI EAFE. Thank you again for all your work.
Thanks so much!
After 1970, I use the MSCI World Ex-USA. Prior to 1970, I use the same returns as the S&P 500, because I don’t have monthly returns for the MSCI series.
I keep coming back to this issue that I struggle with. How should capital gains taxes impact one’s SCR? I understand that taxes are part of one’s expenses but capital gains are constantly moving and somewhat impossible to predict. I would imagine if somebody had a $5m portfolio at the start of retirement their SCR would be different if $3m of that portfolio was capital gains vs if there were capital gains of $0. I feel like it would make sense at the time of retirment to assume a 15% tax (or 20% for higher portfolios) on the full capital gains and divide it by the years of retirement and assume that as an expected expense. Is this the right way to look at it? Should capital gains be ignored when calculating ones SCR? Thanks.
I would estimate the average (sometimes called “effective”) tax rate on your retirement consumption. That’s normally much lower than your marginal rate due to the progressivity in the tax code and the fact that part of your withdrawals are the cost basis. Then gross up my withdrawals as W=C/(1-tax).
Hello Karsten, thank you so much for all the hard work you’ve put into this blog and this tool. I’ve been successfully doing your options strategy for a few years now and have also invested in a number of preferred shared with the brokerage where I do the options trading. Question: how do you account for the preferred shares on the tool? I assume they are not considered bonds. I thought maybe I put them as custom series with the average return? Curious as to how you do it. Thanks again.
It seems like your latest version only allows Fama-French projections and not projected future real returns. I was using a conservative 4%/yr return before, how does that translate to Fama-French assumptions? Not sure what to enter for the weights either. My portfolio is 70% stocks with the majority in VTSAX and VFIAX.
“projected future real return projections” should have essentially zero impact on the simulations, because they are only used in the last few years of long simulations, say 1968 and a 60-year horizon. They ould have no impact on the 1929 cohort.
I suggest not to mess with those values.
Hi, I am using the spreadsheet and have filled out the basic information and I’m seeing something strange:
The form is showing a smaller SWR% for CAP20 and CAP>20 and SPX all-time high which seems counter-intuitive to me although the difference is quite small.
Is this expected? or perhaps I did something wrong while filling out the form?
Thank you very much for providing this resource and information it is very helpful.
Sorry I think there is a typo or formatting issue, meant to say
“Showing a smaller SWR for CAPE less than 20 vs CAPE greater than 20”
It’s possible. Depends on your asset allocation. If the equity weight is low enough you may find that the all-time worst WR occurred when the CAPE was below 20.
Big ERN, just to confirm. On the SWR Toolbox, on the “Parameters & Main Results” tab, when we enter and change the “Retirement Horizon (Months) cell B28, do we also need to then change the inputs on the “Cash Flow Assist” tab? For example, if we change from 600 months to 480 months, does that then just ignore the cash flow items for months 480 to 600? Or do we need to 1) change the Retirement Horizon, but then also 2) delete the cash flows for those later months? OR, does changing the Retirement Horizon take care of that and the later months are therefore ignored and don’t need to be deleted? Thanks for all you do!!
The cash flow items that are beyond your retirement horizon are ignored. You can prove this to yourself by putting a large number in one of the cells past your horizon and you’ll see that your results stay the same.
Correct! Good way to test this!
The sheet would ignore the additional cash flows outside your retirement horizon.
thank you!
something that is not very intuitive to me..
when I increase horizon I get larger Target Withdrawal (% p.a.).
maybe this is because PV of supplemental flows is getting bigger? but if I am so young to retire then pension can arrive too late and the protfolio will be gone, what am I missing?
I mean how can you take into considaration supplemental flows if your protfolio can be gone before they take place? how this is handle in the calc?
With a final value of 0, the SWR normally goes down as we increase the horizon.
Two effects can change that:
1: If your final value target is hugger than 0%, maybe even set to 100% of the initial, it’s possible that a longer horizon gives the portfolio more time to recover.
2: You have cash flows later in retirement that are so large, they make the withdrawals negative, i.e., you make net contributions later in retirement.
Is there a way to represent equities split equally between “growth” and “value”? In other words 50% value (HML) and -50% (HML)? Or is the other 50% automatically assumed to represent a blend of the S&P 500 by default?
If you have 50% growth and 50% value you’re back at 100% blended, so you’d set the HML parameter to 0%.
The present value of cash flows in the CAPE withdrawal sheet is =sumproduct(R19:R738,’Cash Flow Assist’!P11:P730) with S18 being the monthly discount rate. Using the built-in NPV function: =NPV(S18,’Cash Flow Assist’!P11:P730) results a slightly different result. not material but is that due to cumulative rounding error in column R?
No. It’s likely due to beginning or end of period assumption in the NPV Excel formula. I assume beginning of period cash flows, NPV uses end of period as a default.
Should the cells for the CAPE estimate on the CAPE-based rule tab (d6-d9) auto update each day? Mine has been stuck on June 20th. I even pulled down a clean sheet and still the same. Thanks!
I’ve been on vacation and don’t get to update this daily. Should be updated to 7/3/2025 now.
Thank you for the content!
If I am 100% stocks at retirement, what should be the slope and intecept? And why? What is the intuition?
The slope is still 0.5. You can check what kind of intercept works for you with the toolkit. But you will face a very volatile path of withdrawal amounts with 100% equities.
Hi, Karsten. I would like to extend the Case Study sheet to a different number of months. (Btw, the sheet text says 360, but it the sheet is set up for 480.) Are these the right steps to do so? Am I missing anything? Thank you.
– Extend the sheet rows to the number of months.
– Change the pmt() formula in BF to the number of months (it was originally 480).
– Add appropriate Y… rows to AU.
Hmm maybe this is more involved than I thought. 🙂 Also:
– Update the chart data ranges and horizontal axis range.
– Update the 5y window cells in column AN.
Correct
Thanks! I updated the cell. It now says 480 months.
For the most basic simulation of a case study, i.e., fixed allocation and glidepath, you can just copy/paste down the extra rows.
The CAPE-based simulations would need the adjustment of the PMT formulas, correct.
Thanks a lot for this amazing spreadsheet! What if someone wanted to take yearly withdrawals instead of monthly withdrawals? Can you update the spreadsheet every January and multiply the monthly by 12? Would you recommend discounting the yearly amount at all to add a margin of safety?
If you set your annual withdrawal to 12x the monthly you’ll get very similar results. I showed in Part 47 of the series that the frequency of withdrawals doesn’t change the safe withdrawal math very much.
Would it be possible for saferetirementspending.com to add a Generate URL button, where the plan parameters would be part of the URL (as an alternative to saving/loading from Excel). An example is at engaging-data.com (https://engaging-data.com/freedom-calculator/?sav=500000&spend=40000&wr=3.3), where you can modify the URL parameters and can save and share specific instances of a calculation just by sharing the URL. It would also be useful in a personal Excel sheet where a cell can build the URL parameters based on the latest personal financial info that’s contained in the sheet.
Thank you for all the great work in the early retirement community.
Would it be possible for saferetirementspending.com to add a Generate URL button, where the plan parameters would be part of the URL (as an alternative to saving/loading from Excel). An example is at engaging-data.com (https://engaging-data.com/freedom-calculator/?sav=500000&spend=40000&wr=3.3), where you can modify the URL parameters and can save and share specific instances of a calculation just by sharing the URL. It would also be useful in a personal Excel sheet where a cell can build the URL parameters based on the latest personal financial info that’s contained in the sheet.
Thank you for all the great work in the early retirement community.
Interesting idea. I’m not the programmer, but i can forward this request to my buddy.
Can this toolkit be used to include the accumulation phase? i.e. what if I start my timeline 10 years BEFORE retirement, then add in a cashflow that’s my savings amount in the first 10 years. Will it let the portfolio balance build and then start drawing down when that cashflow is gone?
I can estimate my portfolio balance in 10 years, but there’s not a great tool to help me understand the variability of that build-up with the same level of care and detail that you’ve put into your toolkit.
You could. Go to the Supplemental Cash Flow tab:
1: Model the future contributions as positive cash flows.
2: During the months when you don’t withdraw yet, set the scaling parameter in column S to zero.
The SWR is the one in effect during the months when the column S value is 1.
I don’t think this works.
I did the following:
1) Set initial portfolio value to today’s value (and used today’s allocation)
2) Put in my monthly savings as adjusted for inflation (I really only adjust annually, but this should be a small effect), and set the “start date” to today, and the Retirement Horizon to (100-current age)*12
3) Added other cashflows
4) Set Cash Assist column S to 0 for dates before retirement, and 1 after that.
5) Adjust retirement date to get $X safe spending, assuming money needs to last until I’m 100yo.
It gave an answer of 3.5 years from now.
Then I found the starting date for the lowest SCR (in this case it was July of 1911) because I wanted to look at inflation adjusted cashflow.
So for each month, I track the balance as the prior month balance adjusted by the monthly returns (asset returns column AF), inflation and (asset returns column E, current month divided by prior month) starting with row 492 (July 1911), then added monthly cashflows (cashflow assist column R times the current portfolio value; the result is positive for savings/income, negative for expenses) including the $X used in step 4 above starting 3.5 years from now.
But the portfolio balance went negative just 6 years into retirement.
I had to adjust the retirement date to 8 years from today to get to the point where the portfolio balance stayed positive until I turn 100. I also went back and change the inflation adjustments to use the straight consistent inflation from Cashflow Assist E6, but it only made a small difference.
3.5 years vs. 8 years is a really different answer.
Am I thinking about this the wrong way?
Note, that when I say Column R times the current portfolio value, I mean today’s value (i.e. Cash Assist cell E5), not the monthly balance I’m tracking.
And also, when I adjusted retirement date to keep the tracked balance above zero (3rd to last paragraph), I properly adjust the timing of savings/withdrawals to match the retirement date (e.g., I don’t keep starting the $X at 3.5 years, but rather have it start at retirement (which settled at 8 years to keep a positive portfolio balance).
Noted.
This scenario often happens when someone has a very small initial net worth but enters large cash flows later in retirement, larger than your retirement needs later in retirement needs.
The sheet then calculates an initial withdrawal early, the portfolio goes into “debt” and then pays back the debt with future net-positive cash flows.
In such a scenario, my sheet is not useful because you don’t have a typical safe withdrawal exercise, i.e., large initial portfolio supplemented by some future small flows. Your problem is a different one: how do you borrow against future wealth. You might still use the number from my sheet, but your main job is to figure out how to borrow against that future wealth.
That’s not what’s happening in my case. While the future cash flows (particularly Social Security) do become a big portion of my living expenses, they never exceed them, and the portfolio is being drawn down every month according to the “Cash Assist” tab. The SWR on the “parameters & main results” tab is computed as 6.21%, while my positive cash flows max out at 4.9% (1.1% 9 years from now – not adjusted for inflation thereafter, and an additional 3.8% 13 years from now – adjusted for inflation thereafter).
The problem is that if I then try to model that with the return data in your spreadsheet, it goes negative after only 1 of 3 positive cash flows start, but it stays negative through the end of the period – it never recovers (because the positive cash flows are never enough to cover living expenses, much less pay off debt).
I am not sure if I’ve made an error, or if the spreadsheet doesn’t compute correct safe withdrawal rates when you’re modeling the accumulation phase, but I can certainly see how it makes sense when I look at my detailed cash flow – if I really did retire in July of 1911, I would have seen my real portfolio balance drop 15% even though I’m adding 10% of today’s balance over the next 3.5 years (so really kind of a 25% drop).
That big drop and the inflation between now and then causes my real initial withdrawal to be 9.6% of the real portfolio value for that first month of retirement, even though 0% failure rate withdrawal percentage on the “parameters and main results” tab is 6.21%. That high level of withdrawals is unsustainable, even with positive cash flows coming into play 6 and 10 years into retirement.
So how do I reconcile this?
Then you likely made an error.
It’s impossible to gauge what’s wrong from just this comments back-and-forth.
If you want to share the link to the sheet that generated this result I can take a look.
OK, now I understand it better.
For case studies, you should use the “Case Study” tab. I did the example as you requested, i.e., 42 months of substantial contributions and scaling = 0. Then check how the 7/1911 cohort would have fared:
https://docs.google.com/spreadsheets/d/1r0_1jR6Xld5IIwZnkvxWpXcKmyrb99nQh9PGXShBDAE/edit?usp=sharing
The 1911 cohort runs out of money in month 480 exactly as calibrated, i.e., if the withdrawal rate is set to the 1911/07 SWR. Not after 6 years.
You made the mistake of adjusting the withdrawals by inflation, as I now noticed in your initial comment. You shouldn’t do that. That parameter is simply used to discount nominal supplemental cash flows. It’s not meant to adjust anything else in this sheet. All figures, i.e. withdrawals, portfolio values, etc., are already in real terms and should not be adjusted for inflation again. Also did you adjust the monthly withdrawals by that annual number? That would explain how you run out of money so quickly.
So, I recommend simply using the features that are already there. They are correct and time-tested. Somebody just hacking around themselves might get crazy answers like yours.
Ah, I found my mistake! I was adjusting my real portfolio value column by the inflation numbers, not realizing that your returns were REAL returns. Once I fixed that I get a very similar answer (not identical because I didn’t do the asset splits in mine).
So it says I can retire in 2 years at 57. But that is still SO scary. I worry about health care costs. Even though I’ve done research on what insurance will cost through the exchange, and I haven’t accounted for my HSA (which will ameliorate the risk), the uncertainty of health care just really scares me.
Good! So, we’re on the same page. Good luck with your retirement planning!
thank you for this tool.
also would love a video on other ways to use it – if you have the energy for it.
? for you on sinking funds: I read your posts about not having emergency funds. would you recommend always withdrawing the max SWR every month and putting extra money into cash for sinking funds?
would that answer change for known / unknown sinking funds?
for example: known would be things like property tax, vacation. unknown might be house / car repair funds
or would you recommend leaving that money invested and then if you have to pull a “large” amount (say 3 months of withdrawals) at once, just re-retire and rerun the spreadsheet and start over?
tyty
I didn’t have an emergency fund while accumulating, because I preferred 100% equities.
Now, in retirement, I tread more cautiously. I have a bond allocation. I don’t see the need additional buckets of money market. I have enough assets to cover any expense, small and large repairs without a designated E.F.
Thank you so much for the incredibly detailed and careful spreadsheet, and the impressively comprehensive series of SWR articles.
I’m trying to calculate the failure rates using an asset glidepath rather than a static asset allocation. Is it possible for the spreadsheet to do that? Or is that something that could only be calculated in Octave or similar?
There would be a way to do it in Excel, but it requires a bit of work.
My other site: https://saferetirementspending.com/ has the glidepath simulation option. Maybe try that one first.
Hi Karsten, first of all, thank you for the awesome job you have been sharing with us for so many years. Most likely you was asked the same question many times, but I was not able to find any specific answer to this topic: how could a foreign user (Italian, in my case) best adapt the toolbox to geographic peculiarities?
For equities I am ok with mixing US with non-US stocks in order to mimic a world index. Just a clarification here: are non-US stocks including emerging markets, or only developed countries?
What I am concerned about, is the impact of the following geographic-specific parameters on the final output:
1. EU bonds historical returns, both governative and corporate vs US 10y / 30y Treasury. Here I do believe that at least a
2. Inflation (Italian / European vs US)
3. Currency (USD vs EUR)
It would be really bad if the rigor of the analysis behind the tool, is ruined by a wrong data input.
Some doubts I hope you could cast some light on:
– How could Custom Series be used to integrate the asset allocation?
– Exactly what data should be used if someone wants to integrate the above 3 parameters on the tools, and how could it be done properly?
– Which is the minimum set (how many years in the past) to be used in order to maintain the proper representativity?
– Some hint on where these data could be found?
Pretty sure your feedback could be the basis for a dedicated post or some future part of the SWR Series, couldn’t it? 😉
I implemented two custom series. They can be used to simulate portfolio positions for assets that are not included in my dataset. But you’ll have a hard time finding similar data coverage for non-US assets. I would target returns going back at least to 1929. But I have no idea where those returns are available. The paper by Cederburg and friends (https://papers.ssrn.com/sol3/papers.cfm?abstract_id=4590406) uses international data but it’s not publicly available. You’d need to pay for that.
Hi Karsten,
I’ve played the toolkit for a while and read many posts. I’m surprised from 0% to 20% allocation of “Bonds: 10y U.S. Treasury” can make a huge difference on SWR. But math doesn’t lie and thank you for your wonderful work to bring this critical information to us!!!
Before I start to retire, I’d like to understand a bit deeper so I’ll be able to check if I’m on the track or not over time. One thing I’m not sure I get is the data in “column G: 10Y BM” in tab “Asset Returns”.
I assume it represents the bond value, and is the product of the bond price and the share, and the latter increases sightly on each coupon payment based on the bond price after the payment. This number is NOT CFI adjusted (adjustment is done in column R).
For coupon payment, I’m aware it probably comes from DSG10 (https://fred.stlouisfed.org/series/DGS10), but what’s your data source for the bond price?
On the market, IEF (https://finance.yahoo.com/quote/IEF/) tracks DSG10, and its gain in for example 2015 and 2016 is about 1.50% & 1.01%, however, column G doesn’t reflect the same gain, even after applying the 0.15% expense rate for IEF.
A B G
12 2,014 79,221.28
12 2,015 80,120.65 80,120.65/79,221.28-1 = 1.13526315% v.s. 1.50%+0.15%
12 2,016 80,071.86 80,071.86/80,120.65-1 = -0.0608956617% v.s. 1.01%+0.15%
I hope I’m missing something here.
Thanks
I take the Total Return data from here: https://www.spglobal.com/spdji/en/indices/fixed-income/sp-us-treasury-bond-current-10-year-index/#data
Direct link to the data Excel Sheet: https://www.spglobal.com/spdji/en/idsexport/file.xls?hostIdentifier=48190c8c-42c4-46af-8d1a-0cd5db894797&redesignExport=true&languageId=1&selectedModule=PerformanceGraphView&selectedSubModule=Graph&yearFlag=tenYearFlag&indexId=1307902
The 10-year benchmark bond index is not equal to the 7-10y bond index that’s tracked in the IEF. In fact, for the 7-10 returns, please use this index: https://www.spglobal.com/spdji/en/indices/fixed-income/sp-us-treasury-bond-7-10-year-index/#overview
Since the IEF is not identical to the 10yBM, the returns may deviate, sometimes substantially. Overall, though they are pretty close, despite the occasional mistracking. The closest ETF to the 10yBM is the ETF with the ticker UTEN. I would still prefer the IEF, though due to higher liquidity and less turnover.
Thanks Karsten for these pointers! I downloaded the last 10 year data of the index from your links.
The 10y index matches the data in column G 10Y BM in tab “Asset Returns”, not in absolute value but in relative ratio, for every single year. As you already explained, that column is populated with that data.
I also downloaded the 7-10y index from your link, and it matches return from IEF at least in the year of 2016 (Every ETF dividend is reinvested on the next day close for more shares of ETF).
What I’ve found interesting is that during 2015-2024, 7-10y index outperforms 10y index a lot (1.060 v.s. 1.007). I guess the yield should be close, so the difference (6.0% v.s. 0.7%) is mainly driven by the bond price that fluctuates in the market?
If bond price plays such a role for these 10 years, this leads to my question deep in my mind: When I actually prepare this journey, is it possible I purchase the bond at relatively high market price per share and that hurts the return by a few percentage and invalidates the big conclusion that 20% bond holding cancels risks of dawnturn of stock market?
I actually replaced your column G with numbers that grows at the rate of 7-10y index, rather than the 10y index, but the safe withdrawal rate doesn’t change. That said, I believe the safe withdrawal rate is dominated by a few very bad years, which 2015-2024 probably doesn’t belong to. Therefore, my question remains open: to overcome those bad years, does bond purhcase price actually matter?
Raw data:
A: Effective date
B: S&P U.S. Treasury Bond 7-10 Year Index
C: Yearly return of B
D: S&P 10y Index
E: Yearly total return of D (~1% difference max)
F: 10y BM in Toolkit
G: Yearly difference of F (closely matches column E)
A B C D E F G
12/31/2015 568.59 492.64 80,120.65
12/30/2016 573.84 1.009233367 492.34 0.9993910361 80,071.86 0.9993910434
12/29/2017 589.14 1.026662484 503.47 1.022606329 81,882.00 1.022606444
12/31/2018 594.86 1.009709067 503.86 1.000774624 81,945.42 1.000774529
12/31/2019 644.65 1.083700366 547.77 1.087147223 89,086.74 1.087147274
12/31/2020 709.42 1.100473125 606.35 1.106942695 98,613.91 1.106942627
12/31/2021 686.92 0.9682839503 583.65 0.9625628762 94,922.09 0.9625628879
12/30/2022 583.3 0.8491527398 487.51 0.8352779919 79,286.34 0.8352780686
12/29/2023 606.18 1.039225099 504.75 1.035363377 82,090.17 1.035363343
12/31/2024 602.48 0.9938962024 496.32 0.9832986627 80,719.15 0.9832986093
9y total 1.059603581 1.007469958 1.007469984
Using the 7-10y index will not make a huge difference because the 2015-2024 interval is mostly inconsequential for the worst-case cohorts.
The two indexes are have better matching returns outside that window. I would not stress out over the return differences. Bonds are diversifier. If stocks tank like in 1929 and 2008, bonds will save the day. whatever index you take, whether its the 10y or the 7-10y.
Does reducing the timeline and rerunning every (month, quarter, year, etc) reset the SORR clock?
Clarifying info: I am in my first year of full (portfolio drawing) retirement inside of a 30 year window. In order to hedge against SORR, we have instigated a 55/45 >> 75/25 rising EQ Glidepath based on my impression that the first 2 years have the greatest SORR impact (that then reduces over the next 3-8 years. I am 10 years away from full Social Security (end of Glidepath). Currently my SCRs cover our needs and wants (even at today’s super high bubble-iscious ” CAPE 2 values, so I am more interested in protection than jucing my initial SWR. I have reviewed parts 19, 20, and 43, but the question remains. Since “re-running” the toolbox and then looking to stay under the new ceiling (similar to Jason on 2 Sides of FI) could be considering re-retiring I am wondering if that means I need to re-examine my Glidepath timing as well? Thanks. Appreciate all you do for us all.
You could consider an “active GP,” i.e., wait until stocks are well underwater before ratcheting up the equity portion.
Rob Berger just released a video about how it’s not so easy to figure out.
What video? Link? Time stamp?
No. See part 38: https://earlyretirementnow.com/2020/07/15/when-can-we-stop-worrying-about-sequence-risk-swr-series-part-38/
Karsten, thanks so much for developing the web-based tool. I still prefer the spreadsheet, but the simpler interface of the web-based tool will open up the concepts you discuss here to a wider audience. I have friends and colleagues who are not “personal finance nerds” (I most assuredly AM a personal finance nerd) but would still benefit from empirical analysis of their potential spending during retirement. Your new web-based tool offers a solution for these folks!
Cheers
Thanks for the feedback! Yes, that’s why we created the web-based tool! 🙂
Hello Karsten. First thank you for awesome SWR blog posts and the SWR toolkit, it has been tremendously helpful in planning my retirement!
It looks like the “scaling of withdrawals” values in the cashflow assist tab are not taken into account in the CAPE-based rule calculations. Would it be possible to factor those in? (based on my understanding of how the CAPE-based calculations are done, the answer would be “no”, but you’d obviously know better.)
if not possible, may be worth making a note of that in the sheet to avoid misunderstanding/confusion.
Thank you!
Fadi
They are not taken into account in the CAPE-based tab (yet) for the calculations in B10 to B17. Correct. I will try to fix that soon.
But you can run case studies in the so names tab. Those will take into account the scaling and supplemental flows.
Hello Karsten. Very much appreciate the level of detail and the very helpful spreadsheet. I have noticed since downloading the sheet in excel in both October and November the formulas in the Case Study all somehow are not working and basically showing errors. Looks fine until I download into excel. Not sure if others have had this issue, or any tips from anyone who may know excel a lot more than me.
There have been some changes in the Case Study tab. They work on my side, but I can’t guarantee portability to MSFT Excel.
Hi, Karsten. I got curious about the inflation estimate in ‘Cash Flow Assist’!E6 and how it is used in the spreadsheet. When calculating SCRs based on historical data, that inflation estimate is used instead of historical inflation. So cash flows not adjusted by inflation (columns J-N) are adjusted by that estimate instead of historical. Would it be helpful to switch to using historical inflation in that case? I am wondering what impact it has on the results (I have some non-inflation adjusted cash flows and also like to sanity check some potentially inflation adjusted cash flows by making them non-inflation adjusted).
I made an attempt at this change here on this test spreadsheet:
https://docs.google.com/spreadsheets/d/1g6-c13Uyyv_rDe9ialVBJbePWVatSTzNvEKEUJlKJK0
The changes are:
– Parameters & Main Results
– Added C63 D63 and below
– Cash Flow Assist
– E8 boolean flag to toggle this functionality
– Columns U V to divide R into inflation and non-inflation adjusted
– Asset Returns
– Columns AL AM to apply the AJ calculation to above U V data
– Column AJ updated to use AL AM
– Case Study
– Updated columns G BJ BK
What do you think? Are these the right changes?
Toggling the flag does change the results, such as the fail safe rate changing from 3.48% to 3.4% with the test data in the spreadsheet.
Thanks for working on this. If you like to apply the historical CPI dynamics to the future nominal cash flows, then that’s how you would do it. Apply the discounted supplemental values for each cohort as you did in “Asset Returns.” I haven’t checked everything 100%, but the results certainly make sense.
I still prefer my method, where I take historical real returns, but calibrated future CPI rates because we won’t go back to the crazy CPI dynamics we’ve seen 100+ years ago. See this chart from my recent post:
But it’s still valid to use the real return distribution from 100 years ago, because that distribution hasn’t changed as much as the CPI volatility.
Ah I see. I was wondering whether some hidden historical connection between sequences of inflation and returns might exist and impact the results, but even if so, they would not necessarily apply due to these changes in inflation regimes.
Fwiw, here are the SCR time series charts (pasted in first tab of the test sheet) with inflation set to 3% vs historical inflation when I use my anticipated future cash flows with 50 year horizon. The SCR drops slightly, though interestingly it comes from 1930 and late 1960s swapping worst cases.
https://docs.google.com/spreadsheets/d/1g6-c13Uyyv_rDe9ialVBJbePWVatSTzNvEKEUJlKJK0/edit?gid=1130923200
Yes, the 1930s had unnaturally low inflation rates and the 1970s/80s had much higher rates. So they will impact your SCR accordingly, i.e., depending on whether your nominal flows are net positive or negative.
What wpuld be the easiest way to constrain the simulations to a given time period? For example, when analyzing failure rates for a portfolio containing gold, it doesn’t seem fair to include the gold standard era in the SWR calculations. Can I just delete the rows of the gold standard era in the asset returns and it will work?
I know there is the “since 1926“ result, but I would like to get the” conditional to CAPE” also restricted to the post gokd standard era.
In tab “Parameters & Main Results”, the formulas for the SWRs, cells E9 to V26 all access values in other tabs: array formulas of large vectors. You could pick the vectors refereeing to time periods you like.
Thank you for the comprehensive update on the SWR Toolbox! The enhancements, especially the Fama-French factors and the new web-based simulation tool, are impressive. It’s great to see user input streamlined and the addition of glide path simulations. Your dedication to improving this resource is evident and much appreciated!
You bet! Glad you find this helpful!