I?m working on a project, and I need an accurate and precise answer
A.S.A.P. This may be an easy question for someone familiar with Robert
J. Shiller?s work.
Yale Professor and economist Robert J. Shiller keeps a table of stock
market statistics on Yale economics? site in an Excel format located
at http://aida.econ.yale.edu/~shiller/data/ie_data.xls . The table is
supposed to be an updated version of the data used in Irrational
Exuberance (2000); let?s assume that it is.
I understand that Shiller uses an inflation adjusted 10 year moving
average to calculate his version of trailing P/E ratios; that is NOT
what this question is about.
This question is about calculating P/E ratios for the trailing 12
months in the more traditional sense, based on Shiller's data in the
table mentioned above.
I am not a pro or an expert in economics / investing.
My question is about June 2004?s dividend and earnings as per this
table, located at cells C/1610 & D1610.
This is where my confusion lies:
Is the $18.60 dividend included in the $56.15 earnings?
If so than I assume the correct and most common/traditional method to
calculate the P/E ratio is 1132.76/56.15 = 20.1738201 P/E ratio. Is
If the dividend is not included in the earnings (I don?t think this is
the case), than what I guess I have to do is add the dividend to the
earnings and calculate like this: 1132.76/(56.15 + 18.60) =
15.1539799. I think this is wrong. Is this right or wrong?
If I made an error in both cases, show me the correct way to calculate
trailing 12 months P/E ratios in a traditional sense based on trailing
12 months earnings as shown in Shiller?s table referred to above.
Request for Question Clarification by
29 Nov 2005 23:32 PST
As an economics major, I feel like I can help you here, but I just
want to be sure we're on the same page here.
First of all, dividends are not used in a calculating price/earnings
ratios. Dividends are irrelevant to the p/e ratio.
It's not exactly accurate, though, to say that dividends are
"included" in earnings. It's usually true that companies pay
dividends from their earnings (e.g., the company might earn $1 per
share and pay out 50 cents as a dividend). Dividends are often a
subset of earnings, but they don't have to be. Sometimes companies
pay out dividends that exceed their earnings. For example, a company
might earn 10 cents per share but pay out a 50 cent dividend
(obviously using cash reserves rather than earnings to fund the
Is this helping?
Clarification of Question by
30 Nov 2005 00:19 PST
Hello Juggler, I appreciate your fast response to my question. I like
to think where moving towards my precise answer. I understand your
point that a company that makes $1.00 a in earnings might pay 0.50 or
1.50 in dividends;that explains that perfectly. If the market price is
$20, and the prior 12 months earnings are $1.00, then the trailing P/E
is 20; again the dividends are irrelevent. Perfect explanation.
I think what through me for a loop is the fact that Shiller's methods
are so unusual (for what I'll call 'Shiller's Ratio'), that I thought
perhaps he may have done something very non-traditional with the
earnings he listed in column "D". Are you familiar enough with
Shiller's methods to say he didn't do anything non-tradition/weird
with the 'earnings' column in this row? For example if $56.15 in
earnings is aproximately $18.60 different than what most agree the
earnings for the S&P 500 is for June 2004, than I would be extremely
worried about basing my research on it.
If your familiar enough with Shiller's methods to tell me that he
absolutely did not do anything unusual in this column like add
dividends or whatever, than proceed and answer the question.
If you can assure me he did not do anything weird/unusual with the
earnings for June 2004, because the numbers jive with Standard and
Poors or another respected source, than roceed and answer the
I apreciate your request for clarification, I really need this worked
out before I can proceed. I hope you have the information to give me
an precise answer.
Okay... yeah, I can assure you that Shiller did not do anything weird with the
earnings for June 2004, as the numbers are taken directly from
Standard and Poors' own data.
The $56.15 figure in D1610 of Shiller's spreadsheet is the sum of the
reported earnings by the component companies of the S&P 500 for the
12-month period ending 6/30/2004.
See this spreadsheet from Standard & Poors:
The relevant figures are cells D30, D31, D32 & D33:
D30 06/30/2004 15.25
D31 03/31/2004 15.18
D32 12/31/2003 13.16
D33 09/30/2003 12.56
I hope that this explanation, along with my comments above about the
irrelevance of dividends in calculating p/e, consitutes an acceptable
answer. If anything is unclear, please let me know via the "request
clarification" feature. Thanks.
Clarification of Answer by
30 Nov 2005 01:30 PST
I should clarify my second sentence above a bit...
I meant to say that he 56.15 figure in D1610 of Shiller's spreadsheet
is the sum of the reported earnings of the S&P 500 for the 12-month
period ending 6/30/2004.
The S&P 500 is a market-capitalization weighted index, so the 56.15
figure isn't literally the sum of the earnings of the component
companies. Obviously, the combined earnings of the component
companies would be a multibillion dollar figure.
Clarification of Answer by
30 Nov 2005 01:31 PST
Typo: "...that THE 56.15 figure..."
Request for Answer Clarification by
30 Nov 2005 02:26 PST
Perfect answer juggler. I also happened to find that same S&P
earnings data, and verified the 56.15 after my last post. If I would
have thought of the whole market like one stock, the answer would have
been clear to me. Thanks for setting me straight. I'm off on an
adventure to test some theories, thanks to your help.
Clarification of Answer by
30 Nov 2005 03:25 PST
You're welcome. Thank you!