JWR1945 ***** Legend
Joined: 26 Nov 2002 Posts: 1697 Location: Crestview, Florida

Posted: Sun Jan 18, 2004 1:11 pm Post subject: 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 yearbyyear 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: 
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: 
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: 
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: 
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: 
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)*100100
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 dividebyzero 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)*100100
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 redparentheses 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 JanSzChips Deluxe V1.0 to JanSzChips Deluxe V1.0 A. (I made my changes initially to the JanSzChips 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 JanSzChips 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.

