From Chapter 3

Research on Safe Withdrawal Rates

Moderator: hocus2004

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

From Chapter 3

Post by JWR1945 »

In Chapter 3 of Common Sense on Mutual Funds, our featured book at the NoFeeBoards, John Bogle addresses asset allocation. His advice is worth reading. He goes into detail about risk, reward, cost and time.

I had originally wanted to address the basic concept of asset allocation and what it really provides. As in the past, I found a lack of clarity although John Bogle, to his credit, was very forthcoming in what he said.

Asset allocation, we have been told, explains a very large percentage of the variation in the returns among professional investors. But what does this mean? I don't know for sure. I think that it has to do with the variance (or standard deviation) of returns, but not with the returns themselves.

For example, if you had 100 fund manages investing only in the money market, their returns should be very close to each other. On the other hand, if you had 100 fund managers all invested totally in stocks, you would expect quite a bit of variation in their returns (along with a larger variance or standard deviation). If you had 100 fund managers all invested 50% in the money market and 50% in stocks, you would expect an intermediate amount of variation (i.e., an intermediate size of the variance or standard deviation). If you had 100 fund managers, each with his own allocation between the money market and stocks, the amount of variation would vary with the allocation percentages. Those with higher money market allocations would have very similar, tightly clustered returns. Those with higher stock allocations would have returns that differed from each other more and more as their stock allocations increased.

Notice that there is no mention of the typical or average returns, just the amount of variation.

Again, I am not sure. This is what I think people have actually discovered about asset allocation. This is far different from what many claim.

Instead, I will address the rebalancing bonus. Or, more precisely, the fallacy of a rebalancing bonus. What has actually been established is almost always misstated.

The Rebalancing Fallacy

I strongly recommend that you read Gummy's tutorial about the Rebalancing Bonus. It is an eye opener.
http://home.golden.net/~pjponzo/rebalancing-bonus.htm

This is what rebalancing actually does. If you start out with two investments and they produce annualized returns of P and Q and if you rebalance your portfolio to maintain a constant fraction x (of the overall portfolio amount) in the first investment and y in the other (with x + y = 1), the combined portfolio can provide a return bigger that [x*P+y*Q]. If it does, the improvement is called the rebalancing bonus. Whether there is a rebalancing bonus depends upon how well correlated the investments happen to be.

What rebalancing does not guarantee is a return bigger than what you would have had if you had not rebalanced. That is, if you start out with allocations of x and y and leave everything alone, you can end up with a much bigger return. The better investment would gradually increase its allocation percentage. Your final portfolio balance could be larger, much larger.

[In the special case that the two investments have the same annualized returns (statistically) and they are not perfectly correlated, an improvement can be guaranteed (in a statistical sense).]

In particular, it makes no sense to invest in an underperforming asset simply because it is uncorrelated with the rest of your portfolio. There can a good reason to do so such as an uncertainty as to when you will make withdrawals. The rebalancing bonus is not such a reason.

Rebalancing and Safe Withdrawal Rates

I have constructed tables of 30-Year Historical Database Rates for retirements beginning in 1871-1980 under four test conditions. One pair is for a portfolio consisting of 50% stocks and 50% commercial paper. I call them HDBR50N without rebalancing and HDBR50 with annual rebalancing. The other pair is for a portfolio consisting of 80% stocks and 20% commercial paper. I refer to them as HDBR80N without rebalancing and HDBR80 with annual rebalancing.

I collected data using my latest update of the Retire Early Safe Withdrawal Calculator version 1.61 from 7 November 2002. All of these conditions can be run on the unmodified calculator. It is just that data reduction would be harder.

In all cases the initial portfolio balance was $100 000 to reduce the effects of rounding errors. Expenses were 0.20%. Withdrawals were adjusted annually to match inflation according to the CPI. I left the other conditions at their default settings. A portfolio survived for the entire 30-Year period at the Historical Database Rate. It failed (became negative) before the end of the 30-Year period when withdrawals were increased by 0.1%.

Results

HDBR50N and HDBR50 produced similar results. On average, HDBR50N (without rebalancing) did slightly better than HDBR50 (with rebalancing). The mean difference was 0.12% with a standard deviation of 0.388% (with 109 degrees of freedom). The lowest Historical Database Rate for HDBR50N (without rebalancing) was 4.0% in 1966. The lowest Historical Database Rate for HDBR50 (with rebalancing) was 3.9% in 1937. The highest Historical Database Rate for HDBR50N was 9.3% in 1879. The highest Historical Database Rate for HDBR50 was 9.3% in the years 1871, 1872 and 1879.

There was only one year that HDBR50N did worse than HDBR50 by as much as 0.3%. It was 1928 and the Historical Database Rates were 5.2% and 5.5%, respectively. The most that HDBR50N did better than HDBR50 was 1.5% in 1942. The Historical Database Rates in 1942 were 7.7% and 6.2%, respectively.

HDBR80N and HDBR80 produced similar results. On average, HDBR80N (without rebalancing) did slightly better than HDBR80 (with rebalancing). The mean difference was 0.04% with a standard deviation of 0.226% (with 109 degrees of freedom). The lowest Historical Database Rate for HDBR80N (without rebalancing) was 3.9% in 1966. The lowest Historical Database Rate for HDBR80 (with rebalancing) was also 3.9% in 1966. The highest Historical Database Rate for HDBR80N was 10.9% in both 1948 and 1950. The highest Historical Database Rate for HDBR80 was 10.4% in 1879.

There were only two years that HDBR80N did worse than HDBR80 by as much as 0.3%. They were 1926 and 1927. The Historical Database Rates for 1926 were 7.2% and 7.5% for HDBR80N and HDBR80, respectively. For 1927 they were 6.9% and 7.2% for HDBR80N and HDBR80, respectively. The most that HDBR80N did better than HDBR80 was 0.8% in 1942. The Historical Database Rates in 1942 were 9.8% and 9.0%, respectively.

Conclusions

In terms of the investment options examined on the calculator (stocks and commercial paper), annual rebalancing actually degraded Historical Database Rates (by several measures). It limited the growth of stock holdings when conditions were favorable. When rebalancing helped, it provided only a small improvement as seen in the Historical Database Rate comparisons.

Using Gummy's choice of words, we see Rebalancing Deficits, not bonuses.

There can be reasons for rebalancing in spite of these observations. The obvious situation is when stocks are greatly overvalued. The key is having a reliable method of identifying such a situation which also avoids trading excessively. John Bogle included a few remarks about such situations.

Have fun.

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

Post by JWR1945 »

For 50% stocks and 50% commercial paper.

These are the Historical Database Rates for HDBR50N without balancing, for HDBR50 with annual rebalancing and their differences.
Years 1871-1920

Code: Select all

1871   9.2    9.3    (0.1)
1872   9.2    9.3    (0.1)
1873   8.7    8.8    (0.1)
1874   8.5    8.7    (0.2)
1875   8.3    8.4    (0.1)
1876   8.6    8.7    (0.1)
1877   8.3    8.3    0.0
1878   8.0    7.9    0.1
1879   9.3    9.3    0.0
1880   7.4    7.5    (0.1)
1881   7.4    7.5    (0.1)
1882   7.5    7.6    (0.1)
1883   7.1    7.2    (0.1)
1884   6.7    6.8    (0.1)
1885   7.2    7.2    0.0
1886   6.5    6.6    (0.1)
1887   6.6    6.6    0.0
1888   6.4    6.5    (0.1)
1889   6.1    6.2    (0.1)
1890   6.1    6.2    (0.1)
1891   6.0    6.0    0.0
1892   5.9    6.1    (0.2)
1893   5.0    5.1    (0.1)
1894   5.4    5.4    0.0
1895   5.5    5.5    0.0
1896   5.3    5.3    0.0
1897   5.5    5.4    0.1
1898   5.1    5.1    0.0
1899   5.3    5.4    (0.1)
1900   5.2    5.3    (0.1)
1901   4.9    5.0    (0.1)
1902   4.9    5.1    (0.2)
1903   4.6    4.7    (0.1)
1904   5.2    5.3    (0.1)
1905   4.6    4.7    (0.1)
1906   4.4    4.5    (0.1)
1907   4.3    4.4    (0.1)
1908   5.2    5.2    0.0
1909   4.9    5.0    (0.1)
1910   4.3    4.3    0.0
1911   4.3    4.4    (0.1)
1912   4.6    4.7    (0.1)
1913   4.5    4.6    (0.1)
1914   4.8    4.8    0.0
1915   5.2    5.2    0.0
1916   5.1    5.2    (0.1)
1917   6.0    6.1    (0.1)
1918   7.9    7.9    0.0
1919   8.8    8.9    (0.1)
1920   8.2    8.3    (0.1)
Years 1921-1980

Code: Select all

1921   8.3    8.1    0.2
1922   8.2    8.0    0.2
1923   7.6    7.5    0.1
1924   7.7    7.6    0.1
1925   7.2    7.3    (0.1)
1926   6.4    6.6    (0.2)
1927   6.2    6.4    (0.2)
1928   5.2    5.5    (0.3)
1929   4.3    4.5    (0.2)
1930   4.3    4.4    (0.1)
1931   4.5    4.5    0.0
1932   5.8    5.1    0.7
1933   6.6    5.7    0.9
1934   5.3    4.8    0.5
1935   5.9    5.2    0.7
1936   4.7    4.3    0.4
1937   4.1    3.9    0.2
1938   5.3    4.6    0.7
1939   5.0    4.4    0.6
1940   5.2    4.5    0.7
1941   6.4    5.4    1.0
1942   7.7    6.2    1.5
1943   7.4    6.1    1.3
1944   7.0    5.9    1.1
1945   6.6    5.7    0.9
1946   6.5    5.9    0.6
1947   8.2    7.1    1.1
1948   8.7    7.4    1.3
1949   8.4    7.3    1.1
1950   8.6    7.6    1.0
1951   7.7    7.1    0.6
1952   7.2    6.7    0.5
1953   6.9    6.5    0.4
1954   7.0    6.6    0.4
1955   5.7    5.6    0.1
1956   5.2    5.2    0.0
1957   5.2    5.3    (0.1)
1958   5.7    5.7    0.0
1959   4.8    4.9    (0.1)
1960   4.8    4.9    (0.1)
1961   4.7    4.8    (0.1)
1962   4.4    4.6    (0.2)
1963   4.7    4.8    (0.1)
1964   4.3    4.4    (0.1)
1965   4.1    4.2    (0.1)
1966   4.0    4.1    (0.1)
1967   4.3    4.4    (0.1)
1968   4.2    4.3    (0.1)
1969   4.2    4.3    (0.1)
1970   4.6    4.7    (0.1)
1971   4.7    4.8    (0.1)
1972   4.6    4.7    (0.1)
1973   4.7    4.7    0.0
1974   5.6    5.6    0.0
1975   6.9    6.7    0.2
1976   6.1    6.0    0.1
1977   6.2    6.2    0.0
1978   7.2    7.0    0.2
1979   7.4    7.3    0.1
1980   7.5    7.4    0.1
Have fun.

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

Post by JWR1945 »

For 80% stocks and 20% commercial paper.

These are the Historical Database Rates for HDBR80N without balancing, for HDBR80 with annual rebalancing and their differences.

Years 1871-1920

Code: Select all

1871   9.4    9.4    0.0
1872   9.2    9.3    (0.1)
1873   8.7    8.7    0.0
1874   8.8    8.8    0.0
1875   8.5    8.6    (0.1)
1876   8.9    9.0    (0.1)
1877   9.1    9.1    0.0
1878   9.0    9.0    0.0
1879   10.3  10.4    (0.1)
1880   7.6    7.7    (0.1)
1881   7.2    7.3    (0.1)
1882   7.4    7.5    (0.1)
1883   7.0    7.1    (0.1)
1884   6.9    6.9    0.0
1885   7.7    7.7    0.0
1886   6.7    6.7    0.0
1887   6.6    6.7    (0.1)
1888   6.6    6.7    (0.1)
1889   6.4    6.4    0.0
1890   6.3    6.4    (0.1)
1891   6.4    6.5    (0.1)
1892   6.3    6.3    0.0
1893   5.3    5.4    (0.1)
1894   6.2    6.2    0.0
1895   6.3    6.3    0.0
1896   6.2    6.1    0.1
1897   6.5    6.4    0.1
1898   5.9    5.9    0.0
1899   5.8    5.9    (0.1)
1900   5.7    5.8    (0.1)
1901   5.2    5.3    (0.1)
1902   5.1    5.2    (0.1)
1903   4.7    4.8    (0.1)
1904   5.8    5.9    (0.1)
1905   4.8    4.9    (0.1)
1906   4.4    4.5    (0.1)
1907   4.4    4.5    (0.1)
1908   5.8    5.8    0.0
1909   5.1    5.2    (0.1)
1910   4.4    4.4    0.0
1911   4.5    4.6    (0.1)
1912   4.9    4.9    0.0
1913   4.8    4.9    (0.1)
1914   5.2    5.3    (0.1)
1915   5.8    5.9    (0.1)
1916   5.5    5.5    0.0
1917   6.3    6.4    (0.1)
1918   9.0    9.1    (0.1)
1919   10.0  10.0    0.0
1920   9.1    9.3    (0.2)
Years 1921-1980

Code: Select all

1921   9.9    9.8    0.1
1922   9.9    9.9    0.0
1923   8.9    8.9    0.0
1924   9.1    9.2    (0.1)
1925   8.4    8.5    (0.1)
1926   7.2    7.5    (0.3)
1927   6.9    7.2    (0.3)
1928   5.6    5.8    (0.2)
1929   4.3    4.4    (0.1)
1930   4.4    4.5    (0.1)
1931   4.9    5.0    (0.1)
1932   7.2    6.9    0.3
1933   8.5    8.0    0.5
1934   6.4    6.2    0.2
1935   7.4    7.1    0.3
1936   5.5    5.4    0.1
1937   4.6    4.5    0.1
1938   6.4    6.0    0.4
1939   5.9    5.6    0.3
1940   6.2    5.8    0.4
1941   7.8    7.3    0.5
1942   9.8    9.0    0.8
1943   9.4    8.7    0.7
1944   8.7    8.1    0.6
1945   8.2    7.7    0.5
1946   7.7    7.4    0.3
1947   10.1   9.5    0.6
1948   10.9  10.2    0.7
1949   10.7  10.0    0.7
1950   10.9  10.3    0.6
1951   9.6    9.2    0.4
1952   8.7    8.5    0.2
1953   8.4    8.2    0.2
1954   8.6    8.4    0.2
1955   6.6    6.6    0.0
1956   5.7    5.7    0.0
1957   5.8    5.9    (0.1)
1958   6.4    6.5    (0.1)
1959   5.1    5.2    (0.1)
1960   5.0    5.1    (0.1)
1961   5.0    5.1    (0.1)
1962   4.5    4.6    (0.1)
1963   4.8    4.9    (0.1)
1964   4.3    4.4    (0.1)
1965   4.0    4.0    0.0
1966   3.9    3.9    0.0
1967   4.3    4.4    (0.1)
1968   4.1    4.1    0.0
1969   4.1    4.1    0.0
1970   4.7    4.8    (0.1)
1971   4.7    4.8    (0.1)
1972   4.6    4.6    0.0
1973   4.5    4.6    (0.1)
1974   5.9    5.9    0.0
1975   7.9    7.8    0.1
1976   6.6    6.6    0.0
1977   6.7    6.6    0.1
1978   8.0    7.9    0.1
1979   8.2    8.2    0.0
1980   8.2    8.2    0.0
Have fun.

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

Post by JWR1945 »

Here are HDBR50N Historical Database Rates in a form suitable for copying.

For 1871-1920

Code: Select all

9.2
9.2
8.7
8.5
8.3
8.6
8.3
8.0
9.3
7.4
7.4
7.5
7.1
6.7
7.2
6.5
6.6
6.4
6.1
6.1
6.0
5.9
5.0
5.4
5.5
5.3
5.5
5.1
5.3
5.2
4.9
4.9
4.6
5.2
4.6
4.4
4.3
5.2
4.9
4.3
4.3
4.6
4.5
4.8
5.2
5.1
6.0
7.9
8.8
8.2
For 1921-1980

Code: Select all

8.3
8.2
7.6
7.7
7.2
6.4
6.2
5.2
4.3
4.3
4.5
5.8
6.6
5.3
5.9
4.7
4.1
5.3
5.0
5.2
6.4
7.7
7.4
7.0
6.6
6.5
8.2
8.7
8.4
8.6
7.7
7.2
6.9
7.0
5.7
5.2
5.2
5.7
4.8
4.8
4.7
4.4
4.7
4.3
4.1
4.0
4.3
4.2
4.2
4.6
4.7
4.6
4.7
5.6
6.9
6.1
6.2
7.2
7.4
7.5
Have fun.

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

Post by JWR1945 »

Here are HDBR80N Historical Database Rates in a form suitable for copying.

For 1871-1920

Code: Select all

9.4
9.2
8.7
8.8
8.5
8.9
9.1
9.0
10.3
7.6
7.2
7.4
7.0
6.9
7.7
6.7
6.6
6.6
6.4
6.3
6.4
6.3
5.3
6.2
6.3
6.2
6.5
5.9
5.8
5.7
5.2
5.1
4.7
5.8
4.8
4.4
4.4
5.8
5.1
4.4
4.5
4.9
4.8
5.2
5.8
5.5
6.3
9.0
10.0
9.1
For 1921-1980

Code: Select all

9.9
9.9
8.9
9.1
8.4
7.2
6.9
5.6
4.3
4.4
4.9
7.2
8.5
6.4
7.4
5.5
4.6
6.4
5.9
6.2
7.8
9.8
9.4
8.7
8.2
7.7
10.1
10.9
10.7
10.9
9.6
8.7
8.4
8.6
6.6
5.7
5.8
6.4
5.1
5.0
5.0
4.5
4.8
4.3
4.0
3.9
4.3
4.1
4.1
4.7
4.7
4.6
4.5
5.9
7.9
6.6
6.7
8.0
8.2
8.2
Have fun.

John R.
Mike
*** Veteran
Posts: 278
Joined: Sun Jul 06, 2003 4:00 am

Post by Mike »

A chart of HDBR80 N versus Earning's Yield produces a very strong correlation. This is visually discernable when I plot HDBR80 N and Earning's Yield together, with date being the x axis. The data points slope up and down together in a roller coaster fashion. The early 20's have HDBR80 N quite a bit higher than earnings, most likely due to the high returns in the early years of those retirement dates, but even there the data points move in the same direction.

I can certainly see where the stock/commercial paper ratio is much more important than rebalancing between stocks and commercial paper. Paper has low volatility, and low returns. The traditional statement that a person's asset allocation has the greatest effect on returns seems to be confirmed here. It seems to me that the main benefit from rebalancing studies between S&P/commercial paper (MPT) would be to convince those too timid to invest in any equity at all that at least a small dose of equity can improve performance without undue risk. The studies that I have seen indicate that small doses of rebalanced equity (about 20%) initially increase performance, while actually reducing volatility compared to bonds alone. Volatility increases as the equity percentage increases thereafter. At least during periods of normal valuations.

Raddr's section shows a much greater benefit from rebalancing between SCV and the S&P, but results similar to yours for rebalancing between commercial paper and the S&P. I have not quite figured out how to incorporate the French SCV total return data into the REHP calculator, so I can't verify this, but it seems reasonable. SCV has tremendous volatility compared to commercial paper, and a much larger long term return.
Last edited by Mike on Sun May 16, 2004 12:51 am, edited 3 times in total.
Mike
*** Veteran
Posts: 278
Joined: Sun Jul 06, 2003 4:00 am

Post by Mike »

This is the French total return data for SCV from 1927 to 2003 for reference, at least it is if I understood Professor French's web site correctly.

Code: Select all

31.42
34.86
-44.23
-35.85
-42.7
-5.25
159.41
35.89
48.34
37.1
-48.64
43.81
10.72
0.57
-17.34
16.76
45.08
41.23
64.28
-12.4
-8.38
-7.16
23.52
31.01
16.26
8.55
-0.68
43.2
13.95
7.65
-16.99
75.22
21.42
-1.78
22.2
-22.33
7.98
8.13
39.99
-5.32
88.42
32.73
-23.68
-20.25
25.86
0.39
-45.07
-31.9
61.32
38.2
19.35
17.65
48.84
52.66
-11.53
19.72
22.12
-12.84
28.91
1.95
-12.24
16.63
20.58
-17.74
54.73
5.82
12.64
-4.36
35.13
12.36
15.29
3.04
54.75
-24.15
0.16
-30.87
53.2
JWR1945
***** Legend
Posts: 1697
Joined: Tue Nov 26, 2002 3:59 am
Location: Crestview, Florida

Post by JWR1945 »

Mike wrote:I have not quite figured out how to incorporate the French SCV total return data into the REHP calculator, so I can't verify this, but it seems reasonable. SCV has tremendous volatility compared to commercial paper, and a much larger long term return.
You paste the new data onto row 2550, which presents the total return from stocks. [Actually, you Paste Special and Transpose.] If your calculator doesn't use row 2550 already, you put it in. The calculator uses S&P500 data from 1871 through 1926 (and dummy S&P500 data from 2004 though 2010). It uses the new data from 1927 though 2003.

If Mike had had price and dividend information, the changes could have been onto rows 184 and 185. Most likely, some adjustments would have been needed in order to make a smooth transition between the two data sets.

The new data are used only when switching is selected. If you wish not to switch allocations, enter dummy thresholds below 5 and above 44. All of the P/E10 data in the calculator is between 5 and 44.

Have fun.

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

Post by JWR1945 »

Here are the detailed instructions for using Mike's data.

1. Bring up a calculator that you wish to modify and bring up a blank spreadsheet. We will still keep the original. You can use any version of the calculator that uses row 2550, which calculates the stock gain. (The formula is =+((C184-B184)/B184*100)+(C185/C184*100).)
2. Click the Select All square on the existing calculator. It is the gray square just above row 1 and to the left of column A. This will highlight entire spreadsheet of the existing calculator.
3. Bring up the blank spreadsheet and click Edit and then Paste.
4. Write the name of your new calculator in cell E1. I wrote Small Cap Value V1.0 modification of JanSz-Chips Deluxe V1.0B.

1. Bring up another blank spreadsheet.
2. Highlight Mike's data and copy it. It is optional as to whether you use the Quote button.
3. Paste Mike's data into the blank spreadsheet. It will fill cells A1 through A77. The first time that I did this, I got an error message about the shape and size of the data. It allowed me the option to Paste Anyway, which I did and it was successful.

1. Scroll down to row 2550.
2. Return to the spreadsheet that has Mike's data. Highlight it. This includes cells A1 through A77. Copy it.
3. Bring up the new calculator and highlight cells BF2550 through ED2550. (Click on cell BF2550, press the Shift key, scroll to cell ED2550 and click on it. Then, release the Shift key.)
4. Click Edit, then Paste Special. In the box that comes up, put a check mark for Transpose. Then click OK. Click on any cell (it does not have to be empty) to complete the operation.

What this does

These changes enter into the calculations as the Fixed Income Interest Rate (row 183) when switching is selected. When you read the equation of any cell in row 183, you will see a variety of choices for the fixed income component that depend upon the entry in cell B7 (or, more precisely, the fixed location $B$7). When switching is selected (with cell B7 being 6 or anything else greater than 5), the fixed income component is chosen from row 182, Stock Switch by P/E.

For versions that do not have already use row 2550, including the original, unmodified Retire Early Safe Withdrawal Calculator:

1. Click cell B182. Read the formula.
2. In the original, unmodified calculator, the formula is:
=IF($F$19<B186,+(C177*(1-$I$20)+$I$20*(((C184-B184)/B184*100)+(C185/C184*100))),+((C177*(1-$B$20)+$B$20*(((C184-B184)/B184*100)+(C185/C184*100)))))
3. Replace each part that has (((C184-B184)/B184*100)+(C185/C184*100)) with (C2550).
4. With the original, unmodified calculator, the new equation becomes:
=IF($F$19<B186,+(C177*(1-$I$20)+$I$20*(C2550)),+(C177*(1-$B$20)+$B$20*(C2550)))
5. Locate the fill handle on the lower right hand corner of cell B182 (which you have highlighted). When the fat plus sign changes to a narrow set of cross hairs, click on your mouse button and hold it down. Drag the formula to the right, ending with cell EK182. Then click on an empty cell remove the highlighting and to complete the process. [You can do this in multiple steps. For example, you could drag the formula from B182 to cell CB182 and then start again from cell CB182 to another cell to the right and so on, finally completing the process at cell EK182.]
6. Go to cell A2550. A quick way to do this is to press F5 (which brings up the GO TO operation) and enter a2550 as the reference and then click OK.
7. Write Stock gain into cell A2550.
8. Highlight cell C2550. Write the equation =+((C184-B184)/B184*100)+(C185/C184*100) into the formula bar and click the check mark (or press enter or click on an empty cell).
9. Use the fill handle to drag the formula from cell C2550 through cell EK2550. [That is, Locate the fill handle on the lower right hand corner of cell C2550 (which you have highlighted). When the fat plus sign changes to a narrow set of cross hairs, click on your mouse button and hold it down. Drag the formula to the right, ending with cell EK2550. Then click on an empty cell remove the highlighting and to complete the process. You can do this in multiple steps. For example, you could drag the formula from C2550 to cell CB2550 and then start again from cell CB2550 to another cell to the right, continuing and finally completing the process at cell EK2550.]
10. Now enter Mike's data into cells BF2550 through ED2550 (not EK2550), following the earlier set of instructions. That is, you will Paste Special Mike's data into cells BF2550 through ED2550 using the transpose operation.

Warning: Read the formula in cell B182 to see which of the switching entries are actually used. In the original, unmodified Retire Early Safe Withdrawal Calculator, only the entries in locations F19 (actually, the fixed location $F$19), B20 (or $B$20) and I20 (or $I$20) are used. That is, only the lower threshold is used and the lowest and highest allocations. The higher threshold and the intermediate stock allocations are both dummy entries.


Using the new data:

The calculator is unchanged except when you select switching (by placing a 6 into cell B7 and putting a 0 into the normal stock allocation cell B6).

The fixed income component that you have will depend upon what is available with switching. In the original calculator, the only choice was limited to Commercial Paper. I have made separate, modified versions for TIPS and ibonds (which I refer to as versions 2.0 and 3.0, respectively).

If you do not want to switch allocations, make your lower thresholds less than 5 and your higher thresholds greater than 44.

Making other versions

If you do not have a TIPS version already, you need to change the formula in row 182 to use the interest rates from row 180 instead of row 177. To make an ibonds version, you need to change the formula in row 182 to use the interest rates from row 181 instead of row 177. For example, with the original calculator, you would change the equation in cell C182 from: =IF($F$19<B186,+(C177*(1-$I$20)+$I$20*(C2550)),+(C177*(1-$B$20)+$B$20*(C2550))) to =IF($F$19<B186,+(C180*(1-$I$20)+$I$20*(C2550)),+(C180*(1-$B$20)+$B$20*(C2550))) to make a TIPS version and to =IF($F$19<B186,+(C181*(1-$I$20)+$I$20*(C2550)),+(C181*(1-$B$20)+$B$20*(C2550))) to make an ibonds version.

You would then use the fill handle to drag the formula from cell C182 through cell EK182.

Final Comments

The calculator remains unchanged except for the new entries pasted onto row 2550. The calculator uses S&P500 data up through year 1926 and then it uses the new Small Cap Value data. It reverts back to the previously used dummy data when it gets to 2004.

If you are using a version with the Chips feature that allows you to specify the percentage of dividends that are reinvested, it will still work except for the years 1926 though 2003. The JanSz feature, which allows you to withdraw a fraction of the gains (but not losses) from six years earlier, is unaffected.

Have fun.

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

Post by JWR1945 »

Mike wrote:I can certainly see where the stock/commercial paper ratio is much more important than rebalancing between stocks and commercial paper....The studies that I have seen indicate that small doses of rebalanced equity (about 20%) initially increase performance, while actually reducing volatility compared to bonds alone. Volatility increases as the equity percentage increases thereafter. At least during periods of normal valuations.
I think that the word initially is the key to understanding what happens. Both the portfolio with rebalancing and the portfolio without rebalancing start with the same allocation.

From previous investigations we know that the first years are the most important in determining portfolio survival. Most likely, after eleven years or so, a portfolio will have grown enough to guarantee success or it will be in danger. This might explain why the lowest Historical Database Rates are all very close to each other.

OTOH, when stocks take off on the upside, they quickly grow enough to assure survival. It becomes less and less important to worry about downward part of volatility.

Have fun.

John R.
Mike
*** Veteran
Posts: 278
Joined: Sun Jul 06, 2003 4:00 am

Post by Mike »

Thank you John. I followed your clear directions, and got the SCV/debt instrument calculator mod working as directed.

I also wanted to explore switching between S&P and SCV, so I copied the SCV returns onto cells BF177 to ED177 of a new copy of your original commercial paper switching calculator mod. I left the S&P values alone (row 2550), and labeled it S&P/SCV switching mod. This seemed to produce a switching calculator that alternated between the S&P and SCV, at least for the years 1927 to 2003. Now I have two new tools to work with. EVentually, I would like to figure out how to incorporate switching between multiple asset classes, but this is beyond my present skill level.
got an error message about the shape and size of the data.
When pasting numbers from NFB, it is better to right click, and choose unicode for formatting under paste special. The HTML formatting that NFB uses causes the cell size problems that you encountered. Unicode produces cells in Excel that are of uniform size.
JWR1945
***** Legend
Posts: 1697
Joined: Tue Nov 26, 2002 3:59 am
Location: Crestview, Florida

Post by JWR1945 »

Eventually, I would like to figure out how to incorporate switching between multiple asset classes, but this is beyond my present skill level.
I am going to put out a wish list request. I am pretty sure that I can do the kind of thing that you are talking about.

Come up with a lot of very specific wishes. It will be easier for me to satisfy five or six very specific wishes than a single, more general request.

Handling four or five different asset classes is not difficult (assuming that you can locate their data sources). I would need to know the criteria that you would use to switch from one combination to another. We have P/E10, inflation rates and the nominal and real interest rates available. There might be other criteria that you would want to import in a manner similar to the Small Cap Value returns.

In fact, I think that I will be able to allow buy and hold bond purchases at some point. It would be easy to allow you to lock in a bond rate for a number of years, but not a bond purchase dollar amount. If you were to lock in a rate for five years, for example, that means that you could purchase any amount in any of those five years and still get the same rate. What is harder to do is to purchase a specified fraction of the current balance in a particular year and hold on to them until maturity (e.g., five years). Their percentage of the current balance would vary as the current balance varies.

Have fun.

John R.
Post Reply