JWR1945 ***** Legend
Joined: 26 Nov 2002 Posts: 1697 Location: Crestview, Florida

Posted: Sat Jul 24, 2004 11:08 am Post subject: JanSzChips Deluxe Version 1.0C Calculator 


I doubt that anyone will want this version of my new calculator. (Another version will follow shortly.) I think that it is worth knowing the kind of tables and withdrawal conditions that I will be able to generate. I may be able now to generate some numbers of special interest to you.
JanSzChips Deluxe Version 1.0C Calculator
This is a description of a recent set of modifications to the Retire Early Safe Withdrawal [Rate] Calculator, Version 1.61, 7 November 2002. I refer to this as the JanSzChips Deluxe Version 1.0C Calculator.
This version combines the features of the JanSzChips Deluxe Version 1.0B, the JanSzChips Deluxe Version 2.0B and the JanSzChips Deluxe Version 3.0B. You no longer have to bring up separate calculators for switching with commercial paper (Versions 1.0), TIPS (Versions 2.0) and ibonds (Versions 3.0). You enter your choice of the Switching FI Type in cell F22.
I have added the ability for you to specify your percentage of Interest Reinvestments in cell B23. This allows you to do with fixed income/bond type investments what you can currently do with stock dividends in cell B22. You can strip off any fraction of the interest income that you specify. You can now live off dividends and interest instead of living off dividends alone.
I have added data analysis tables in the rows 4000 and 6800. These tables let you track the amount that has been withdrawn when removing a percentage of dividends, when removing a percentage of interest, when removing a percentage of the current balance and when removing a percentage of (current yeartosixyearsprevious) portfolio gains. You can keep track of your total withdrawals as well. There are tables for amounts both in nominal dollars and in real dollars.
I have retained the JanSz algorithm in this version. It removes a percentage of the increase in the portfolio balance from six years earlier. There is no withdrawal when the balance decreases.
Combining the Calculators
In cell D22, I wrote Switching FI Type. In cell G22, I wrote 1=Commercial Paper, 2=TIPS, 3=Ibonds. In cell H22, I wrote (Cell B7 must equal 6).
You put your choice of the Fixed Income Type (more precisely, commercial paper, TIPS and ibonds) into cell F22.
The critical logic is in row 2553. I wrote FI Selection Logic in cell A2553. I wrote =IF($F$22>2,B181,IF($F$22>1,B180,B177)) into the Formula Bar at the top of the spreadsheet and then I clicked the checkmark. Finally, I used the fill handle to drag the formula to the right across the entire row.
[Any number less than or equal to 1.0 causes commercial paper to be selected. This includes the number 1. Any number above 1.0 and less than or equal to 2.0 causes the selection of TIPS. This includes the number 2. Any number greater than 2.0 causes the selection of ibonds. This includes the number 3.]
Row 2552 contains Intermediate Logic. I wrote =IF($H$19<B186,IF($I$19<B186,C2553,+(C2553*(1$I$20)+$I$20*C2550)),+(C2553*(1$F$20)+$F$20*C2550)) into cell C2552.
The reference is now C2553 instead of C177 or C180 or C181.
Previously, cell C2552 had contained =IF($H$19<B186,IF($I$19<B186,C177,+(C177*(1$I$20)+$I$20*C2550)),+(C177*(1$F$20)+$F$20*C2550)) for commercial paper. It had contained =IF($H$19<B186,IF($I$19<B186,C180,+(C180*(1$I$20)+$I$20*C2550)),+(C180*(1$F$20)+$F$20*C2550)) for TIPS. It had contained =IF($H$19<B186,IF($I$19<B186,C181,+(C181*(1$I$20)+$I$20*C2550)),+(C181*(1$F$20)+$F$20*C2550)) for ibonds.
I clicked on cell C2552, located the fill handle and dragged the formula to the right.
Row 182 is titled Stock Switch by P/E. I clicked cell C182. I wrote =IF($B$19<B186,IF($F$19<B186,C2552,+(C2553*(1$B$20)+$B$20*C2550)),C2550). Notice that this is one more entry in which cell C2553 has replaced a reference to C177 or C180 or C181.
I clicked on cell C182, located the fill handle and dragged the formula to the right.
Checking Out the Combination
I brought up the JanSzChips Deluxe Version 1.0B, the JanSzChips Deluxe Version 2.0B and the JanSzChips Deluxe Version 3.0B. I compared the exact dollar amounts in the CALCULATION RESULTS MATRIX (NOMINAL VALUES) in rows 26 through 42 and columns D through I.
I started with an initial balance of $100000, an 80% stock allocation, an initial withdrawal rate of 4%, a frontend allocation for withdrawals and expenses of 50%, January data, used the CPI to adjust for inflation, rebalanced the portfolio, reinvested 100% of dividends (and interest) and withdrew 0% of portfolio gains. I set the coupon rate for TIPS and ibonds at 2.00%.
I selected each Fixed Income Series in sequence from 1 through 6. With each Fixed Income Series from 1 through 5, I showed that changing the Switching FI Type in cell F22 had no effect (on the dollar amounts in the results matrix in rows 26 through 42 and columns E through I). I showed that dividend (and interest) reinvestment percentages and withdrawing a percentage of portfolio gains did have an effect. Finally, I showed that changing F22 did have an effect when the Fixed Income Sequence was 6 (for switching). I showed that changing the TIPS and ibond coupon rates changed the effects as well.
Then I brought up the individual JanSzChips Deluxe Versions (1.0B, 2.0B and 3.0B) (with the B level updates) to compare with the new version JanSzChips Deluxe Version 1.0C (with the C level update).
I went through the initial comparisons with each Fixed Income Series with version 1.0B. Everything was identical. The most difficult comparisons were with switching. Dollar comparisons between TIPS and ibonds with the same interest rate are very close and often identical. Small differences show up in summaries around 30 to 40 years. [Remember that ibonds never allow nominal interest rates to fall below zero, but TIPS do. This means that ibonds will have better balances than TIPS in some cases. They never have lower balances.]
At first, when I set up switching, I forgot to make all of the allocations and thresholds equal. Once I had them the same among the various calculators, I was able to confirm that the Fixed Income Series selected in cell F22 agreed with the appropriate calculator.
Nominal Investment Expenses
The table with dollar amounts beginning in cell C4000 has both of the Investment Expense withdrawals for each sequence, starting with 1871. At year 1 of the 1871 sequence, the frontend expense is found in cell C201. I wrote =C201 into cell C4000. I used the fill handle to drag the formula to the right for 60 years (to cell BJ4000). At year 1 of the 1871 sequence, the backend expense is found in cell C214. I wrote =C214 into cell C4001. I used the fill handle to drag the formula to the right 60 years (to cell BJ4001).
I wrote Investment Expenses 1 in cell A4000 and Investment Expenses 2 in cell A4001. I wrote 1871 in both cells B4000 and B4001. I wrote = B4000+1 into cell B4002. I used the fill handle down to drag the formula to produce pairs of years through 2000.
Once again, it was necessary to increment the column by one letter and the row number by 18 for corresponding expenses (i.e., frontend and backend expenses). The entry in cell C4002 is =D219. The entry in cell C4003 is =D232. I used the fill handle to drag the formulas 60 years to the right to column BJ (or earlier, when appropriate).
The expense lines are deceptive because they include withdrawals according to the JanSz algorithm beginning in year 7 of each sequence. For example, in row 201 (for the 1871 sequence), cell H201 is simply =IF(H199<=0,0,$B$15*H199)*$B$10, but cell I201 is =IF(I199<=0,0,$B$15*I199)*$B$10+IF(I199<=C199,0,$B$17*(I199C199))*$B$10. The second term +IF(I199<=C199,0,$B$17*(I199C199))*$B$10 is my (crude) implementation of the JanSz algorithm. The new term is present in all columns starting with column I and those to the right.
A similar condition applies to the backend withdrawal. The formula for cell H214 reads =IF(H212<=0,0,$B$15*H212)*(1$B$10) but the formula for cell I214 reads =IF(I212<=0,0,$B$15*I212)*(1$B$10)+IF(I212<=C212,0,$B$17*(I212C212))*(1$B$10).
Notice that investment expenses include (1) the expense ratio of the investment account, (2) intentional withdrawals based upon the account's current balance and (3) withdrawals of gains according to my implementation of the JanSz algorithm. Expenses are applied at the beginning and at the end of each year. They vary depending upon the total balance at the beginning of a year (01/01) and the total balance at the end of the year (12/31). They are not adjusted for inflation.
Nominal Annual Withdrawals
The table with dollar amounts beginning in cell C4300 has both of the normal annual withdrawals for each sequence, starting with 1871. At year 1 of the 1871 sequence, the frontend withdrawal amount is found in cell C200. I wrote =C200 into cell C4300. I used the fill handle to drag the formula to the right for 60 years (to cell BJ4300). At year 1 of the 1871 sequence, the backend expense is found in cell C213. I wrote =C213 into cell C4301. I used the fill handle to drag the formula to the right 60 years (to cell BJ4001).
The procedure for filling out the table is the same as with Investment Expenses except that alternating rows are labeled Annual Withdrawal 1 and Annual Withdrawal 2.
This table is easily checked out by removing the inflation adjustment for withdrawals. That is, the Withdrawal for Inflation ? entry in cell B14 is set to 2 (where Yes = 1 and No = 2).
Initial Stock and Bond Balances
The table with dollar amounts beginning in cell C4600 has the Initial Stock Balances of each sequence, starting with 1871. (At year 1, the initial stock balance is the original stock balance allocation from 1871 after the frontend withdrawals and expenses have been removed.)
I wrote =C203 in cell C4600. Row 103 has the initial stock balances for the 60years of the sequence that began in 1871. The title of the row is in cell A103. It is Stock Balance 01/01. The initial stock balance for the second year of the 1871 sequence in cell D203 and so forth. The formula for the initial stock balance depends upon whether there is rebalancing.
I clicked on cell C4600, located the fill handle and dragged the formula 60 years (or columns) to the right (to cell BJ4600). The numbers in row 4600 correspond to the initial stock balances for the sequence that began in 1871.
Row 221 has the Stock Balance 01/01 for 1872. The first entry is in cell D221. I wrote =D221 in cell C4601. I used the fill handle to drag the formula 60 years (or columns) to the right (to cell BJ4601).
Notice that each new row of the table starting with cell C4600 requires that the first column letter be incremented by one letter. Each row number must be incremented by 18. Although doing this is tedious, the data analysis benefits are huge.
There is a similar table for bonds. The table with dollar amounts begins at cell C4800. It shows the Initial Bond Balances of each sequence, starting with 1871.
I wrote =C208 in cell C4800. Row 208 is titled with the Bond Balance 01/01. Cell C208 is the balance for the first year of the 1871 sequence. As before, I located the fill handle for cell C4800 and dragged the formula 60 years (or columns) to the right (to cell BJ4800).
As before, there is an increment of one letter and 18 rows to reach the location of the second sequence (1872). It is in cell D226. I wrote =D226 in cell C4801 and used the fill handle to drag the formula 60 years (or columns) to the right (to cell BJ4801). I continued this process until the table was complete.
Sequences eventually run out of data. The sequence that began in 1942 has 60 years of data (to 2002). The sequence that began in 1943 has only 59 years of data plus a single year of dummy data. Similarly, the sequence that began in 1952 has 50 years of data (to 2002). And so forth.
I abbreviated many of the sequences in these tables to limit them somewhat according to data availability. I did this in tenyear increments as a matter of convenience. More accurate tables would limit sequence lengths in single year increments.
Dividend and Interest Reinvestments
I typed the words Dividend Yields into cell A4990. In cell C4990 I wrote =100*(IF(C175>0,+C175,+C176))/C184. I clicked on cell C4990, clicked on the pull handle in the lower right hand corner of the cell and dragged the formula all of the way to the right to cell EK4990.
This is the same formula that is in row 2560 except that I used number format in row 4990 and the percentage number format in row 2560.
The original formula for the S&P500 dividend amount in cell C185 was =IF(C175>0,+C175,+C176). It was changed to =IF(C175>0,+C175,+C176)*$B$22 to limit the dividend reinvestment to the percentage specified in cell B22).
Cell C184 is the S&P500 index value or price.
I wrote Dividends Reinvested in cell A4991. I wrote =C4990*$B$22 in cell C4991. I used the fill handle to drag the formula all of the way to the right (to cell EK4991). Cell B22 (fixed at location $B$22) is the Dividend Reinvestments percentage.
I wrote Dividends Withdrawn in cell A4992. I wrote =C4990*(1$B$22) in cell C4992. I used the fill handle to drag the formula all of the way to the right (to cell EK4992).
The Dividends Reinvested and Dividends Withdrawn are fractions of the Dividend Yield. The dollar amounts reinvested and withdrawn are these numbers (divided by 100) times the total dollar amount of the stocks in the portfolio.
I typed the words Interest Reinvestments in cell A23.
I typed the words Interest Rates into cell A4995. In cell C4995 I wrote =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). I clicked on cell C4995, clicked on the pull handle in the lower right hand corner of the cell and dragged the formula all of the way to the right to cell EK4995.
This is the same formula that was originally in cell C183 for the FI Interest Rate.
I changed the formula in cell B183 to =IF($B$7>1,(IF($B$7>2,(IF($B$7>3,(IF($B$7>4,(IF($B$7>5,+B182,+B181)),+B180)),+B179)),+B178)),+B177)*$B$23 so as to limit the interest reinvestments to the percentage specified in cell B23 (more precisely, the fixed location $B$23). That is, I multiplied the original contents of B183 by the contents of cell B23. I used the fill handle to drag the formula from cell B183 all the way to the right (to cell EK183).
Note: the calculations do not use the contents of cell B183. The first application from row 183 is from cell C183.
I wrote Interest Reinvested in cell A4996. I wrote =C4995*$B$23 in cell C4996. I used the fill handle to drag the formula all of the way to the right (to cell EK4996). Cell B23 (fixed at location $B$23) is the Interest Reinvestments percentage.
I wrote Interest Withdrawn in cell A4997. I wrote =C4995*(1$B$23)) in cell C4997. I used the fill handle to drag the formula all of the way to the right (to cell EK4997).
The Interest Reinvested and Interest Withdrawn are fractions of the Interest Rate. The dollar amounts reinvested and withdrawn are these numbers (divided by 100) times the total dollar amount of the bonds in the portfolio.
Nominal Dividend and Interest Amounts Withdrawn
The table with dollar amounts beginning in cell C4600 has the Initial Stock Balances of each sequence, starting with 1871. (At year 1, the initial stock balance is the original stock balance allocation from 1871 after the frontend withdrawals and expenses have been removed.)
There is a similar table with dollar amounts beginning in cell C4800 with the Initial Bond Balances.
The contents of the table that begins with cell C5000 are the dividend amounts withdrawn.
The contents of cell C5000 are =C$4992*IF(C4600<0,0,C4600/100). Cell C4992 has the percentage of the dividend yield that is withdrawn. It has to be divided by 100 to convert it into a decimal fraction. Cell C4600 has the total stock balance that applies to the withdrawals. The IF statement logic excludes dividends from being withdrawn when the portfolio has been depleted. It returns an easily identifiable exact number equal to zero.
The contents of the table that begins with cell C5200 are the interest amounts withdrawn.
The contents of cell C5200 are =C$4997*IF(C4800<0,0,C4800/100)). Cell C4997 has the percentage of the interest rate that is withdrawn. It has to be divided by 100 to convert it into a decimal fraction. Cell C4800 has the total bond balance that applies to the withdrawals. [The word bond refers to whatever security class is included in addition to stocks.] The IF statement logic excludes interest from being withdrawn when the portfolio has been depleted. It returns an easily identifiable exact number equal to zero.
Combined Investment Expenses
I refer to the entries starting with cell C5400 as Expenses+Withdrawals 1 and Expenses+Withdrawals 2. The formula for cell C5400 is =C4000+C4300. The formula for cell C5401 is =C4001+C4301. The formula for cell D5400 is =D4000+D4300. All formulas can be dragged to the right and they can be dragged down. The difficult part was making the original tables starting with cells C4000 and C4300.
I refer to the entries starting with cell C5700 as Expenses+Withdrawals 1+2. The content of cell C5700 is =C5400+C5401 and the formula can be dragged to the right. For example, the content of cell D5700 is =D5400+D5401.
The content of cell C5701 is =C5402+C5403. It can be dragged to the right as well. However, the contents of cells in column C cannot be dragged down directly.
What I did was to make a full table with a single increment for each row. That is, column C originally contained the entries =C5400+C5401 and =C5401+C5402 =C5402+C5403 and =C5403+C5404 and so forth. Later, I removed every other row. (From Edit, click Delete and then select Entire row.)
The contents of the table that begins with cell C5700 are the expenses and withdrawals for each year. This includes amounts at both the frontend (01/01) of a year and the backend (12/31) of the same year.
Total Withdrawals
I have listed the Total Withdrawals in the entries starting with cell C5899 (oops!). The formula for cell C5899 is =C5000+C5200C5700 and you can drag it both to the right and downward.
The contents of the table that begins with cell C5000 are the dividend amounts withdrawn. The contents of the table that begins with cell C5200 are the interest amounts withdrawn. The contents of the table that begins with cell C5700 are the expenses and withdrawals for each year. This includes amounts at both the frontend (01/01) of a year and the backend (12/31) of the same year.
Real Dollar Tables
I have normalized the deflators in the table that starts at cell C6099 start at C3001/$C3001. That becomes the new deflator for year 1 of the 1871 sequence. This new deflator multiplies previously tabulated nominal dollar amounts.
Upon careful examination, the new deflator is one row down in the old table. The new deflator for year 1 of the 1871 sequence equals the entry in cell C3002. That would have made the new tables a little easier to build. There would have to be special calculations for the year 2000 since all of the tables end with 2000.
The adjustment is necessary because of the relative position of the terms as inflation adjustments are made. For example, the initial withdrawal made in the 1871 sequence is in column C, which corresponds to 1872 instead of 1871. The first inflation adjustment to the 1871 withdrawal amount occurs in column D, which corresponds to 1873.
The table for Total Withdrawals in real dollars begins with cell C6099. Its formula is =C5899*C3001/$C3001. Cell C5899 has the Total Withdrawal amounts in nominal dollars. The product C3001/$C3001 is the new deflator just mentioned. The cell in the next column over is D6099. Its formula is =D5899*D3001/$C3001.
The table for Expenses+Withdrawals 1+2 in real dollars begins with cell 6300. Its formula is =C5700*C3001/$C3001. The only change is the reference to cell C5700, which presents the Expenses and Withdrawals 1+2 in nominal dollars. Otherwise, the patterns remain the same.
The table for Dividends Withdrawn in real dollars begins with cell C6500. Its formula is =C5000*C3001/$C3001. The only change is the reference to cell C5000, which presents the Dividends Withdrawn amounts in nominal dollars. Otherwise, the patterns remain the same.
The table for Interest Withdrawn in real dollars begins with cell C6700. Its formula is =C5200*C3001/$C3001. The only change is the reference to cell C5200, which presents the Interest Withdrawn in nominal dollars. Otherwise, the patterns remain the same.
The last row used is row 6829.
Availability
I have put a selfopening zip file with this calculator into my Yahoo briefcase. To go to the briefcase, you need my username: jwr19452000. You must let me know your own Yahoo username for me to be able to make it available to you.
I have found that Yahoo is likely to be fast with the first download and much slower with subsequent downloads.
This calculator is a memory hog. The zipped file requires just over 3.0 Mbytes. The unzipped file requires 8.5 Mbytes.
I anticipate that my next version, which replaces the sixyear JanSz feature with a yeartoyear comparison, will find better acceptance. I have made this version available just in case someone is really interested in using (my implementation of) the JanSz feature. I would hate to redo either the current approach or the new yeartoyear approach.
Have fun.
John R.

