## Newer Deluxe Versions of the SWR Calculators

Research on Safe Withdrawal Rates

Moderator: hocus2004

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

### Newer Deluxe Versions of the SWR Calculators

Newer Deluxe Versions of the SWR Calculators

This is my latest modification to the Retire Early Safe Withdrawal [Rate] Calculator, Version 1.61, 7 November 2002. I refer to these as Deluxe Versions 1.0 A.

With this change, you can read year-by-year balances in easy to read tables. They used to be difficult to locate. Now, the initial balances are always in column B. The balances after one year are always in column C. The balances after two years are always in column D. And so forth. The balances after 60 years are always in column BJ. Column A lists the start years, the years at the very beginning of individual historical sequences.

I have included tables of Current Balances, in both nominal and real (i.e., inflation adjusted) dollars, nominal and real percentage returns (annualized) and the inflation multiplier (or, more precisely, a deflator) for each year into a historical sequence.

I have also improved my presentations by better Formatting. I list all losses in red, which makes analysis easier.

As always, these latest changes may be made on any or all of the previously modified calculators. Nothing is lost. Nothing is destroyed.

Current Balances (Nominal)

This is the most important improvement. It is also the most difficult.

If you have made some of the previous changes, rows 2550, 2552 and 2560 are already used. They are the stock gain and year for use in the switching formulas and dividend yield for reference.

The new data series are placed into blocks of 200 rows each, beginning with row 2600.

Highlight cell A2600. Write Year into the formula area and click the check mark. Highlight cell B2600. Write the number 0 into the formula area and click the check mark. Highlight cell C2600. Write =B2600+1 into the formula area and click the check mark.

Find the fill handle on the lower right hand corner of cell C2600. When the fat plus sign of the cursor changes to narrow cross hairs, click and hold down your mouse button. Move the fill handle to the right to column BJ (i.e., cell BJ2600). Release your mouse button and click on any cell. I chose cell BJ2600, but it could have been anything else. You can use the fill handle in several steps, including going backwards. That makes things even easier.

Row 2600 now shows the number of years into retirement, beginning at zero in column B and ending with 60 in column BJ.

Highlight cell A2601. Write 1871 and press Enter. Highlight cell A2602 and write =A2601+1 into the formula area. Click the check mark. The cell will now read 1872. Find the fill handle in the lower right hand corner of cell A2602. When the cursor changes from a fat plus sign to narrow cross hairs, click and hold down your mouse button. Drag the formula down to row 2730 (i.e., cell A2730). Release your mouse button and click on any cell. I clicked on cell A2730.

Return to cell B2601. It is necessary at this point to write the locations of the first cell in each year for starting a retirement into this column. I have listed those locations below. This is the most difficult step. I recommend that you make a hard copy of the table to make this easier for you. (Use the Quote feature at the upper right hand corner of this post to make copying easier.)

In all cases, it is necessary to write = before writing the cell location and then clicking on the check mark (or pressing Enter). For example, the formula for cell B2601 is =B215. The formula for cell B2602 is =C233.

Code: Select all

``Year   Location1871   B2151872   C2331873   D2511874   E2691875   F2871876   G3051877   H3231878   I3411879   J3591880   K3771881   L3951882   M4131883   N4311884   O4491885   P4671886   Q4851887   R5031888   S5211889   T5391890   U5571891   V5751892   W5931893   X6111894   Y6291895   Z6471896   AA6651897   AB6831898   AC7011899   AD7191900   AE737``

Code: Select all

``Year   Location1901   AF7551902   AG7731903   AH7911904   AI8091905   AJ8271906   AK8451907   AL8631908   AM8811909   AN8991910   AO9171911   AP9351912   AQ9531913   AR9711914   AS9891915   AT10071916   AU10251917   AV10431918   AW10611919   AX10791920   AY10971921   AZ11151922   BA11331923   BB11511924   BC11691925   BD11871926   BE12051927   BF12231928   BG12411929   BH12591930   BI1277``

Code: Select all

``Year   Location1931   BJ12951932   BK13131933   BL13311934   BM13491935   BN13671936   BO13851937   BP14031938   BQ14211939   BR14391940   BS14571941   BT14751942   BU14931943   BV15111944   BW15291945   BX15471946   BY15651947   BZ15831948   CA16011949   CB16191950   CC16371951   CD16551952   CE16731953   CF16911954   CG17091955   CH17271956   CI17451957   CJ17631958   CK17811959   CL17991960   CM1817``

Code: Select all

``Year   Location1961   CN18351962   CO18531963   CP18711964   CQ18891965   CR19071966   CS19251967   CT19431968   CU19611969   CV19791970   CW19971971   CX20151972   CY20331973   CZ20511974   DA20691975   DB20871976   DC21051977   DD21231978   DE21411979   DF21591980   DG21771981   DH21951982   DI22131983   DJ22311984   DK22491985   DL22671986   DM22851987   DN23031988   DO23211989   DP23391990   DQ2357``

Code: Select all

``Year   Location1991   DR23751992   DS23931993   DT24111994   DU24291995   DV24471996   DW24651997   DX24831998   DY25011999   DZ25192000   EA2537``

It is also necessary to use the fill handle to drag each year's formula to column BJ. It is only necessary to highlight the cell in column B, locate the fill handle and drag the formula to column BJ. (I.e., click and hold down your mouse button starting with the fill handle at column B. Move the cursor to column BJ and then release the mouse button).

It is not necessary to enter the formula for filling in the data. (That is, moving one cell to the right for the next year.) Excel uses the formula from row 2600.

Continue this process for all of the years shown.

Prior to 1951, the maximum number of years will always be 60. Starting with 1951, the maximum number of years decreases by one year for each year later than 1950. This is because the data ends in 2010. Of course, all of the years after 2002 have dummy numbers. They are not, nor can they be, based on actual historical returns.

Annualized Percentage Returns (Nominal)

Highlight cell A2800. Write =A2600 and click the check mark. Locate the fill handle on the lower right hand side of the cell. Press down on the mouse button and drag the fill handle to column BJ (i.e., to cell BJ2800). Release the mouse button and click on any cell.

Row 2800 is now identical to row 2600. A2800 is Year. B2800 is 0. C2800 is 1. D2800 is 2. And so on. BJ2800 is 60.

Highlight cell A2801. Write =A2601 and click the check mark.
Highlight cell B2801. Write =IF(\$B2601>0,0,-100) and click the check mark.
Highlight cell C2801. Write =POWER(IF(C2601<0,0,C2601)*IF(\$B2601>0,1/\$B2601,0),1/C\$2600)*100-100
and click the check mark.

Highlight cell A2801. Locate the fill handle on the lower right hand corner of the cell. Press your mouse button and hold it down when the fat plus sign changes to narrow cross hairs. Drag the formula down to cell A2930. Release the mouse button. Then click on any cell. (I clicked on cell A2930.)

Do exactly the same thing for columns B and C. Start with cell B2801 and drag the formula down to B2930. Start with cell C2801 and drag the formula down to C2930.

Return to cell C2801. Use the fill handle to drag this formula across the entire row from cell C2801 to cell BJ2801.

Repeat this step for every remaining row in column C. Use the fill handle to drag the formula across the entire row. (Starting with 1951, the length of the rows should all decrease since the last data is for 2010. Of course, it is all dummy data after 2002.)

[These table entries are percentages. An alternative method to present percentages is through Formatting. If you choose to use Formatting instead, the formula for cell B2801 becomes =IF(\$B2601>0,0,-1) and the formula for cell C2801 becomes =POWER(IF(C2601<0,0,C2601)*IF(\$B2601>0,1/\$B2601,0),1/C\$2600)-1. I will say more about Formatting later.]

Inflation Multipliers (Deflators)

Highlight cell A3000. Write =A2600 and click the check mark. Locate the fill handle on the lower right hand side of the cell. Press down on the mouse button and drag the fill handle to column BJ (i.e., to cell BJ3000). Release the mouse button and click on any cell.

Row 3000 is now identical to row 2600. A3000 is Year. B3000 is 0. C3000 is 1. D3000 is 2. And so on. BJ3000 is 60.

Highlight cell A3001. Write =A2601 and click the check mark.

Highlight cell A3001. Locate the fill handle on the lower right hand corner of the cell. Press your mouse button and hold it down when the fat plus sign changes to narrow cross hairs. Drag the formula down to cell A3130. Release the mouse button. Then click on any cell. (I clicked on cell A3130.)

Highlight cell B3001. Write =\$B\$190/B\$190 and click the check mark.
Highlight cell B3002. Write =\$C\$190/C\$190 and click the check mark.
Highlight cell B3003. Write =\$D\$190/D\$190 and click the check mark.

Change the column letter to correspond to the next year each time. Continue doing this until you have reached cell B3130. The formula for cell B3130 is =\$EA\$190/EA\$190.

I found it helpful to drag the formula from cell B3001 down to cell B3130 and then modify the formula for each new row. I found it helpful to refer to the tables. The years and the column letters are the same as before. I also found it helpful to go back and highlight cells in their order to verify that no letters were skipped or repeated.

As before, the maximum number of years decreases starting in 1951. This time, however, if you copy the formula beyond the available data, you will get a divide-by-zero error. I left out protection because it would make entering the formulas a little bit more time consuming.

The real value of current balances is the product of these numbers and their corresponding nominal current balances.

Current Balances (Real)

Highlight cell A3200. Write =A2600 and click the check mark. Locate the fill handle on the lower right hand side of the cell. Press down on the mouse button and drag the fill handle to column BJ (i.e., to cell BJ3200). Release the mouse button and click on any cell.

Row 3200 is now identical to row 2600. A3200 is Year. B3200 is 0. C3200 is 1. D3200 is 2. And so on. BJ3200 is 60.

Highlight cell A3201. Write =A2601 and click the check mark.

Highlight cell A3201. Locate the fill handle on the lower right hand corner of the cell. Press your mouse button and hold it down when the fat plus sign changes to narrow cross hairs. Drag the formula down to cell A3330. Release the mouse button. Then click on any cell. (I clicked on cell A3330.)

Highlight cell B3201. Write =B2601*B3001 into the formula bar and then click the check mark. Locate the fill handle on the lower right hand corner of the cell. Press your mouse button and hold it down when the fat plus sign changes to narrow cross hairs. Drag the formula across BJ3201. Release the mouse button. Then click on any cell. (I clicked on cell BJ3201.)

Highlight cell B3201 once again. Locate the fill handle as before. This time, drag the formula down to cell B3330. I clicked on cell B3330 after I released the mouse button.

For every row from 3201 through 3330, start in column B. Use the fill handle to drag the formula to the right. Drag it to the right as far as there is data. (That is column BJ before 1951. Starting in 1951, it decreases by one column each year.)

Annualized Percentage Returns (Real)

Highlight cell A3400. Write =A2600 and click the check mark. Locate the fill handle on the lower right hand side of the cell. Press down on the mouse button and drag the fill handle to column BJ (i.e., to cell BJ3400). Release the mouse button and click on any cell.

Row 3400 is now identical to row 2600. A3400 is Year. B3400 is 0. C3400 is 1. D3400 is 2. And so on. BJ3400 is 60.

Highlight cell A3401. Write =A2601 and click the check mark.
Highlight cell B3401. Write =IF(\$B3201>0,0,-100) and click the check mark.
Highlight cell C3401. Write =POWER(IF(C3201<0,0,C3201)*IF(\$B3201>0,1/\$B3201,0),1/C\$2600)*100-100
and click the check mark.

Highlight cell A3401. Locate the fill handle on the lower right hand corner of the cell. Press your mouse button and hold it down when the fat plus sign changes to narrow cross hairs. Drag the formula down to cell A3530. Release the mouse button. Then click on any cell. (I clicked on cell A3530.)

Do exactly the same thing for columns B and C. Start with cell B3401 and drag the formula down to B3530. Start with cell C3401 and drag the formula down to C3530.

Return to cell C3401. Use the fill handle to drag this formula across the entire row from cell C3401 to cell BJ3401.

Repeat this step for every remaining row in column C. Use the fill handle to drag the formula across the entire row. (Starting with 1951, the length of the rows should all decrease since the last data is for 2010. Of course, it is all dummy data after 2002.)

[These table entries are percentages. An alternative method to present percentages is through Formatting. If you choose to use Formatting instead, the formula for cell B3401 becomes =IF(\$B3201>0,0,-1) and the formula for cell C3401 becomes =POWER(IF(C3201<0,0,C3201)*IF(\$B3201>0,1/\$B3201,0),1/C\$2600)-1. I will say more about Formatting later.]

Titles and Formatting

I wrote these titles for the new sections. In cell A2599, I wrote Current Balances (Nominal). In cell A2799, I wrote Nominal Rates of Return (Annualized). In cell A2999, I wrote Inflation Multipliers (Deflators). In cell A3199, I wrote Current Balances (Real). In cell A3399, I wrote Real Rates of Return (Annualized). In every case, I clicked on Format, then Cells, then Font, then Bold and finally OK.

I formatted the numbers in each table. I first highlighted the appropriate cells. Then I clicked on Format, Cells and Number. I selected the category Number, set the number of decimal places to zero and chose to use the combination of red and parentheses for negative numbers for B2601:BJ2730. I selected this area by highlighting cell BJ2601, pressing down on the Shift Key, clicking on cell B2730 and then releasing the shift key. After I had finished my Formatting, I clicked on an empty cell. (Any cell would have been OK.)

For cells C2801:BJ2930, I did the same except that I set the number of decimal places to 2. With cells B3001:BJ3230, after clicking on Format, Cells and Number, I chose the category of Percentage with 3 decimal places. With cells B3201:BJ3330 I chose category Number once again, with the number of decimals equal to zero and with the red-parentheses combination for negative numbers. With cells B3401:BJ3530, I did the same except that I used two decimal places.

In cell F1, I changed the title from JanSz-Chips Deluxe V1.0 to JanSz-Chips Deluxe V1.0 A. (I made my changes initially to the JanSz-Chips Deluxe Version V1.0 for use with commercial paper.)

In cell F12, I wrote For Improved Data Summaries, Press F5 and Enter.
In cell F13, I wrote a2600 and in cell G13 I wrote Current Balances (Nominal).
In cell F14, I wrote a2800 and in cell G14 I wrote Nominal Rates of Return (Annualized).
In cell F15, I wrote a3000 and in cell G15 I wrote Inflation Multipliers (Deflators).
In cell F16, I wrote a3200 and in cell G16 I wrote Current Balances (Real).
In cell F17, I wrote a3400 and in cell G17 I wrote Real Rates of Return (Annualized),

I was concerned about the visibility of these instructions. As a result, I made frequent use of the Format, Cells selections under Font and Pattern headings. I used bold fonts and the colors of Red and Blue as well as Black. I several cases I chose a background color under Pattern. They are listed as Cell Shading. I was partial to the lighter backgrounds of light yellow, tan, light blue and light green.

Since this was a JanSz-Chips calculator, I put a tan background (i.e., cell shading) for the Capital Gains Percentage and Dividend Reinvestments cells (A17 and A22).

I changed several cells to Bold Fonts and, in a few cases, to the color Red. These were for some of the more important entries and for the newer entries.

I set the horizontal alignment to Left or Right, as appropriate.

Needless to say, I have a colorful display.

Making Copies

Highlight cell BJ2599. Press the Shift Key and hold it down. Click cell A3530. Click Edit and then Copy.

Open a calculator that you want to modify. Highlight the same cells. Click Edit and then Paste.

Use CTRL+HOME to return to cell A1. Highlight cells F12:I17. (Highlight cell F12. Press down the Shift key and then click cell I17.) Copy it from the old spreadsheet to the new. (Highlight cells F12:I17 in the new spreadsheet. Then Paste. Finally, click on an empty cell.)

Click the appropriate cell which lists the version V1.0 (or V2.0 or V3.0) and change it to V1.0 A (or V2.0 A or V3.0 A, as appropriate).

Have fun.

John R.