Question

Where do I find price/earnings ratio (PE) and how do I equate it in excel? Is it in the income sheet? If so, what is the formula?

Answer 1

The price earning 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 PE ratio, simply divide its market price per share by its EPS. Its available in income sheet.

To equate PE ratio in excel between two companies, first right click on columns A, B and C and left click on column width, and change the value to 25 for each columns. Then click ok. Enter the name of the first company into cell B1 and of the second company into cell C1.

For example, A Inc. and B Inc. are competitors. A has trailing 12-month diluted EPS of $8.05, while B has trailing 12-month diluted EPS of $21.16. On June 25, 2018, A closed at $127.50, while B's Class A shares closed at $557.95.

In cells B1 and C1, enter A and B, respectively. Then, enter Diluted EPS into cell A2, Market Price Per Share into cell A3 and P/E Ratio into cell A4.

Enter =8.05 into cell B2 and =127.50 into cell B3. Calculate the P/E ratio for A by entering the formula =B3/B2 into cell B4. A's resulting P/E ratio is 15.84.

Next, enter =21.16 into cell C2 and =557.95 into cell C3. Calculate the P/E ratio for B by entering the formula =C3/C2 into cell C4. The resulting P/E ratio is 26.37.

Investors are willing to pay $15.84 for $1 of A's current earnings, while willing to pay $26.37 for $1 of B's current earnings.