October 12, 2022 – As promised in the “Building a Better CAPE Ratio” post last week, here’s an update on how I like to use the CAPE ratio calculations in the context of my Safe Withdrawal Rate Research. I have studied CAPE-based withdrawal rates in the past (see Part 11, Part 18, Part 24, Part 25) and what I like about this approach is that we get guidance in setting the initial and then also subsequent withdrawal rates based on economic fundamentals. That’s a lot more scientific than the unconditional, naive 4% Rule. In today’s post, I want to specifically address a few recurring questions I’ve been getting about the CAPE and safe withdrawal rates:
- Can a retiree factor in supplemental cash flows like Social Security, pensions, etc. when calculating a dynamic CAPE-based withdrawal rate, just like you’d do in the SWR simulation tool Google Sheet (see Part 28 for more details)? Likewise, is it possible to raise the CAPE-based withdrawal rate if the retiree is happy with (partially) depleting the portfolio? You bet! I will show you how to implement those adjustments in the CAPE calculations. Most importantly, I updated my SWR Simulation Google Sheet to do all the messy calculations for you!
- With the recent market downturn, how much can we raise our CAPE-based dynamic withdrawal rate when we take into account the slightly better-looking equity valuations? Absolutely! It looks like, the 4% Rule might work again! Depending on your personal circumstances you might even be able to push the withdrawal rate to way above 4%, closer to 5%!
- What are the pros and cons of using a 100% equity portfolio and setting the withdrawal rate equal to the CAPE yield?
Let’s take a look…
CAPE primer
Because I haven’t specifically written about CAPE ratios and their application to safe withdrawal rates in a while, let me just get everybody up to speed again on why and how CAPE ratios matter for retirees. Even if you don’t pursue a dynamic CAPE-based safe withdrawal rate, but prefer to use the standard Trinity Study-style calculations with a fixed withdrawal amount (though adjusted for inflation), CAPE ratios should matter for setting that withdrawal amount and percentage. Here’s again a chart from an earlier post to drive home this point. All the historical failures of the 4% Rule have occurred when the CAPE earnings yield (= one divided by the Shiller CAPE Ratio) was under 5%, i.e., when the CAPE Ratio was above 20. So it would be way too conservative to use a 4% Rule when the CAPE Ratio is in the low teens. But a 4% Rule might be way too aggressive conditional on facing a high CAPE ratio because the conditional failure probability is much higher than the (unconditional) failure probability you’d get from a Trinity Study.
Because of the great relevance of this CAPE ratio to retirement success, I dedicate a portion of the main results table of my SWR Google Sheet (see Part 28 for the link and explanation) to the different valuation regimes, please see the screenshot of the table below. It’s the failure probabilities for different withdrawal rates (ranging from 3.25% to 5.25%) over the entire sample, but also conditional on equity valuations. For example, a 4% WR might seem somewhat safe with an 11.62% failure rate. But this disguises the fact that we encountered failures in 37% of the retirement cohorts when the initial CAPE ratio was elevated, at above 20. The failure rate rises even further, to 43.2% when both the CAPE was above 20 and the S&P 500 index stood at an all-time high at the commencement of retirement. (and I should note that this is the baseline scenario with a 60-year horizon, 25% final value target, and modest supplemental flows later in retirement coming from Social Security and a pension)
I also like to highlight the extreme sensitivity of failure rates as a function of the withdrawal rate. And the sensitivity is more extreme if equity valuations are elevated. For example, shifting withdrawal rates from 3.50% to 3.75% and then 4.00%, when the CAPE is below 20, the failure rates go from essentially 0% to 1.7% and then 5%. But when the CAPE was above 20, the failure rates go from 2% to 24% and then over 37%. So, to everybody who’s making fun of my blog for calculating withdrawal rates with precision behind the decimal point, I laugh straight back at you for not understanding the simple finance wisdom that small changes in your financial strategy will amount to a huge difference after 60 years of compounding!
So, how can you use the new CAPE ratio in the traditional SWR worksheet? Simple, at the bottom of the parameters section you can toggle back and forth between the old, traditional Shiller CAPE and the new adjusted CAPE ratio. Use option 1 (=old) or 2 (=new) respectively. The new default setting is 2, for the new adjusted Shiller CAPE.
CAPE-based Dynamic Safe Withdrawal Rates – a primer
Just to get everyone back to speed on dynamic withdrawal rates, here’s a quick primer on the CAPE-based SWR rules. In the most basic setup, we calculate our withdrawal rate as
WR = intercept + slope / CAPENotice that our setup also encompasses, as a special case, another popular withdrawal rate rule: the Bogleheads variable percentage withdrawal (VPW) rule, if we set the slope to zero and fix the withdrawal amount to a certain percentage of the portfolio. But of course, we do want equity valuations to have an impact because economic fundamentals should matter. There are three neat features of using a withdrawal rate contingent on equity valuations:
First, we find an initial withdrawal amount that’s calibrated to be consistent with equity valuations. Second, our subsequent withdrawal amounts will adjust to changing portfolio values but also equity valuations. That’s a great improvement over the naive Bogleheads rule that uses a fixed withdrawal rate (though adjusted for retirement horizon). With a variable withdrawal rate, your withdrawal amounts will be significantly less volatile than the portfolio value. The portfolio may be down 20%, but the withdrawal amount is only down by, say, 10% because you also adjust the percentage withdrawal rate to reflect the better-looking CAPE ratio after an equity market correction.
And third, our CAPE approach satisfies Bellman’s Principle of Optimality. It implies that subsequent retirement planning decisions are following an optimal path, as though the retiree had simply re-retired under the new prevailing conditions. This Bellman Principle is violated in the naive Trinity-style fixed withdrawal amount calculations, and that always bothers me! But if you’re not a math geek, please ignore this one point! 🙂
If you’re familiar with my Google Sheet (see Part 28 for the most recent comprehensive guide to the sheet), you can enter supplemental cash flows to be taken into account in the traditional safe withdrawal rate simulations. But not in the CAPE-based simulations. As you might recall, I set up a separate tab where you can enter your future supplemental cash flows, like Social Security, pensions, home sales or purchases, anticipated nursing home expenses, etc. Until now, the CAPE-based rule is calibrated to target capital preservation and it ignores all supplemental cash flows. A reader asked me a while ago how he would factor in those supplemental flows. I had some ideas on how to “hack” my Google sheet, and I implemented those in the latest Google Sheet. This brings me to the next point…
Factor in supplemental cash flows and (partial or complete) asset depletion
So, here’s how to calculate that new CAPE-based withdrawal rate:
Step 1: CAPE Rule basics
We still calculate an initial CAPE-based safe withdrawal rate, assuming no additional cash flows and capital preservation. In this case, I use a CAPE of 27 and the following CAPE parameters: intercept=1.75% and the slope of 0.50, as I recommended in previous posts, e.g., in Part 18. So, the SWR calibrated for capital preservation and no additional cash flows would be…
WR = 0.0175 + 0.5 / 27 = 0.036In other words, the initial CAPE-based SWR is 3.60%.
Step 2: Supplemental Cash Flows
Enter the supplemental cash flows as you would always do in the Google Sheet, namely in the tab “Cash Flow Assist”. Notice that there are columns for cash flows that are already inflation-adjusted (e.g., Social Security, most government pensions, etc.) as well as cash flows that are in nominal dollars (e.g., most corporate pensions). For example, as the baseline scenario in that Google Sheet, we have a retired couple where one spouse expects $2,000 in Social Security in month 301 (25 years into retirement) and the other spouse expects $800 in month 313. Furthermore, to account for higher health expenses we model a negative cash flow of $1,000 starting in month 361, and $2,000 starting in month 481. There’s also a corporate pension worth $300 a month starting in month 133 (year 11), but it’s not CPI-adjusted. You’d also enter your current portfolio value here, $3,000,000 in this case.
Calculate the present value of all supplemental cash flows, using the initial CAPE-based withdrawal rate as a discount rate. Add this discounted value to the initial (financial-only) net worth. And of course, you don’t have to do that by hand, the Google Sheet does it for you! In this case, the portfolio was worth $3,000,000 and the supplemental cash flows add another $189,499 to that net worth.
Step 3: Factor in the (partial) asset depletion.
Partial asset depletion is already modeled in my standard SWR package. In the baseline example, the couple has a 60-year (720-month) horizon and they like to leave a bequest worth 25% of the initial portfolio value, i.e., $750,000. You already entered those parameters on the main parameters page, together with all the other parameters, like portfolio weights, see the screenshot below:
In the context of the CAPE-based WR calculations, we can now compute the target withdrawal amounts through the Excel (or Google Sheets) PMT function, calculating the level of monthly payments to deplete a total net worth of $3,189,499 down to $750,000 over 720 months assuming an average return equal to the initial CAPE-based SWR. I get $10,368 or an annualized 4.15% withdrawal rate relative to the initial portfolio value of $3,000,000. That’s pretty awesome because we’ve just made the 4% Rule sustainable again!
So, enter your parameters and see how much you can raise your CAPE-based safe withdrawal rate. And please share your results in the comments section if you like!
With the new CAPE, the 4% Rule looks even better!
So it looks like even with those still-lofty Shiller CAPE stock valuations of about 27, this couple can use a withdrawal rate of more than 4%, despite their long horizon. If we use the new adjusted CAPE of just about 21 (as of 10/11/2022), we can even increase the withdrawal rate to 4.13% (basic) and 4.57% (factoring in the additional cash flows and the asset drawdown).
A small caveat here: This CAPE-based dynamic withdrawal rate is not a one-time, set-it-and-forget-it kind of deal. The CAPE-based withdrawal amounts are still subject to portfolio risk over time. If the market were to tank another 20% you’ll certainly start reducing your withdrawals as well. But the nice feature of the CAPE-based withdrawal amounts is that even if your portfolio drops you may not have to reduce your withdrawals one-for-one by the same percentage. That’s because a further drop in the equity market will also make equity valuations more attractive and thus raise the CAPE-based withdrawal rate again. Let me cook up the following experiment to showcase this.
Imagine we start with that same scenario, a 60-year horizon, a $3m initial portfolio, a $750k bequest target, and the moderate cash flows described earlier. The CAPE stands at 21.00. We end up with an “initial” CAPE-based of 4.13% (ignoring supplemental flows, targeting capital preservation) and an adjusted monthly spending target of $11,427, or 4.57% p.a., relative to the $3,000,000 initial portfolio.
Now assume that the 75/25% portfolio takes a 20% hit in the stock portfolio and a 10% drop in the bond portfolio, which translates into a 17.5% drop in the overall portfolio. For simplicity, I also assume that this drop happens all at once. What would that do to the SWR calculations? First, the paper portfolio is down to $2,475,000. But the CAPE ratio also drops to 17.60. We would drop the monthly withdrawals to $10,299. But that’s only a 9.9% drop even though the portfolio is down by 17.5%.
So, there’s this nice offsetting effect that cushions the drop in the portfolio. Also, notice that because the drop in the portfolio happened instantaneously, we slightly overstate the drop in the withdrawal amount. If the 17.5% drop in the portfolio had happened over the next several months, the target withdrawal amount would have been slightly higher because a) we would have already slightly shortened the remaining retirement horizon and b) we’d be increasing the present value of the future positive supplemental cash flows.
Even in the static withdrawal simulations, 4% and even 4.5%+ may work again!
How about the traditional Trinity-style simulations? Well, the rationale for higher withdrawal rates today, as in October 2022, is that since the market has already dropped by quite a bit, it would seem overly conservative to calibrate today’s withdrawal rate to the 1929 or 1968 market peak. Do I really believe that after a 23% or so drop in nominal terms and even a 28% drop in real terms, the stock market will now tag on another Great Depression-sized drop? Highly unlikely. What’s more realistic and reasonable is to calibrate the current (October 2022) withdrawal rate to historical situations that were equally beaten down from their respective recent stock market peaks. That’s easy to get from my spreadsheet! In the Main Results tab, there’s a table that lists the fail-safe consumption rates as a function of the equity drawdown. Turns out, that after a 20% drop, we already make the 4% Rule viable again. And in the real drop between 25% and 30% region, we’re already up at 4.17 and 4.38%, respectively. So, in light of the recent drop, we can be a little bit more aggressive.
Notice that this calculation used the 60-year horizon. Using a traditional 30-yer horizon, we can easily push that conditional rate up to above 4.5%, even close to 5%! Go ahead and play around with your own parameters. You’ll be surprised! The 4% Rule, maybe even the 5% Rule may be alive and well!
Can I use a 100% equity portfolio and set the withdrawal rate to the CAPE yield?
This is a question I’ve been thinking about for a while and then recently it came up again in the comments section and in a newsletter/blog post that I subscribe to. Today’s blog post is a great excuse to write about this idea!
The rationale against a 100% equity portfolio is that it would be far too volatile for the average retiree. But Victor Haghani (Elm Partners Management) made this important point in his excellent and thought-provoking post “A Sheep in Wolf’s Clothing“: Maybe we shouldn’t focus so much on the portfolio volatility. If we withdraw the 10-year rolling EPS from an equity portfolio then the earnings volatility rather than the portfolio volatility impacts our retirement happiness. And the annualized standard deviation of earnings was only 7%, much lower than the volatility of the stock market.
So, I wanted to check how a CAPE-based withdrawal rate with a slope of 1.0 and intercept of 0% would look in practice. Right now, with a CAPE of about 21, that would translate into a withdrawal rate of 4.76%, and that’s before the adjustments for supplemental flows and partial depletion of the portfolio, which could easily lift the rate to above 5%.
In any case, I first set the equity weight to 100% and all other asset classes to 0% in the main tab:
Next, how do we evaluate a CAPE-based strategy? The CAPE-related tab helps us simulate how this simple CAPE-based rule would have performed over time (without supplemental flows though!). In fact, we can study how the withdrawal rates and withdrawal amounts would have behaved over the 1871-2022 period, see the screenshot below. The results are not that promising. There were a few cohorts that would have suffered massive declines in their retirement spending. Above all, the cohort that retired right before the 1907 banking panic and then went from one disaster to another, WW1, the Depression of 1920-1921, and then the Great Depression. They eventually had to cut their withdrawals by more than 81% over a 30-year horizon. Tightening the belt doesn’t start to describe that. We’re now entering “eating cat food in retirement” territory.
In fact, even the 1929 cohort saw a drawdown of 44+% of withdrawals during their retirement, though the final withdrawal amount recovered and even surpassed the initial 1929 amount by 2.26%. The 1965-69 and 1970s cohorts also got hammered if they had applied this rule. The good news is that the cohorts that retired around the dot-com and Global Financial Crisis peaks both fared really well.
What causes the precipitous fall in the portfolio value and the withdrawal amounts during those two episodes? Very simple: The CAPE ratio dropped deep into the single digits, and that caused withdrawal rates of almost 20%. Even if the stock market eventually recovered again, the portfolio minus the withdrawals had taken such a severe hit that the retiree suffered massive declines in purchasing power. Sequence of Return Risk!
Bummer! Suddenly, the prescription from Victor Haghani’s article doesn’t sound so attractive anymore. Sure, the volatility of annual changes in withdrawals is low, around 4-6%, but that’s of little help if the trend is down 3.5-5.0% on average every year and 60-80% over a 30-year horizon.
So, unfortunately, the CAPE-based rule WR=1/CAPE doesn’t work so well in practice during the very deep bear markets in the 1920s, 30s, and 70s. But during the 2000s you would have fared very well, thanks in part to an extremely low initial withdrawal rate of only 2.37 at the peak in 2000. What also helped you is that the CAPE didn’t even drop below 10. The low point around the Global Financial Crisis was about 11 (based on month-end index data). The CAPE stayed below 15 for only 9 months in 2008-2009. So, there was never any risk of severely depleting your 100% equity portfolio. If we believe this pattern repeats in the current bear market you might get away with a 100% equity portfolio and this CAPE rule.
If you’re uncomfortable and worried about a sharp drop in withdrawals again, you could choose a rule slightly more cautious. For example, pick an intercept of -0.25% instead 0% and a slope of only 0.9 instead 1.0. I also added a feature of capping the CAPE withdrawal rate. An upper limit of 10% seems to work pretty well in the simulations. So, we’d limit the extreme drawdowns in the early periods, while also maintaining pretty solid initial withdrawal rates in today’s environment: 4.04% as the raw withdrawal rate and 4.49% when taking into account the supplemental flows and partial asset depletion.
Side note: A TIPS ladder approach
This side note has nothing to do with the CAPE, but I just wanted to mention how today’s improved bond valuations also improve our safe withdrawal math. Specifically, here’s another approach to make the 4% Rule work again: Invest in a ladder of TIPS (=inflation-protected government bonds) because real yields on TIPS have now reached levels that would easily sustain a perfectly risk-free retirement income stream, albeit only for 30 years. A quick look at the TIPS term structure on 10/11/2022 (via Bloomberg) tells me that the entire real yield term structure is now between 1.5% and 2% again.
If we apply a weighted average of, say, 1.8% real return over the next 30 years, we can generate a real safe withdrawal amount of $42,633 per $1m of capital, using the Excel PMT function. So, a safe withdrawal rate (with depletion over 30 years) would stand at 4.26% again:
=PMT(0.018,30,-1000000,0,1)That’s significantly better than I Bonds. They currently yield 0% real, giving you only a 3.33% safe withdrawal rate with depletion. And you can’t even move $1m all at once into I bonds due to the $10,000 per person/entity per year limit.
Conclusion
In the post last week, I introduced a few adjustments to the Shiller CAPE and they seem to shift the CAPE into a slightly more reasonable range. And sure, the market is still a bit overvalued. But chances are that we can push the CAPE-based withdrawal rate to 4% and above. Even higher when we take into account partial asset depletion and supplemental cash flows later in retirement. As someone in the comments pointed out, this is a bit of a “hollow victory” because you can apply a higher withdrawal rate but everybody’s portfolio is down since January 2022. Granted, but I still see folks applying 3% and even sub-3% withdrawal rates in today’s market. Relax, everybody, the risk of another bad market event on top of the current drawdown is low!
Likewise, with the adjusted CAPE quite close to dropping below 20 and the S&P 500 dropping more than 25% in real terms since the beginning of the year, I am also ready to announce that even in the traditional static SWR calculations, we should now safely move the withdrawal rate to 4% and above. Well, you heard it here first; the 4% Rule works again! And with a little bit of flexibility and a generous pension and Social Security benefits later in retirement, you can certainly go crazy and justify 4.5% or higher!
I’m confused about what asset allocation the CAPE based rule sheet assumes? It doesn’t look like any of the formulas are connected to the asset allocation that is chosen on the parameters sheet? If I change my asset allocation it doesnt change the target withdrawal rate on the CAPE rule sheet. Similarly, I dont understand what choosing CAPE1 vs 2 on the parameters tab affects since on the CAPE based rule tab, it shows you the current CAPE1 and CAPE2 and then it seems that you just manually enter the desired CAPE in cell b9 of that sheet. Thanks for any clarification you can provide. I did read this post, the instructions, etc but i’m still confused.
You need enough equity exposure to make it work. But anything between 60% to 100% equity exposure should be suitable for the CAPE rule. WIth less equities you have less long-term growth but also less short-term vol.
That’s why you want to use the SWR sheet and especially the CAPE tab to calibrate how much short-term vol you can accept.
Just want to confirm the WR equation. You have WR = intercept + slope / CAPE Here, order and parenthesis (or lack of) matter. Is it truly the slope divided by the CAPE then the result is added to the intercept? Or, is it the slope added to the intercept result that is divided by the CAPE?
No bracket. so its a+b/CAPE and most certainly not (a+b)/CAPE
So, if I use the defaults for the portfolio allocation on the parameters tab (with 100% as the final value target and 720 month horizon) the highest SWR with a 0% failure rate is 3% (for both Cape 20). On the CAPE sheet, the SWR with today’s CAPE2 being 23.57 is 3.87%. What accounts for the difference (what parameters and which result should I expect to be similar to the CAPE rule SWR)? I realize that the CAPE calculation is just a static calculation but I’d still expect that I should be able to get a similar result on the main sheet w/ the right parameters…
The 3% is the historically safe WR. You’d likely never have to adjust that.
The CAPE-based rule can start with more initially but has the potential for some sharp declines in your retirement budget.
Thanks for all the work you’ve done, both for the tools and write-ups! They are extremely helpful, and I feel like I learn a lot just by following along with your thinking over time.
I love the intuition and motivation for the CAPE-based dynamic withdrawal. But, (after rereading parts 11, 18, 28, 54) I still feel like there’s a gap in posts/toolbox in terms of demonstrating that the dynamic-CAPE strategy is really “safe”, or directly quantitatively comparing its upsides versus static withdrawal (% of original portfolio). For the static strategy, the toolbox does a great job of answering questions like:
1. For some initial endowment/allocation strategy, what lifestyle (minimal $ withdrawal) can I support with high probability? What is the p25/p50/p75 outcome I can expect?
2. How does my situation change if I vary my initial endowment, retirement duration, allocation strategy, etc. What are the tradeoffs for working another year, shifting into bonds, etc.
In contrast, it looks like the tuning and evaluation of the CAPE dynamic strategy is based on picking a good tradeoff between max drawdown vs withdrawal-at-one-point-in-time. But, at least for me, this doesn’t map to my real-life values for either risk (do I need to make sure I can live off a monthly income corresponding to the max drawdown indefinitely? That’s surely too conservative, but not sure how else to use this datapoint) or reward (neither SWR at start nor worst-case-drawdowns are great at capturing the likely dollar withdrawals throughout retirement).
You make a point that dynamic strategies can’t ‘fail’ in the sense of having assets fall to 0, but they still can fail to meet an expected minimal lifestyle in retirement. If this is the most-used metric for static strategies, then probably it’s an important way to evaluate dynamic ones as well.
My wishlist would be something like [and, if I can’t excite you enough to do it, may take a stab at it myself :) ]
– Simulation support for withdrawal strategies like: Withdrawal(in dollars)=Max(some small % initial portfolio, CAPE-based dynamic % of current portfolio). Intuitively this corresponds to having some minimum lifestyle, such that not being able to maintain it throughout retirement is a failure case. (And, I imagine this is what many people would do in practice – adjustments based on the market, but a floor on withdrawals for essentials);
– Or, alternatively, have some definition for ‘failure’ which can be shared across static/dynamic strategies, and captures some other intuition for what it means for a strategy to be safe (e.g. could be something like, >x months with <y withdrawal is a failure)
– Since the dynamic strategy now has a failure rate, it can be directly compared with static strategies. e.g., get some results which look something like (numbers made up):
— You can get a 2% failure rate with a static withdrawal of 3.25%. You can get the same failure rate with Max(3.15% initial, CAPE-rule (1.5,0.5)). And, the latter results in [some distribution] of additional consumption. So, at constant failure rate, you're buying X additional consumption in expectation by being willing to drop spend by 10% in bad times. If you're willing to drop spend 20% sometimes then instead you can use CAPE parameters (1.75, 0.5), etc. etc.
In a simple spreadsheet like the one I post (See Part 28), I can easily simulate the fixed rate withdrawals for all retirement cohorts, as we all know. See the simple math behind that explained in Part 8.
Or the CAPE-based because there’s no path dependence. So we can just plot the entire chart of 150+ years of asset values, and you can pick any x-year window and rescale to the year 0 amount that applies to you.
Mixing CAPE-based rules with the floors and ceilings you describe is harder because each cohort has to be simulated separately over t time loop, and then we need another loop over cohorts. I have done simulations like that, but it needs to be done in Matlab, Python, or some other language. Excel/Google Sheets won’t accommodate this. So, thanks for the wishlist, but unless someone helps me translate the Google Sheet into an App that can perform calculations with loops over all retirement cohorts, this is not feasible in the current form.
I need to introduce you to CrystalBall (now owned by Oracle) or @Risk. Both of these are add-ins for Excel that do MonteCarlo analysis. They can to exactly what you say can’t be done in Excel.
Thanks. As outlined in this series many times before, I prefer not to us Monte Calo simulations for SWR analysis.
> unless someone helps me translate the Google Sheet into an App that can perform calculations with loops over all retirement cohorts, this is not feasible in the current form.
I put some time in and have built out a library to support this kind of simulation support in Google Sheets on top of the current Toolbox, using Apps Script to write custom functions (https://developers.google.com/apps-script/guides/sheets/functions).
It supports some things which were already not too hard (e.g. define some arbitrary withdrawal function and simulate what happens for single cohort). But also, it can use the simulations to find the SWR for these new withdrawal functions for each cohort (or find one parameter in a multi-parameter withdrawal function, for fixed values of other params). You can also define new failure conditions other than the value of final portfolio (e.g., if withdrawal amount is less than x for y months, that counts as a failure), which may be helpful for proportional withdrawal strategies.
Let me know if this is something you’d be interested in playing around with yourself or including in the toolbox. I can walk through what I’ve done in more detail if there’s interest.
Thanks for the link. I will check it out. Of course, the function has to accommodate not just the trivial examples like simply doubling a number. We’d need the functionality to run loops over return months to better accommodate the path-dependent strategies like Guyton-Klinger, floors/ceilings, etc.
Where would be a good source to see all the functions, like for-loop or matrix algebra to be used within the function?
The link above is just a reference for how I’m getting this kind of arbitrary code into google sheets. The code itself can just be any arbitrary javascript you want, so I coded up the logic for actually doing the simulations, as well as for finding SWR. You can see my version of the toolbox but with simulation code added here: https://docs.google.com/spreadsheets/d/13Lw39K1tv8b-9kpgSl7RcQhXD4Jz2omcGtc7Dxkoc_E/. To see the code I wrote, you click Extensions > Apps Script (or, I think you can jump directly to the code here: https://script.google.com/u/0/home/projects/1H9g7H2tEA1D1dxlspn2270YvcOUFIMP58ZJL0yliseFMQkU7ibUVp87D/edit)
—-
Comments on the sheet:
Most of the sheets are unchanged from the current toolbox. Places you can see the new logic used are:
1. In the new ‘Sim Tests’ tab, I pulled a bunch of examples of results from other parts of the sheets, which I then replicated using the new method (to show how it’s possible, and also to make sure that no errors). e.g., first column has SWR for constant withdrawal pulled from the ‘SCR time series’ tab, and then the column next to it computes it using the new FIND_SWR_CONSTANT_WITHDRAWAL function I wrote. Similarly, I directly compute the highest safe ‘a’ parameter in a CAPE-based constant withdrawal strategy for b=0.5, etc. Nothing in this tab does anything which wasn’t possible before, it just shows how you’d do old things using the new simulation library.
2. In the ‘SCR time series’ tab I have a new column ‘Simulated SCR’, which is just the SCR for the full dataset computed using the new function. This means you can compare the full time-series using the new method next to the old one and confirm that they’re the same. It also shows the way I found to get reasonable performance – each of the blue cells represents a function call, each of which returns a batch of SWR outputs for 500 months. This is the best way I was able to get good performance for the sheet – if each function call returns the results for only one month, then you need >1k function calls every time you update the sheet, which google sheets does not handle well. If you have one function call to compute all months though, that single function call can take a while. The compromise I’ve found is to batch into sizes of 500, which I’ve found keeps the time to rerun all the simulations to ~10s.
3. Also in the ‘SCR time series’ tab I have a column ‘Floor in Max(CAPE-prop, floor) Sim’, which is an example of a new kind of behavior which these functions can support. For each month, it computes the highest safe-value for ‘floor’ where withdrawal is given by MAX(floor, current_portfolio * MIN(a + b/CAPE, max_wr)) (a, b, max_wr pulled from the sheet), and where any situation where you withdraw the ‘floor’ value for >= half of the months also counts as a failure. So this example shows how these methods let you pretty easily incorporate more complicated withdrawal functions, as well as more complicated failure criteria.
—-
Comments on the code:
The ‘Public’ file has the actual functions which are used within the Sheets. For that one I tried to be a bit more careful with the comments, so should be reasonably accessible in terms of what each of the function arguments mean.
You can think of the rest of the files as a kind of library I wrote to support these custom functions.
The ‘Simulation’ file has the basic logic for looping over portfolio returns and additive flows with a given withdrawal function and returning portfolio value. The ‘Search’ file implements a variant of binary search, which the code in ‘SWR’ file then uses to find the SWR for a withdrawal function with one free parameter by binary searching over some specified range. ‘WithdrawalFuncs’ and ‘FailFuncs’ are where I’ve put the different withdrawal/failure functions I’ve implemented so far. You can think of all of these files as just the javascript library I built to support the custom functions used within Sheets.
It should be pretty easy to code up a SWR function for pretty much any new withdrawal strategy you come up with. You mostly just make implement a new withdrawal function generator (following the pattern you see in WithdrawalFuncs), and then pass all the relevant inputs into findSWRMulti. The only assumption that the simulation/search code is making which you need to keep in mind is that failure is monotonic in the parameter you’re searching over (i.e. if you fail for some parameter p, then you also fail for all parameters >p), but I feel like this should be true for any reasonable parameterized withdrawal function.
OK, that’s a mouthful. Thanks for putting this together! I will research this some more this coming weekend!
Hi Karsten, First, thanks very much for your valuable series on SWR and the toolbox. I have learned so much from your posts and use your toolbox as a valuable input to my own retirement decision making. I have been looking at the CAPE-based Rule sheet and wonder how to think about the interest rate used in B16. It bothers me that the target withdrawal rate is so much higher with the CAPE-based rule compared to the static SWR from the main tab (5.4% @ CAPE=30 v. 4.2%). I would have thought the initial CAPE-based withdrawal rate at high CAPE should be close to the failsafe SWR from the main sheet. This is similar to the way (I think) you set the CAPE intercept and slope to give a reasonable (capital preservation) SWR of 3.27% at CAPE 30.
Part of this is driven by the different discount factors applied to the supplemental cash flows using the static and CAPE-based SWRs. If I adjust B16 to split the payment into two parts: one for the main portfolio using the B10 interest rate and another for the supplemental cash flows using the inflation rate from cash flow assist tab I can knock down the CAPE target withdrawal by about 0.2% which narrows the gap a bit.
I’d be grateful if you can steer me in the right direction in thinking about the difference between the static and CAPE-based target withdrawal rates.
The two SWRs shouldn’t be similar at all. You can start higher with a CAPE-based rule because if the market tanks you will decrease your actual withdrawals, while the fixed withdrawals stay fixed.
Does the Total Withdrawal Amount shown in cell B16 (CAPE-Based Rule tab) equate to a Safe Withdrawal Amount or a Safe Consumption Amount? Thanks, Joe.
Safe consumption. Withdrawals are adjusted for supplemental cash flows.
My supplemental cash flow has the next 10 years of mortgage. The 11th years onwards have security security income. How does this strategy accommodate the irregularity of supplemental cash flow over time. In my case, I need a larger consumption for the first 10 years.
You enter the future cash flows in the tab “Cash Flow Assist,” columns E-N.
Those flows will be factored into the CAPE-based rule in Cell B14.
I have added the flow in “Cash Flow Assist”. I can see that Cell B16 takes the supplement cash flow Cell B14 to produce the target withdrawal. But then, for example, if Year 1 if I have supplement cash flow of -20,000 to pay my mortgage. Do I add 20,000 to Cell B16 to become my Year 1 budget? Thanks!
You add the $20k withdrawal to your withdrawal from the portfolio. Your baseline budget is the part that’s constant throughout retirement.
And when you receive pensions/Social Security, you subtract those payments from the budget to get a smaller withdrawal.
Hi, very interesting and useful blog and tool. I looked everywhere, but can’t figure out how to find today’s CAPE every day. If I understand correctly, you adjusted the Shiller CAPE somehow. Where can I find your adjusted CAPE periodically, so I can plug in the right number every day?
I write about the CAPE in this post: https://earlyretirementnow.com/2022/10/05/building-a-better-cape-ratio/
Daily Updated CAPE estimates here: https://drive.google.com/file/d/1ugtRN3TaAVwQi-20mjt4DctF-glppSMD/view?usp=sharing
I try to update this daily. But if the numbers are outdated you can extrapolate yourself:
new CAPE = my CAPE(=CAPE.ERN1 or CAPE.ERN2) / my last SPX quote(=SPX.ERN in column C) * today’s SPX quote.
Using this tool more and more so thanks! I’m wondering how to manage the “Distribution of Final Value” sheet while I’m using the CAPE based rule sheet. What value should I put in the Annual consumption rate box? The SWR (capital preservation), or the Target Withdrawal ($/month) or something else?
The distribution of final value tab in the sheet refers to the fixed withdrawal rates only. You enter that fixed rate in cell B6 and you can then study the distribution. This tab does not assume a CAPE-based rule.
Thank you for the great SWR discussion and tools! It is quite enlightening. For deriving your equation fitting the withdrawal rate based on CAPE, I wondered if you considered quantile regression? Instead of only adjusting the least squares line intercept by eyeball, quantile regression could give, for example, the 5th percentile line which would project 95% of the SWR’s to be above it at a given CAEY level. The intercept and slope would likely be different than in the current model and perhaps fit the extreme points better. Also, is the data set plotted in your CAEY vs. SWR scatterplot available?
Yeah, you can run all sorts of other regression variations if you want. For my purposes, OLS sufficed to show what I wanted.
If you want to run something more complicated, the data are all available in the SWR spreadsheet:
Both SWR/SCR and CAEY are in the tab “SCR time series”: Columns E and G. Good luck!
Thank you so much for pointing me to the data! I ran some quantile regressions on it and here are a few results at various percentiles:
(5th Percentile): WR = 0.0081 + 0.4433/CAPE
(10th Percentile): WR = 0.0085 + 0.4780/CAPE
(15th Percentile): WR = 0.0086 + 0.5028/CAPE
(20th Percentile): WR = 0.0076 + 0.5338/CAPE
These provide much more conservative SCR’s than the current model in use. In fact, I had to run one above the 30th percentile to get something comparable:
(32nd Percentile): WR = 0.0174 + 0.5035/CAPE
My question: Is having 68% of the historical SCR’s above and 32% below the selected WR too risky in the context of how it is used on a monthly basis? At the 5th percentile, you would only be overshooting in 18 randomly disbursed months during a 30-year retirement horizon — something that sounds reasonable to me. But at 32%, that would be overestimating the WR for four months out of a year — maybe that is still tolerable?
Good question: “My question: Is having 68% of the historical SCR’s above and 32% below the selected WR too risky in the context of how it is used on a monthly basis?”
It’s not. That view would be comparing apples and oranges. The SCRs you used are historically fixed consumption rates, i.e., set it and forget it WRs that would have succeeded over the specified horizon.
A CAPE-based rule would readjust the withdrawal amounts constantly in response to market returns and valuations. So, with a WR=1.75%+0.5*CAEY you can start with a higher initial rate, but you trade off against potentially steep and extended spending cuts. In my SWR Google Sheet, tab “CAPE-based Rule” you can study the time series of withdrawals of such a rule and note the volatility of withdrawals and the length of drawdowns.
Thank you, that makes a lot of sense! With the constantly adjusting withdrawal amounts, could you make the argument that you can be more aggressive with the WR=1.75%+0.5*CAEY equation? If the line were based on, for example, median values, wouldn’t the withdrawal rates still balance out, in some sense, over time?
I ran quantile regression for the median line fit and got WR=2.86%+0.49*CAEY. Plugging those parameters into your “CAPE-based Rule” sheet, the worst 30-year change and drawdown both decreased by about 10 percentage points (approximately -58% to -68%). My question is if a person were comfortable with the increased drawdown, what other risks should one need to be aware of in using such a rule? I guess I would like some insight as to what makes the WR=1.75%+0.5*CAEY an attractive choice? Thank you in advance for your response!
The a=1.75% is not set in stone. Some folks would use 1%, which seemed a bit too conservative. I was able to push it to 1.75% and the historical drawdowns (depth and length) were still OK. If you push this too far, you are again “squeezing the balloon,” i.e., trade off higher initial rate for steep drawdowns later in retirement.
Also keep in mind that with partial asset depletion you can also increase the initial withdrawal amount a bit more.
Frankly i’m lost in all the math relationships, but really grateful for all the hard work and care that went into this to simplify for us mere mortals. A big thank you. I’ve read as much as possible on the SWR series, but even w/ the elegant simplification explicating everything, i’m a little unclear on a few things.
When I fill in inputs w/ supplemental cash flows to run different life scenarios like early SS, proceeds from sale on a rental, w/ CAPE 2.0 and a 75/25 portfolio, I consistently get SWR results > 6% w a $2M portfolio, a 0% failure rate = 140k/year. Is that’s reasonable to what one would expect?
2nd question, would then a safe withdraw of 140k year be considered safe for SORR? Or is 6% too high? Sorry if this is already covered.
I can’t confirm your numbers but it’s certainly possible to get a SCR >6% when factoring in future cash flows, especially large amounts from the sale of properties, etc.
$140k out of $2m is 7%. That’s possible too, but I would check the math to make sure.
Obviously, the $2 cannot include the value of the rental property you’re later planning to sell. Otherwise, you’d be double-counting.
Thank you for the reply. And yes that’s right, the future sale of a property is figured into supplemental flows, not added into initial portfolio starting number of 2M.
OK, thanks for confirming. You’re on the right track then!
Thanks for asking that question, David!
I’m in the same boat as I have been spending many days learning how best to use this toolbox. I too am getting WR’s in the 6-6.5% range with a $1.5M portfolio (which includes future SS cash flows). I was certain I was doing this all wrong… especially after reading about the potential issues with blindly following a static 4% rule. It’s all starting to come together… I think.
When you say you are getting 6% WR with 0% failure, is that what you are seeing on the ‘Parameters & Main Results’ tab?
Reason for asking, the only place I see the “SWR” reference is on the CAPE-based Rule tab… which seems to always be hovering around 3.5% (given it seems to be based on the current CAPE and default settings) and assumes you are trying to preserve your initial portfolio value. I am assuming your use of ‘SWR’ is based on the 0% chance of failure on the ‘Parameters & Main Results’ tab?
Now… to better understand how to use this to manage actual withdrawals.
Love your series, and I’m a big fan of CAPE withdrawals.
But either I’m confused, or can I make a suggestion for what I think is an important fix to the toolbox? “The PV of supplemental flows” on the CAPE-based sheet works for finite cash flows, but (I believe) is incorrectly finding the PV for indefinite flows like Social Security and Pensions. It calculates out 720 months on the Cash Flow Assist sheet, but few will get that exact number of payments. Instead an easier and more appropriate PV formula can be used: PV(discount_rate, months_until_payments_start, -payment_amount).
Like with discount rate 0.25%, 120 months away for $2,000/month, the PV is $207,123. That is, if you earmark $207,123, you can take $2,000/month from that part, and end up with FV $0 just as your income stream starts (make sense, should be <$240,000). In contrast, the spreadsheet now computes $462,977. Or even easier, discount rate of 0% should yield exactly $240,000, right?
So you could keep your current formula, but only apply it to "definite"/one-off cash-flows. But then have/rename a column for cash flows that go on forever, and figure the PV for those ,and add the two PV's together. That's the amount to subtract from balance.
(Oh, note I’m assuming you’re using PV like I’ve seen it used before; since I see you subtract it from Today’s Portfolio). So instead of finding the PV of say Social Security for up to 720 months, you find the PV you need to “pay yourself” that amount from retirement until start of Social Security. That PV is subtracted from the rest of your total portfolio and your earmark/withdraw from that separate from the CAPE withdrawals. If I’m wrong, ignore all this.
Noted. I hope the fix I proposed solves this issue.
Also note that in the regular SWR sheet, the supplemental flows are only counted up to the retirement horizon, even if the SocSec payments may show up past your retirement horizon.
Good catch. I changed the formula for the discount factors in column R in the CAPE tab. values are set to zero if > CAPE sheet horizon. Does that solve the problem?
Ah yes, thank you! As soon as I posted, I was going to suggest this as a possible fix. I will probably use the alternate method of sectioning of a PV of the indefinite cashflow, but your fix integrates well into your existing spreadsheet methodology.
Exactly. Because we might have different horizons for the the regular and CAPE tabs, I thought the fix has to be done in the CAPE sheet only.
Interesting post as always! Me and my spouse are a few years away from early retirement, and we were wondering what would be good scenarios where TIPS would be better than bonds as part of a 60/40 glidepath?
I’ afraid that the horses have left the barn. Inflation is probably in its way down now.
Also, despite the inflation protection, TIPS were not performing well between 2021 and 2023 because the real yield rallied and you got hit with the duration effect, even in TIPS, though not as badly as in nominal bonds.
Have you seen the overlay of 1966-1976 CPI to 2013-2013? They almost overlap perfectly with a 2% offset.
I’m not saying that means were going to get a repeat of late 1970s next, but its not out of the realm of possibility depending on fiscal and monetary policy the next few years.
There may be some parallels. Both times the Fed tried to push on a string to stimulate the economy. But I think in 2022 the Fed stepped on the brakes pretty well. Much better reaction than in the mid to late 70s. So, no, I don’t think this is the proof for doom and gloom.
Thank you, Karsten. What a great post. I really appreicate all the work you’re doing.
Just to clarify a very basic question: for this CAPE-based withdrawal rate system, would the investor re-evaluate market conditions each year in retirement, then adjust accordingly based on where the CAPE ratio was? So, each year (month?) the withdrawal amount will adjust?
In the simulations, I assume every month, which is mathematically easier. Would the average investor do that so frequently? Probably not, but it wouldn’t make a big difference either if you do annual vs. quarterly vs. monthly.
Please advise where we can find CAPE values used herein (I assume these are the newly developed Karsten values and not Shiller)?
See this link:
https://earlyretirementnow.com/2022/10/05/building-a-better-cape-ratio/
And the link to a csv file updated (almost) daily:
https://drive.google.com/file/d/1ugtRN3TaAVwQi-20mjt4DctF-glppSMD/view?usp=sharing
I am sorry for this stupid question but I just want to ensure that I am understanding the definitions and terminology correctly –
1. In the sheet, I see the word initial (consumption or withdrawal), does this imply that it is always based on the initial portfolio value or as we progress through retirement, we can adjust the portfolio value to come up with the appropriate withdrawal rates?
2. Essentially what does the “initial” mean if this sheet can be used to calculate both initial and subsequent withdrawal rates.
Thank you so much for taking the time to respond.
You can and should certainly adjust the withdrawals over time if the portfolio performs better than some of the historical worst-case scenarios.
The SWR analysis should never be one-time set-it-and-forget-it exercise.
Thank you! Can you elaborate on what the word “initial” means then if it can be used for both initial and subsequent withdrawals?
The initial stays constant in real terms due to the CPI-adjustments. Thus “subsequent” = “initial”
However, there is a feature in the Google Sheet by which you can model growing or declining real consumption levels. In that context, it’s only the initial, and then subsequent retirement budgets rise or fall according to your parameter inputs.
Understood. Thank you!
You might have commented on this in a separate post, but wouldn’t the CAPE ratio need to be adjusted for inflation?
I may miss something but if inflation was 20% over the past three years (vs 7-8% from 2017-2020), wouldn’t the E in CAPE also need to be backwards adjusted to reflect increased inflation? Share prices might have gone up because of inflation and the fact that earnings are lagging (7 of the 10 past years earnings were in a low inflation environment) would likely mean the current CAPE is too high and might require adjustment.
The CAPE ratio is an inflation-adjusted measure. Prior earnings are discounted by inflation. Then you calculate a ratio between two price measures and that’s by definition independent of price levels. See this post for more details: https://earlyretirementnow.com/2022/10/05/building-a-better-cape-ratio/
Thanks for the great CAPE based rule function on the spreadsheet! I appreciate all the hard work the toolbox spreadsheet must have taken. I had a question about how the calculations work.
1. In the “Cape-based rule” tab, cell B10 “SWR Capital preservation”, the value I have based based on my inputs is 3.42%. Cell B17 (Todays withdrawal # p.a.) is 3.36%. I have set the “Final Value %” to 100% , and have $0 in supplemental flows, so I had thought the numbers should be identical. Just wondering why the numbers are different?
2. Changing the asset allocation on the “parameters and main results” tab doesn’t seem to affect the SWR or target withdrawal (B10 or B17) Just wanted to double check if this is by design? What asset allocation does the Cape based rule tab use?
Thanks so much for your help!
1: small differences are due to a) using “beginning of month” vs. end of the month” withdrawals in the PMT formula and b) annualizing the SWR with linear vs. compounding formulas.
2: the asset allocation changes the simulation results, but for fixed a,b parameters, the SWR is determined simply by the CAPE. But you still want to check if that a,b together with your asset allocation will generate palatable drawdowns in the simulations. Hence the table on the right!
Hi Big ERN, when using the CAPE-based rule spreadsheet what is the assumption of the asset allocation (70% stocks/30% bonds for example)?
I’d use this strategy with a 60/40 to 80/20 portfolio. So, yes, 70/30 would fall right into that range.
One last question on the CAPE-based rule spreadsheet, what is the approximate probability of success if you spend at or below the calculated target withdrawal (assuming you update the spreadsheet say on an quarterly or annual basis)?
To further clarify, using a (intercept) of 1.0% to 1.5%, b (slope) of 0.5 and WR Cap 12%.
CAPE-based rules (and many other variable spending rules) don’t have a failure event per se. You don’t run out of funds but you may have to curtail your spending. Since everybody has a different definition of what constitutes failure, there’s no clear answer to that question.
understood, so presumably the failure rate would be rather small (e.g. <5%) if use an a (intercept) of 1-1.5% and considering a failure condition of account balance reaching $0?
Again: your account balance will never reach $0 with a CAPE-based. It’s because you don’t withdraw a fixed amount but a certain percentage of the account balance.
Hi ERN,
Following up on this comment, it would be great if you could elaborate on how the asset allocation interacts with the CAPE-based rule. On the spreadsheet, altering the asset allocation on the main tab doesn’t seem to affect the CAPE-based SWR. I feel like I might be misunderstanding something but haven’t been able to find a clear explanation for why a ’60/40 to 80/20 portfolio’ is used.
Thanks so much in advance!
The CAPE-based rule has just two parameters: intercept and slope. It’s independent of the asset allocation you specify in the main tab. But clearly, the asset allocation determines the path of portfolio values and withdrawal amounts in the simulations. See the charts and tables in the CAPE tab.
You want to equity allocation high enough to get long-term sustainability. But not so high that the portfolio and withdrawal amounts become too volatile. Hence, the recommended range 60-80%.
Hi Karsten–
First of all, buckets of gratitude for the gift of the SWR series, toolbox, modified CAPE, etc (and all for free!). That is a rare showing of generosity and kindness and is a great example for us all.
I’m preparing for retirement this year (age 58), have recently lowered my AA and am trying to merge together some parts of the SWR and forgive me for not being a math geek. Implementation of all of this is a lot different than just reading/studying it.
It seems clear that glide paths only work well in CAPE>20 environments, and it looks like GPs heading to 100% provide the highest failsafe SWR. However, it also seems that CAPE-based dynamic withdrawals fare best around 65-80%. If that is correct, can you provide a bit of practical insight into the mechanics of GP implementation/horizon? Does it generally mean riding a GP over 10+/- yrs to ~80% (bypassing some SORR) and then going static on AA? Or have I just not put this together enough for clarity?
Again thank you so much.
I haven’t done careful simulations for GP and CAPE-based WRs combined. But I would suspect the benefits add up.
In parts 19+20, I played around with different GP assumptions. One could do an automated GP and move a certain % from bonds to stocks. I also found that an “active” GP does slightly better, i.e., only do the GP shift if the market is significantly below the all-time high.
PER M*: Owing to higher equity valuations and slightly lower bond yields, the highest starting safe withdrawal percentage for a retiree seeking fixed real portfolio withdrawals and a 90% success rate over a 30-year horizon is just 3.7%. That is down slightly from the starting safe withdrawal percentage of 4.0% we estimated in last year’s report. (The highest starting safe withdrawal rate for a 30-year horizon with a 90% probability of success was 3.3% in 2021 and 3.8% in 2022.)
Yeah, I’d use these numbers with a grain of salt. In historical simulations, the failsafe (i.e. 0% failure rate, not 10%) was at 3.8% that would have survived the Great Depression and the 1970s. So, these Monte Carlo sims used in Morningstar don’t really capture market dynamics too well. What they call 10% failure rate, may be a 0% failure rate with real data.
Hi Karsten.
First, thank you for all the work you have put into the SWR series and the toolbox that I can see is helping so many people.
I read your SWR series about two years ago and recently reread 4 or 5 parts (including all comments and answers) to refresh CAPE based rule concepts before I start playing with the toolbox being about 4 years to retirement at 52 in a capital depletion scenario. I’m completely sold to use the CAPE base rule, and I feel comfortable with an intercept between a=1.5% and 1.75% (with b=0.5). I really would like to tilt my retirement towards the beginning when I’ll be more active, travel more and hence will demand a costlier lifestyle. I do not expect the typical increase in healthcare cost later in life as I plan to be living in a country with decent free health care, and very affordable private health insurance.
1) Considering all this, what do you think about starting retirement with a=1.75% and gradually reduce it to a=1.5% towards my final years or even a=1.25% if certain unusual adverse conditions occur? I think implementing the tilt this way will work just fine, with the premise of accepting increased WR volatility risk for the reward of supporting a more costly retirement while still young and more active.
2) Do you see any better way to implement the “more while still active” tilt?
Thank you in advance for your time and wisdom.
PD: What is the reason you picked Google sheets in favor of Excel to develop the always up to date SWR toolbox?
Correction: PD: What is the reason you picked Google sheets instead of Excel, to develop the always up to date SWR toolbox?
Yes, that sounds like a plan. I would do this like you explained. Good luck with your CAPE-based SWR strategy.
I set up the Google Sheet long time ago and have added a lot of features. It’s always easy to take Google Sheet and turn it into Excel. Not so much the other way around. So, I felt Google was more flexible.
Hi Karsten,
First, I want to thank you again for sharing all of your hard work with the rest of us. It is a tremendous resource.
Second, I have been having some problems recently with your SWR calculator. Specifically, I have been getting wide variations calculated on my SCR varying between 2.94% and 3.99%. This is before taking into account future Social Security and pension payments. Obviously, the calculated CAPE ratios have varied a bit during this time but have all been historically high (averaging about 28 according to your revised CAPE calculator). All other data has remained the same. Initially, I thought that there might be a glitch in the copy that I downloaded, and so I downloaded another copy and re-entered the data there, but got similar results. I am confused. Based on my understanding of things, I don’t believe that my SCR should very much, if at all, with historically high CAPE ratios. Perhaps I’m wrong.
Any insight that you can provide would be greatly appreciated.
All the best,
Kevin
If the CAPE rule is 1.75% + 0.5xCAEY, then a swing of 105bps in the SCR must have been caused by a swing of 210bps in the CAEY.
That’s possible:
March 2020, my CAPE was 18.13, CAEY =5.5%
March 2025: my CAPE is 30, CAEY=3.3%
Thanks, Karsten. Unfortunately, all of these conflicting values have been over the past several weeks (sometimes on the same day) in 2025 when the CAPE was hovering between 28 and 30. I am a bit confused. My assumption (perhaps incorrect) is that the calculation of SWR on the Parameters and Main Results tab is due ONLY to the inputs on the lefthand side of the results on that page and is independent of what inputs I have entered on the Cash Flow Assist tab for Social Security and pension income. Is that correct?
A second somewhat related question is which inputs are used for the calculation of the SWR on the CAPE-Based Rules tab (line 10) and which failure rate is used to calculate the SWR (it appears to be about 10% if I’m reading things correctly)? Re the SWR calculation on THIS page I am assuming that inputs from the Parameters and Main Results and the Cash Flow Assist tabs are used. Yes?
Many thanks for clarifying,
Kevin
Unless you can show me actual sheets, I can’t guess as to what went wrong.
CAPE-based rules don’t have a failure rate. They never run out of money. But they risk cutting your retirment budget substantially.
Here are the sheets…
Before entering SS and pension:
https://docs.google.com/spreadsheets/d/1rrv6dHSAENNicIojaoSuBue6_bKh4H4rjYB7PcMOwdM/edit?gid=0#gid=0
After entering SS and pension:
https://docs.google.com/spreadsheets/d/1sOPHTg6Ix2Js0Z9uVp32fP_J_EgW_pkFyguVmvHGwBA/edit?gid=0#gid=0
Many thanks for any insight you can provide,
Kevin
Both files are “Access Denied”
But I can already sense what your mistake is: the change in the WR is not from the change in the CAPE but from other inputs. That, too, is not anything I’d be surprised about. If your SWR rises from 2.94% to 3.99% after including SS and pension then it’s as expected. Case closed.
Is there any reason I can’t use a 100% stock portfolio for the CAPE based rule, but keep the intercept at 1.75% and slope at .5 just as you did with the 80/20 portfolio?
What’s the downside of this?
You could do that, too. But the withdrawals become very volatile. If you’re fine with that, more power to you.
When you calibrate your intercept and slope are there certain metrics you target? Is it a specific drawdown %, annual volatility of withdrawals, or something else?
I have the tables and charts in the tab and I look for a) non-declining trend path of withdrawals, and b) low volatility and drawdowns.
It’s more art than science.
When making sure that the trend path is non-declining do you just look at the worst case retirement start or do you do an average of all possible retirement starts?
“worst case retirement start or do you do an average of all possible retirement starts?”
Both! 🙂
How do you use the number calculated by the CAPE-based rule when your income and spending is no homogeneous for the entire retirement period. For example maybe I want to spend $1000 more a month for travel before age 70, so I enter $1000 in each month in the cash flow. I also get $4000 in social security from age 62 (not saying it’s the right thing to do, just an example), so I add $4000 from age 70 + $2000 for spousal. I model I pass away at 90 so my spouse gets only $4000 for the year after I pass. My understanding is that the “Target Withdrawal ($/month)” is the smoothed spending. Let’s assume it’s $10K. at 61 I’d withdraw $10K+$1500? between 62 and 70 it’s be $10K-$4K-$2K+$1500 right? After I pass it’d be $10K-$4K? (the spousal SS and travel money are no longer there). so in fact if I assume my passing saves my spouse $1000 a month, I should add a cash flow of -$1000 between my death and the end of the plan to show this money is no-longer needed?
If you spend more than the baseline you’d enter this as a negative cash flow in the sheet.
If you have extra income you’d enter that as a positive cash flow.
Do you think CAPE can be used to calculate an SWR on a concentrated portfolio?
For example if I had 100% of my portfolio in NVIDIA, could i calculate a CAPE ratio for that individual stock using the same methodology and predict my SWR based on that?
I would not recommend retiring with a 100% NVDA portfolio, period. With or without CAPE. Too risky.
If you insisted, you’d probably end up with a very high CAPE ratio because the EPS from 3-10 years ago were essentially zero, according to this site: https://www.alphaquery.com/stock/NVDA/earnings-history.
So, with the CAPE-based earnings formula you’d end up with a SWR of WR=1.75%+0.5xCAEY and CAEY about 0, so WR a little bit above 1.75%.
If would be better to divest from NVDA and diversify into a broader portfolio. Hard to do in a taxable account, but wherever you can, i.e., IRA, Roth, etc., that would be highly recommended.
Great content. I am however confused with the spreadsheet result. for a basic 60/40, 1M nest egg, 40 Year horizon with no cash flow, the withdrawal % in cell B17 of the CAPE-Based Rule (4.61%) seems much higher than the main results tab and the cash flow assist tab (3.44%). How’s that possible given that the current CAPE2 is too high compared to historical average(32.55 – As 0f 7/20/25).
With the 7/18 CAPE of 32.55 I got an initial WR of 1.75%+0.5/32.55=3.29%. Then, applying capital depletion over 40y, I get 4.45%, not 4.61%.
But close enough.
Your CAPE-based WR is higher because you will face substantial volatility in your withdrawals. If a recession is long enough and deep enough you will probably cut your withdrawals by almost 50%. There is a tradeoff between the initial WR and stability of your retirement budget. If you insist on 100% certainty of never running out of money and never having to reduce your withdrawals you can withdraw only 3.4%.
The Klinger guard rail approach seems to be popular these days within the Project Lab software (I believe) which is discussed by many YouTube financial planners. Withdrawals near 5% to 5.5% are mentioned as safe as long as adjustments are made during downturns. The CAPE-based WR presumably generates a lower SWR due to the fact it evaluates the market condition in a bit more dynamic fashion (as opposed to the Klinger guardrail approach which I assume in some fashion just recalculates things using a Monte Carlo approach with a target say near 80%). Is this a fair statement and thus the CAPE-based approach is a bit more accurate?
Sorry I misspoke. Instead of Klinger guardrails I was referring to the more current risk-based guardrail approach.
Noted. I replied to the other comment assuming this was about risk-based guardrails
I generally like the risk-based guardrails. But they do not enable you to raise the SWR to 5.5% in today’s environment. Today’s valuations look close to the all-time-highs in historical simulations where the SWR was 4% and below. If a 4% static WR runs out of money over 30y and you start with 5.5% you risk having to drastically reduce your dynamic withdrawals along the way.
So my opinion of the Youtube financial planners is that they are 100% Youtubers and 0% financial planners. They value their clicks and views more than your retirement safety.
Not to be difficult but my sense is those following the risk-based guardrails are still claiming SWR of 5%+. I assume conceptually this is because it is not taking into account the current environment (as opposed to your CAPE-based SWR) and rather just using monte carlo to solve for something like 80% chance of success. So my original question was just to confirm my conceptual understanding of why your CAPE-based SWR is more accurate because of the following: CAPE-based SWR accounts for the current environment (thus utilized appropriate initial model conditions either good or bad) and honors historical data to develop more accurate SWRs (which are likely to have less fluctuations than the risk-based guardrails). Is this somewhat accurate?
I agree with your take here. The problem is Monte Carlo. I can certainly also calculate the WR that would give you a 20% failure rates. Unconditionally, that may be 5%, especially when the horizon is 30y. But conditional on today’s valuations, you don’t get to 5% even with 20% failures.
Hi, Karsten. I am digging into the “Case Study” tab and the CAPE columns at the far right. It looks like they do not incorporate cash flows from the “Cash Flow Assist” tab, if I understand correctly. I would like to add them in so I can see CAPE charts with cash flows (like the SWR charts with cash flows on the left side of the “Case Study” tab).
So I tried adding the cash flows by changing BF “CAPE Consumption (w/ depletion)” to include column G “Supplemental Cash Flow” like this:
=-G26+pmt(BD25/12,480-C25,-BE25-G26,$BE$8,1)
Is that the correct way to do it?
I haven’t implemented that feature (yet).
The formula you propose is not the way to do this, though.
Shoot, ok, I will wait for your implementation. Generally speaking, what changes would be required? I will play with the sheet and give them a try in the meantime.
Yes, the calculation is more complicated. We’d need to continuously calculate the PV of all future supplemental flows.
Might it look like this? I tried imitating the CAPE-based Rule tab’s PV formula inside the Case Study tab but in a way that can calculate it for each month.
https://docs.google.com/spreadsheets/d/1pbGjWO6LhN5C1WYQsXwuCcQQGXqtao1vjkOnJKgeyxM
– Added Case Study column BJ with PV calculation
=sum(map(‘Cash Flow Assist’!$A11:A$490,’Cash Flow Assist’!$P11:P$490,lambda(m,cf,cf*1/(1+BD25/12)^(m-1))))
– Changed column BF Cape Consumption to
=pmt(BD25/12,480-C25,-BE25-BJ26,$BE$8,1)
Yes, I’m 99% sure that’s what’s needed. We need to calculate the discounted sum of all cash flows from month t to the end, with the varying discount rate. Let me do some more checks, but this looks correct, indeed. Thanks for doing this!
Cool, glad it’s on the right track. 🙂 I think one update is that the consumption needs to actually incorporate column G Supplemental Cash Flow:
=pmt(BD25/12,480-C25,-BE25-BJ26,$BE$8,1)-G26
Noted! I will check that and see if I see any other bugs.
I also added a new tab “Case Study N” (could use a better name) to support a variable number of retirement months instead of only 480. Nothing fancy, just a batch edit of the formulas to include a check on the month number column generated as a sequence.
I also added a few extra columns with stock market returns (nominal and real) in order to help myself visually grasp the impact of SoR in bad times. For example, seeing how starting in 1965 with 50 year retirement horizon my portfolio including withdrawals would drop by more than 50% in less than 10 years (with 40 years left to go …) and stay there for decades (not recovering as much in the CAPE withdrawal case) helps me understand how I’d feel during a difficult retirement!
Hopefully I didn’t introduce any bugs, and perhaps these are helpful enhancements you might be interested in incorporating into your official version.
OK thanks! I’m traveling this week. But I will check it out when I’m back and I can use my big screens on my home PC again.
Tough to see all that on my small laptop.
Hi, Karsten. Hope your travels went well (or are still going well! :). Not to bother you, but just wanted to see if you had a chance to check out the formulas for correctness.
I also added some columns and charts to calculate and show the CAPE portfolio with glide path. I was surprised to find (assuming no bugs) that there is not much difference between the CAPE portfolios with and without GP. Not sure why, but I’m guessing this is because pmt() prevents excess returns from accumulating in the 100% stock portfolio.
Sorry, I dropped the ball on that. If you like, please provide your latest version of this and I can take a look. This is definitely a huge priority for me add to the toolkit.
Yes, no problem. See here
https://docs.google.com/spreadsheets/d/1pbGjWO6LhN5C1WYQsXwuCcQQGXqtao1vjkOnJKgeyxM
I made the changes in tab “Case Study N” in the CAPE section at far right.
– Changed formula in column BF
– Added various columns at the right to aid with some visuals in updated charts and investigate CAPE+GP
Thanks a lot! I’ve implemented your tab on my official online sheet.
A few changes I made:
1: your CAPE+GP portfolio time series was wrong. For example, BO26 reads: =if(C26<>“”,(BE25-BP26)*(1+I26),””) but it should read =if(C26<>“”,(BO25-BP26)*(1+I26),””) to iterate forward that portfolio value series, instead of reverting back to the baseline, No-GP portfolio series in column BE.
2: I cleaned up some of the charts. I also included some basic stats: CB5-CD14
3: I reverted back to the user entering the date as year and month. I also like the pull-down menu with all the worst-case scenarios, but sometimes I like to simulate a few months before/after those as well.
4: What you call CAPE Consumption in columns BF and BP is not really consumption but the net withdrawal from the portfolio. For example, in the formula after the PMT formula, you always have the -$BG$7 times column G term. That’s fine, because in the portfolio value calculation we need exactly that. But to get the actual consumption, we need to add column G again to go from withdrawals to actual consumption again. I relabeled the column headers.
5: I cut off the sheet at row 746 that would correspond to month 721. I don’t plan to use this beyond 60 years.
In any case, this is awesome. Thanks for making a huge improvement to this sheet.
(The comment nesting appears restricted, so replying here one level up.)
Oops, I knew I had some bugs in the calculations. 🙂 GP results make more sense now. Happy to make a little contribution to the spreadsheet since like so many others it’s helped me plan my FI, so I’m very thankful to you for that!
Regarding the date pull-down menu, it’s still possible to enter a custom date by typing yyyy-mm into the pull-down menu cell since the data validation settings have “show a warning”, which permits typing in the cell (but shows a warning).
I found another bug in my original formulas when adding cash flows and getting odd results. The PV formula in column BK has
cf*1/(1+BD25/12)^(m-1)
but it should use the month offset from the starting point, i.e.
cf*1/(1+BD25/12)^(m-‘Cash Flow Assist’!A11)
I updated the formula in my test spreadsheet column BK for your reference.
OK, thanks. I changed it in my version, too.
Also, I was puzzled whether the CAPE ratio needs to be inflation adjusted back to the case study starting year, but I convinced myself otherwise when I realized the 10 years of earnings are each inflation adjusted to the CAPE year, and then if all those earnings and the price are inflation adjusted to the case study starting year, those inflation factors cancel out. Probably obvious to you, but please let me know if I’m mistaken.
Good question. CAPE is not inflation-adjusted. It’s a ratio, and the $ drops out, so it’s no longer nominal. That way it’s comparable across time.
Thanks!
I will mull that over if I want to reinstate the date pull-down menu.
Hmm, I am blanking out on whether I already asked this, sorry if I did.
I wonder if there’s some error in my calculations for the SWR to deplete
Portfolio = 100 (example)
Years = 40
CAPE = 39.65
SWRp = 0.015 + (0.5 / CAPE) = 2.76% (preservation SWR?)
SWRd = PMT(SWRp,Years,-Portfolio,0,1) / Portfolio ~= 4.05% (depletion SWR?)
Even with the most conservative SWR you use, 0.01 + 0.5 / CAPE, it’s ~3.74%
Are such high withdrawal rates expected? I assumed with CAPE 40 it should be south of 3.25%…
The CAPE-based WR is higher than the constant (fail-safe) rate. That’s because the Failsafe should work without any reduction in spending (unless you believe the future will be worse than 1929-1932 or 1968-1982). On the other hand, the CAPE-based rule will potentially cut your spending significantly if there’s a bear market.
Is there an explanation for why the PV of supplemental income is multiplied by .5 by default in the spreadsheet e.g. only 50% of it is applied? Wouldn’t you want the full value?
Where is that 0.5 factor applied? I don’t see that.
Whoops sorry to waste your time ERN. I just copied the sheet again and don’t see it. I guess I added it for reasons I now don’t remember.
No problem. Thanks for checking.
Running the CAPE withdrawal gives a Target Withdrawal ($/Month). Would you use this amount for remaining months of the year then recalculate for the next year?
This is designed as a monthly exercise so every month you will run the estimations and withdraw just that month. Remember to decrease by 1 the Retirement Horizon (months)
If you want to reduce the amount of work, you could choose to use a month’s estimation and withdraw the same amount for consecutive months, do the estimation every three months, but you would be responding to changes more slowly and increasing risks and probably withdraw volatility.
Good compromise! We don’t have to do the calculations every month.
Thanks for the help!
The simulations assume you calculate the withdrawal % every month, which will imply a different $ withdrawal amount every month.
I’ve found the Toolbox to be a real eye opener. I’m 76 years old and well into retirement. I find that my spending is way below (like 20%) what the Target Withdrawal says I could spend right now. Even if there was a 30% market drawdown, my wife and I could live comfortably. It’s hard to change gears from saving and investing for more than 38 years. I guess that I should spend it while I can enjoy it.
Great! Glad you could use this to increase your withdrawals!
Thanks for everything, ERN! We may have large RMDs coming in the future. How do you suggest we account for the related taxes in the toolbox? I suspect we should somehow estimate those and input them as extra expenses? We are likely going to reinvest what we pull from RMDs and not spend it. In other words, we aren’t going to consume those extra withdrawals but of course still need to pay taxes on them. Thanks again.
You calculate your average blended, weighted tax rate out of all accounts and apply an adjustment to your gross withdrawals to get your net withdrawals.
Thank you, sir. I was able to estimate what those giant RMD triggered taxes might be 25 years in the future and entered them into the Cash Flow Assist > expenses adjusted for inflation column. All taxes before that naturally fit into the failsafe withdrawal rate $, so doesn’t make sense to add those in the same column as it will inflate the withdrawal rate. Hope this makes sense?
Two replies:
1: you may want to do some Roth conversions before that.
2: If you absolutely insist, sure consider the RMD. Imagine you expect RMDs of $200,000 but you need only $120,000.
Take the portion of the RMD not needed ($80k) apply your marginal tax (e.g., 25%) and model the RMDs as -20k annual cash flow.