Faster and Easier SWR Calculations

We can take advantage of the linearity between withdrawal rates, the percentage earnings yield 100E10/P and portfolio balances to speed up data collection and to conduct detailed analysis.

My Standard Procedure

Here is my standard procedure for calculating Safe Withdrawal Rates.

1) I collect Historical Surviving Withdrawal Rate HSWR data on one of my calculators. I start with a balance of $100000. I increase the withdrawal rate in increments of 0.1%. I record the rate for each year when the portfolio would have first failed. I subtract 0.1% and report the result as the Historical Surviving Withdrawal Rate. Most of the time, for reasons of data analysis, I use a portfolio time span of 30 years.
2) I use these data to construct a table in an Excel spreadsheet. It lists the year, the percentage earnings yield 100E10/P in January of that year and the Historical Surviving Withdrawal Rate for that year.
3) I use Excel to make a scatter plot of HSWR versus the percentage earnings yield 100E10/P. I have Excel fit a straight line (i.e., make a linear fit) to the data, display the equation and report its goodness of fit (R-squared). I refer to the straight line as the Calculated Rate.
4) I construct confidence limits. Although I can calculate the confidence limits, I am usually satisfied with eyeball estimates. You can do this by drawing lines parallel to the line with the Calculated Rates. Draw the lines close to covering the range of the data, but not any wider. The scatter increases as the percentage earnings yield increases. I place my greatest emphasis on the data with low percentages of earnings yields (below 8% or 10%).
5) The Safe Withdrawal Rate is the lower confidence limit. The High Risk Rate is the upper confidence limit.
6) The most likely outcome associated with a specified percentage earnings yield is the Calculated Rate. The odds are (roughly) 95% that the actual outcome will be higher than the Safe Withdrawal Rate. The odds are (roughly) 95% that the actual outcome will be lower than the High Risk Rate.

A Faster and Easier Procedure

How can we make this faster? And easier? We can take advantage of linearity.

We can fit lines to portfolio ending balances at year 30. Each line requires only a single application of the calculator.

If the linearity were perfect, two lines would be sufficient. But linearity is not perfect. We should normally collect data at withdrawal rates of 3.0%, 4.0%, 5.0% and 6.0%.

Next, copy the real balances at year 30 from the calculator and paste them into a spreadsheet. Prepare the spreadsheet in advance with columns listing each start year and its percentage earnings yield 100E10/P in that year. Paste the four sets of data on the right.

Make Excel charts of balances at year 30 versus earnings yield at withdrawal rates of 3.0%, 4.0%, 5.0% and 6.0%. Have Excel report the formulas and R-squared values.

Estimate confidence limits visually.

Using the equations and confidence limits and algebra, calculate a host of Withdrawal Rate and Safe Withdrawal Rate related information.

An Example with 50% Stocks

I used the HDBR50 portfolio. It consists of 50% stocks and 50% commercial paper. Its expenses are set at 0.20%. It is rebalanced annually.

I started with a balance of $100000.

Here are the equations at year 30 along with eyeball estimates of the confidence intervals. They are the equations for the Calculated Rates. y and the percentage earnings yield 100E10/P is x.

When the withdrawal rate is 3.0%, the Calculated Rate equation is:
y = 12711x+47014.
When the withdrawal rate is 4.0%, the Calculated Rate equation is:
y = 16078x-36082.
When the withdrawal rate is 5.0%, the Calculated Rate equation is:
y = 19483x-119533.
When the withdrawal rate is 6.0%, the Calculated Rate equation is:
y = 23021x-204381.

Here are my eyeball estimates of the confidence limits:
With a withdrawal rate of 3.0%, the confidence limits are minus 50000 and plus 60000.
With a withdrawal rate of 4.0%, the confidence limits are minus 50000 and plus 60000.
With a withdrawal rate of 5.0%, the confidence limits are minus 50000 and plus 50000.
With a withdrawal rate of 6.0%, the confidence limits are minus 70000 and plus 50000.

Subtract the lower confidence limit to calculate Safe Withdrawal Rates. These are the equations for Safe Withdrawal Rates.

When the withdrawal rate is 3.0%, the Safe Withdrawal Rate equation is: y = 12711x-2986.
When the withdrawal rate is 4.0%, the Safe Withdrawal Rate equation is: y = 16078x-86082.
When the withdrawal rate is 5.0%, the Safe Withdrawal Rate equation is: y = 19483x-169533.
When the withdrawal rate is 6.0%, the Safe Withdrawal Rate equation is: y = 23021x-274381.

Add the upper confidence limit to calculate High Risk Rates. Here are the equations for High Risk Rates.

When the withdrawal rate is 3.0%, the High Risk Rate equation is: y = 12711x+107014.
When the withdrawal rate is 4.0%, the High Risk Rate equation is: y = 16078x+23918.
When the withdrawal rate is 5.0%, the High Risk Rate equation is: y = 19483x-69533.
When the withdrawal rate is 6.0%, the High Risk Rate equation is: y = 23021x-154381.

We can calculate Half Value Calculated Rates at year 30 by setting the final balance to one-half of the initial balance (i.e., 50000) instead of zero. [However, we cannot use these equations to calculate Half Failure Rates. They allow the balance to fall to one-half of the initial balance at any time. The minimum balance does not have to occur in year 30.] We can calculate Constant Terminal Value Calculated Rates by setting the final balance equal to the initial balance of 100000. We add and subtract the confidence limits about these rates as well to get Half Value Safe Withdrawal Rates, Half Value High Risk Rates, Constant Terminal Value Safe Withdrawal Rates and Constant Terminal Value High Risk Rates.

With all of these equations, we have exact values of y, which are 3.0% and 4.0% and 5.0% and 6.0%, and we determine their corresponding earnings yield (100E10/P), which are values of x.

In short, we can calculate a host of rates. They hold up as well as the linearity approximation holds up.

Continuing the Example with 50% Stocks

Now we determine Calculated Rates.

When we use the equation derived from a 3.0% withdrawal rate, the final balance is zero when the percentage earnings yield is x=-3.70%.
When we use the equation derived from a 4.0% withdrawal rate, the final balance is zero when the percentage earnings yield is x=2.24%.
When we use the equation derived from a 5.0% withdrawal rate, the final balance is zero when the percentage earnings yield is x=6.14%.
When we use the equation derived from a 6.0% withdrawal rate, the final balance is zero when the percentage earnings yield is x=8.88%.

Now we determine Safe Withdrawal Rates.

When we use the equation derived from a 3.0% withdrawal rate, the final balance is zero when the percentage earnings yield is x=0.23%.
When we use the equation derived from a 4.0% withdrawal rate, the final balance is zero when the percentage earnings yield is x=5.35%.
When we use the equation derived from a 5.0% withdrawal rate, the final balance is zero when the percentage earnings yield is x=8.70%.
When we use the equation derived from a 6.0% withdrawal rate, the final balance is zero when the percentage earnings yield is x=11.92%.

Now we determine High Risk Rates.

When we use the equation derived from a 3.0% withdrawal rate, the final balance is zero when the percentage earnings yield is x=-8.42%.
When we use the equation derived from a 4.0% withdrawal rate, the final balance is zero when the percentage earnings yield is x=-1.49%.
When we use the equation derived from a 5.0% withdrawal rate, the final balance is zero when the percentage earnings yield is x=3.57%.
When we use the equation derived from a 6.0% withdrawal rate, the final balance is zero when the percentage earnings yield is x=6.71%.

I could calculate a full set of Half Value Rates, but I will limit myself to Half Value Safe Withdrawal Rates. For these calculations, I calculate the earnings yield x that would produce a final balance y of 50000. I use the set of equations for Safe Withdrawal Rates.

Now we determine Half Value Safe Withdrawal Rates.

When we use the equation derived from a 3.0% withdrawal rate, the final balance is 50000 (or one-half of the initial balance) when the percentage earnings yield is x=4.17%.
When we use the equation derived from a 4.0% withdrawal rate, the final balance is 50000 (or one-half of the initial balance) when the percentage earnings yield is x=8.46%.
When we use the equation derived from a 5.0% withdrawal rate, the final balance is 50000 (or one-half of the initial balance) when the percentage earnings yield is x=11.27%.
When we use the equation derived from a 6.0% withdrawal rate, the final balance is 50000 (or one-half of the initial balance) when the percentage earnings yield is x=14.09%.

Continuing, I limit myself to presenting the Constant Terminal Value Safe Withdrawal Rates. For these calculations, I calculate the earnings yield x that would produce a final balance y of 100000. I use the set of equations for Safe Withdrawal Rates.

Now we determine Constant Terminal Value Safe Withdrawal Rates.

When we use the equation derived from a 3.0% withdrawal rate, the final balance is 100000 (which equals the initial balance) when the percentage earnings yield is x=8.10%.
When we use the equation derived from a 4.0% withdrawal rate, the final balance is 100000 (which equals the initial balance) when the percentage earnings yield is x=11.57%.
When we use the equation derived from a 5.0% withdrawal rate, the final balance is 100000 (which equals the initial balance) when the percentage earnings yield is x=13.93%.
When we use the equation derived from a 6.0% withdrawal rate, the final balance is 100000 (which equals the initial balance) when the percentage earnings yield is x=16.26%.

Applying Excel For the Final Calculations

We use Excel once again to make some final calculations to present the results in the form that we want. The most useful form is to report withdrawal rates as a function of earnings yield. This is exactly the opposite of the data that we have collected.

Calculated Rates

We use Excel once again, this time to convert our four data points (in each set) into a convenient line.

Our four data points for Calculate Rates are:

1) A 3.0% withdrawal rate and a –3.70% Calculated Rate.
2) A 4.0% withdrawal rate and a 2.24% Calculated Rate.
3) A 5.0% withdrawal rate and a 6.14% Calculated Rate.
4) A 6.0% withdrawal rate and an 8.88% Calculated Rate.

Excel calculates the following equation, where x is the percentage earnings yield (which corresponds to the calculated rate) and y is the corresponding withdrawal rate. Here is the equation.

y = 0.2332x+3.7096
R-squared = 0.9709

Safe Withdrawal Rates

We use Excel to convert another four data points (in each set) into Safe Withdrawal Rates.

Our four data points for Safe Withdrawal Rates are:

1) A 3.0% withdrawal rate and a 0.23% Safe Withdrawal Rate.
2) A 4.0% withdrawal rate and an 5.35% Safe Withdrawal Rate.
3) A 5.0% withdrawal rate and an 8.70% Safe Withdrawal Rate.
4) A 6.0% withdrawal rate and an 11.92% Safe Withdrawal Rate.

Excel calculates the following equation, where x is the percentage earnings yield (which corresponds to the Safe Withdrawal Rate) and y is the corresponding withdrawal rate. Here is the equation.

y = 0.2567x+2.8188
R-squared = 0.9861

High Risk Rates

We use Excel to convert another four data points (in each set) into High Risk Rates.

Our four data points for High Risk Rates are:

1) A 3.0% withdrawal rate and a -8.42% High Risk Rates.
2) A 4.0% withdrawal rate and a –1.49% High Risk Rates.
3) A 5.0% withdrawal rate and a 3.57% High Risk Rates.
4) A 6.0% withdrawal rate and a 6.71% High Risk Rates.

Excel calculates the following equation, where x is the percentage earnings yield (which corresponds to the High Risk Rate) and y is the corresponding withdrawal rate. Here is the equation.

y = 0.1928x+4.4822
R-squared = 0.9726

Half Value Safe Withdrawal Rates

We use Excel to convert another four data points (in each set) into Half Value Safe Withdrawal Rates.

Our four data points for Half Value Safe Withdrawal Rates are:

1) A 3.0% withdrawal rate and a 4.17% Half Value Safe Withdrawal Rate.
2) A 4.0% withdrawal rate and an 8.46% Half Value Safe Withdrawal Rate.
3) A 5.0% withdrawal rate and an 11.27% Half Value Safe Withdrawal Rate.
4) A 6.0% withdrawal rate and an 14.09% Half Value Safe Withdrawal Rate.

Excel calculates the following equation, where x is the percentage earnings yield (which corresponds to the Half Value Safe Withdrawal Rate) and y is the corresponding withdrawal rate. Here is the equation.

y = 0.3033x+1.6193
R-squared = 0.9879

Constant Terminal Value Safe Withdrawal Rates

We use Excel to convert another four data points (in each set) into Constant Terminal Value Safe Withdrawal Rates.

Our four data points for Constant Terminal Value Safe Withdrawal Rates are:

1) A 3.0% withdrawal rate and an 8.10% Constant Terminal Value Safe Withdrawal Rate.
2) A 4.0% withdrawal rate and an 11.57% Constant Terminal Value Safe Withdrawal Rate.
3) A 5.0% withdrawal rate and a 13.93% Constant Terminal Value Safe Withdrawal Rate.
4) A 6.0% withdrawal rate and a 16.26% Constant Terminal Value Safe Withdrawal Rate.

Excel calculates the following equation, where x is the percentage earnings yield (which corresponds to the Constant Terminal Value Safe Withdrawal Rate) and y is the corresponding withdrawal rate. Here is the equation.

y = 0.3687x-0.0953
R-squared = 0.9895

Current Rates

Today’s percentage earnings yield is 3.5% (with P/E10 around 28 to 29).

Today’s Calculated Rate is: 4.53%.
Today’s Safe Withdrawal Rate is: 3.72%.
Today’s High Risk Rate is: 5.16%.
Today’s Half Value Safe Withdrawal Rate is: 2.68%.
Today’s Constant Terminal Value Safe Withdrawal Rate is: 1.20%.

Cautions

These results depend heavily upon linearity. Looking at the R-squared values of the final equations, however, this was not a serious problem.

Our historical sequence calculators produce unreliable outputs after a balance becomes negative. The issue is how do you treat a stock market gain (such as a 20% gain) on a negative investment balance? Fortunately, this did not lead to serious difficulties either.

Conclusions

We can take advantage of the linearity of withdrawal rates versus the percentage earnings yield 100E10/P to simplify the collection and analysis of detailed withdrawal rate information.

We can copy and paste the balances at year 30 (or at some other number of years) at a few withdrawal rates using our calculators to generate a complete set of withdrawal rate projections. This includes Safe Withdrawal Rates, Calculated Withdrawal Rates, High Risk Rates, Half Value Rates of all types (safe, calculated and high risk) and Constant Terminal Value Rates of all types (safe, calculated and high risk). In the example, I was able to generate a full set of data with only four withdrawal rate inputs (3.0%, 4.0%, 5.0% and 6.0%) to the calculator.

Have fun.

John Walter Russell
I wrote this on May 8, 2005.