Since I first published Part 7 of the SWR Series with the accompanying Google Sheet in early 2017, I’ve made several changes and enhancements. Sometimes without much explanation or documentation. So, it would be nice to do a quick update and itemize the changes since then. Whether this is the first time using the toolbox or you check it out again after more than a year, I hope you all find the new features useful…
Here again is the Google Sheet Link:
Link to the EarlyRetirementNow SWR Toolbox v2.0
As always, please save your own copy because the current (clean) version posted on Google Sheets has to be write-protected so visitors don’t mess around with my formulas! You will be asked to create your own personal copy, which you can edit! 🙂
Also, if the Google Sheet and all the options seem intimidating, my friends Jason and Eric at Two Sides of FI put together a video tutorial on YouTube explaining how to get started with this toolkit.
Main Tab: more detailed results
As before, you enter almost all parameters in the main tab “Parameters & Main Results.” All fields in Orange are user inputs, such as:
- Equity/Bond/Cash/Gold share. To make sure the weights sum up to 100%, the Gold share is set to 100% minus the sum of the rest.
- Fama-French style factors. This is new, see item #3 below for more details.
- Assumed projected asset returns for the asset classes going forward. Why do this? I’d like to be able to simulate 60-year windows of the 1960s and 1970s retirement cohorts and don’t want to be constrained by a slightly shorter than 60-year data availability. Since the SWR is overwhelmingly determined by the first 10-15 years of the retirement horizon (Sequence Risk, see SWR series Part 15), the last few years of “made up” returns during retirement don’t have much of an impact on the SWR estimates. Example: the failsafe WR is 3.46% during the 1960s with our assumed 3.75% p.a. equity return. If we increase the equity expected return to 20% p.a., the failsafe increases to only 3.52%. But, of course, the 2000 and especially 2007 fail-safe numbers would be significantly impacted by the return forecasts. So, use those figures with a grain of salt!
- The retirement horizon in months and the target final value (e.g. for bequests) as a percentage of the initial value.
- Notice that the supplemental cash flows are no longer inputted here in this main tab, but in a separate tab, see item #2 below.
The main results table is pretty large so I split it in two, the first part is below:
- As before, I display the failure rates of different initial withdrawal rates in the top half of the table.
- The bottom portion of the table is new and presents the calculation the other way around: Pick a desired failure rate and look up the withdrawal rates to match it. The first row in that portion at 0.00%, i.e., this is the fail-safe initial withdrawal rate. But you can also look for other failure rates, 1%, 2%, 5%, and 10% as well and see how much extra initial withdrawal you could have sustained in historical simulations. And I hope that nobody would even consider a strategy with a failure rate of 25 or even 50%, but I display those just for reference.
- The columns calculate the corresponding stats for all retirement starting months and years since 1926 (the start of the Fama-French database and also the starting point for the Trinity Study) and 1950 and also for different Shiller CAPE Ratio regimes (under 20 vs. 20 to 30 vs. 30+). Notice that as of late August 2018, we’re at over 32! As conservative as I am normally, though, I’d concede that today’s elevated CAPE ratio doesn’t quite “feel” like the 30+ CAPEs of the past. Maybe I’d still consider the current CAPE a high 20s, not really a low 30s!
- Notice how sensitive the failure rates can be when changing the WR in 0.25% steps, especially when the CAPE is high!
And Part 2, see below. The table has the same format as the first part, but now the columns are conditional on how far the S&P 500 index is away from its most recent high. Why does this matter? The stock market is a Random Walk and past returns have no bearing on future returns, right? Wrong! As is well-known in finance and as I pointed out in a post a few months ago, stocks have the tendency to mean-revert. So, expected returns tend to be higher after a steep drop and lower after a long run-up in stock prices. That’s reflected in the failure probabilities of the 4% Rule below. The unconditional failure probability was just under 10%, but it was 18.55% when we were at the market peak. So, failure probabilities are indeed impacted by past returns. File this as another piece of evidence that the stock market isn’t exactly a Random Walk!
Why is this relevant? Well, on August 22, 2018, the market just became the longest-running bull market in history (though not everyone agrees on the definition of the longest bull market, see this article on Bloomberg). If history is any guide the retiree in this numerical example will be wise to be more cautious with the initial withdrawal rate and not just blindly apply the 4% Rule mantra. Maybe withdraw a little bit less, 3.25-3.50% to have a bit a cushion. In other words, after a potential drop in the portfolio the effective withdrawal rate might reach 4% right around the time when the S&P had a ~20% drop, which is when, historically, the 4% becomes very safe again!
I also added a small table with the fail-safe withdrawal rates in five important time intervals around stock market peaks. That’s because I usually like to see during what period the all-time fail-safe occurred. Most of the time it’s either 1929 or the 1960s, depending on the stock vs. bond. vs. cash allocation! It’s always amazing to see that the 1970s and early 80s recessions did an even worse trick on the mid-60s retirement cohorts than the ones that retired around the 1973 market peak!
Supplemental cash flows: now easier to input!
One of the biggest challenges for folks trying to use this spreadsheet used to be how to correctly enter the supplemental cash flows. I tried to make this a little bit easier and more intuitive, so I created a separate tab, appropriately named “Cash Flow Assist” to help with that. At the top, we can input the initial portfolio value and a projected inflation rate to discount the value of any non-inflation-adjusted future cash flows, e.g., corporate pensions, etc. in the orange fields below. As always, it’s best to go through a simple example:
- The portfolio is worth $3m at the beginning of retirement.
- We expect 2% inflation going forward.
- Spouse 1 expects Social Security after 25 years (=month 301) worth $2,000 per month.
- Spouse 2 expects Social Security after 26 years (=month 313) worth $800 per month.
- Spouse 1 expects a small corporate pension of $300 (not inflation-adjusted) 11 years into retirement.
- We expect $1,000 in additional monthly expenses (e.g. medical) 30 years into retirement (in today’s dollars, adjusted for inflation) and that amount rises to $2,000 after 40 years. This corresponds to the two spouses reaching a certain age where they may scale back other expenses (travel) but face an increase in medical, in-home care expenses, etc. which will case in a net increase in expenses.
How do we input all of this? At the top of the page we input the net worth and an inflation figure and leave the other orange fields empty (=$0) for now because there are no supplemental cash flows during the first year, see below:
Next, we input the corporate pension, starting after 11 years (month 133), see below. Notice that we input this in the fifth column where the non-COLA cash flows reside. So, this will be discounted to make sure it’s comparable to today’s real dollars!
Next, the Social Security payments start 25 and 26 years into retirement, see below. The benefits are inflation-adjusted so they are entered in the corresponding columns with COLA cash flows:
And, finally, the additional budget for medical expenses, care, nursing homes, etc., see below. This is now inputted as a negative cash flow!
In the “green” column on the right, the program translates the different cash flows into percentages of the initial portfolio:
Also in the same tab is the same table that showed up in the main tab but the percentages are translated into withdrawal amounts in dollars p.a.:
Fama-French Style Factors (since 1926)
I added that feature pretty early in the spring of 2017, mostly out of curiosity about how much of a difference some of the widely cited style premia such as value and size would have made. See Ken French’s site for the data and more documentation on the construction of the Fama-French factors. For example, let’s simulate the following scenarios:
- The 80% S&P500 baseline
- Keep the overall equity share at 80%, but use a 25% small caps and 25% value stocks tilt: Set the SMB and HML allocation to 25% each. Make sure you keep the overall stock allocation at 80%!
- Keep the overall equity share at 80%, but use 50% growth stocks. Set the HML allocation to -50%. Again, make sure you keep the overall stock allocation at 80%!
The small-cap plus value bias would have easily lifted the SWR to above 4%, see table below. But I’d probably not get too excited about this result. There’s no guarantee that the size and value premium will persist forever and continue to help future retirees. All this could just be backward-looking bias. Certainly, in 1926 nobody would have known about the work by Fama and French. In fact, if you had been wrong and bet on the wrong style, for example, “growth” instead of “value” you would have totally ruined your safe withdrawal rates. Fail-safe withdrawal rates are now in the low 2% range. Ouch!
Case Study: glide path simulation
In the tab “Case Study” where you can simulate one single time series of the portfolio values for a specific starting date and initial withdrawal rate, I also added a simple glidepath simulation for comparison (see Part 19 and Part 20 of the SWR Series). This is the simplest possible version with just the static glidepath going between two different equity weights in fixed steps (e.g. 0.3% per month) and investing the residual in bonds, see below:
Compared to an 80/20 static stock/bond allocation, the glidepath would have made a huge difference during the Great Depression! But glidepaths were not a panacea because, during the 1970s, bonds offered much less diversification.
Simulate CAPE-based Withdrawal Rules
I added another tab to simulate CAPE Rules with different parameters. Just as a recap, the CAPE-based rule, in its simplest form, expresses the annualized target withdrawal rate as a+b times the inverse of the Shiller CAPE (=CAEY = Shiller Earnings yield). See Part 18 for more details and why I like this approach. My preferred rule would be to set the intercept to around 1.5-1.75% and the slope to one-half. In the example below I use 1.75%/0.50. Notice that a constant percentage rule would be a special case if we set a=4% (or whatever rate you like) and b=0, i.e., withdraw 4% p.a., regardless of equity valuations.
Running out of money is no longer an issue with the CAPE-based rules. Failure comes in the form of deep and extended cuts to consumption. So, to compare how much (or how little) I like different CAPE I tend to look for 3 key stats:
- What’s the change in real, inflation-adjusted withdrawal amounts over a 30-year horizon?
- Notice that the point-to-point comparison over 30 years can deep cuts in spending, so I also like to know the lowest withdrawal amount relative to the initial withdrawal amount
- And finally, what’s the average over the 30-year window relative to the initial amount?
See below for a numerical example:
I’m interested in how the three measures would have evolved in the worst-case scenarios, so I calculate them for different time intervals (all months vs. 1926 onward) as well as the worst-case scenarios in the five different “troublemaker” retirement cohorts (Great Depression, the 1960s, early 1973, Dot-Com bust and Great Recession), see the table below. I also add the volatility of year-over-year withdrawals and some stats on the withdrawal rates implied by this CAPE rule:
A little side note: I frequently get questions and comments about the CAPE parameters just like recently when a reader wondered why I assign a weight of 0.5 on the CAEY. Shouldn’t the withdrawal rate be less volatile with a higher intercept and lower slope? Yes, but as a retiree, I’m less worried about volatility in withdrawal rates and more worried about volatility in withdrawal amounts. So if I were to set the intercept to 3% and the slope to 0.3 I’d have a more volatile stream of withdrawals, see below. And the more you reduce the slope parameter the closer you get to the good old constant percentage rule where your withdrawals become just as volatile as the portfolio itself!
I guess it’s up to you and what you feel comfortable with but I like the way the CAPE rule cushions the volatility in withdrawal amounts, see below:
OK, so much for today! I hope you enjoy the new features! Please let me know if you find bugs or like to suggest more features!
Thanks for stopping by today! Please leave your comments and suggestions below! Also, make sure you check out the other parts of the series, see here for a guide to the different parts so far!
Also notice, all the usual disclaimers apply!
Picture Credit: Pixabay
Hello Big ERN,
In running my numbers on the DIY SWR spreadsheet, I came up with a couple of questions.
1. In looking at the Cash Flow Assist tab, that shows how much we can safely withdraw each month in Column Q. I can see that the pension and eventually social security totals (Column M) are discounted as the months go by to account for inflation. Also, the withdraw rate goes up by the same amount to keep the annual cash flow at our failsafe rate.
So, many years down the line, we’re still showing the same annual consumption amount (withdrawals + cash flow), but I assume that this is because it’s in today’s dollars. All along, our withdrawals have been adjusted for inflation and SS amounts will actually increase with inflation. So, do I understand correctly that because of SS increases, the actual amount 20 years from now will be greater than our starting failsafe consumption amount, but the purchasing power will be about the same (provided our inflation estimates are reasonably close)?
2. Reducing the future real returns of stocks on the Parameters sheet seems to have no effect on our failsafe withdrawal and safe consumption amounts. Why is that?
All results are in CPI-adjusted dollars to make comparisons easier.
And keep in mind that this is a safe consumption rate, because withdrawals will be reduced 1-for-1 when you draw Social Security and pensions.
2: the future “expected return” figures are merely used to forward-fill some of the return for cohorts that would otherwise fall out of the sample. Using higher returns here may not impact the fail-safe because if the fail-safe is based on 1929, no future expected return data are used for that number.
#2 One thing you could do is put in very low future returns such as near 0% equity returns and you’ll probably start triggering 2000 as the worst year to retire instead of 1929/1968.
Good point! But it depends on the horizon. Looks like the 30-year retirement starting in 2000 will likely be safe considering how well the market recovered recently. But for longer horizons, yeah, why not enter negative expected returns for the medium-term and then only 4-5% long-term for stocks, and 1% fore bonds Should do the trick. 🙂
Love the sheet and thank you for doing it. Why do cash flows from other retirement income such as SSI impact my safe withdrawl rate?
Because future cash flows can lower your required withdrawals. Pleas see Parts 4 and 17,
If you look closely then you will see that the idea is that a larger withdrawal rate is supported early on because the amount you withdraw later is reduced by the guaranteed income streams that are external to your investments. In other words your income is evened out giving a more sensible profile.
I find it more comprehensible if I look at the Safe Consumption Amounts to target different Failure Rates on the Cash Flow Assist tab where the absolute amounts are listed rather than the percentage as it is more natural to think of the total income received as being a mixture of drawdown and other income streams than to think of the percentage as being a mixture.
Sorry, UK-centric language used. When I say “drawdown” I don’t mean fall in the stock market I mean “withdrawal from your pot of wealth”.
Noted! I knew what you meant! 🙂
Good explanation! Thanks! 🙂
Thanks for all your great work on this. I am particularly interested in using your CAPE-based strategy as I start to plan out my drawdown plans for my retirement.
One thing I noticed (assuming I’m using it correctly) was that the data entered in the Cash-Flow Assist tab, like adding in income from Social Security, does not impact the calculation of SWR when using the CAPE-based Rule, like it does on your Parameters & Main Results tab. Is this something you are planning to add to the CAPE-based Rule tab? I think it would be tremendously useful.
Keep up the great work!
Correct.
The cash flows are not (yet) considered in the CAPE sheet. It’s likely too complicated to implement into a simple spreadsheet and would probably take a Matlab code to loop over the different starting dates. 🙂
Thanks Big ERN! Fair enough. I think I’ve found a way to factor in using the tables you gave in the case study of Mr. Corporate (in the Adjustments for Social Security Section). I think I can just look up my own specific scenario in the tables (80% stocks, 40y retirement length, 15 years to collect) and get around .339% and multiply it with the percentage benefit I get (yearly benefit / portfolio value). Basically just do it as a lookup as you did in that example.
The other factor I’m not sure how to incorporate is fees. I’m paying 0.81% to a financial advisor to manage our portfolio. I know, its higher than generally recommended, but it is an advisor we trust and we have some complexity in our portfolio that makes me feel better knowing a professional is on the case. Not sure how to factor that into the CAPE formula to generate a percentage impact based on fees. Any hints on how to adapt the formula to take into account fees so the resulting percentage is net of fees? I don’t think it is a straight subtraction; at least that is not the relationship when I play with the Expense Ratio in the main tab.
Again thanks so much for your work. I’m learning a ton :).
I have a parameter for the expense ratio in the Google Sheet simulations.
In the simple CAPE formula you’d need to subtract the mgmt fee one-for-one.
Hi Michael, I’m hoping you still have a subscription to this comment and thus will see my message. Based on what you’ve written here, I’m suspecting our scenarios are pretty similar from a modeling perspective. If you’re up for it I would love to exchange some notes. You can reply here or reach me at jason at rainsong dot net
Karsten, thanks again for all the great work you have done! A quick tech question on the Google spreadsheet – how do you model withdrawals with COLA+X% adjustments? I cannot find an obvious input for that, and, as any IB analyst knows, it is better to ask the model author then try to decompile it independently 🙂
I presume I should use the scaling factor on the cash flow assist sheet and set it to (1+X%/12)^(Ty-T0) for month y. Correct?
Yes!
Tab: “Cash Flow Assist”
Column P. If you grow the values at a rate of, say 1% annually (e.g. P12 = P11*1.01^(1/12)) and then copy down that formula you are now growing the real withdrawals at that rate.
I’m confused, because when I increase the Portfolio Value Today value the failure rates go UP, not down. Leaving all the other numbers the same, if Portfolio Value Today is $3M to target a 0% failure rate SWR is 3.27%. If I change the Portfolio Value Today to $1M, to target a 0% failure rate SWR is 3.51%. So I can withdraw a higher percentage safely with a lower starting portfolio value? Either there’s a bug or I still really don’t get it after reading this whole page.
There is no bug.
If you have positive cash flows built in tab “Cash Flow Assist” then the added benefit of those cash flows is spread over a larger initial net worth and the SWR has to fall.
If you set those cash flows to zero, then the SWR in % should be independent of the initial net worth.
It took a couple of days, but I get it now. Thanks for helping me understand!
I am not good at math. I stared at this for so long and I gave up. Can you please explain like you explain to a 5 year old. How could cash flow from SS affect what the SWR failure rate is, when the portfolio value is changed from 5 mil to 6 mil. I am not understanding this. @Myth seems to have to understood it!
The value of SS as % of the net worth changes. Thus the SWR as % of the net worth changes.
I have a remedial question about the input parameters. If I select a stock/bond allocation of 80/20 I get a higher SWR than if I select 100/0. If I’m planning on getting to 100% equities via a glidepath, should I assume the input parameter should be 80/20 and I use the corresponding SWR for that as I plan for retirement?
You can currently only simulate fixed asset weights for the entire universe.
You can only study case studies (see the corresponding tab) and simulate a single cohort with a GP.
Q: are the calculations of amount below S&P high in real or nominal dollars?
Hoping it’s the former (to be more meaningful during the high-inflation 1960s and 1970s) but guessing it might be the latter because that’s simpler to calculate. And nominal probably doesn’t include reinvested dividends either.
That’s conditional on real drawdown of the total return index. As you stated, it’s easier to calculate. It’s also the only sensible thing to do, otherwise numbers wouldn’t be comparable across different decades with different inflation regimes.
Officer, officer! I need to report a problem!
Seriously, with the current version of the Google sheet (whether downloaded to excel or just using a copy in Google), the numbers no longer match those of the version from last year.
In particular, using the new sheet, with 65% stocks, 35% bonds, 360 months, 0% final value, its shows a failsafe WR of 3.94%
Last year’s version (which I downloaded in the spring) shows the same 3.83% that your Part 20 post shows.
The new version shows consistently higher SWRs than the old one did.
With the initial values that come with the latest sheet (75/25, 720 months, 25% final value target) it shows a SWR of 3.37%. Plugging those same parameters into last year’s version shows 3.20%
I checked and rechecked and am pretty positive that it’s not user error.
While it would be good news for all retirees if the new numbers are correct, it seems unlikely the ERN we all know and love had a) been publishing incorrect data all these years, and b) would without announcing it slip the newer numbers into a spreadsheet 🙂
Verify that the supplemental cash flows are the same in the two sheets.
Thank you! Problem solved. The latest version has many entries in the cash flow section. I assumed it was blank like the prior.
Clearing those out does indeed fix the problem!
Apologies for the false alarm
That would be another explanation! 🙂
That’s possible because if your fail-safe is 1968 and you have a 60-year horizon, you’re filling new data with very strong equity returns over the last few years. It should make a noticeable difference in the SWR.
Thanks for leaving the comments on this older thread open. I think the SWR Toolbox is possibly the most valuable resource on the site. I am writing up our personal financial plan (for my spouse) and started really looking at how I was interpreting the “SWR” toolbox output. Browsing the comments above, it is important to note that what the tool is outputting is a Safe Consumption Rate (SCR), not a SWR! I recommend you change the terminology on the workbook as I had been thinking of the output as an SWR, which it is not, since the value is modified by supplemental cash flows.
I’ve added the following explanation to my copy of the workbook:
“SWR has been changed to SCR throughout this workbook. The reason is that the sheet includes entry of supplemental cash flows, such as social security and pensions and it modifies the rate based on those. Obviously, supplemental cash flows do not affect the depletion of a portfolio at a given withdrawal rate. The SCR reduces to a SWR only when all supplemental cash flows are zero (no values entered).”
Added another disclaimer and took out most (all?) references to SWR. Good point! Thanks for your comment!
True. I changed the verbeage to “initial consumption rates” and “Safe Consumption Amounts”.in some of the places.
I sill carry a few referedfens to SWR here and there and assume that the educated reader will understand the subtle difference.
But I will look into ways to make this more obvious. 🙂
Thanks!
First, thank you so much for the spreadsheet. This gives me a quick way of exploring ‘maximum’ spending instead of comparing expenses versus income.
I’ve got an embarrassingly simple, yet also extremely important question: Does the SWR refer to withdrawal rates from the net worth portfolio ONLY, exclusive of supplemental income? I’m getting confused by the difference between SWR and SCR that pervious question addresses.
Another way of phrasing my question is:Does the Safe Consumption Amounts (in $) on the ‘Cash Flow Assist’ tab refer to TOTAL consumption (portfolio withdrawals + supplemental income) or the consumption of the portfolio only?
And then how does this feed into the WR listed in the ‘Parameters and Main Results Tab’? In my example, I’ve got a 6.5% safe WR for all cases with 0.0% failures for any CAPE ratio. Is that 6.5% of my initial portfolio value, or is it really consumption rate of 6.5% of initial portfolio value, meaning the consumption rate is the sum of supplemental income+’some number lower than 6.5% of initial portfolio value’?
I’m just trying to figure out if I can safely spend 6.5% of my initial portfolio value, IN ADDITION to the supplemental income, or if that 6.5% INCLUDES my supplemental income.
Good question.
The answer to is “Does the Safe Consumption Amounts (in $) on the ‘Cash Flow Assist’ tab refer to TOTAL consumption (portfolio withdrawals + supplemental income) ” —> YES!
“or the consumption of the portfolio only” —-> NO!
In other words, if you calculate a safe consumption amount of $5,000 one month, but you have +$1,000 in supplemental cash flow, you withdraw only the net $4,000 from the portfolio.
I’m wanting to make sure I understand the right way to enter pensions in the cash flow assist tab. In my case I will be receiving a federal pension 20ish years after separating based on salary history up until separation. The amount will be COLA driven BUT not until payouts start. It looks like I need to decrement the pension amount for the 20ish years of inflation in between and input in column B or C if I’m tracking how this is all calculated.
Then you could do one of the tow things:
discount this nominlal value by 20 years of inflation and enter this in the real column
Or enter this nominal value 20 years into the future in the nominal column, but apply inflation growth every year.
Just curious if the returns are updated yearly on this workbook? Just trying to determine how accurate it is after the past 24 months of dismal returns/inflation…or maybe these blips in time shouldnt have any real affect on my results…not sure
I update the returns every 3-4 months. Currently up o 8/31/2022.
Part of the job is updating in my Python code, so it’s already automated, but there’s still some manual work involved.
The recent vol shouldn’t materially impact the simulation results.
Thanks for the reply. I know I have zero danger of being accused of being hyperbolic when I say this post is in the top 2-3 most important posts on FIRE in the blogosphere…no lie
Thanks for your kind words! Makes the effort worthwhile!
Love the EarlyRetirementNow SWR Toolbox v2.0 worksheet! I was wondering if you had a spreadsheet where one can track their monthly expenses and withdrawal amounts as their retirement progresses. I suppose you can redo the numbers on the spreadsheet and take subtract one from the Retirement Horizon row each month, but there has to be a better way? Would be good to see what my historic withdrawals rates were.
I don’t offer that. I can tell you how much you can withdraw. Pre-tax.
How much you spend and how much you owe in taxes is something everyone has to figure out individually.
I created an additional column, next to fail safe withdraw amounts, just subtract fail safe amount from portfolio value (say 1M-(36902/12)) for every month on a 360m time period. I was looking for a zero balance (or whatever i choose as bequest), but the numbers don’t reflect that. No cash flows whatsoever.
For example, start at 1M, 2% inflation, 360m period with a 80/20 portfolio..cape model 2…i get 3.69% or 3075/m withdraw. At month 360, the balance is -107054…meaning it ran our of money long ago (actually month 325)…i am not sure my analysis is correct. Any thoughts? I am just trying to track total account balance on a monthly basis SWR…..the idea being, if i have excess in the portfolio when markets are booming, i get to spend more.
I have tested this with all cash asset, no growth ever, no inflation ever and still not able to get to 0 at the end of month 360.
1M-36902/12*360 is clearly not the way to calculate the final balance. So, it’s no surprise that this will give you a false answer.
HI ERN,
Nice work on the new CAPE – adjusted features. I’ve used the toolbox for a couple of years now and with the current volatility and corrections the utility of a CAPE based approach to retirement modelling has become very apparent to me. The withdrawal smoothing it embodies is priceless.
On the spreadsheet main results page a heads up about the code in the panels that refer to the SCR-time-series sheet. The formulae refer up to row 1746 while the data in the stock bond sheet goes up to row 1825 (Aug 2022). I updated this in my own copy but not sure I ougfht to have done so.
BTW I also use a global CAPE based on this article from Monevator. https://monevator.com/cape-ratio-by-country/
regards
IAn
I calculate the SCR only up to 12/2015 retirement dates. There’s no need to go further because we have not much return data to justify 30+ years of retirement horizon for any of the later retirement cohorts.
So, this is not a mistake. It’s done intentionally.
Yeah, thanks for the CAPE link. I like the Barclays platform
When rebalancing, sometimes you’ll sell Treasury notes to buy stocks, right? How does the sheet figure out the sale price of those notes? Does it assume that they can always be sold for face value?
No. You can’t sell bonds at the face value. You sell at the actual market value of the 10-y BM bond.
Hi Ern, I’m fairly new here but have been voraciously reading for a couple days and am ready to take a swing at modeling my situation in your spreadsheet. I (like you) have about 15% of our money in real-estate (outside our residence), so I was a little surprised to see the portfolio section of the spreadsheet doesn’t contain a cell for RE allocation. Do you assume RE will be lumped in with equities? There is a high correlation between stocks and RE but it isn’t 100% which is part of the reason I find RE attractive, so it seems like it should be modeled on it’s own.
No, RE is not like equities in the context of the SW calculations. I’d use the method described in Part 36 of the series: Consider only the financial wealth in the portfolio but model the RE as supplemental cash flows.
See here: https://earlyretirementnow.com/2020/02/26/real-estate-investments-and-safe-withdrawal-math-swr-series-part-36/
Thanks for the pointer. I had read part 36 but I guess had forgotten that you published a filled in spreadsheet showing RE modeled in cash flow assist. After reviewing what you provided there I’m still somewhat struggling. I (like you again) don’t own individual properties. We have investments in 5 different multi-family value-add syndications and plan to add more next next year. As I’m sure you know, these investments are about both cashflow and appreciation which should be significantly above inflation, with realization of those capital gains coming every 3-5 years.
I think what I can do is hack the cash flow assist mechanism with assumed purchase and sale transactions happening every year that assume a rate of capital gains. That will give me a rough estimate, but it would be much better if it had the same level of treatment as stocks, bonds, and gold by bringing in historical returns for RE into account.
Also to get to the level of accuracy you preach (and I strive for) we need to account for the effects of depreciation from these investments.
Since you are also invested in syndications, you must be modeling these investments into your personal spreadsheet. Is it possible to dummy the numbers and provide a copy so I can see how you’ve handled them in your modeling?
We don’t have a return series for RE that’s long enough. If you find anything it will be annual. Even if you find monthly returns for the overall RE asset class, they are likely useless because a) your syndication will use leverage, b) your syndication will have a lot of idiosyncratic risk around the national index, and c) you will not be able to do any monthly portfolio rebalance in practice.
So, it’s the intellectually honest way is to simply assume a baseline RE return. If your RE portion is small enough you can get a away with this. Don’t do this if you have 90% RE in your portfolio.
So, I would assume some rental dividend yield for x number of years and some appreciation of the principal to be paid back at the end. Also, all returns are already net of depreciation.
I think I may have a bug report. I don’t *think* there’s any way the SWR for CAPE>20 can be higher than CAPE<=20 but that's what the spreadsheet is showing me for fail safe scenarios. See https://drive.google.com/file/d/1qY2IZCj87b8MMF4jvV0Kz1uluJoPrz0C/view?usp=sharing
Not true. There can always be some outliers just below a CAPE of 20 that look even worse than the 1929 episode. Depends a lot on your supplemental cash flows!
A few questions for you regarding the “Failsafe by Equity Drawdown” table in the spreadsheet.
1. I’ll put my standard caveat on this one.. I’ve tried to self-serve so my apologies if this is covered somewhere and I missed it. I’m assuming the Drawdown column is the percentage that the S&P is below it’s all-time high?
2. What is the “Relative” column? Amount of consumption implied relative to if the S&P was at high?
3. Again, if my assumption is correct about this table, I’m a little confused about what it implies about the predictive power of CAPE vs. draw down. So many of your posts have used CAPE to show that equities are not a random walk which makes a good argument for using it as the key indicator. Throwing in drawdown seems like a bit of a curveball. I do remember a post about equities tendency to revert to mean but it leaves me wondering which one is more predictive and thus should I use when determining SCR. Perhaps there is room for you to create a new indicator which combines the two?
So appreciate all the work you’ve done and making it available for all of us to use. I love your data driven approach which is part of the reason for my queries in #3.
1: Yes!
2: Yes! For example, after a 50% DD, your failsafe is 6.07, which is 81.45% higher than the 3.35 baseline. So 181.45 elative to 100 in the baseline.
3: The CAPE and equity drawdown are related. If equities are below their ATH the CAPE is also lower than before. The difference is that the CAPE is an absolute valuation indicator and the DD is relative to the previous ATH. I have not thought about how to combine the two into one single indicator. They both have pros and cons. That’s why I include them both in the table: once for CAPE <20 vs. >20 and once for the drawdowns.
Hi, updated to the most recent version of the sheet yesterday.
2 insights:
1. Noticed that non US stocks were added so I updated my sheet to divide the stock allocation equally between US and non US, as it is also the my stock allocation in practice.
What I’ve noticed is that (at least in my use case) it improved the failsafe SCR. Specifically it happens because 12/1968, which was the worse retirement date, is improved by mixing in the non US stocks.
2. In addition to the non US stocks, I see that 30 years bond was added. In addition to existing asset classes (ignoring the custom series) it sums up to 6 portfolio possibilities. When there were 3, it was relatively easy to find the combination that maximizes the failsafe SCR by trial and error, but with 6, it is quite challenging.
An axillary sheet section that will help do this automatically, assuming it’s possible, will be very useful.
1: Agree with the sentiment of the international stock. It’s not so much that international stocks did better during the bear markets in 1974 and 1982 (everything tanked!!!), but international stocks did well during the subsequent recoveries.
2: Google Sheets has an optimizer, but it’s not very good. MS Excel has a pretty decent one, so you could download the sheet as xlsx and then use the Excel optimizer. But I also warn against overfitting in past recessions. That may not be the best way forward.
Thank you, I agree about having to be cautious with overfitting.
But my curiosity led me to try the solver in excel and the result that maximizes the failsafe SCR for a 720 month retirement, 0 final value, no supplemental cash flow, and leaving gold outside of the game, is:
44% US stocks
32% non US stocks
7% 10y bonds
12% 30y bonds
5% cash
this gave a 3.23% failsafe SCR
Thanks! Intersting!
No allocation to Gold? Or did you not include that in the optimization?
I decided not to include gold in the optimization.
Also didn’t mention before but expense ratio left with the pre-set value of 0.05%.
What I also noticed is that the solver result is not deterministic, after running it several more times I was able to find a better allocation that improved the SCR to 3.29% (09/1929) with:
5% US stocks
68%: non US stocks
19% 10y bonds
1%: 30y bonds
7% cash
did we mention overfitting? 🙂
Yup, it runs the risk of overfitting.
And I would expect that including gold would make this even better.
And you might find a local max, not always the global max. It’s a difficult problem to solve.
ERN, not sure why it’s taken me this long to come to the realization that I don’t understand the purpose of the “Project Future Real Returns” section of the Parameters sheet. This seems like such a basic question that I’m concerned I’m asking a dumb question. I did go back and reread the post for the original release of the sheet as well as all the text above in this post. I see mention of filling it in but don’t see any mention of how it is used. By using my very advanced Excel skillz (tongue in cheek) I was able to discover that there is projections into the future where this data is used in the Stock/Bond Returns sheet. So it appears that it’s used to prepare something like the Actuarial view that you covered in Part 33. Is this data used in any of the reports?
If you retired in 2000 or 2008 for example, there is not enough real data to cover 30 years or more of retirement. So he added this section in order to simulate future returns. This way we can check with the tool also how retirement in such dates played out under different values of future returns.
Nice explanation! Thanks for sharing! 🙂
Correct. This is not projecting your personal returns going forward in years 1-10 of your retirement. It’s for the following thought experiment: If you had retired in 1973, and you apply those returns to your current retirement assumptions, you have “only” 49 or 50 years worth of returns. If you like to simulate a 60-year retirement, you need another 9-10 years of returns to simulate a 60y retirement starting in 1973. That’s where those “Projected” returns are used.
I need a few more details so I can make sure I completely understand the results I’m getting from the sheet. Let me play back what I read above to make sure I understand correctly. The future returns created in “StockBond Returns” are for use in simulations where the Retirement Horizon extends past dates where you have empirical data. If that is correct, here are some follow up questions.
1. Based on the importance of the first 10 years of returns on sequence risk. I had made an assumption that “Stocks for next 10Y” and then “Stocks after that” meant that “Stocks for the next 10Y” would be used for the first 10 years of retirement in the simulation, in order to give us a mechanism to play with how returns in the first 10y impact the overall plan. But, if I understand what is happening, those 10y could fall anywhere in the simulation timeline. I guess now what I’m seeing is that the thought is more that we have a CAEY that we can use to make an educated guess at the next 10y and then after that we just have to assume long term averages. Is that correct?
2. What is the last date that you start a simulation on? I’d like to get a sense of how many of the simulations for my 40y horizon encompass the non-empirical data.
3. What do you think about having a column in the results that shows my SCR only using empirical data? So for me with an 40y horizon that would be a column labeled “Before 1983”. I realize would exclude a bunch of interesting data from the high inflation and interest rates of the 80s as well as the crash in 87.
Thanks again for your continued work here. I did try to self-serve on some of this by diving into how the “StockBond Returns” sheet is constructed but quickly realized that fully reverse engineering it was going to take lots of time and brain power.
Jason
Correct.
1: The “projected returns” are tagged onto the end of the simulations. Never to the beginning. Never “anywhere” (i.e., in the middle), but only attached to the end.
2: The last date of a simulation start is 2015. I would use those with a grain of salt. But we can certainly get a good sense of the 2000 cohort’s success. The final return data I have right now is Dec 2022.
3: I could do that but I won’t. Because it sends the wrong signal that somehow a cohort with only 479 months of actual return data falls off a cliff and should be wholly ignored in a 40y simulation. If we fully understand SoRR we appreciate that even the final ten years of returns of a 50-year retirement have a negligible impact on the SWR. So, if people want to come up with their way of hacking the sheet, go ahead and add a column that calculates probabilities only up to the 1983 cohort. But I’m not going to offer this as a built-in feature.
Also, notice that the failsafe WRs are usually caused by the 1929 and 1965 cohorts, so this should have zero impact on the 0% failure WR target anyway.
Please, please, please don’t take any of this as me trying to poke holes. I absolutely love what you’ve done and have started to incorporate it deeply in our planning, so it is my nature to want to understand it very deeply.
1: I realize “anywhere” was a very bad word choice. I was referencing the “Stocks for next 10Y” which as I now understand it could indeed end up towards the beginning, in the middle, or at the very end, depending on where the simulation starts.
2: Here’s my concern.. I have a tendency to overanalyze so let me know if that’s what I’m doing. 🙂 With simulations starting in the 2000s and a 40y horizon, many results will be based on over 50% synthetic data. Taking on 10-20y at the end seems fine given what you’ve shown in this series about being less sensitive to sequence risk in later years. But, especially during the first 10y synthetic data (with positive returns, even if below long term market averages) seems to not jive well with all the work you’ve done to determine SCR based on simulations rather than actuarial computation. The count of those results goes in the denominator of the failure probabilities so is having some impact.
3: Understood. I think I may just hack my copy of the sheet to make sure I’m not using simulations where the synthetic data falls in the first 10y. That seems like it is easy enough to do and inline with what I’ve leaned here as mentioned above. I’m sure it won’t make any meaningful difference in the results, especially since it only eliminates three years of simulations, but will be fun for me to try as a learning exercise.
1: good, I’m glad we’re on the same page.
2: If you start with the 8/2000 cohort, and a 40y horizon you now have roughly 22.5 years of actual data and 17.5 years of “made up” data at the end for the simulations. 22.5/40=56.25%. Also note that that as I’ve expressed in the past, the first 1/2 of your simulation window is responsible for about 80% of your sequence risk. So, it’s not a crazy assumption to estimate the success or failure of the 8/2000 cohort. But I’m not going to yell at you if you want to consider only the results up tp 1983.
For failsafe results it will have zero impact. For probabilities it will likely raise the failure probabilities (at least in the very low range) because you’re including all the failures but fewer observations in the denominator. You will likely come up with more conservative recommendations then.
3: Good!
Hi Karsten – I believe I found a bug with your latest spreadsheet – 2/1 changelog date. On the CASH FLOW tab when I change the Inflation (p.a.) field the resulting safe cash amount is reversed / opposite direction. Higher interest rate results in a higher safe amount and a lower rate reduces the safe amount. Thanks
Why would that be a bug?
In the current setting, 2% inflation -> $100,402 safe consumption
3% inflation -> $100,113 safe consumption
That’s the way it should be: higher inflation means my future nominal supplemental income from a corporate pension is discounted more, so I feel a little bit poorer and consume less in real terms.
Not sure but I am seeing the issue when populating that tab with our details. Appears to occur when I have too many values in COL K. Linked is the example with that only change to that tab. Specifically when you remove the values in cells K66:K166 (-3000) the issue goes away. Thanks
https://docs.google.com/spreadsheets/d/1ywmWKCXrtsdsGdX0aWnnYsWckkvkYvu2obfUJujat30/edit?usp=sharing
I could take a look, but the sheet is still locked and not accessible, even if I have the link. Maybe make a copy, click “share” and allow everybody with the link to edit.
Thanks for taking a look. Just made the change and the file is accessible.
You have a net negative nominal cash flow because of the large mortgage payments. So, with a higher inflation rate you deflate more of the negative impact and you increase the safe consumption amount. Seems legit.
Ahhh ok. Thanks for reviewing and the explanation.
Hi ERN, first thank you for all your knowledge, insight and effort you put into your blog. I’ve been working with your latest SWR 2.0 toolbox. I keep my portfolio pretty simple and for my bond allocation I use AGG, total US bond market ETF. My question, what portfolio parameter does this best it? If I have to do a custom series, where would I find the data to enter into the “Custom Series” under the Stock/Bond Return tab?
Good question.
For ETFs/funds not covered here, I’d usually run a regression factor model of the fund returns on the relevant underlying index returns. The AGG fund appears to behave like a mix of 9% S&P500, 61% 10y BM Bond and 31% Cash (money market). This mix would have a 0.90 correlation and 0.95 R^2 (measure of fit) with the AGG ETF.
I’m planning to release some guidance on this issue n a future post. Thanks for the reminder!
Where do you get you inflation data? Do you use chained CPI or unchained?
Traditional CPI: https://fred.stlouisfed.org/series/CPIAUCSL
I’ve been playing around with the SWR spreadsheet by adding different Social Security benefits-claim-ages for me and my husband, trying to determine if it would be optimal to claim early, full, delayed, or split (one claiming earlier than the other). My assumption is that if we both claimed at 70 (i.e. delayed), and with the assumption we’ll live to 100, that we’d maximize our overall income.
However, plugging different numbers into the SWR spreadsheet, varying by age of claim, and using a $ amount that is 75% of the SSA estimate (for early/full/delayed), what I found was surprising. Particularly at a 0.00% failure rate, the highest annual withdrawal amount is associated with both of us retiring early at 62, not 70 as I expected (see below). Can someone help me understand this outcome? Below are some extracted figures based on the different age variables.
Spouse 1-Age 62 / Spouse 2-Age 62
Failure Rates All Since 1926 CAPE20
0.00% $76,905 $76,905 $83,007 $76,905
1.00% $82,764 $81,794 $86,064 $79,079
2.00% $85,095 $83,989 $88,607 $81,418
5.00% $88,569 $88,542 $92,905 $83,333
Spouse 1-Age 62 / Spouse 2-Age 70
Failure Rates All Since 1926 CAPE20
0.00% $76,118 $76,118 $81,865 $76,118
1.00% $83,102 $82,941 $84,983 $79,869
2.00% $84,766 $84,829 $88,827 $81,807
5.00% $87,707 $87,258 $92,549 $83,894
Spouse 1-67 / Spouse 2-67
Failure Rates All Since 1926 CAPE20
0.00% $76,678 $76,678 $81,343 $76,678
1.00% $83,052 $82,893 $84,534 $79,744
2.00% $84,351 $84,386 $87,871 $81,911
5.00% $87,711 $87,105 $92,248 $83,752
Spouse 1-67 / Spouse 2-Age 70
Failure Rates All Since 1926 CAPE20
0.00% $76,007 $76,007 $81,191 $76,007
1.00% $83,363 $83,401 $84,720 $80,379
2.00% $84,389 $84,412 $88,449 $81,683
5.00% $87,535 $87,016 $92,246 $84,115
Spouse 1-Age 70 / Spouse 2-Age 70
Failure Rates All Since 1926 CAPE20
0.00% $75,593 $75,593 $80,931 $75,593
1.00% $83,413 $83,613 $84,970 $80,768
2.00% $84,354 $84,393 $88,666 $81,880
5.00% $87,424 $86,888 $92,211 $84,070
Depends on your parameters. It’s possible that the early claiming hedges against Sequence Risk near-term.
Maybe also calculate the fail-safe rate conditional on the market having dropped 15-20% already.
Good time of day. I didn’t catch – in the tab of the CARE-based Rule, do all the values of SWR (capital preservation) after the first retirement period require adjustments for inflation or not? Thanks
All amounts, unless otherwise noted, are always in CPI-adjusted real dollars.
I am a bit confused by the retirement horizon. When I change it from 720 months to 480 months, it drops the safe withdrawal rate down significantly. Why?
Love this tool but having a problem with one thing. When I change the Portfolio Today amount (tab 2, cell E5) the RW changes in the opposite direction that I would think. For example, if I enter $500,000 my WR is 12.25 but if I enter $1,000,000 the WR drops to 7.75%. Shouldn’t my WR get bigger as my net worth goes up? The more money I have the more I can withdraw, right? Not sure what I am doing wrong. Or if I am not reading it correctly.
That’s the way it’s supposed to be. If you have supplemental positive cash flows, you lower the rate because the positive flows are spread over a larger principal.
I love this spreadsheet. I’ve been using it for a while and I still find myself learning new things all of the time about how to use it and how it works. Thanks for all the work you have put into this and sharing your knowledge with the community.
I have a couple questions:
1) I’m not sure how to interpret the SCR results with negative cash flow on the cash flow assist tab.
My simple example: $1M portfolio, 80/20 stock/bonds, 30 year horizon, $0 final value target. With no supplemental cash flow, the safe consumption for 0% failure rate is ~$36k/yr. If I add an inflation adjusted -$1000/month expense for 30 years, the SCR in the table goes down to $24k/yr. However the failsafe withdrawal amount (Column T on the Cash Flow Assist tab) remains the same.
Regardless of the additional monthly expense I think I can still “consume” the same amount of my portfolio, but now $12k/year of that consumption goes towards the $1k expense and I have just $24k available for “other” expenses. Is the result in the table is showing me how much of my portfolio I can consume *after* I’ve already consumed enough to pay for this negative cash flow item?
How do I interpret Column T (failsafe withdrawal amount)? It shows I can withdrawal $3k each month, but the table shows $2k/month. So maybe I can interpret Column T as saying I can pull $3k out of my portfolio, and the table is telling me how much of that I have left for “other” spending?
2) When I first started looking at this spreadsheet, the portfolio parameters were pretty simple: stocks, bonds, cash. Now “stocks” are more specifically S&P 500 US Large Cap, “bonds” are broken up into 10y and 30y US treasury, and “cash” is based on 3m T-bills. My portfolio mostly consists of index funds spread out across a few accounts. Do you have recommendations for how a non-sophisticated investor like myself 🙂 can best map funds into those buckets.
1: That’s the way it should be. Imagine you start with 0 supplemental flows. You can withdraw $36k a year or $3k a month. If you now make $1000 of your budget a mandatory expense (e.g., housing or taxes) and enter them as a -1000 supplemental cash flow, then you can now withdraw the same as before. But $2000 is your consumption, and 1000 is the negative supplemental cash flow. You can consume 2000 for discretionary purposes. The sheet doesn’t “know” what’s the purpose of the $1000 negative flow. You as the owner of the sheet have to make that determination and interpret the results correctly.
2: Calculate the overall allocation. Then determine how much US large cap, cash 10Y and 30Y you enter into the parameters sheet.
Hi Karsten, Love the spreadsheet. Thank you very much for this wonderful tool. I’m a couple years from my retirement and getting a lot of use out of this. I’ve been searching all around and have been unsuccessful in finding information pertaining to modeling a portfolio that uses a total bond market index fund that tracks the Bloomberg U.S. Aggregate Bond Index. That is about 45% of my portfolio and I’m curious if there is a way for me to factor that in versus using 10-year Treasury & 30-year Treasury. Any feedback would be much appreciated. Joe Porten
Great question. Since I can’t post dozens of different investing style returns, I stuck to the most fundamental ones and then argue that all other styles can be replicated. For example, over the last 10 years, the iShares fund AGG would have been adequately replicated with a portfolio of 8.6% S&P 500, 56.2% 10Y Benchmark Bonds and 35.2% short-term (cash). If you distribute those 45% into the buckets in the SWR sheet accordingly, you should be good to go.
That is an outstanding (and easy to execute) response and is exactly what I will do. Thanks for the guidance. Take care.
Thanks for the amazing work. You mentioned Bond ETFs and other ETFs above – like AGG. Is there an “easy” way to take my porfolio tickers and figure out which Parameters they map to? I have a portfolio primarily composed of ETFs (from VTI, VBK, EEM to bond ETFs like BND, BNDX, etc.) and I’m not sure the most efficient way to map them to the Parameter categories. Thank you!
I might include a table in the future with a larger list of ETFs. For now, you can run a factor model regression, i.e., subtract the cash return from all ETFs and all factors. Then run an OLS regression. The cash weight is one minus the sum of the betas. That’s how I generated the AGG betas.
Thanks. I will admit that I don’t know how to do what you’ve suggested. Any tips? Or can you give an example with, say, BND or BNDX as bond ETFs?
BND and AGG are essentially the same. You can use the same trick as I suggested for the AGG ETF.
I don’t know if this is the question you’re asking, exactly, but if you go to Morningstar.com (or probably lots of other sites), type in the ticker symbol, and then go to the “portfolio” link, it will tell you what percentage of the fund’s holdings are U.S., International, large-cap, small-cap, etc., including the amount of the fund’s assets held in cash. You can then enter this information into a spreadsheet for all your ETFs . It’ll take a few minutes to set up initially, but then you’d just need to update the spreadsheet for the new values every 6 months or so (not sure how often they update the data online.)
This trick may work in some instances but not here. Go check out what’s in AGG or BND. It doesn’t help you in determining how much equity and how much US 10Y treasury exposure you should use. For example: corporates bonds have credit risk and macro risk, so they are properly approximated by a mix of 10y Treasury bonds (duration risk) plus a little bit of US stocks (to mimic credit risk). But the exact exposures must come out of a statistical model. There is Morningstar sector weight that can tell you that.
My copy of the SWR Toolbox is freezing every time I open it. It takes a few minutes to start working on the main results tab. Then it freezes again when I try to click into the cash flow assist tab. It’s been doing this for the last week or so. I tried making a fresh copy. Same problem. All of my other Google Sheets are working fine. Any idea what could be causing this?
Never heard of that issue, sorry.
The sheet has become quite large and a lot of calculations are involved. So, maybe you’re reaching the limits of your computer’s power.
Thanks for the quick reply. I’ll continue to troubleshoot. Brand new laptop so hopefully I’m not tapping out the power already. 🙂
I’m having the same problem. I’ve tried an Intel iMac and and M1 MacBook Pro today and the SWR sheet is hanging after opening. Activity Monitor shows CPU jumping up to 200% immediately when I open the Google sheet and back down again when I close it. It is also hanging when I open an older copy of the SWR sheet (from Jan this year) so isn’t just related to the latest version. I’ve tried clearing the cache and disabling blockers but no joy.
Maybe try to download as xlsx file and see if the problem persists. If so, there’s an option in Excel to perform calculations only manually (after pressing F9). Go to Options -> Formulas -> Calculation options. Set to “Manual”
Hi Big ERN. Posted the following in the new forum, but maybe that’s not working or you’re not using it? So thought I would try here:
3 questions on the Cash Flow Assist tab:
1. Do you recommend entering everything before-tax, assuming that part of the “consumption” amount is used to pay taxes due?
2. Column T seems to exclude supplemental flows and is therefore what we can safely withdrawal from the portfolio itself. Meanwhile cell V15 seems to include supplemental flows and is therefore what we would be safe to consume “all-in”. Correct?
3. Column T annualized in some years differs from V15 due to variable ongoing cash flows, i.e. in some years it is less than the all-in # due to positive flows (supplemental income etc). But in other years it may exceed the “safe all-in” due to things like conservative medical expense assumptions. But per this tool, we are still “safe” to consume the higher column T numbers in those certain years, since it’s baked into the overall blended safe consumption amount and overall SWR. Is that correct?
Hi – I had some similar questions as JT1717.
1. Are the Cash Flow Assist tab columns E & J (pension and SS in my case) before tax or after?
2. Is Cash Flow Assist tab cell V15 in today’s dollars and will increase in the next few years as I get closer to retirement and then through retirement?
3. It looks like in Cash Flow Assist tab V15 the safe consumption amount doesn’t change if I change the % in Parameters & Main Results cells B8-B14 (asset allocation) or B21-B22 (future stock performance). Is that correct? And why wouldn’t higher or lower expected returns not change the safe consumption $?
Thanks for this amazing tool. It’s huge, now just trying to understand it all.
1: before-tax
2: All $ amounts are in CPI-adjusted dollars
3: The “projected returns” are merely filling in missing data at the end of the 1960s and 1970s cohorts with not enough history for simulating very long horizons. They will not be used to simulate your immediate retirement returns. So, for the most part, the fail-safe WRs are not impacted much by changing those values.
In response to #3, I’ve decided to remove the “Project Future Real Returns” from the parameter section, because that’s likely going to confuse readers. I moved that section to the bottom and put a note: “Do Not Change”
1: All simulations are done pre-tax. You’ll have to figurer out yourself what kind of tax haircut you have to apply to translate your withdrawals into net-of-tax consumption.
2: Correct
3: Depends on what you mean by consumption. Consumption + those supplemental outflows (medical, college expenses). But you can’t consume the higher amount on discretionary items and then also fund the medical expenses. That’s double-counting.
A simple example with a $1M portfolio and just a sinlge supplemental cash flow of negative $10k in month 13: Cash Flow Assist cell V15 says $31,853. The sum of months 1-12 column T = $31,853. The sum of months 13-24 column T= $41,853.
So we are safe to spend the $41,853 in year 2, even though it is higher than the failsafe “all in” consumption amount of $31,853.
That’s a simplistic example – the variances could be larger depending on the supplemental flows. But one should not be concerned about those year-to-year differences from cell V15 as long as (to your point) total consumption within the year is capped at that amount. Correct?
Yes, that’s correct!