Easier Data Analysis

Research on Safe Withdrawal Rates

Moderator: hocus2004

Post Reply
JWR1945
***** Legend
Posts: 1697
Joined: Tue Nov 26, 2002 3:59 am
Location: Crestview, Florida

Easier Data Analysis

Post by JWR1945 »

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.
User avatar
BenSolar
*** Veteran
Posts: 242
Joined: Mon Nov 25, 2002 5:46 am
Location: Western NC

Post by BenSolar »

JWR1945 wrote:Easier Data Analysis....
These steps make the review of the Retire Early Safe Withdrawal Calculator results manageable. It is much better than my previous, entirely manual approach.
Rec, rec, rec! :great: :D
"Do not spoil what you have by desiring what you have not; remember that what you now have was once among the things only hoped for." - Epicurus
JWR1945
***** Legend
Posts: 1697
Joined: Tue Nov 26, 2002 3:59 am
Location: Crestview, Florida

Post by JWR1945 »

I have added annualized returns to my data analysis spreadsheets. Those spreadsheets allow for detailed analysis of the Retire Early Safe Withdrawal (Rate) Calculator, Version 1.61, November 2002. You can make similar spreadsheets for data analysis from all of my modified calculators as well.

Continuing with my usual practice, I first made a new spreadsheet and then copied from an original. Most recently, I copied the data analysis spreadsheet that I had labeled WR 6 stocks 80%. It is for a 6% withdrawal rate for an 80% stock / 20% commercial paper portfolio, starting with a $100000 initial balance and with 0.20% expenses (using fixed allocations).

Details

To make the copy, I highlighted cell BI87, pressed Shift and clicked on cell A1. Then I went to Edit and chose Copy. I brought up a new spreadsheet, highlighted cell BI87, pressed shift and clicked on cell A1 (which highlighted the target area of the new spreadsheet). Then I went to Edit and chose Paste Special. I chose to paste Values instead of All since I did not want to carry any hidden formulas from the old data analysis spreadsheet. (The years and the number of years have hidden formulas.)

To get to BI87 easily, I pressed my (keyboard) function key F5, typed in bi87 for the reference and clicked on OK. I saved the new spreadsheet and titled it Annualized WR 6 stocks 80%. I returned to my original data analysis spreadsheet, clicked on a cell to remove the highlight (to remove a highlighted copy area, click on the formula bar right above the data) and closed it.

I changed the words at the top of the new spreadsheet. In cell A1, I wrote Return versus year:. In cell C1, I wrote annualized (80% stocks, 20% commercial paper, 0.20% expenses and 6% withdrawals). I deleted the entire row four times until the number of years was in row 2. (Use Edit, Delete, Entire Row. The CTRL or Shift key can speed up this process, allowing you to highlight several rows in the same column.)

The new spreadsheet extends to column BI for year number 60 and to row 83 for year 2001. I copied it (highlight BI83, press shift, highlight A1, Edit, Copy) onto rows 101 through 183 (with the same columns). I highlighted cell BI183, press Shift, highlighted cell A101, went to Edit and clicked on Paste. (Actually, I tried cell A100 a couple of times before I realized what was going on. I never make my write up on the first try. I wait until I have been successful and duplicate every step of my second try. That way, I don't leave anything out.) I went to the formula bar at the top and clicked in the formula area to end the active highlighting of the copy area. I highlighted cell A100. I wrote These rows are 100+above row numbers into the formula bar and I clicked on the check mark.

I highlighted cell B103 and wrote:
=IF(B3>0,POWER(B3/100000,1/B$102),0)

and then I clicked the check mark.

I located the fill handle on the lower right hand corner (of the highlighted cell B103), clicked down on my mouse, held the button down, pulled it down to the last row (cell B183). Then I released the button and then clicked on a cell (usually the last cell that I had highlighted). Actually, I did this in several steps. All of the 100000 numbers changed to 1 as I dragged the formula down to the last row.

Note about the fill handle: the mouse cursor's plus sign changes to cross hairs (as if you were targeting something). This lets you know when you can use it.

I returned to cell B103, located the fill handle and dragged the formula across the row (to cell BI103). Starting from BI103, I located the fill handle and dragged the formula down to cell BI183 (which was in an empty/white area). The empty cells in the column had zeroes after I dragged the new formula down.

Finally, I highlighted a cell with the formula (in column B or BI, but nowhere else) for each row. I dragged the formula across all of the columns except for column A (which shows the year).

Do not use the Shift key with the fill handle. If you make a mistake, as I have done at times, remember that the formula references the original table (in rows 3 through 83) and not itself. Simply find a cell where the formula has been applied correctly (usually in column B or BI), find the fill handle and drag it across the entire row (excluding column A). If you invalidate an entry in column B or BI, click on a cell above it, locate the fill handle and drag the correct formula down. This is best done in column B since the correct value of the cell is always 1.

About the formula

The formula makes a check of whether a cell is positive. If it is not, it returns a zero. If you were to write a simpler version: =IF(B3>0,POWER(B3/100000,1/B$102)) instead of =IF(B3>0,POWER(B3/100000,1/B$102),0), the formula would return FALSE whenever a balance was negative or zero.

Notice that the cell reference B3 for cell B103 (and C3 for cell C103 and so on) goes back to the original data. If you were to change the value of B3, it will affect cell B103 (and so forth). For example, if you replace the 100000 in cell B3 with 0, cell B103 becomes 0 instead of 1.

Notice also that I have kept the row with the number of years (with values B$102) constant. I failed to do that on my first try and it created an error. I was raising everything to a power very close to zero. The columns vary now, but the row remains constant.

Potential Applications

With my first spreadsheet, I set the withdrawal rate equal to zero but I left the expenses at 0.20%. That produced the total return of portfolio that matches the S&P 500 index (allocated between stocks and, in this case, commercial paper) with dividends reinvested but with expenses for the account. The annualized gain multipliers show the annualized returns as they vary with time. (To calculate the rate of return, subtract 1 from the gain multiplier and multiply by 100%. The annualized return is less than the average return whenever there is volatility.)

With my later spreadsheets, I am using nonzero rates of withdrawal and I am coming up with an equivalent annualized rate of return. I suspect that this information will be useful.

Those familiar with Gummy's formula for Safe Withdrawal Rates will recognize that the balance after N years (divided by the initial balance) is the product of two terms. The first corresponds to with total return without withdrawals and the second includes the effects of making withdrawals. The second term falls to zero or becomes negative when a portfolio fails. By comparing the balances with withdrawals to those without (i.e., dividing the two), you can calculate the second, more difficult term, in Gummy's equation.

I do not know for sure where these calculations will take us.

Have fun.

John R.
Post Reply