TIPS Ladder Calculators

This is an outline of the changes that I made to my Deluxe Calculator V1dot1A08 (also known as my Deluxe Calculator V1.1A08) so that I could investigate TIPS Ladders.

Deluxe Calc V1dot1A08P01

INDIVIDUAL CELLS

A24: Bond Ladder Length
B24: input
C24: years
D24: ONLY WHEN NOT REBALANCED

A25: Threshold
B25: input
C25: *Initial Bal =
D25: =$B$25*$B$4
E25: Dollars

INITIAL CHANGES

A198: Increment
C198: =$B$4*(1-$B$6)*IF($B$24=0,1,1/$B$24)
D198: =C198*(D$190/C$190)
Then use the fill handle.

A203: 'Stock Balance 01/01 [unchanged]
C203: =IF($B$16=1,+$B$6*C202,+B206/B212*C202) [unchanged]
D203: =IF($B$16=1,+$B$6*D202,+(C206+C211)/C212*D202)
Then use the fill handle.

A208: Bond Balance 01/01 [unchanged]
C208: =IF($B$16=1,+(1-$B$6)*C202,+B210/B212*C202) [unchanged]
D208: =IF($B$16=1,+(1-$B$6)*D202,+(C210-C211)/C212*D202)
Then use the fill handle.

A211: Adjustment
C211: =IF(C210>C198,IF(((($B$25*$B$4)*(C$190/B$190))>(C205+C209)),C198,0),0)
Then use the fill handle.

THROUGHOUT THE CALCULATOR

Row 198:

Copy A198, Increments, into all of the subsequent Increment positions: cell A216, etc., between Total 12/31 and Total 01/01.

Copy row 198 from C198 through BJ198 (1 row, 60 columns):

Paste this entire sequence into the proper position into all of the subsequent Increment positions.

Row 203:

Notice that the Adjustment is just above the Subtotal 12/31 row and that the denominator is in the Subtotal 12/31 row. The change is in the ratio from the previous column. Using that column, change E221 from: =IF($B$16=1,+$B$6*E220,+D224/D230*E220) to=IF($B$16=1,+$B$6*E220,+(D224+D229)/D230*E220).That is, look at the denominator. It is D230. Subtract a row to get D229. Then add it to the numerator.

Then use the fill handle.

Row 226:

Do the same with the Bond Balance 01/01 except subtract instead of adding.
Continue with this pair of changes (Stock Balance 01/01 and Bond Balance 01/01) for all subsequent sequences.

Row 229:

Copy A211, Adjustment, into all of the subsequent Adjustment positions: cell A229, etc., between Bond Balance 12/31 and Subtotal 12/31.

Copy row 211 from C211 through BJ211 (1 row, 60 columns):

Paste this entire sequence into the proper position into all of the subsequent Adjustment positions. Notice that the Adjustment row is between two equal total balances at the leftmost column. The first column for the Adjustment code is just to the right of this.

SPECIAL NOTE:

This is what I have done. In the portion without rebalancing, I subtract an increment from the bond total whenever interest plus dividends is lower than a threshold (equal to a percentage of the initial balance plus inflation) and I add it to the stock total (in the next column to the right).

The increment equals the initial principal plus inflation divided by the length of the ladder. If the initial stock allocation were 70%, the initial bond allocation would be 30%. With a 10-year ladder, the increment would be 3% of the initial balance (plus inflation).

I do not subtract the increment if there are not enough funds. That is, I do not subtract the increment unless the 12/31 Bond Balance is greater than the increment.

Because the calculator has only one row for Interest Paid, it lumps changes in principal along with the interest from TIPS and IBonds. This can result in a negative amount with TIPS during deflation. This can cause the Dividends plus Interest amount to fall below threshold when the actual interest paid (without the decrease in principal) plus dividends is above the threshold. For this reason, I recommend collecting data with IBonds whenever there is deflation.

Note: the Retire Early Safe Withdrawal Calculator has an incorrect algorithm for the interest of IBonds. It allows the total amount reported as Interest Paid to fall to zero. In fact, the coupon amount is always paid, even during deflation. The principal remains where it started during deflation, but the bondholder still receives the full amount of the coupon as interest.

This calculator adds stock when dividends plus interest are below threshold.

Deluxe Calc V1dot1A08P02 (Error)

Changes:

FROM:

A25: Threshold
B25: input
C25: *Initial Bal =
D25: =$B$25*$B$4
E25: Dollars

TO:

A25: P/E10 Threshold
B25: input (format changed from percentage to number)
C25: blank
D25: blank
F25: Buy more shares when dividends plus interest are above threshold.

FROM:

A211: Adjustment
C211: =IF(C210>C198,IF(((($B$25*$B$4)*(C$190/B$190))>(C205+C209)),C198,0),0)
Then use the fill handle.

TO:

A211: Adjustment
C211: blank
D211: =IF(C210>C198,IF(D$186>$B$25,D198,0),0)
Then use the fill handle.

Deluxe Calc V1dot1A08P03

Changes from V1dot1A08P02

FROM:

A211: Adjustment
C211: blank
D211: =IF(C210>C198,IF(D$186>$B$25,D198,0),0)
Then use the fill handle.

TO:

A211: Adjustment
C211: =IF(C210>D198,IF(D$186>$B$25,D198,0),0)
Then use the fill handle.

Buy more stocks when P/E10 is above threshold.
Result: set threshold to zero.

Deluxe Calc V1dot1A08P04

Changes from V1dot1A08P03

FROM:

A211: Adjustment
C211: =IF(C210>D198,IF(D$186>$B$25,D198,0),0)
Then use the fill handle.

TO:

A211: Adjustment
C211: =IF(C210>D198,IF(D$186<$B$25,D198,0),0)
Then use the fill handle.

This buys more shares when P/E10 is below threshold.
Result: set threshold to 12 or 13.

Withdrawal rate was 4.6% with zero failures. Best of this series.

Deluxe Calc V1dot1A08P05

Changes from V1dot1A08P04

FROM:

A211: Adjustment
C211: =IF(C210>D198,IF(D$186<$B$25,D198,0),0)
Then use the fill handle.

TO:

A211: Adjustment
C211: =IF(C206>D198,IF(D$186>$B$25,-D198,0),0)
Then use the fill handle.

Add bonds if P/E10 is above threshold.
Increments are still in terms of the initial bond allocation.

Result: set threshold at 22 or above.

Deluxe Calc V1dot1A08P06

Changes:

INDIVIDUAL CELLS

FROM:

A24: Bond Ladder Length
B24: input
C24: years
D24: ONLY WHEN NOT REBALANCED

A25: Threshold
B25: input
C25: *Initial Bal =
D25: =$B$25*$B$4
E25: Dollars

TO:

A24: Bond Ladder Length
B24: input
C24: years
D24: ONLY WHEN NOT REBALANCED

A25: Threshold
B25: input
C25: *Initial Bal =
D25: =$B$25*$B$4
E25: Dollars
F25: Buy more shares when dividends plus interest are above threshold.

FROM:

A211: Adjustment
C211: =IF(C210>C198,IF(((($B$25*$B$4)*(C$190/B$190))>(C205+C209)),C198,0),0)
Then use the fill handle.

TO:

A211: Adjustment
C211: =IF(C210>C198,IF(((($B$25*$B$4)*(C$190/B$190))<(C205+C209)),C198,0),0)
Then use the fill handle.

Add stock if dividends plus interest are above the threshold.

Withdrawal Rate with IBonds was 4.7%. TIPS had problem because of principal reduction. Favors threshold around 9% and a very short ladder of 2 to 3 years.

This story seems to be: if you can get a bargain (via high dividends), buy lots of stock.
Have fun.

John Walter Russell
I originally posted this on May 4, 2005.