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.