Advertisements

How to correctly calculate portfolio PE ratios?

7 Oct

 

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

http://www.dailymathproblem.com/2017/05/price-earning-ratios-for-portfolios.html

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: