**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.

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.