How to correctly calculate
portfolio PE ratios?
Question: Two analysts are given the task of calculating
the PE ratio of the DOW using the data below.
(Note: Data on one firm is
missing because of a recent merger.)
The
first analyst uses method one and takes the ratio of the weighted average of
market caps for the 29 companies to the ratio of earnings for the 29 companies.
The
second analyst uses method two and takes the weighted average of the PE ratios
of the 29 stocks.
What is the correct way to calculate the PE
ratio for this portfolio?
What
are the ramifications of using the wrong method to calculate the PE ratio for
this portfolio?
The Data:
Financial Information on
Stocks In The Dow
|
||||
Share of Dow
|
Market Cap ($B)
|
Trailing Earnings ($B)
|
Trailing PE
|
|
MMM
|
0.0645
|
124.9
|
5.2
|
23.87
|
AXP
|
0.0278
|
79.8
|
4.3
|
18.5
|
AAPL
|
0.0474
|
793.6
|
45.5
|
17.44
|
BA
|
0.0781
|
149.7
|
6.7
|
22.18
|
CAT
|
0.0383
|
73.7
|
0.1
|
696.65
|
CVX
|
0.0361
|
222.6
|
5.8
|
38.1
|
CSCO
|
0.0103
|
166.5
|
9.4
|
17.7
|
KO
|
0.0138
|
192.0
|
4.0
|
47.5
|
DIS
|
0.0303
|
152.1
|
8.7
|
17.48
|
XOM
|
0.0252
|
347.4
|
11.7
|
29.6
|
GE
|
0.0074
|
209.4
|
7.1
|
29.45
|
GS
|
0.0729
|
92.1
|
7.4
|
12.44
|
HD
|
0.0503
|
192.8
|
8.2
|
23.5
|
IBM
|
0.0446
|
135.2
|
11.3
|
12
|
INTC
|
0.0117
|
178.9
|
12.3
|
14.5
|
JNJ
|
0.0400
|
348.9
|
15.9
|
22
|
JPM
|
0.0294
|
336.1
|
23.8
|
14.1
|
MCD
|
0.0482
|
126.9
|
4.9
|
25.7
|
MRK
|
0.0197
|
174.6
|
5.0
|
34.7
|
MSFT
|
0.0229
|
573.7
|
20.9
|
27.5
|
NKE
|
0.0159
|
85.1
|
4.1
|
20.7
|
PFE
|
0.0110
|
212.3
|
8.1
|
26.1
|
PG
|
0.0280
|
232.0
|
14.2
|
16.3
|
TRV
|
0.0377
|
33.8
|
2.8
|
12.2
|
UTX
|
0.0357
|
92.7
|
5.2
|
17.7
|
UNH
|
0.0602
|
189.4
|
8.1
|
23.5
|
VZ
|
0.0152
|
201.9
|
15.9
|
12.7
|
V
|
0.0323
|
240.7
|
6.2
|
39.1
|
WMT
|
0.0240
|
233.4
|
12.4
|
18.8
|
Methodological Note: Assume the columns of your spreadsheet are – (1)
Share of DOW in A, (2) Market Cap in B, Trailing Earnings in C, and Trailing PE
in D.
Also assume there are 29 rows, 1 to 29 for
each variable.
The
formula for method one is =SUMPRODUCT(a1:a29,b1:b29)/SUMPRODUCT(a1:a29,c1:c29)
The
formula for method two is
=SUMPRODUCT(a1:a29,d1:d29)
Analysis: The
DOW PE ratio for method one is 20.5, a pretty high number compared to the
historic norm of PE ratios for this index.
The
DOW PE ratio for method two is 46.7, a number that is implausible for the
portfolio of DOW stocks
Market Cap Weighted Total
|
203.4
|
Earings Weighted Total
|
9.9
|
Dow PE Ratio Method One
|
20.5
|
DOW PE Ratio Method Two
|
46.7
|
The
PE ratio of one company in the DOW, CAT is 696, an extreme outlier. This outlier drives up the weighted average
of the PE ratios by a lot.
It
is inappropriate to take the average of PE ratios because often a PE ratio for
a particular company is an outlier or is below zero.
PE
ratios below zero are economically meaningless. For a discussion of how to calculate the PE
ratio of a portfolio when some stocks in the portfolio have negative earnings
go to the following site.
PE
Ratios When Some Firms Have Negative Earnings
Many
analysts deal with the issues of negative or outlier PE ratios by dropping
firms from their analysis. There is no need to drop firms when you
calculate a portfolio PE ratio if you are using an appropriate method.
No comments:
Post a Comment