P/E 10 Correlated With Subsequent 10 Year Return

Research on Safe Withdrawal Rates

Moderator: hocus2004

Post Reply
Mike
*** Veteran
Posts: 278
Joined: Sun Jul 06, 2003 4:00 am

P/E 10 Correlated With Subsequent 10 Year Return

Post by Mike »

Yesterday I made a simplified chart comparing the above. To obtain the data, I set the REHP withdrawals to 0, the stocks to 100%, and pasted the annual portfolio terminal values into a column (t15 column after 10 years, b215-L215 for first 10 numbers). I used the special paste values to just paste the numbers, not the formulas. I then offset the terminal portfolio values by 10 years in a column next to it. A third column simply divided year 10 by year one. Next to this I pasted the annual P/E 10 numbers. (I had to tell Excel to convert the data from a row [186] to a column, since the calculator displays it in row form.) I then charted P/E 10 on the x axis, and subsequent 10 year return on the y axis. Show me what I may have done wrong here.

The utility to convert charts into HTML are an option available for installation using the custom install feature on Excel. After installation, an option appears under the file menu, asking if you want to prepare a HTML document, which is then saved in your document section for later uploading onto the web.
JWR1945
***** Legend
Posts: 1697
Joined: Tue Nov 26, 2002 3:59 am
Location: Crestview, Florida

Post by JWR1945 »

I think that your calculations are correct. If I understand you correctly, they are more difficult than they have to be.

Bring up the calculator and look at columns S and T. For the start year in S, the number in the same row in column T has the balance after 10 years in nominal dollars. You can divide each entry in column T by the initial balance (at cell B4). [You make =T15/$B4$ as your first calculation and then use the fill handle to drag the formula to the rest of the years.] The real balances are in column V. They have been calculated for you.

Other columns have balances after 20, 30, 40, 50 and 60 years. For example, cell W15 has the nominal balance twenty years after 1871 and cell Y15 has the real balance.

The way to do this, of course, is to copy and paste selected columns onto a new spreadsheet, which is what you have done already. Except for P/E10, everything is available and in a column. As you have indicated (correctly), you need to copy and paste special (to transpose) the P/E10 numbers from row 186. These days, I would insert a column for earnings yield. Click Insert and then Columns. A new column appears to the left of the highlighted cell. Assuming that you have started your data in row 5, that P/E10 is in column B and you want to put the percentage earnings yield into C, you would write =100/B5 for cell C5 and then use the fill handle to drag the formula for the other years.

You did not mention the expense ratio. I always leave it at 0.20%, the default value. You might wish to set it to some other number (such as zero).

What you have calculated is the ratio of the balance after 10 years to the initial balance. This is the annualized gain multiplier raised to the tenth power. It also equals (1 + the annualized return)^10, when the annualized return is expressed as a decimal fraction such as 0.08 as opposed to it percentage (in this case 8%).

The ratio is best presented on a logarithmic plot. You may prefer to convert the data to an annualized return. I prefer to use the POWER function POWER(number,power) to take the Nth root. That is, I use =POWER(cell reference, 1/N) or =POWER(cell reference, 0.1) for the 10th root. You may prefer to use =(cell reference)^(1/N) or =(cell reference)^(0.1). If you convert to an annualized gain multiplier or and annualized return, I recommend using a linear plot.

As a final note, the Retire Early Safe Withdrawal Calculator comes with dummy data for the years after 2002. Even though cell T144 shows a small balance ten years after 2000, it does not really tell you what the balance in 2010 will be.

Have fun.

John R.

P.S. The calculator actually does tell you what the balance will be in 2010. Just don't let anybody in on the secret. Be prepared for the shock of a generation as the market sinks far below the levels of the Great Depression.
Mike
*** Veteran
Posts: 278
Joined: Sun Jul 06, 2003 4:00 am

Post by Mike »

Thank you John.
Be prepared for the shock of a generation as the market sinks far below the levels of the Great Depression.
Demographics has also indedpendently led me to allow for this possibility. I am concerned that this time we may have an inflationary bear market, since the fed seems utterly determined to avoid deflation at all costs. Defined benefit pensions could be savaged at the same time as 401k balances. We could wind up with a situation similar to that in France a few decades ago, where most retirees became utterly dependent upon the government when inflation destroyed private pensions. Only we will have 1 retiree for every 2 workers.
JWR1945
***** Legend
Posts: 1697
Joined: Tue Nov 26, 2002 3:59 am
Location: Crestview, Florida

Post by JWR1945 »

I think that the United Kingdom may provide a better parallel than that of France. You might look at some of peteyperson's very early posts. It seems as if the United Kingdom has a semi-private, semi-public equivalent to Social Security. People were forced to buy annuities from one of several private companies. Their investment choices are limited, but most especially for retirement.

The annuities offered very high payouts for life, with a Government guarantee. Until the bubble popped. Annuity payouts were cut permanently. The Government guarantee was worth something, just not as much as retirees had been led to expect. No one will lose everything, which is what could have happened if the insurance companies had been allowed to go bankrupt.

I expect something similar to happen here. There are lots of ways that the social security program could luck out because of subtle estimation errors. For example, baby boomers may decide to work later in life because they want to maintain their lifestyle but haven't provided for it. But the number of lucky possibilities keeps on decreasing. Adding new benefits only hastens a funding crisis.

In terms of what the Federal Reserve's actions, they are focusing on employment statistics and the sustainability of economic growth. Admittedly, it is strange to be worried about employment when the unemployment rate is below 6%. OTOH, from political rhetoric, you would think that unemployment barely exceeded 6% even during the Great Depression. Still, we are clearly outside the range of applicability of the economic models available to the Federal Reserve. The members of the Federal Reserve feel confident that they can handle the problems of inflation. They have very little confidence about their ability to handle the problems associated with declining prices. Needless to say, they have gained no confidence based upon what they have seen in Japan.

Have fun.

John R.
Post Reply