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

Posted: Sat Dec 20, 2003 6:14 pm Post subject: More Data Summary Modifications 


More Data Summary Modifications
I have included an example. It demonstrates how powerful our new tools can be.
These are changes apply to previously modified versions of the Retire Early Safe Withdrawal (Rate) Calculator, Version 1.61 from 7 November 2002. This assumes that my earlier data summary modification has already been made. Refer to this thread: Modifications to Improve Data Summaries dated Monday, Dec 15, 2003 at 5:56 pm CST. http://nofeeboards.com/boards/viewtopic.php?t=1841
One of the problems with the data summaries has to do with partially completed sequences. If you are interested in retirement portfolio lifetimes of 40 years, for example, the relevant data summary excludes all of the portfolios begun after 1970. Even worse, the last nine years of investment returns (from 20022010) are based entirely on dummy values. This means that all of the summaries after 1961 are in doubt.
This modification identifies all balances at the end of 2000 for portfolios begun in 1940 or later. That is, the balances are for December 31, 2000 after all trading for the year has ended (and for January 1, 2001 before any trading has begun). In addition, it lists the number of failures associated with each decade from 1940 through 1990. (All references to years are the same as with the FIRECalc calculator.)
Details
Write 31Dec00 into cell BH12 and press enter. Write Balance into cell BH13. Press TAB. Write Fail 2000? into cell BI13. Press TAB. Write Start year into cell BJ13. Press enter.
Highlight cell BH12. Press the Shift key. Highlight cell BJ13. Release the Shift key. At the top, click Format, click cells, click the Alignment tab at the top. Select the Horizontal alignment that you prefer. My choice was Left (Indent). Next, click Edit and then Copy.
Scroll down to cell BH82 and highlight it. Press the Shift key. Highlight cell BJ83. Release the Shift key. At the top, click Edit, then click on Paste. Click on any cell. (I clicked on cell BH82.) Finally, click on the formula area to end the highlighting of the copy area.
Highlight (i.e., click) cell BA84. It shows the year 1940. Press the Shift key. Highlight cell BA144. Release the Shift key. At the top, click Edit and then Copy.
Highlight cell BJ84. Press the Shift key. Highlight cell BJ144. Release the Shift key. At the top, click Edit and then Paste Special. In the gray box that pops up, click Values under Paste instead of All. Click on any cell. (I clicked cell BJ84 and its value was 1940.) Click on a blank cell and then click in the formula area. That will complete the copying process.
Now begins a tedious process that I have not been able to automate.
Highlight cell BH84. Write =$EA$1457 into the formula bar and then press enter (or click the check mark). Locate the fill handle on the lower right hand corner of the cell (BH84). Position the mouse cursor so that the fat plus sign changes to narrow cross hairs and click and hold (i.e., click but do not release) the mouse button down. Drag the fill handle down to cell BH144 and release. Then click any cell. The amount that is in cell BH84 will show up in all of the cells. If you highlight any of the cells from BH84 to BH144, the same formula =$EA$1457 will show up in the formula area.
Scroll over to columns S and T. Make a list with the year from column S and the formula contents (as seen in the formula bar) from column T. Actually, you are only interested in the row number that appears when you highlight cells in column T. To start, highlight cell T84. The formula bar shows =EM1457, which refers to a cell at column EM and row 1457. Cell S84 shows the year 1940. Write down the year and write down the row number (1457) that you see in the formula bar. Next, highlight cell T85. Write down the year 1941 that appears in cell S85 and write the row number (1475) from the contents of the formula bar (=EM1475). Continue until you have a complete list from 19402000.
Replace all of the row numbers in the formulas for cells BH85 through BH144 with those from your list. (Cell BH84 has the correct row number for 1940 already.) Cell BH85 should read =$EA$1457 before making the change and =$EA$1475 after making the change. Cell BH86 will read =$EA$1457 before making the change and =$EA$1493 after making the change.
When you modify subsequent spreadsheets, you will be able to copy the formulas for the entire column. You do not have to enter the row numbers again. (Use Copy and Paste to do this. Do not use Paste Special.)
That ends the tedious process.
Highlight cell BI84. Write =IF(BH84<0,1,0) into the formula bar and click the check mark. Locate the fill handle on the lower right hand corner of cell BI84. Click and hold down the mouse button. Drag the cursor down to cell BI144. Release the mouse button and then click on any cell. This fills in the formula for all of the rows from 84144. If the balance in column BH is negative, then the corresponding cell (i.e., in the same row) in column BI equals 1. That means that the portfolio had failed on or before year 2000. Otherwise, it is 0. That means that the portfolio still had a nonnegative balance on December 31, 2000. The balance may have been zero.
Highlight cell R1. Write Start Years into the formula bar and press enter. Highlight cell R2. Write 19401949 into the formula bar and press enter. Highlight cell R3. Write 19501959 into the formula bar and press enter. Highlight cell R4. Write 19601969 into the formula bar and press enter. Highlight cell R5. Write 19701979 into the formula bar and press enter. Highlight cell R6. Write 19801989 into the formula bar and press enter. Highlight cell R7. Write 19901999 into the formula bar and press enter.
Highlight cell S1. Write Number Depleted in 2000 into the formula bar and press enter. Highlight cell S2. Write =SUM($BI$84:$BI$93) into the formula bar and press enter. Notice that it is necessary to use the $ signs (for fixed cell references). Highlight cell S3. Write =SUM($BI$94:$BI$103) into the formula bar and press enter. Highlight cell S4. Write =SUM($BI$104:$BI$113) into the formula bar and press enter. Highlight cell S5. Write =SUM($BI$114:$BI$123) into the formula bar and press enter. Highlight cell S6. Write =SUM($BI$124:$BI$133) into the formula bar and press enter. Highlight cell S7. Write =SUM($BI$134:$BI$143) into the formula bar and press enter.
When you modify subsequent spreadsheets, you will be able to copy all of this in one operation. Highlight cell R1. Hold down the Shift key. Highlight cell S7. Release the shift key. At the top, click Edit and then Copy. On the new spreadsheet, highlight the same cells. (Highlight cell R1. Hold down the Shift key. Highlight cell S7. Release the shift key.) At the top, click Edit and then Paste. (Use Copy and Paste to do this. Do not use Paste Special.) Click on any cell in the new spreadsheet to complete the Paste operation. Finally, click on the formula bar on the old spreadsheet and then on any cell.
Does it help? Definitely!
I made a brief survey similar to what Mike did earlier, but with these enhanced data analysis tools. I looked at switching among stock and commercial paper allocations. I set the initial balance at $100000 and the withdrawal rate equal to 5% of the initial balance plus inflation. I set the expense ratio to 0.20%. I left the other settings at their default values.
I restricted my investigations to start years of 19211980.
In my initial investigation I set the lower P/E10 threshold equal to 12.0 and the higher threshold equal to 20.0. My starting allocations were 100% stocks below the lower threshold, 50% stocks in midrange and 0% stocks above the higher threshold. It showed 3 failures with 30year lifetimes and 13 with 40year lifetimes. Changing the allocations to 80%, 50% and 0% produced 6 failures with 30year lifetimes and 14 failures with 40year lifetimes. Clearly, that was inferior. Changing the allocations to 100%, 50% and 20% resulted in 3 failures with 30year lifetimes and 13 failures with 40year lifetimes.
Looking at the new summaries that count the failures in 2000, I found that the 100%50%0% (stock) allocations resulted in 3 failures from start years in the 1950s and 8 failures from start years in the 1960s. The 100%50%20% (stock) allocations had 3 failures from start years in the 1950s and 9 failures from start years in the 1960. All of the start years were the same except for 1968. (Portfolios started in 1956, 1957, 1959 and 19601967 had failed in year 2000 in both cases.) A portfolio that began in 1968 would have survived in year 2000 if the stock allocations were 100%50%0%. It would have failed if the stock allocations were 100%50%20%.
Notice that all of the failures in year 2000 came from portfolios that were begun before 1970. That means that all of the failures associated with a 30year lifetime occurred before 2000. Notice that the total number of failures in 2000 was either 11 or 12, not the 13 associated with the traditional summaries. That means that the additional failure or failures occurred after 2000, possibly because of dummy values for 20022010.
I took another set of runs with the 100%50%0% (stock) allocations. I varied the lower P/E10 threshold in steps of 2. I kept the upper threshold at 20.0.
With the lower threshold set at 10.0, there were 6 of the 30year failures and 21 of the 40year failures. At a lower threshold of 12.0, there were 3 of the 30year failures and 13 of the 40year failures. At a lower threshold of 14.0, there were 15 of the 30year failures and 12 of the 40year failures. At a lower threshold of 16.0, there were 15 of the 30year failures and 12 of the 40year failures. At a lower threshold of 18.0, there were 7 of the 30year failures and 12 of the 40year failures.
From the 30year data, we would place the lower threshold at 12.0.
From the 40year data, we would be confused.
Looking at the threshold of 10.0, we find that the failures in 2000 occurred for start years of 19401946, 1956, 1967, 1959 and 19601967. Looking at the threshold of 12.0, we find that the failures in 2000 occurred for start years of 1956, 1957, 1959 and 19601967. Looking at the threshold of 14.0, we find that the failures in 2000 occurred for start years of 1959, 19601969 and 19701973. Looking at the threshold of 16.0, we find that the failures in 2000 occurred for start years of 1959, 19601969 and 19701972. Looking at the threshold of 18.0, we find that the failures in 2000 occurred for the start years of 1959 and 19601968.
We can even get into greater detail.
By checking column BD, we find that the 30year failures with a threshold of 12.0 occurred for the start years of 19641966. By checking column BE, we find that the 40year failures with a threshold of 12.0 occurred for the start years of 1956, 19591970. By checking column BD, we find that the 30year failures with a threshold of 18.0 occurred for the start years of 1962, 19641967, 1972 and 1973. By checking column BE, we find that the 40year failures with a threshold of 18.0 occurred for the start years of 19591970.
A comparison of 30year portfolio lifetimes strongly favors a lower threshold of 12.0 as opposed to 18.0. A comparison of 40year portfolio lifetimes identifies one difference as being the failure in 1956 with a threshold of 12.0, but not with a threshold of 18.0. When we look at failures in the year 2000, we notice that none of the failures for start years after 1967 (with a threshold of 12.0) or for start years after 1968 (with a threshold of 18.0) should be counted. The 40year failure for the start year of 1956 (at a threshold of 12.0) is balanced against the failure for the start year of 1968 (at a threshold of 18.0). (With a threshold of 12.0, a portfolio begun in 1957 lasted for a full 40 years, but not to 2000.)
This is more that sufficient to resolve the confusion caused by the 40year portfolio lifetime summary data.
Have fun.
John R.

