NoFeeBoards.com! No Fees! No Ads! No Spam!

Author Message
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 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: 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)*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.

 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT - 9 Hours Page 1 of 1

 Jump to: Select a forum General----------------NFBTown CenterTest Posts Personal finance----------------FIRE BoardIndex Funds BoardSWR Research GroupLBYMGeneral FinanceNewbies Board Community----------------WorkspacePolitically CorrectYour HealthGummy Stuff
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum