### 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.

I’ve been exploring this spreadsheet – thanks for creating and sharing it! I’m particularly fascinated by the opposing nature of savers and retirees as it relates to Sequence of Returns Risk. What if I can be a saver during the first phase of early retirement through austere budgeting? It seems that we can explore this scenario by choosing a target portfolio value at >100% of the original. But there are a few aspects of your SWR Toolbox I’m not sure about that I hope you could clarify.

1) You describe the tab “Distribution of Final Value” as showing real dollars. In the main tab “Parameters & Main Results” is the ‘Final Value Target (% of Initial)’ in real or nominal dollars? I want to preserve 100% of my purchasing power after 60 years, not 100% of my nominal value.

2) As the portfolio grows faster through reinvestment of funds in excess of income & inflation, the safe yearly income will be higher than a portfolio that only grows to satisfy income & inflation. However many SWR studies choose the WR as a percentage of the original portfolio value, which is then adjusted for inflation over time but not adjusted for a growing portfolio. For a 100% asset preservation target, this seems fine — the portfolio only needs to grow to keep up with inflation. Does the math behind the SWR Toolbox allow for computing an additional growth factor separate from inflation that represents income growth through reinvestment? It seems that this would amount to a variable WR that grows relative to the original portfolio value over time, as opposed to WRs that vary with portfolio value at a fixed moment in time.

3) I’ve extended the spreadsheet with a coarse calculation for the yearly raise in income one would receive when the final target value is greater than 100%. This helps me explore just how low a WR I must commit to if I want a retirement income that increases faster than inflation. For a 2% yearly raise above inflation (growing to 328% of the original portfolio value after 60 years) it seems the SWR is quite low (1.6% for 0% failure). However this is a simple yearly compounding that doesn’t take into account sequence risk (or sequence benefit, in the case of a saver). It seems that another way to approach this is to provide numbers for Supplemental Income that represents the additional yearly savings due to austere budgeting. However this would be much more compelling if it were computed based on historical values like the rest of the SWR toolbox. For some retirement cohorts, the returns in early retirement are so poor that even low SWRs require spending capital instead of growing the portfolio; yet these are exactly the cases where being a saver (choosing an excessively conservative SWR) does best for the portfolio. Budgeting for an income that is a few basis points below the SWR during the first decade of early retirement should lead to a much better SWR later on. I’m curious what this dynamic looks like. Specifically I’m envisioning a scatterplot with one axis showing the basis point reduction (or increase) over the SWR for the first X years, and the other axis showing the basis point increase (or reduction) over the SWR for the remaining time. Ideally there won’t be a simple linear regression fit, due to compounding.

I’ve read your entire blog (not just the SWR series), so at this point I suppose I’m just pressing you for more material 🙂 Thanks for all that you’ve done, and I look forward to your future posts!

From this comment:

I’ve been exploring this spreadsheet – thanks for creating and sharing it! > I’m particularly fascinated by the opposing nature of savers and retirees > as it relates to Sequence of Returns Risk. What if I can be a saver during > the first phase of early retirement through austere budgeting? It seems > that we can explore this scenario by choosing a target portfolio value at > >100% of the original. But there are a few aspects of your SWR Toolbox I’m > not sure about that I hope you could clarify. > > 1) You describe the tab “Distribution of Final Value” as showing real > dollars. In the main tab “Parameters & Main Results” is the ‘Final Value > Target (% of Initial)’ in real or nominal dollars? I want to preserve 100% > of my purchasing power after 60 years, not 100% of my nominal value. > > 2) As the portfolio grows faster through reinvestment of funds in excess > of income & inflation, the safe yearly income will be higher than a > portfolio that only grows to satisfy income & inflation. However many SWR > studies choose the WR as a percentage of the original portfolio value, > which is then adjusted for inflation over time but not adjusted for a > growing portfolio. For a 100% asset preservation target, this seems fine — > the portfolio only needs to grow to keep up with inflation. Does the math > behind the SWR Toolbox allow for computing an additional growth factor > separate from inflation that represents income growth through reinvestment? > It seems that this would amount to a variable WR that grows relative to the > original portfolio value over time, as opposed to WRs that vary with > portfolio value at a fixed moment in time. > > 3) I’ve extended the spreadsheet with a coarse calculation for the yearly > raise in income one would receive when the final target value is greater > than 100%. This helps me explore just how low a WR I must commit to if I > want a retirement income that increases faster than inflation. For a 2% > yearly raise above inflation (growing to 328% of the original portfolio > value after 60 years) it seems the SWR is quite low (1.6% for 0% failure). > However this is a simple yearly compounding that doesn’t take into account > sequence risk (or sequence benefit, in the case of a saver). It seems that > another way to approach this is to provide numbers for Supplemental Income > that represents the additional yearly savings due to austere budgeting. > However this would be much more compelling if it were computed based on > historical values like the rest of the SWR toolbox. For some retirement > cohorts, the returns in early retirement are so poor that even low SWRs > require spending capital instead of growing the portfolio; yet these are > exactly the cases where being a saver (choosing an excessively conservative > SWR) does best for the portfolio. Budgeting for an income that is a few > basis points below the SWR during the first decade of early retirement > should lead to a much better SWR later on. I’m curious what this dynamic > looks like. Specifically I’m envisioning a scatterplot with one axis > showing the basis point reduction (or increase) over the SWR for the first > X years, and the other axis showing the basis point increase (or reduction) > over the SWR for the remaining time. Ideally there won’t be a simple linear > regression fit, due to compounding. > > I’ve read your entire blog (not just the SWR series), so at this point I > suppose I’m just pressing you for more material 🙂 Thanks for all that > you’ve done, and I look forward to your future posts! >

oops – please delete this weird self-reply

I performed some coarse calculations to examine the effects of a WR that is lower than the SWR, which leads to surpluses that are reinvested to grow the portfolio faster than inflation. Comparing this Austere Withdrawal Rate to the SWR over time shows that the yearly income generated by the AWR eventually surpasses the SWR, and this can take a long time (>10 years) for the tail case. However, here’s a big gap in the Final Portfolio Value between the tail case (safe 0 percentile) and even the 5% level. Since I’m exploring the possible upside from a low WR early on, I’m willing to take a look past the lowest “safe” upside amount. At the 5 percentile it takes a fraction of the time for the AWR income to surpass the SWR income. When comparing an SWR of 2.68% (for FV = 100% of original) to an AWR of 2.44% (nearly 0.25% difference), the tail case takes 14 years for AWR income to win, compared to less than 5 years for the 5 percentile.

My results are visualized here: https://public.tableau.com/profile/datanerd#!/vizhome/SWRvsAWR/YearsuntilAWRpayoff

Thanks! I provided the math for calculating the exact initial SWR with growing or declining the withdrawal amounts by rates over/under CPI. See SWR series part 8. Maybe you like to play with that!

Thanks!

1: all numbers are always in real, CPI-adjusted terms.

2,3: I have done some calculations for changing the withdrawals by less than inflation, see SWR part 5. This feature is not in the SWR Google Sheet, though.

But there is a relatively simple rule of thumb: If you increase the withdrawals by x% over and above inflation you’d have to decrease the SWR by about that same number.

For more on the math, see SWR series part 8 under “Time-varying withdrawal rates”!

Looking at part 5, I note that the WR changes to provide for different levels of consumption. I’m instead exploring a fixed WR at a level lower than the SWR, in order to fuel increasing consumption by way of portfolio growth through reinvestment. My WR could remain lower than the SWR perpetually, and eventually my smaller consumption of a larger portfolio would outpace the larger SWR consumed from a smaller portfolio. I’ve been digging in to determine if the payoff happens quickly enough to be worth considering, given the up-front lifestyle commitment it would require.

If you pick the WR and apply that same % every year, that would certainly do the trick provided you keep the WR below the target real portfolio return!

Hi, great posts so far. I’m new to this and trig to wrap my head round all the info. 1) So if I understood this correctly, in practice, when I’m coming up to retirement, I could input all the numbers into the parameters in the Google spreadsheet, look at what the CAPE will look like for the next 10 years and then check the spreadsheet in the 1st sheet entitled parameters and main results to see what could be my SWR? 2) I am looking to retire in approx 10 years, so I could use the tool from now to see what value my portfolio would need to grow to have my desired SWR? 3) I am an EU citizen so I’m wondering to what extent this can generalised to Europe? Im holding a world stock etf and global gov bond etf. I’m thinking using your spreadsheet and cfiresim which allows for more global portfolios. Cfiresim says with my pension contributions – 4% gave a 0% failure rate. With your spreadsheet I get 3.5% with max 5% failure rate. But that makes a big difference in terms of how long I work and I’m not sure what to go with. What do you suggest? Thanks a lot!

This spreadsheet is set up to work for simulations at the beginning of retirement. I would not recommend using this to plan your path to ER in 10 years. What you could do is set a “reasonable” portfolio return, iterate that forward 10 years and see where you stand in terms of portfolio value.

My sheet allows you to enter the pension income as % of the initial portfolio. The pension would then reduce your portfolio withdrawals in the future. Careful with nominal vs. inflation-adjusted pensions!

Hiya

1) So I could take the spreadsheet just before I retire, plug in the numbers and will know what SWR I should use by looking at what the CAPE would look like for the next 10 years? Eg cape 20-30 Quill tell me 5% failure rate if I withdraw 3.5%?

2) when inputting pension into the spreadsheet, let’s say I know that I would get eg USD1k/ month. So I would convert it to an annual amount and then divide it by my initial portfolio eg USD1M=0.012. I then input that and should divide it by 12?

1: correct

2: you input the monthly supplemental income (positive numbers) or expenses (negative numbers) as % of your net worth at retirement. So if you have $1m and expect $1,000 in monthly benefits then this would be 0.001. Or 0.1%.

Hiya

1) So I could take the spreadsheet just before I retire, plug in the numbers and will know what SWR I should use by looking at what the CAPE would look like for the next 10 years? Eg cape 20-30 Quill tell me 5% failure rate if I withdraw 3.5%?

2) when inputting pension into the spreadsheet, let’s say I know that I would get eg USD1k/ month. So I would convert it to an annual amount and then divide it by my initial portfolio eg USD1M=0.012. I then input that and should divide it by 12?

Thanks

A few questions regarding the Case Study calculations:

1) It appears that there aren’t any CAPE rules applied to the monthly withdrawel rates.The withdrawel amount remains constant based on the initial withdrawel rate. If I chose an intial withdrawel rate of 4% with a starting portfolio value of $1M, then I will see a monthly CPI adjusted withdrawel of $3,333 for the duration of the retirement horizon. Is this correct?

2) I entered a supplemental income and noticed the monthly withdrawel amount remained at $3,333 for all months regardless of the months which incurred supplemental income. Shouldn’t the monthly withdrawel amount be reduced by the supplemental income?

fantastic update, would like to know the details of the S&P drawdown and cashflow additions.

Perhaps you could post your Octave files on Github? Putting it in a Jupyter notebook would also make it more accessible.

Are you aware of any data sources that provide dividend and distribution history in addition to total return? This would be useful in incorporating tax drag into back tests.

Well, for the average FIRE person, running the whole Matlab/Octave gauntlet is probably overkill. That’s why I created the spreadsheet here.

Prof. Shiller has the SPX data (both price return and total return), so if you like you can gather the monthly dividend income from his spreadsheet.

I post the 10y benchmark returns in my spreadsheet. If you compare it with the 10y yield data on Prof Shiller’s you can also split that into yield vs. price return vs. total return

http://www.econ.yale.edu/~shiller/data.htm

A question about using the Toolbox v2.0: Somehow my results indicate that the CAPE>20 (I assume it should be 20-30) is riskier than CAPE>30. For example, at 5.5% WR, the probability of failure for CAPE>20 is 42.65%, but was 0% for CAPE>30. At WR 5.75%, the probability of failure is 53.01% for CAPE>20, and 32.2% for CAPE>30.

I played with the parameters I entered, and I found that these kind of results are mostly due to the fact that I had entered 45% as stocks. I wonder if the results are legitimate, or it’s due to a bug?

I am reading your PDF section 3.2-Simulation Results, and I noticed similar phenomenon for 30 yr horizon (but not for the 60 yrs). I set my horizon at 40 yrs, so I got similar results to your 30 yr simulation. So this is not a bug. 🙂

But why this happens? Why with a 30-40 yr horizon, and when CAPE is sky high, a bond dominant portfolio will have 100% chance of success?

Very interesting! I do a lot of data analysis on biological/medical data, and I see a huge similarity between the finance simulation and bio-data analysis.

Use the conditional failure rates with a degree of caution. There were only a few instances where the CAPE was this high and we observed failures.

It’s possible that for very high bond shares (and low=45% equity shares) the 1960s/1970s were the worst possible staring point, even though the CAPE at 25 was lower than in 1929 (CAPE >30).

It’s not a bug. That’s just how the stock/bond returns worked out. There isn’t a perfect correlation between CAPE and future returns! 🙂

Found this sheet, which led me to the rest of your site (loving it!). The sheet is extremely helpful as I am 9 months away from FIRE (been FI for a while, but finally getting around to retiring early). I am having a file problem and I am hoping it is no big deal. When I copy, no issues. But when I saved the file and opened it, I get an error.

“Excel completed file level validation and repair. Some parts of this workbook may have been repaired or discarded.

Removed Records: Formula from /xl/worksheets/sheet1.xml part”

This is what popped up. It had a link to a file, so this is what is in it.

error322800_01.xml

Errors were detected in file ‘E:\Users\Scott\Downloads\Copy of EarlyRetirementNow SWR Toolbox v2.0 – save your own copy before editing!.xlsx’

–

Excel completed file level validation and repair. Some parts of this workbook may have been repaired or discarded.

–

Removed Records: Formula from /xl/worksheets/sheet1.xml part

Using Office Pro Plus 2016 and I know that isn’t the very latest (if you count 365), but it seems too recent for that to be the issue.

Thanks again, terrific product that on initial look might mean I withdraw a higher number than I was thinking (I was thinking 3% since I will be 54, but might go just below the worst failsafe).

Oh wow, I can not guarantee any conversions, especially not to older versions of Excel. No idea why that happens. I occasionally export this to Excel and the formulas work. But the formating is completely messed up. So I just keep all the calculations in Google Sheets…

Big ERN, love the site and the SWR series. We RE last November and were living the 4% rule until I stumbled here.

I am 52, married with 62k pension. NW is $1.1m.

Looking to take SS @ 62, approx 22k. Mrs @ 62 for 16k.

Is it possible I am looking at 11% SWR at 60/40 or did I screw something up with your calculator?

Keep up the great work bringing clarity to the masses.

Doesn’t look too crazy. It’s because you almost fund that $121k annual budget with your SS alone 10 years down the road.

But make sure you account for the pension as a nominal (non-COLA) cash flow if it doesn’t have COLA.

Also, make sure you check if it isn’t optimal to use different timing on the SS. Normally, the higher earner to take benefits at age 70.

Check https://opensocialsecurity.com/

Love the site and SWR series. I do have a question about the term SWR in the spreadsheet.

Using defaults with a 0% target and a $2M NW, the spreadsheet calculates “0% Failure Rate – All” as about 3.19% and $5311/ month. If I add $1000/month of COLA income to the scenario, the SWR goes to about 3.79%.

It seems to me that the 3.79% figure is more of a safe spending rate than a withdrawal rate. It really means that I will withdraw safely at 3.19% and will supplement with $1000 to have $6311/month. The $6311 equates to 3.79%.

In fact, without the additional income, the SWR stays the same as I vary the NW.

Am I misinterpreting?

Correct. It’s the spending rate. 🙂