I have modified a copy of the Retire Early Safe Withdrawal Rate Calculator (modified from version 1.61 of November 7, 2002). The original version allows you to vary allocations of a stock/commercial paper portfolio based on P/E10. My modified calculator is identical except that I have changed the portfolio mixture to stocks and TIPS (Treasury Inflation Protected Securities).
I manually changed the formula for all of the cells in row 182. That row does the actual switching. For example, the old formula for cell D182 was:
=IF($F$19<C186,+(D177*(1-$I$20)+$I$20*(((D184-C184)/C184*100)+(D185/D184*100))),+((D177*(1-$B$20)+$B$20*(((D184-C184)/C184*100)+(D185/D184*100)))))
The new formula for cell D182 is:
=IF($F$19<C186,+(D180*(1-$I$20)+$I$20*(((D184-C184)/C184*100)+(D185/D184*100))),+((D180*(1-$B$20)+$B$20*(((D184-C184)/C184*100)+(D185/D184*100)))))
I changed D177 to D180 in two places. I changed 177 to 180 twice in all of the remaining cells on row 182. Row 177 lists the return for commercial paper. Row 180 lists the return for TIPS.
I did all of this manually. To change cell D182, I highlighted it. The equation was shown in the formula bar (i.e., top of the screen in the tool bar area). I clicked on each 177 and changed it to 180 (in two places).
Notice that the formula shows $F$19, $I$20 and $B$20. Those correspond to cells F19, I20 and B20 respectively. Notice that there is no mention of cells I19 (or $I$19) and F20 ($F$20). Those are two of the cells in which you enter your switching criteria. Neither the "Switch to High PE"Â threshold nor the allocation for "Mid PE Years" have any influence on the results. The calculator does use the "Low PE to Mid PE"Â entry to change allocations. The calculator uses the "Stock Allocation for Low PE" and the "High PE Years"Â entries for allocations.
If my memory serves me correctly, intercst (the creator of the Retire Early Safe Withdrawal Rate Calculator) originally wrote a program that used two levels for switching and it worked. He tried later to upgrade the calculator for three levels, but it had bugs. He has now returned to his original formula (in version 1.61).
For those who wish to make similar modifications (perhaps for ibonds or other treasuries), it is important to copy the entire program. I know from experience that the rectangle defined by A1 and EY2540 is sufficient. The rectangle defined by A1 and EX2537 should be sufficient. I caused myself some unneeded grief early on, when I failed to copy the entire program.
When using the program, remember that the TIPS suffer the same expense ratio as stocks. Thus, if you put in a 2.8% interest rate with the 0.20% expense ratio (i.e., the default value), you are actually getting 2.6% interest (plus adjustments for inflation).
The program outputs are not nearly as easy to study as those of the FIRECalc. OTOH, the outputs are much more comprehensive than those of the FIRECalc. Although I have collected some preliminary data, I will need to look at individual years to get a full understanding of what is happening. In particular, I will need to isolate the results from 1871-1920 for analysis purposes. I will need to identify the reasons that the data behave as they do. We need to keep in mind that today's stocks differ in important quantitative respects from those of previous decades. We need to remember as well that today's valuations are outside of the historical range. Historical Database Rate results do not translate directly into Safe Withdrawal Rates.
I have some preliminary results. I have found that switching (over the limited range that I have examined) has reduced the 30-year survivability (for "100% safety"Â) below that of TIPS alone (with interest rates of 2.6% and 2.8%). But it increases the allowable withdrawal rate (at the "100% safety"Â level) increasingly for 40-year, 50-year and 60-year portfolio lifespans. View this behavior with caution. The "100% safe"Â level introduces artifacts (via granularity) in the model.
Switching clearly shows potential. Here are some results using a 4.19% withdrawal rate. TIPS with an interest rate of 2.8% (and no expenses) will last exactly 40 years. At the end of 40 years, the portfolio balance will be zero.
Using an 80% stock allocation at a low P/E10 level and TIPS at 2.8% interest (or 2.6% after expenses), this table shows some early survivability results (covering the entire time period):
Code: Select all
Years Sw12&20% Sw16&20% Sw16&30% Sw20&30%
30 100% 100% 100% 100%
40 98% 100% 100% 100%
50 91% 100% 100% 100%
60 86% 97% 99% 100%
Here are some additional results. For a switching level of 16 or 20 and a 30% stock allocation at high levels of P/E10, the survivability numbers remain the same with 80% and 90% stock allocations at low P/E10 levels. For a switching level of 20 and an 80% stock allocation at low levels of P/E10, the survivability numbers remain the same with 0% through 30% stock allocations at high levels of P/E10.
The message that I get from the table is that switching allocations can extend a 40-year portfolio to a 60-year portfolio. I expect to be able to draw stronger conclusions as I look more closely into the data. I mention once again that it is necessary to extract cause and effect relationships going forward. Stock dividends and valuations are well outside of the historical ranges and the TIPS that are modeled are largely hypothetical. There are known errors if you project Historical Database Rates directly to Safe Withdrawal Rates under these circumstances.
Have fun.
John R.