Deluxe Calculator V1.1A04

Research on Safe Withdrawal Rates

Moderator: hocus2004

Post Reply
JWR1945
***** Legend
Posts: 1697
Joined: Tue Nov 26, 2002 3:59 am
Location: Crestview, Florida

Deluxe Calculator V1.1A04

Post by JWR1945 »

These are the latest updates to my calculator, Deluxe Calculator V1.1A04. My calculators consist of a series of modifications to the Retire Early Safe Withdrawal [Rate] Calculator, Version 1.61, 7 November 2002.

Handy Cell References for Easier Data Analysis

The first part of the update includes handy references for data analysis. On each of the data summary boxes in rows 1-9 and columns B through BD, I have written cell locations to get to the raw data. For example, in the PORTFOLIO BALANCE summary, I have written EO2600 in cell Z1 and EO3200 in cell Z3. If you press the special function key F5, type in EO2600 and then press enter, you will move to the data summary area for PORTFOLIO BALANCES in nominal dollars. If you scroll to the right, you will find a data summary box for nominal dollars. If you scroll down instead, you will see a list of events according to their years. The number 1 appears when a failure occurs within a stated number of years (i.e., 10, 20, 30, 40, 50 or 60 years) and at the same time occurs within the date range established by cells M1 and M2. All other entries are zero. The total number of 1's in a column is the number of failures for the number of years for the column.

Pressing the special function key F5 and entering EO3200 takes you to the corresponding location for real dollar PORTFOLIO BALANCES.

The locations are written for ease of entry when repeating a large number of times. It is easier to type in EO than a better column location because both E and O are on the same line on the keypad. The rows are round numbers because they are easier to remember than more precise locations. That is, it is easier to type in EO3200 than it would be to type in EL3198, especially when you must repeat this step many times.

To return to the data entry area, press CTRL+Home. This takes you to the beginning of the spreadsheet at location A1. Then scroll to the right to the extent needed.

Rolling Averages for the Real TOTAL AMOUNT WITHDRAWN

Many withdrawal schemes produce large fluctuations in the amount of money withdrawn. I have calculated the 4-year and 5-year rolling averages for the TOTAL AMOUNT WITHDRAWN in real dollars.

The 4-year rolling averages are presented in the rectangle defined by EY6097 and EY6228 and HD6097 and HD6228 with blanks in the triangle of locations defined by HD6179 and FG6228 and HD6228. In cell EZ6099, the formula is =AVERAGE(C6099,D6099,E6099,F6099) The 4-year rolling averages are presented for years ending from 4 years to 60 years.

The 5-year rolling averages are presented in the rectangle defined by HG6097 and HG6228 and IQ6097 and IQ6228 with blanks in the triangle of locations defined by IQ6199 and HN6228 and IQ6228. In cell HH6099, the formula is =AVERAGE(C6099:G6099) The 5-year rolling averages are presented for years ending from 5 years to 40 years.

I used the fill handle down and then from left to right to apply the formula to the other cells.

Either form of the AVERAGE function may be used for calculating either rolling average.

Revised Formulas for Interest Calculations and Reinvestments

An issue comes up when using the Interest Reinvestments feature in cell B23. I was withdrawing principal as well as interest with TIPS and I-Bonds. In addition, I have corrected an error in the I-Bond interest calculation.

Bond Interest is calculated by multiplying an initial bond balance by the FI Interest Rate on row 183. For example, the Bond Interest Paid in 1872 (for the 1871 sequence) is found in cell C209. The bond balance at the beginning of January is in cell C208. [The first part of withdrawals and expenses are removed from the year's initial balance. What remains is placed in cell C202. The stock allocation is placed in cell C203. The bond allocation is in cell C208.] The Interest Paid formula in cell C209 is =C208*C$183/100.

If we look at the formula for cell location C183 in its original form, it was =IF($B$7>1,(IF($B$7>2,(IF($B$7>3,(IF($B$7>4,(IF($B$7>5,+C182,+C181)),+C180)),+C179)),+C178)),+C177).

This seems complicated, but it is just a matter of which row of interest rates to use. The choices are determined by the entry in cell B7, the Fixed Income Series. The numbers in B7 select commercial paper (row 177), 5-year treasury notes (row 178), 30-year treasury bonds (row 179), TIPS (row 180), I-Bonds (row 181) or the Switching Option (row 182).

When I added the ability to change the amount of Interest Reinvestments by putting a percentage in cell location B23, I simply changed the formula in cell location C183 to =IF($B$7>1,(IF($B$7>2,(IF($B$7>3,(IF($B$7>4,(IF($B$7>5,+C182,+C181)),+C180)),+C179)),+C178)),+C177)*$B$23.

This works fine for commercial paper, 5-year treasury notes and 30-year treasury bonds.

But the TIPS and I-Bonds change principal as well as interest. These changes are in rows 180 and 181, respectively. Notice that any change of principal must be put into the Interest Paid location in a historical sequence (such as row 208 and cell location C208 for the year 1872). There is no separate place for putting in changes of principal.

What was happening is that my Interest Reinvestments entries in cell B23 included non-cash changes in principal as well as the interest from coupons.

Similar effects applied to I-Bonds (row 181) and the Switching Option (row 182).

I made my changes within the formulas for rows 180, 181 and 182.

The final equation in cell C183 is =IF($B$7>1,(IF($B$7>2,(IF($B$7>3,(IF($B$7>4,(IF($B$7>5,+C182,+C181)),+C180)),+C179*$B$23)),+C178*$B$23)),+C177*$B$23).

I used the fill handle to drag the formula to the end of the row. [To be very precise, I entered the revised formula into cell B183 and dragged its formula to the end of the row, which included cell C183.]

Row 180 is for TIPS. The revised formula for cell C180 is =(($H$8*$B$23*(C190/B190)+(C190/B190-1))*100). (Cell B180 has no formula. It is set to equal whatever is in cell C180.)

Row 190 has inflation index numbers. The ratio of index values C190/B190 equals (1 + inflation). The interest coupon rate is in cell H8.

Row 181 is for I-Bonds. The revised formula for cell C181 is =IF((C190/B190-1)<=0,$H$8*$B$23,(($H$8*$B$23*(C190/B190)+(C190/B190-1))*100)). This includes a correction to the original formula.

When there is deflation, the principal of I-Bonds is left unchanged but the coupon still gets paid. The interest rate of the coupon is in cell location H8. The Interest Reinvestment is in cell B23.

When there is inflation, the principal amount of I-Bonds is allowed to increase in the same way as with TIPS. The ratio C190/B190 equals (1 + inflation) and (C190/B190-1) equals (inflation alone). The factor of 100 occurs because some numbers are in percentages (but not in percentage formats) internal to the spreadsheet (and others are in percentage format).

[Originally, the coupon was reduced during deflation. Only the combination of interest and inflation was limited to zero.]

Row 183 is labeled Stock Switch by P/E. Stock allocations are changed according to the level of Professor Shiller's P/E10 measure of valuation. To accommodate multiple levels of switching, the formula makes use of new formulas in rows 2550, 2552 and 2553. This is the formula in cell C182. =IF($B$19<B186,IF($F$19<B186,C2552,+(C2553*(1-$B$20)+$B$20*C2550)),C2550) The entries in cells B19, F19, H19 and I19 are switching thresholds and the values in cells B20, F10 and I20 are allocations. [The allocation when P/E10 is below the threshold in cell B19 is 100%. The allocation when P/E10 is above the threshold in cell I19 is 0%.]

Row C2550 has annual stock gains with dividends reinvested. [The amount of dividends reinvested in cell B22 is already taken into account in the calculations in row 185. There is no need for additional changes related to dividend withdrawals and the switching of stock allocations.] Row 2552 places more thresholds and allocations into the original formula (in row 182). This is the formula for cell C2552: =IF($H$19<B186,IF($I$19<B186,C2553,+(C2553*(1-$I$20)+$I$20*C2550)),+(C2553*(1-$F$20)+$F$20*C2550))

Row C2553 determines whether the investment other than stocks is commercial paper (from row 177) or TIPS (from row 180) or I-Bonds (from row 181). This is the formula for cell C2553: =IF($F$22>2,C181,IF($F$22>1,C180,C177*$B$23)). That is, the selections of TIPS (row 180) and I-Bonds (row 181) already incorporate the Interest Reinvestments in cell B23 and requires no change. But the selection of commercial paper (row 177) requires a separate multiplication by the value in cell B23.

It was necessary to make changes in the data reduction area in rows 4985 through 4997. Row 4985 is a new row titled TIPS Coupon Component. The formula for cell C4985 is =$H$8*(C190/B190).

Row 4986 is a new row titled Ibond Coupon Component. The formula in cell C4986 is =IF((C190/B190-1)<=0,$H$8,$H$8*(C190/B190)).

Row 4995 is titled Interest Rates. This calculates the interest coupon. It excludes any additional adjustment to principal caused by inflation. The formula in cell C4995 is =IF($B$7>1,(IF($B$7>2,(IF($B$7>3,(IF($B$7>4,(IF($B$7>5,+C182,+C4986)),+C4985)),+C179)),+C178)),+C177)*100. Notice that C4985 has replaced C180 and that C4986 has replace C181.

Row 4996 is titled Interest Reinvested. The formula in cell C4996 is very simple: =C4995*$B$23, where cell B23 contains the interest reinvestment percentage.

Row 1997 is titled Interest Withdrawn. The formula in cell C4997 is =C4995*(1-$B$23).

Summary

These changes make data reduction easier. They correct the formula for I-Bonds interest. They limit withdrawals of interest from TIPS and I-Bonds to a fraction of the coupons received. They exclude any non-cash adjustments to principal from these withdrawals.

This calculator is available for downloading from the special SWR Research section.

Have fun.

John R.
Mike
*** Veteran
Posts: 278
Joined: Sun Jul 06, 2003 4:00 am

Post by Mike »

Thanks John.
JWR1945
***** Legend
Posts: 1697
Joined: Tue Nov 26, 2002 3:59 am
Location: Crestview, Florida

Post by JWR1945 »

Deluxe Calculator V1.1A05

I have uncovered some bugs now that the TIPS and I-Bonds are working correctly.

I have corrected the following data reduction formula. The factor of 100 must be brought in to multiply entries in cells C4986 and C4985 but not the others.

Row 4995 is titled Interest Rates. This calculates the interest coupon. It excludes any additional adjustment to principal caused by inflation. The formula in cell C4995 is =IF($B$7>1,(IF($B$7>2,(IF($B$7>3,(IF($B$7>4,(IF($B$7>5,+C182,+C4986)),+C4985)),+C179)),+C178)),+C177)*100. Notice that C4985 has replaced C180 and that C4986 has replace C181.

The new formula in cell C4995 is:
=IF($B$7>1,(IF($B$7>2,(IF($B$7>3,(IF($B$7>4,(IF($B$7>5,+C182,+C4986*100)),+C4985*100)),+C179)),+C178)),+C177)

I have corrected adjustments for inflation in rows 6100, 6500, 6700, 7000 and 7200.

I corrected the adjustment for the total withdrawals. I wrote =C5899*C3001 into cell C6099. I used the fill handle to drag the formula throughout the table.

I change the adjustment for dividends withdrawn from =C5000*C3001/$C3001 in cell C6500 to =C5000*C3001. I used the fill handle to drag the new formula throughout the table.

I changed the adjustment for interest withdrawn from =C5200*C3001/$C3001 in cell C6700 to =C5200*C3001. I used the fill handle to drag the new formula throughout the table.

I changed the adjustment for the initial stock balance in cell C7000 to =C4800*B3001. I used the fill handle to drag the new formula throughout the table.

I changed the adjustment for the initial bond balance in cell C7200 to =C4800*B3001. I used the fill handle to drag the new formula throughout the table.

Have fun.

John R.
JWR1945
***** Legend
Posts: 1697
Joined: Tue Nov 26, 2002 3:59 am
Location: Crestview, Florida

Post by JWR1945 »

I have just now uploaded my latest version Deluxe Calculator V1.1A08 from November 20, 2004.

I have corrected several bugs.

Most of these involve the detailed analysis tables with inflation adjustments. No changes were required in any of the tables involving nominal amounts. No changes were required in the basic real dollar balances and annualized real rates of return beginning at A3200 and A3400.

Have fun.

John R.
Mike
*** Veteran
Posts: 278
Joined: Sun Jul 06, 2003 4:00 am

Post by Mike »

Thanks again John.
JWR1945
***** Legend
Posts: 1697
Joined: Tue Nov 26, 2002 3:59 am
Location: Crestview, Florida

Post by JWR1945 »

I have just now uploaded Deluxe Calculator V1.1B08.

This is an abridged edition of Deluxe Calculator V1.1A08. The A version expands to 15.8 Mb after downloading. The B version expands to 11.0 Mb after downloading.

The B version adds no new features. It removes some data analysis capabilities to reduce its size.

Have fun.

John R.
Post Reply