Easier Data Analysis
Posted: Wed Dec 10, 2003 3:26 pm
The Retire Early Safe Withdrawal (Rate) Calculator is powerful, useful and helpful. Unfortunately, its data presentation makes analysis difficult. Here is how to present data in a form well suited for analysis. It still takes time. I have not been able to automate everything.
These steps apply to all versions of the calculator, including those that vary the full three portfolio allocations in accordance to P/E10.
While writing up these instructions, I found that using the Shift key speeds up this process even more. I have included additional comments about how to take advantage of it.
Initial Steps
Bring up a version of the calculator and put in a test condition (i.e., withdrawal rates, expenses, allocations and so on) that you wish to analyze. Open up a new spreadsheet as well.
On the new spreadsheet, type in the conditions. I used the first five lines, starting in column A. Next, save and title your spreadsheet. In my most recent case, I was looking at a 12% withdrawal rate with an 80% stock allocation. I titled my spreadsheet WR 12 stocks 80% (12-10-03), which will be easy for me to find.
In my case I am looking at times later than the 1881-1920 anomaly. My first year is 1921. I clicked on cell A7 and typed in 1921. Next, I clicked on cell A8. Up in the formula bar at the top, I wrote =A7+1 and clicked on the check mark. Pressing enter would have done the same thing (as clicking on the check mark). The entry in cell A8 becomes 1922.
Locate the fill handle at the lower right-hand corner of cell A8. Move your mouse pointer over it until it changes to a cross hair symbol and press down on the (left-hand) mouse button and hold it down. Drag the fill handle down. When you release the mouse button, subsequent years will be shown highlighted (i.e., white numbers on a black background). Click the mouse button once (anywhere, including in the highlighted area) and the numbers will be entered. Highlight the last cell and repeat this process until you have entered all of the years through 2001 (or earlier, depending upon your needs). In my case, the year 2001 occurs in cell A87. If you overshoot your goal, as I did, you can highlight the cells after 2001 and then press delete (and finally click on another cell to complete the process).
Click on cell B6 and enter the number 1. Click on cell C6. Go to the formula bar, write =B6+1 and then enter (or click on the check mark). If you click on the check mark, cell C6 shows the number 2 and it remains highlighted. If you press enter, cell C6 shows the number 2 but cell C7 becomes highlighted. Highlight cell C6, find the fill handle (on the lower right-hand corner), click down on the mouse and drag it to the right. Continue until row 6 shows all of the numbers from 1 through 60. That happens in cell BI6.
[Some of you may prefer to start at zero and end with year 60 at column BJ. That would show the entire 60 years that the Retire Early Safe Withdrawal Calculator calculates. I use an initial balance of $100000 to reduce the effects of rounding errors from withdrawals and expenses. My first entry is always $100000. Hence, I actually show only 59 years. This happens because I use the top row of data for each year instead of the bottom row. The bottom row associated with a year corresponds to December 31st after trading ended. That is where the $100000 first appears. The top row of the next column carries over the same amount ($100000) and corresponds to January 1st before any trading has begun. I prefer to collect my data from that row.
For example, the very first year analyzed begins in year 1871 in cell B215 according to the first method. It extends to cell BJ215 (i.e., year 1931) and it has 61 balances from the end of year zero through the end of year 60. This designation of the start year corresponds to what FIRECalc designates as a start year for the same sequence.
My own preference is to begin with cell C199 and to identify the year as 1872. I take data from cells C199 through cell BJ199, which produces 60 balances corresponding to the very beginning (January 1st) of years 1872 through 1931. I collect data for 59 years after the start, not 60. When I analyze my data, I normally list the FIRECalc year along with my Retire Early Safe Withdrawal Calculator reference, which is one year greater. In this case, FIRECalc would list the year as 1871 and I would list it as 1872. Knowing the FIRECalc year is important when looking at dividend yields, P/E10 and so forth.]
Collecting the Data
Now begins the actual process of copying data from the Retire Early Safe Withdrawal Calculator onto your new spreadsheet.
I first highlight the spreadsheet, starting at the last column BI (assuming that the numbers are from 1 through 60 instead of 0 through 60). I click down on my mouse and hold the button down. I move the pointer all of the way to the left, which highlights everything including the year in column A. I move back to column B and then release my mouse button. I had originally found that this sequence is the fastest for data collection. Now minimize this spreadsheet.
Next, I bring up the Retire Early Safe Withdrawal Calculator. I prefer to use its intermediate size. It makes switching between the calculator and the data analysis spreadsheet easy.
It is very helpful to have a list of column letters and their corresponding years available to prevent making errors. Since my first entry is for year 1921, I move to column AZ and down to the first sequence that starts in that year. It is in row 1081. [As indicated, others may want to start with cell AY1097.] Highlight the first cell (AZ1081) by clicking down on your mouse button and drag it to the right. Continue slightly beyond the spreadsheet. The spreadsheet will start moving to the left. Wait until you get to the end of the data, find the final cell and then release the mouse button.
During this process, there will be an indication of the number of rows and column that are being highlighted. You are looking for 1RX60C. If you start to highlight more than one row, do not be concerned. Adjust your mouse until only one row remains highlighted. It will always be the correct row since it always contains the first cell that you highlighted.
Next, copy the highlighted cells. Click on Edit and then on Copy at the very top of your screen. Restore the data analysis spreadsheet. Click on Edit and then on Paste Special at the top of the screen. A special box will pop up. In the section listed as paste, select Values instead of the default choice, which is All. Then click on OK. Finally, click on an empty cell in the next row. If you have followed my sequence, this will be in column BI (or cell BI8).
Then repeat the process. Highlight the area on the data analysis spreadsheet. Minimize it. Highlight the next row of data on the Retire Early Safe Withdrawal Calculator spreadsheet. Copy it. Restore the data analysis spreadsheet and Paste Special the Values onto it. Be sure to Save the data on the spreadsheet every now and then just in case something goes wrong.
Eventually, you will want to copy fewer than 60 cells since that will be beyond the final year on the calculator. It is OK to copy blank cells. Having the display of rows and columns helps out. I personally tend to make changes in increments of ten. That is, I have started with 60 cells. Then I have reduced the number to 50 cells, 40 cells, 30 cells and finally 20 cells. Just remember that you have to have the same number of cells highlighted on the two spreadsheets.
At some point, you are going to want to Format your analysis spreadsheet. Highlight cell B7. Then locate cell BI87. Press Shift. Then click on cell BI87. That will highlight the data area of the spreadsheet. Click on Format at the very top of your screen and then click on Cells. Using the Number tab, select Currency. Change the number of decimals to zero. I prefer to change the symbol from $ to None. Select parentheses as the method for displaying negative numbers. I have chosen to use black. Others may prefer to select the color red.
You will want to format row 6 as well. First, highlight cell BI6. Return to the beginning. (Do not use CTRL+Home. It will undo your initial highlighting of cell BI6.) Press Shift. Then highlight cell A6 and release your mouse button. Proceed to Format the Cells as you so desire. I prefer General under Number and Right under Alignment.
It may seem simpler to highlight cell A6 (or any other cell on row 6) and then to select to Format Rows. The available commands allow you to improve the physical appearance of the spreadsheet, but they do not affect the contents of the cells.
Finally, you will want to highlight cells A7 through A87 and format the column with the years. This is one case in which it really does not matter if you go beyond the last cell. My choices under Format Cells were General under Number and Right under Alignment.
Faster Data Collection
I now use the Shift key to speed up my data collection. To use it, you highlight the first cell and click. You go to the end of the data (either to the left or right), press Shift and hold it down while you click the final cell in the row. That highlights everything from the first cell to the last. (You can also click on another row so as to highlight everything defined by the diagonal, but we are copying only one row at a time.)
As long as you keep the Shift key down, it will display the number of rows and columns. If you forget, you can press the Shift key once again, then click on the last cell in the highlighted row. (If you click on another cell, it will reshape the highlighted area, leaving the initial cell unaffected.) As long as the Shift key remains down, you will see the number of rows and columns. That comes in handy when the number of columns with data falls below 60.
If you press the Shift key and click on the wrong number of columns, you can correct your error easily. Press the Shift key and click on another cell or click, hold the mouse button down and drag the mouse until you get the right number of columns to copy.
Using the scroll bars and reading the information about the number of columns really speeds up this process.
Special Methods of Presentation
I like to read my read-out vertically as well as horizontally. This is easy enough to do by using Transpose within the Paste Special command.
Open up a blank spreadsheet. You are going to copy the active area of your original spreadsheet onto the new one, except that you will replace rows by columns (and columns by rows). In the original spreadsheet, the active area extended to column BI and row 87. Replacing letters with numbers, BI is the 61st letter. (A-Z is 26 letters. AA-AZ is another 26 letters. BA-BI is 9 letters.) An easy way to determine this is to highlight cell A1, press Shift and then highlight cell BI. It shows 1RX61C. Press Shift once again. Press down on the mouse button and drag it to the right until you see 1RX87C. That lets you know that the 87th column is CI.
Highlight A1 on your blank spreadsheet. Press Shift and then highlight cell CI61. You can do this in several steps. The initial cell remains the same every time that you press Shift and then highlight a new cell, but the highlighted area changes.
Highlight the active area on your original spreadsheet. It extends to row 87 and column BI. Press A1, shift and then click on cell BI87. (It is easy to scroll the rows and columns so as to located this cell using the numbers on the left and the letters on the top. Be sure to keep the Shift button down while you do this, just in case you accidentally click on something that you shouldn't.) This highlights the spreadsheet. Now copy it (using Edit and then Copy at the very top of your screen). Bring up the new spreadsheet. Now Paste Special (starting with Edit at the top of your screen) and click the box labeled Transpose. (Leave the other items unchanged.) Then click on OK. Now click on any cell in the spreadsheet. You have done your basic task. You now have the start years running from left to right and the number of years in retirement running from top to bottom.
At this point I saved and named my new spreadsheet. My choice of names was WR 12 transposed 80% (12-10-03). Next, I highlighted cell B1, cut it, highlighted cell A2 and pasted it. I highlighted cell C1, cut it and pasted it into cell A3. I highlighted cell E1, cut it and pasted it into cell A5.
I highlighted cell B1. Any cell in column B would have done just as well. I pressed Format, then (Format) Column and the (Format Column) Width. I changed the number from 8.43 to 10 and clicked OK. That keeps my text within columns A and B.
Finally, I highlighted cells C1, D1 and E1 (any other choice of rows would have done just as well). Under Edit, I selected Delete. In the box that came up, I selected Entire Column. Then I pressed OK. Everything to the right shifted over. The number of years into retirement is now listed in column C.
You may prefer to save those columns and/or introduce new rows. For example, if you have created one of my special versions of the Retire Early Safe Withdrawal Calculator, in which I have implemented the originally planned for switching at two threshold levels with three stock allocations, you might want to copy part of row 2550. (In this case, it would be from BI through EB, corresponding to years 1921 through 2001.) That row includes the stock market gain multipliers (equal to one plus the total return from the previous year with dividends reinvested). You might want to copy this row on top of the transposed (second) spreadsheet or as a new column inserted to the left of the original spreadsheet. In both cases, you will want to copy and Paste Special only the Values and not the formulas. For the original spreadsheet, you would want to Paste Special the Value and Transpose them into a column as well.
Even with the basic calculator, you may want to extract something from a particular row, such as P/E10. Insert a row or a column and Paste Special the Values that you wish to list and also Transpose when appropriate.
Changes to Calculators
The idea of matching the stock market returns in row 2550 with the year in which they occurred motivated me to add another row to my two-threshold switching calculators.
I wrote Year in cell A2551 and then clicked on the check mark. I wrote 1871 in cell B2551 and then click on the check mark. I wrote =B2551+1 in cell C2551, clicked on the check mark and then located the fill handle at the lower right-hand corner of cell C2551. I dragged it (in several steps) to year 2010 (i.e., cell EK2551). This allows me to look at each year's stock gains.
Summary
These steps make the review of the Retire Early Safe Withdrawal Calculator results manageable. It is much better than my previous, entirely manual approach.
Have fun.
John R.
These steps apply to all versions of the calculator, including those that vary the full three portfolio allocations in accordance to P/E10.
While writing up these instructions, I found that using the Shift key speeds up this process even more. I have included additional comments about how to take advantage of it.
Initial Steps
Bring up a version of the calculator and put in a test condition (i.e., withdrawal rates, expenses, allocations and so on) that you wish to analyze. Open up a new spreadsheet as well.
On the new spreadsheet, type in the conditions. I used the first five lines, starting in column A. Next, save and title your spreadsheet. In my most recent case, I was looking at a 12% withdrawal rate with an 80% stock allocation. I titled my spreadsheet WR 12 stocks 80% (12-10-03), which will be easy for me to find.
In my case I am looking at times later than the 1881-1920 anomaly. My first year is 1921. I clicked on cell A7 and typed in 1921. Next, I clicked on cell A8. Up in the formula bar at the top, I wrote =A7+1 and clicked on the check mark. Pressing enter would have done the same thing (as clicking on the check mark). The entry in cell A8 becomes 1922.
Locate the fill handle at the lower right-hand corner of cell A8. Move your mouse pointer over it until it changes to a cross hair symbol and press down on the (left-hand) mouse button and hold it down. Drag the fill handle down. When you release the mouse button, subsequent years will be shown highlighted (i.e., white numbers on a black background). Click the mouse button once (anywhere, including in the highlighted area) and the numbers will be entered. Highlight the last cell and repeat this process until you have entered all of the years through 2001 (or earlier, depending upon your needs). In my case, the year 2001 occurs in cell A87. If you overshoot your goal, as I did, you can highlight the cells after 2001 and then press delete (and finally click on another cell to complete the process).
Click on cell B6 and enter the number 1. Click on cell C6. Go to the formula bar, write =B6+1 and then enter (or click on the check mark). If you click on the check mark, cell C6 shows the number 2 and it remains highlighted. If you press enter, cell C6 shows the number 2 but cell C7 becomes highlighted. Highlight cell C6, find the fill handle (on the lower right-hand corner), click down on the mouse and drag it to the right. Continue until row 6 shows all of the numbers from 1 through 60. That happens in cell BI6.
[Some of you may prefer to start at zero and end with year 60 at column BJ. That would show the entire 60 years that the Retire Early Safe Withdrawal Calculator calculates. I use an initial balance of $100000 to reduce the effects of rounding errors from withdrawals and expenses. My first entry is always $100000. Hence, I actually show only 59 years. This happens because I use the top row of data for each year instead of the bottom row. The bottom row associated with a year corresponds to December 31st after trading ended. That is where the $100000 first appears. The top row of the next column carries over the same amount ($100000) and corresponds to January 1st before any trading has begun. I prefer to collect my data from that row.
For example, the very first year analyzed begins in year 1871 in cell B215 according to the first method. It extends to cell BJ215 (i.e., year 1931) and it has 61 balances from the end of year zero through the end of year 60. This designation of the start year corresponds to what FIRECalc designates as a start year for the same sequence.
My own preference is to begin with cell C199 and to identify the year as 1872. I take data from cells C199 through cell BJ199, which produces 60 balances corresponding to the very beginning (January 1st) of years 1872 through 1931. I collect data for 59 years after the start, not 60. When I analyze my data, I normally list the FIRECalc year along with my Retire Early Safe Withdrawal Calculator reference, which is one year greater. In this case, FIRECalc would list the year as 1871 and I would list it as 1872. Knowing the FIRECalc year is important when looking at dividend yields, P/E10 and so forth.]
Collecting the Data
Now begins the actual process of copying data from the Retire Early Safe Withdrawal Calculator onto your new spreadsheet.
I first highlight the spreadsheet, starting at the last column BI (assuming that the numbers are from 1 through 60 instead of 0 through 60). I click down on my mouse and hold the button down. I move the pointer all of the way to the left, which highlights everything including the year in column A. I move back to column B and then release my mouse button. I had originally found that this sequence is the fastest for data collection. Now minimize this spreadsheet.
Next, I bring up the Retire Early Safe Withdrawal Calculator. I prefer to use its intermediate size. It makes switching between the calculator and the data analysis spreadsheet easy.
It is very helpful to have a list of column letters and their corresponding years available to prevent making errors. Since my first entry is for year 1921, I move to column AZ and down to the first sequence that starts in that year. It is in row 1081. [As indicated, others may want to start with cell AY1097.] Highlight the first cell (AZ1081) by clicking down on your mouse button and drag it to the right. Continue slightly beyond the spreadsheet. The spreadsheet will start moving to the left. Wait until you get to the end of the data, find the final cell and then release the mouse button.
During this process, there will be an indication of the number of rows and column that are being highlighted. You are looking for 1RX60C. If you start to highlight more than one row, do not be concerned. Adjust your mouse until only one row remains highlighted. It will always be the correct row since it always contains the first cell that you highlighted.
Next, copy the highlighted cells. Click on Edit and then on Copy at the very top of your screen. Restore the data analysis spreadsheet. Click on Edit and then on Paste Special at the top of the screen. A special box will pop up. In the section listed as paste, select Values instead of the default choice, which is All. Then click on OK. Finally, click on an empty cell in the next row. If you have followed my sequence, this will be in column BI (or cell BI8).
Then repeat the process. Highlight the area on the data analysis spreadsheet. Minimize it. Highlight the next row of data on the Retire Early Safe Withdrawal Calculator spreadsheet. Copy it. Restore the data analysis spreadsheet and Paste Special the Values onto it. Be sure to Save the data on the spreadsheet every now and then just in case something goes wrong.
Eventually, you will want to copy fewer than 60 cells since that will be beyond the final year on the calculator. It is OK to copy blank cells. Having the display of rows and columns helps out. I personally tend to make changes in increments of ten. That is, I have started with 60 cells. Then I have reduced the number to 50 cells, 40 cells, 30 cells and finally 20 cells. Just remember that you have to have the same number of cells highlighted on the two spreadsheets.
At some point, you are going to want to Format your analysis spreadsheet. Highlight cell B7. Then locate cell BI87. Press Shift. Then click on cell BI87. That will highlight the data area of the spreadsheet. Click on Format at the very top of your screen and then click on Cells. Using the Number tab, select Currency. Change the number of decimals to zero. I prefer to change the symbol from $ to None. Select parentheses as the method for displaying negative numbers. I have chosen to use black. Others may prefer to select the color red.
You will want to format row 6 as well. First, highlight cell BI6. Return to the beginning. (Do not use CTRL+Home. It will undo your initial highlighting of cell BI6.) Press Shift. Then highlight cell A6 and release your mouse button. Proceed to Format the Cells as you so desire. I prefer General under Number and Right under Alignment.
It may seem simpler to highlight cell A6 (or any other cell on row 6) and then to select to Format Rows. The available commands allow you to improve the physical appearance of the spreadsheet, but they do not affect the contents of the cells.
Finally, you will want to highlight cells A7 through A87 and format the column with the years. This is one case in which it really does not matter if you go beyond the last cell. My choices under Format Cells were General under Number and Right under Alignment.
Faster Data Collection
I now use the Shift key to speed up my data collection. To use it, you highlight the first cell and click. You go to the end of the data (either to the left or right), press Shift and hold it down while you click the final cell in the row. That highlights everything from the first cell to the last. (You can also click on another row so as to highlight everything defined by the diagonal, but we are copying only one row at a time.)
As long as you keep the Shift key down, it will display the number of rows and columns. If you forget, you can press the Shift key once again, then click on the last cell in the highlighted row. (If you click on another cell, it will reshape the highlighted area, leaving the initial cell unaffected.) As long as the Shift key remains down, you will see the number of rows and columns. That comes in handy when the number of columns with data falls below 60.
If you press the Shift key and click on the wrong number of columns, you can correct your error easily. Press the Shift key and click on another cell or click, hold the mouse button down and drag the mouse until you get the right number of columns to copy.
Using the scroll bars and reading the information about the number of columns really speeds up this process.
Special Methods of Presentation
I like to read my read-out vertically as well as horizontally. This is easy enough to do by using Transpose within the Paste Special command.
Open up a blank spreadsheet. You are going to copy the active area of your original spreadsheet onto the new one, except that you will replace rows by columns (and columns by rows). In the original spreadsheet, the active area extended to column BI and row 87. Replacing letters with numbers, BI is the 61st letter. (A-Z is 26 letters. AA-AZ is another 26 letters. BA-BI is 9 letters.) An easy way to determine this is to highlight cell A1, press Shift and then highlight cell BI. It shows 1RX61C. Press Shift once again. Press down on the mouse button and drag it to the right until you see 1RX87C. That lets you know that the 87th column is CI.
Highlight A1 on your blank spreadsheet. Press Shift and then highlight cell CI61. You can do this in several steps. The initial cell remains the same every time that you press Shift and then highlight a new cell, but the highlighted area changes.
Highlight the active area on your original spreadsheet. It extends to row 87 and column BI. Press A1, shift and then click on cell BI87. (It is easy to scroll the rows and columns so as to located this cell using the numbers on the left and the letters on the top. Be sure to keep the Shift button down while you do this, just in case you accidentally click on something that you shouldn't.) This highlights the spreadsheet. Now copy it (using Edit and then Copy at the very top of your screen). Bring up the new spreadsheet. Now Paste Special (starting with Edit at the top of your screen) and click the box labeled Transpose. (Leave the other items unchanged.) Then click on OK. Now click on any cell in the spreadsheet. You have done your basic task. You now have the start years running from left to right and the number of years in retirement running from top to bottom.
At this point I saved and named my new spreadsheet. My choice of names was WR 12 transposed 80% (12-10-03). Next, I highlighted cell B1, cut it, highlighted cell A2 and pasted it. I highlighted cell C1, cut it and pasted it into cell A3. I highlighted cell E1, cut it and pasted it into cell A5.
I highlighted cell B1. Any cell in column B would have done just as well. I pressed Format, then (Format) Column and the (Format Column) Width. I changed the number from 8.43 to 10 and clicked OK. That keeps my text within columns A and B.
Finally, I highlighted cells C1, D1 and E1 (any other choice of rows would have done just as well). Under Edit, I selected Delete. In the box that came up, I selected Entire Column. Then I pressed OK. Everything to the right shifted over. The number of years into retirement is now listed in column C.
You may prefer to save those columns and/or introduce new rows. For example, if you have created one of my special versions of the Retire Early Safe Withdrawal Calculator, in which I have implemented the originally planned for switching at two threshold levels with three stock allocations, you might want to copy part of row 2550. (In this case, it would be from BI through EB, corresponding to years 1921 through 2001.) That row includes the stock market gain multipliers (equal to one plus the total return from the previous year with dividends reinvested). You might want to copy this row on top of the transposed (second) spreadsheet or as a new column inserted to the left of the original spreadsheet. In both cases, you will want to copy and Paste Special only the Values and not the formulas. For the original spreadsheet, you would want to Paste Special the Value and Transpose them into a column as well.
Even with the basic calculator, you may want to extract something from a particular row, such as P/E10. Insert a row or a column and Paste Special the Values that you wish to list and also Transpose when appropriate.
Changes to Calculators
The idea of matching the stock market returns in row 2550 with the year in which they occurred motivated me to add another row to my two-threshold switching calculators.
I wrote Year in cell A2551 and then clicked on the check mark. I wrote 1871 in cell B2551 and then click on the check mark. I wrote =B2551+1 in cell C2551, clicked on the check mark and then located the fill handle at the lower right-hand corner of cell C2551. I dragged it (in several steps) to year 2010 (i.e., cell EK2551). This allows me to look at each year's stock gains.
Summary
These steps make the review of the Retire Early Safe Withdrawal Calculator results manageable. It is much better than my previous, entirely manual approach.
Have fun.
John R.