Page 1 of 1

Newer Deluxe Versions of the SWR Calculators

Posted: Sun Jan 18, 2004 1:11 pm
by JWR1945
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   Location
1871   B215
1872   C233
1873   D251
1874   E269
1875   F287
1876   G305
1877   H323
1878   I341
1879   J359
1880   K377
1881   L395
1882   M413
1883   N431
1884   O449
1885   P467
1886   Q485
1887   R503
1888   S521
1889   T539
1890   U557
1891   V575
1892   W593
1893   X611
1894   Y629
1895   Z647
1896   AA665
1897   AB683
1898   AC701
1899   AD719
1900   AE737

Code: Select all

Year   Location
1901   AF755
1902   AG773
1903   AH791
1904   AI809
1905   AJ827
1906   AK845
1907   AL863
1908   AM881
1909   AN899
1910   AO917
1911   AP935
1912   AQ953
1913   AR971
1914   AS989
1915   AT1007
1916   AU1025
1917   AV1043
1918   AW1061
1919   AX1079
1920   AY1097
1921   AZ1115
1922   BA1133
1923   BB1151
1924   BC1169
1925   BD1187
1926   BE1205
1927   BF1223
1928   BG1241
1929   BH1259
1930   BI1277

Code: Select all

Year   Location
1931   BJ1295
1932   BK1313
1933   BL1331
1934   BM1349
1935   BN1367
1936   BO1385
1937   BP1403
1938   BQ1421
1939   BR1439
1940   BS1457
1941   BT1475
1942   BU1493
1943   BV1511
1944   BW1529
1945   BX1547
1946   BY1565
1947   BZ1583
1948   CA1601
1949   CB1619
1950   CC1637
1951   CD1655
1952   CE1673
1953   CF1691
1954   CG1709
1955   CH1727
1956   CI1745
1957   CJ1763
1958   CK1781
1959   CL1799
1960   CM1817

Code: Select all

Year   Location
1961   CN1835
1962   CO1853
1963   CP1871
1964   CQ1889
1965   CR1907
1966   CS1925
1967   CT1943
1968   CU1961
1969   CV1979
1970   CW1997
1971   CX2015
1972   CY2033
1973   CZ2051
1974   DA2069
1975   DB2087
1976   DC2105
1977   DD2123
1978   DE2141
1979   DF2159
1980   DG2177
1981   DH2195
1982   DI2213
1983   DJ2231
1984   DK2249
1985   DL2267
1986   DM2285
1987   DN2303
1988   DO2321
1989   DP2339
1990   DQ2357

Code: Select all

Year   Location
1991   DR2375
1992   DS2393
1993   DT2411
1994   DU2429
1995   DV2447
1996   DW2465
1997   DX2483
1998   DY2501
1999   DZ2519
2000   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.