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

Posted: Mon Dec 15, 2003 2:56 pm Post subject: Modifications to Improve Data Summaries 


Modifications to Improve Data Summaries
These calculator modifications allow you to look at a particular range of start years. For example, start years from 18811920 differ from those of 19211980 as to how they behave relative to P/E10. You may use this with the Retire Early Safe Withdrawal (Rate) Calculator, Version 1.61 from 7 November 2002, and all of the modified versions that I have reported.
This change shows the number of portfolios that would have failed by 10, 20, 30, 40, 50 and 60 years within a range that you specify. I indicate the number of failures instead of their percentages because the number of complete periods varies. This is strictly a matter of personal preference.
Details
Bring up a calculator that you want to change (or make a copy of it).
Highlight cell L1, type Start Year into the formula bar and then press enter. Highlight cell L2, type End Year into the formula bar and press enter. Highlight cell L3, type Total Years into the formula bar and press enter. Highlight cell L4, type Failed 10 into the formula bar and press enter. Highlight cell L5, type Failed 20 into the formula bar and press enter. Highlight cell L6, type Failed 30 into the formula bar and press enter. Highlight cell L7, type Failed 40 into the formula bar and press enter. Highlight cell L8, type Failed 50 into the formula bar and press enter. Highlight cell L9, type Failed 60 into the formula bar and press enter.
You may click on the check mark instead of pressing enter. If you do so, your highlighted cell will remain the same. If you press enter, the highlighted cell will move down one row, which is the next cell that you want to highlight.
Highlight cell M3. Type =$M$2$M$1+1 and press enter. Highlight cell M4, type =SUM(BB15:BB144) into the formula bar and press enter. Highlight cell M5, type =SUM(BC15:BC144) into the formula bar and press enter. Highlight cell M6, type =SUM(BD15:BD144) into the formula bar and press enter. Highlight cell M7, type =SUM(BE15:BE144) into the formula bar and press enter. Highlight cell M8, type =SUM(BF15:BF144) into the formula bar and press enter. Highlight cell M9, type =SUM(BG15:BG144) into the formula bar and press enter. Quickly click through cells M4 through M9 to make sure that the column references change from BB to BC to BD to BE to BF to BG.
Highlight cell N1. Type Start Row into the formula bar and press enter. Highlight cell N2. Type End Row into the formula bar and press enter. Skip cell N3. Highlight cell N4. Type The final start year is into the formula bar and press enter. Highlight cell N5. Type The final start year is into the formula bar and press enter. Highlight cell N6. Type The final start year is into the formula bar and press enter. Highlight cell N7. Type The final start year is into the formula bar and press enter. Highlight cell N8. Type The final start year is into the formula bar and press enter. Highlight cell N9. Type The final start year is into the formula bar and press enter. (You may prefer to copy the contents of cell N4 and paste it into cells N5, N6, N7, N8 and N9.)
Highlight cell P4, type 2000 and press enter. Highlight cell P5, type 1990 and press enter. Highlight cell P6, type 1980 and press enter. Highlight cell P7, type 1970 and press enter. Highlight cell P8, type 1960 and press enter. Highlight cell P9, type 1950 and press enter.
Highlight cell L1, press the Shift key and click on cell P9. Release the Shift key. Click on Edit and then click on Copy. Bring up each of the other calculators that you wish to modify. On each version, highlight cell L1, press the Shift key and click on cell P9. Release the Shift key. Click on Edit and then click on Paste.
Highlight cell BA12, type Failures within range (from $M$1 to $M$2) and the number of years indicated into the formula bar and press enter. Highlight cell BA13, type Year into the formula bar and press enter. Highlight cell BB13, type Fail 10 into the formula bar and press enter. Highlight cell BC13, type Fail 20 into the formula bar and press enter. Highlight cell BD13, type Fail 30 into the formula bar and press enter. Highlight cell BE13, type Fail 40 into the formula bar and press enter. Highlight cell BF13, type Fail 50 into the formula bar and press enter. Highlight cell BG13, type Fail 60 into the formula bar and press enter.
Highlight cell BA15, type =S15 and press enter. Locate the fill handle on cell BA15. It is in the lower right hand corner of the cell. Move the mouse pointer until the broad plus sign becomes a narrow set of cross hairs centered on the fill handle. Click the mouse button and hold it down. Drag the fill handle down until it shows the year 2000 in row 144. Then click on any cell. You may do this in several steps. If you release the fill handle early, just click on the fill handle of the last cell and drag it down further. Click on the last cell in the list or, if you have overshot the last year (2000), click on it. You will see the year numbers identified in reverse print when you first release your mouse button but before you click once more to confirm your selection.
Highlight cell BB15 and type this equation into the formula bar:
=IF(AND($BA15>=$M$1,$BA15<=$M$2,T15<0),1,0)
Then click on the check mark or press enter.
Highlight cell BC15. Type in this formula:
=IF(AND($BA15>=$M$1,$BA15<=$M$2,W15<0),1,0)
Then click on the check mark or press enter.
Highlight cell BD15. Type in this formula:
=IF(AND($BA15>=$M$1,$BA15<=$M$2,Z15<0),1,0)
Then click on the check mark or press enter.
Highlight cell BE15. Type in this formula:
=IF(AND($BA15>=$M$1,$BA15<=$M$2,AC15<0),1,0)
Then click on the check mark or press enter.
Highlight cell BF15. Type in this formula:
=IF(AND($BA15>=$M$1,$BA15<=$M$2,AF15<0),1,0)
Then click on the check mark or press enter.
Highlight cell BG15. Type in this formula:
=IF(AND($BA15>=$M$1,$BA15<=$M$2,AI15<0),1,0)
Then click on the check mark or press enter.
Notice that all of these formulas are the same except for references to cells T15, W15, Z15, AC15, AF15 and AI15.
You may speed up entering the formulas by dragging the formula from BB15 to BG15 and then making changes in the formula bar. The formulas refer to cells T15, U15, V15, W15, X15 and Y15 before your changes. The correct formulas use cells T15, W15, Z15, AC15, AF15 and AI15 respectively.
Locate the fill handle on the lower right hand corner of cell BB15, click on it and drag the formula all of the way down to row 144 (i.e., to cell BB144). Release the mouse button and then click once again, anywhere, so as to enter the formula.
Repeat this process for columns BC, BD, BE, BF and BG, starting in row 15 and dragging the formula down to row 144.
Save the changes.
Bring up all of the other spreadsheets that you wish to modify. Highlight cell BA12, press Shift and click on cell BG144 to highlight the area of interest. Release the Shift key, click on Edit and click on Copy.
Now highlight the target area of the other spreadsheets, one at a time. Highlight cell BA12, press Shift and click on cell BG144 to highlight the target area. Release the Shift key, click on Edit and click on Paste. (We want to copy everything, including formulas as well as values.) Then click on any cell to complete the process.
You will have to return to your original spreadsheet each time to click on (Edit then) Copy, but your highlighted area does not change.
About the Formula
Notice that references to cells M1 and M2 are absolute ($M$1 and $M$2) and that the reference to column BA is absolute ($BA15:$BA144). That is, the column does not change when you drag a formula to other cells. Column BA lists the years and cells M1 and M2 establish the range of interest. [When I first wrote the formula, I forgot to hold column BA constant.]
Now that I know how to make the formulas work, I think that I could simplify them. But I choose not to do so. Having the special columns (BA through BG) that identify when there is a failure in a year of interest has turned out to be great for debugging.
When I first wrote the formula, I determined whether the contents of a cell of interest were less than or equal to zero. That was a mistake. All of the blank cells in the basic print out are assigned a value of zero. If the logic had identified cells with a balance of zero as failures, many later start years would have shown up as failures. And so they did until I changed the logic to exclude zero.
Using the New Summaries
All of the references in the new summary tables are to the December 31st balances. As such, all of the years indicated correspond to the same years as the FIRECalc calculator. [In my own data analysis, I have generally selected balances from January 1st, which excludes the 60th year in a sequence. My references are one year later than those of FIRECalc.]
The Retire Early Safe Withdrawal (Rate) Calculator includes dummy data entries that extend into 2010. That can create some problems. For example, I have found conditions that show that a portfolio started in 2000 would have failed by the tenth year. Take heed. Be sure to realize when the last year of a partial sequence occurs.
Notice that the rows for the new tables are the same as for the original data. Notice also that you can click on a formula and see a reference to the corresponding column. For example, if you click on cell BD33, the formula bar shows a reference cell Z33. If you scroll over to cell Z33 and highlight it, you will see the formula =EQ539. Now press special function key F5, type in eq539 and click OK. It takes you to the corresponding summary location in row 539. Here is my point. Row 539 is the line with all of the balances for this particular start year (1889 as seen in cells S33 and BA33). You can easily scroll over to where the numbers are calculated so as to perform detailed analysis related to that particular start year.
Difficulty remains when examining partially completed sequences and sequences that end in the future. As a practical matter, I have found that sequences begun in the 1980s have not had time to fail. Only those sequences that fail and also extend past 2002 need to be examined. You can use the procedure that I have just mentioned to locate a relevant row, to go to it and then to scroll over to see whether a failure occurred later than 2002.
Although I have used the words Start Year and End Year, what one specifies in cells M1 and M2 is the range of the years at the beginning of sequences of interest. If you were to write 1921 and 1922, respectively, you would examine all historical sequences of sixty years or less beginning in 1921 and in 1922. You would not be looking at a single sequence that began in 1921 and ended in 1922.
By entering various start years and end years, you can focus upon a specific time period such as the Great Depression or the 1960s. You can look at good times to retire separately from dangerous times.
Have fun.
John R.

