The price-to-earnings ratio, or P/E ratio, is a valuation ratio used in fundamental analysis. The ratio compares a company's market price per share to its earnings per share or EPS. To calculate a company's P/E ratio, simply divide its market price per share by its EPS over a certain fiscal period.
Explaining How to Calculate Price-to-Earnings Ratio in Excel
Assume you want to compare the P/E ratio between two companies, which are in the same sector, using Microsoft Excel. First, right click on columns A, B and C and left click on Column Width, and change the value to 25 for each of the columns. Then, click OK. Enter the name of the first company into cell B1 and of the second company into cell C1.
For example, Apple Inc. and Google Inc. are competitors. Apple has trailing 12-month diluted EPS of $3.30, while Google has trailing 12-month diluted EPS of $45.49. On October 21, 2020, Apple closed at $116.87, while Google's shares closed at $1,593.31.
In cells B1 and C1, enter Apple and Google, respectively. Then, enter Diluted EPS into cell A2, Market Price Per Share into cell A3 and P/E Ratio into cell A4.
Enter =3.30 into cell B2 and =116.87 into cell B3. Calculate the P/E ratio for Apple by entering the formula =B3/B2 into cell B4. Apple's resulting P/E ratio is 35.41.
Next, enter =45.49 into cell C2 and =1593.31 into cell C3. Calculate the P/E ratio for Google by entering the formula =C3/C2 into cell C4. The resulting P/E ratio is 35.03.
Investors are willing to pay $35.41 for $1 of Apple's current earnings, and willing to pay $35.03 for $1 of Google's current earnings.