Description of JWR1945's "Gummy 02 Calculator"

Research on Safe Withdrawal Rates

Moderator: hocus2004

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

Description of JWR1945's "Gummy 02 Calculator"

Post by JWR1945 »

I have not finished checking this new calculator. It adds even more capability to my latest calculator. I will be making it available at the special SWR Research section in the near future.

Gummy 02 Calculator Description

This gives some of the implementation details regarding Gummy 02 version of the Deluxe Calculator V1.1A08 dated January 24, 2005.

I will only describe the Gummy Algorithm 1. I have added an outline of the implementation details for Gummy Algorithm 2.

P/E10 is Professor Shiller's measure of valuation. It is the current price (index value) of the S&P500 divided by the average of the last decade of earnings. [The price term and all of the earnings terms are adjusted for inflation in accordance with the CPI.] The percentage earnings yield 100E10/P is (100% / [P/E10] ).

Gummy's Algorithm 1

Cell C24 has Algorithm 1: (100E10/P+B15)*B25
Cell B15 is the existing input for Investment Expenses. Cell B25 is a new term.

Algorithm 1 multiplier a portfolio's current balance by the Investment Expenses term. That is, it is a percentage of one's assets. It varies as the portfolio's balance varies.

Cell A24 now reads: Gummy's Algorithm 1? Y=1 with the input in cell B24.
Cell B25 now reads: Gummy's Multiplier G1 with the input in cell B25.

The implementation of this algorithm requires programming the first two cells of each expense line (there are two, one at the beginning of a year and one at its end) and then using the fill handle to drag the formula through the rest of the historical sequence.

I make the intermediate calculations in rows 2570 and 2571.

Cell A2570 reads: Gummy's Algorithm 1. Cell B2570 multiplies the contents of cell B2571 and B25. (B25 has Gummy Multiplier 1).
Cell A2571 reads: B15+IF(yes)*100E10/P. Cell B15 has the entry entitled investment expenses. The IF(yes) term refers to the contents of cell B24. If cell B24 has been set equal to 1, IF(yes) = 1. Otherwise, IF(yes) = 0.

B2570=B$2571*$B$25
B2571=B15+IF(yes)*100E10/P
$B$15 = Investment Expenses

Gummy's Algorithm 2

Algorithm 2 multiplies the portfolio's initial balance by a withdrawal rate term. That is, it is independent of the portfolio's balance.

Row 9, columns A, B and C (with the text extending to column H) now read::
Initial Withdrawal Rate 3.600% [or whatever rate you enter] of Initial Balance as before+(special inflation term G2)*G3*(100E10/P-G4)

Cell D23 reads: Gummy Algorithm 2? Y=1 with the entry in cell F23.
Cell E24 (extending to column H) now reads: Gummy's Inflation Term G2 (0=none,1=PPI, 2=CPI) with the entry in cell I24.
Cell D25 now reads: Gummy's Multiplier G3 with the entry in cell F25.
Cell G25 now reads: Gummy's Offset G4 with the entry in cell I25.

When cell F23 is not 1 (for Yes), the calculator works exactly as before. Inflation inputs are in cell B12 (Inflation Series Selected 1=PPI 2=CPI) and in cell B14 (Withdraw for inflation? Yes = 1 No = 2).

Using Gummy's Algorithm 2 adds to this withdrawal rate but it does not change any of the other selections. For example, the added part have different choices for inflation.

In normal use, the special inflation term G2 is set to none. That is, cell I24 is set to 0.

Gummy describes his algorithm in the form:
Amount Withdrawn in nominal dollars = Initial Balance*Multiplier*(withdrawal rate*inflation adjustment + [100E10/P - Constant] ).

With this calculator, you enter the product of your withdrawal rate and the Multiplier into cell B4. You must calculate this product externally. The Constant term in [100E10/P-Constant] is typically set to 7, which is a 7% offset from the percentage earnings yield.

Initial Withdrawal Rate cell$B$9
At cell $C$: Includes Gummy's Algorithms 1and 2 (B24,row 2570;B9,F23,F25,I24.I25,row2580)
' of Initial Balance as before+(special inflation term G2)*G3*(100E10/P-G4)
Gummy Algorithm 2? Y=1 cell $F$23
Gummy's Inflation Term G2 (0=none,1=PPI, 2=CPI) cell $I$24
Gummy's Multiplier G3 cell $F$25
Gummy's Multiplier G4 cell $I$25

Old algorithm:
cell C200 was: =-$B$4*$B$9*$B$10
cell D200 was: =IF($B$14<>1,C200,C200*D$190/C$190)
cell C213 was: =-$B$4*$B$9*(1-$B$10)
cell D213 was: =IF($B$14<>1,C213,C213*D$190/C$190)

New algorithm:
$B$4 is the initial balance
$B$9 is the original Initial Withdrawal Rate
$B$10 is the start of year allocation
(1-$B$10) is the end of year allocation
$B$14 = 1 means to include an adjustment for inflation. If no adjustment, then = 2.
D$190/C$190 is the index value on the Inflation Series row. Its code is =IF($B$12=1,+B189,+B188), where $B$12=1 for PPI and 2 for CPI. Row 189 has the Shiller PPI 1982=100 index values and row 188 has the Shiller CPI 1982=100 index values.

Notice that we cannot use the existing algorithm in rows 200 and 213 and just add a term.

In cell C200, write =$B$4*$B$9*$B$10*a term that refers to row 2580 term. For cell C200, this term is =IF($B$14=1,C$2580/$C$2580,1). Use fill handle with this equation.

Row 2580 has the inflation series for standard withdrawals. Currently, cell B2580 simply uses the contents =B$190. Use the fill handle with this equation.

The new code for standard withdrawals for cell C200 is =$B$4*$B$9*$B$10*IF($B$14=1,C$2580/$C$2580,1)
where C$2580 is the same as C$190.

The new code for standard withdrawals for cell C213 is =$B$4*$B$9*(1-$B$10)*IF($B$14=1,C$2580/$C$2580,1)
where C$2580 is the same as C$190.

The new term has the idea of (special inflation term G2)*G3*(100E10/P-G4).

Gummy's Inflation Term G2 (0=none,1=PPI, 2=CPI) cell $I$24.
Let row 2581 be the same as row 190. Cell B2581 has the contents =IF($I$24=2,B188,1)*IF($I$24=1,B189,1)*IF($I$24=0,1,1). Then use the fill handle.
Additional terms can be included by multiplying by additional IF($I$24,action,1) terms. The term IF($I$24=0,1,1) is included simply to make the code easier to understand.

In the future, we may want to add a special box that adds to the inflation terms. Cell B2581 would have include (current contents + Personal inflation adjustment).

Row 2582 will have the 100E10/P term. Make cell B2582 equal to =IF(B$186=0,0,1/B$186). Use the fill handle.

Row 2583 will have the 100E10/P-G4 term. Make cell B2583 equal to =B2582-$I$25. Use the fill handle.

Row 2584 will be G3*(100E10/P-G4)*IF($F$23=1,0.01,0). The scale factor0.01 is because the earnings yield and G4 are percentages. Make cell B2584 equal to =B2583*$F$25. Use the fill handle.

The new part of cell C200's withdrawal amount becomes +$B$4*$B$10*(C$2581/$C$2581)*C$2584.

and the new part of cell C213's withdrawal amount becomes
+$B$4*(1-$B$10)*(C$2581/$C$2581)*C$2584

The complete code for cell C200 is =$B$4*$B$9*$B$10*IF($B$14=1,C$2580/$C$2580,1)+$B$4*$B$10*(C$2581/$C$2581)*C$2584.

The complete code for cell C213 is
=$B$4*$B$9*(1-$B$10)*IF($B$14=1,C$2580/$C$2580,1)+$B$4*(1-$B$10)*(C$2581/$C$2581)*C$2584.

Use the fill handle with C200 and C213.

The next sequence starts in cells D218 and D231.
Copy the contents of cell D200 and paste it into cell D218. Change both C's to D's. Then use the fill handle.
Copy the contents of cell D213 and paste it into cell D232. Change both C's to D's. Then use the fill handle.

Continue for all sequences.

Have fun.

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

Post by JWR1945 »

I have placed the Gummy 02 version of Deluxe Calculator V1.1A08 into our special SWR Research Section.
http://nofeeboards.com/jwr/jwr.html

It is in a self-extracting zip file. I have checked it out. My dialog box identifies the size as 4.56 MB during downloading.

Have fun.

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

Post by Mike »

Thanks John.
Post Reply