Wednesday, October 4, 2017

Calculating Portfolio PE Ratios Correctly


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.