Odds and Ends (Updated 4/23/07)On this page, I will share a collection of a interesting charts, links and spreadsheets. One or more topics may eventually become a stand-alone article.
Table of Contents Shiller Spreadsheet and Charts
Efficient Market Hypothesis
Real Returns versus P/E10
Immediate Annuities in Retirement
P/E10 Switching
Shiller Spreadsheet and Charts Robert Shiller maintains historical data for large stocks (S&P 500). He has two websites where users can download his spreadsheet:
From Shiller’s spreadsheet, I have derived E10 and Real Total Returns using January values. The modified spreadsheet can be downloaded from this link:
Copy_of_ie_data.xls (zip)
Here are some charts:
Real Earnings 1881-2005 
This chart was created by my Copy_of_ie_data.xls
Real Earnings 1956-2005 
This chart was created by my Copy_of_ie_data.xls
Real Total Return 1871-2005
Growth of $1
This chart was created by my Copy_of_ie_data.xls
Real Total Returns vs Real E1 (1871-2005)
Includes Reinvested Dividends
This chart was created by my Copy_of_ie_data.xls
Real Price vs Real E1 (1871-2005)
Does Not Include Reinvested Dividends
This chart was created by my Copy_of_ie_data.xls
P/E10 1881-2005 
This chart was created by my Copy_of_ie_data.xls
Efficient Market Hypothesis This article has been moved. Click HERE.
Real Returns versus P/E10 As I mentioned above, Robert Shiller has provided historical data for large stocks (S&P 500). From his spreadsheet, I derived Real Total Returns using January values. (Total Returns are gains or loss in price along with reinvested dividends.) I then created scatter plots for Real Returns versus P/E10.
As an example of the procedure, the first period starts in 1881. I would take the P/E10 number for January of that year. Then I would calculate the annualized return. So for 5-year periods, the first 5-year period would be from 1881 through 1885.
The first chart is P/E10. This is followed by the scatter plots.
P/E10 1881-2005
This chart was created by my Copy_of_ie_data.xls
Real Returns 5-Year Periods versus P/E10 (1881-2005) 
This chart was created by my Copy_of_ie_data.xls
Real Returns 10-Year Periods versus P/E10 (1881-2005) 
This chart was created by my Copy_of_ie_data.xls
Real Returns 15-Year Periods versus P/E10 (1881-2005) 
This chart was created by my Copy_of_ie_data.xls
Real Returns 20-Year Periods versus P/E10 (1881-2005) 
This chart was created by my Copy_of_ie_data.xls
Real Returns 25-Year Periods versus P/E10 (1881-2005) 
This chart was created by my Copy_of_ie_data.xls
Real Returns 30-Year Periods versus P/E10 (1881-2005) 
This chart was created by my Copy_of_ie_data.xls
To help in interpreting these charts, I found an article written by Mark Hulbert. Click HERE. Hulbert discusses Shiller's 1996 prediction, P/E ratios based on ten-year trailing earnings (P/E10) and R-Squared. Here is an excerpt:
But when P/E ratios based on ten-year trailing earnings are used to forecast the stock market's return over the subsequent decade, Professors Shiller and Campbell found an admirable success rate. Consider a statistic known as the "r-squared," which reflects the degree to which fluctuations in one factor predicts or explains changes in another. The r-squared ranges between 0 and 1, with 1 indicating the highest degree of predictive power and 0 meaning that there is no detectable relationship.
Few of the indicators that the financial press obsesses about have an r-squared that is statistically different than zero, and even among those that are statistically significant, it is rare to find an r-squared above 0.1 or 0.2.
In contrast, the r-squared is around 0.5 when relating P/E ratios based on ten-year earnings and the market's subsequent ten-year return. No wonder that Alan Greenspan was worried in December 1996, since at that time the P/E based on ten-year earnings was higher than 95% of the time since the 1870s.
Note carefully, however, what an r-squared of 0.5 signifies. It means in this case that the P/E ratio based on trailing ten-year earnings explains 50% of the market's return over the subsequent decade. Though fifty percent is a lot better than nothing, it still means that half of the market's ten-year returns can not be explained in terms of where the P/E ratio stood at the beginning of that ten-year period.
Fixed Immediate Annuities in Retirement This article has been moved. Click HERE.
P/E10 Switching There was considerable discussion on the Vanguard Diehards forum at Morningstar.com about using a valuation metric like P/E10 when determining one's equity-fixed split. Click HERE and HERE. The rationale is that stocks perform better from a lower P/E10 starting point. Conversely, it is assumed that stocks will perform poorly from a higher P/E10 starting point.
Indeed, Robert Shiller made his famous 1996 prediction based on that assumption. This was mentioned in a previous section. So even though P/E10 stood at 25 in 1996 (a high valuation up until that time), the S&P 500 actually returned an average of 9% for the 10-year period from 1996-2005. So with P/E10 still hovering above 25 (as of February 2007), some investors continue to be worried that stocks will perform poorly.
These investors base their worry on a natural tendency to see correlations when looking at historical data. And if these correlations are deemed to be “strong”, these same investors then make predictions based on those correlations. But it could also be that those correlations are only an illusion.
Below is an excerpt from an article titled, A Look in the Mirror.
The third error experts fall prey to is illusory correlations. This relates to the tendency to see a relationship between variables when none exists. Indeed research has shown that the more data a person had to sift through to get his illusory correlation the more strongly he believed it. This brings us to an interesting point. It seems to take a fairly sophisticated understanding of statistics and economics to avoid this error. And many experts in the investment field simply don't have a deep enough understanding of inferential statistics. This includes most consultants. I will be the first to admit that I do not have a strong understanding of statistics. Still, I wanted to satisfy my curiousity to see how a P/E10 switching strategy would have worked. Plus, I enjoy the challenge of creating this kind of spreadsheet.
Copy_of_ie_data.xls (zip)
Below is a description of the spreadsheet along with a sample chart.
Overview
This spreadsheet compares two portfolios (Port A and Port B). In both portfolios, you set the stock allocation according to P/E10 levels. The spreadsheet then calculates the ending balances for each 10-year period. Rather than placing one lump sum amount at the beginning of the period, an equal amount ($1000) is deposited at the beginning of each year. This DCA (dollar-cost averaging) scenario will more realistically simulate an investor’s accumulation pattern.
Each portfolio is composed of two parts. The equity side consists of real total returns for stocks (dividends reinvested, inflation-adjusted). The fixed income side is a constant 2% real return. The 2% real return for fixed income is a gross simplification of historical data. But it is employed in this spreadsheet in order to simulate a TIPS ladder.
Output
There are three charts. The top chart displays the Stock Allocation for each portfolio throughout the 1881-2005 period. The blue line represents Port A and the pink line represents Port B. The middle chart displays the ending balances of each 10-year period. For example, the first data point represents the ending balance for the 1881-1890 period. Note that this is a line chart. So the individual 10-year periods blur together. It is in the bottom chart, with its individual bars, that you can determine clearly how each 10-year period fared. When Port A outperformed, blue bars will be above the line. When Port B outperformed, pink bars will be below the line.
From worksheet “P/E10 Switching” 
This chart was created by my Copy_of_ie_data.xls
