How to calculate a company’s forecast P/E ratio in Excel?

The price-to-earnings (P/E) ratio is a valuation metric that measures a company’s earnings — based on expected earnings per share — and compares it to the current stock price. Companies forecast earnings for each quarter using earnings per share (EPS), which is a company’s earnings divided by the number of common shares outstanding.

Generally, when a company’s EPS is expected to increase, it means that its net income or earnings is likely to increase relative to the number of shares outstanding. Forward P/E measures the ratio of the current stock price to expected EPS numbers. Investors can use Microsoft Excel to calculate a company’s expected price-to-earnings ratio for the next quarter or year.

Understanding the projected P/E ratio

The price-to-earnings ratio (forward P/E) is similar to the price-to-earnings ratio (P/E). P/E measures the ratio of current stock price to current or historical EPS. You can calculate a company’s earnings per share using data from its financial statements, but companies usually calculate earnings per share for you in their reported results.

The companies also provide investors with expected earnings per share for each of the upcoming quarters. From this, investors can calculate the forward P/E as follows:

  What your function keys do in Microsoft Excel

  • Projected P/E: Current share price/expected EPS for the period.

Forward P/E is useful because it can signal investors that a company’s stock price is high or low relative to expected earnings per share for the coming quarters. Investors can also compare a company’s Forward EPS to other companies in the same industry to determine if the stock price is over- or undervalued.

Executives often adjust their EPS projections up or down throughout the year. Investors tracking a company’s expected earnings per share can determine if the stock price is accurately priced relative to the newly adjusted earnings per share. Therefore, forward P/E can more accurately reflect a company’s valuation than historical P/E.

In Microsoft Excel, first increase the width of columns A, B, and C by selecting the entire sheet. Click on the corner of the worksheet (to the left of column A and above the number 1 in the first row). Once the sheet is highlighted, right-click on any column (labeled A, B, C) at the top and a drop-down menu will appear. In the drop-down menu, left-click “Column Width” and change the value to 30.

Before we calculate the ratio, we need to set the column and row header names in Excel first.

Row 1:

Write the title of the sheet: “Projected P/E Calculation”.

Row 2:

Enter fields including company, formula data, and calculation results. Headings should be labeled and placed as follows:

  • A2 = company
  • B2 = stock price (or market price)
  • C2 = EPS (expected)
  • D2 = Extended P/E
  • A3, A4 etc will be the company name locations.

Your headings should line up the same way as in the screenshot below:

Projected P/E calculation in Excel.

As an example, assume Company A’s current stock price is $50 and its expected earnings per share are $2.60 for a given quarter.

row 3

We can enter Company A’s data into our spreadsheet:

  • Cell A3 = Enter the name of company A
  • Cell B3 = 50
  • Cell C3 = 2.60

Please see the image below to see how your spreadsheet should look like:

Projected P/E calculation in Excel.

Calculate Projected P/E in Excel:

  • As a reminder, the formula used to calculate Projected P/E is as follows: Market Share Price / Expected EPS.
  • Place your cursor in cell D3.
  • Please note that all formulas in Excel start with the same sign.
  • Enter the forward P/E formula in cell D3 as follows: =B3/C3
  • Press Enter or Return on your keyboard

Look at the screenshot below to see what the formula in cell D3 should look like:

Projected P/E calculation in Excel.

You will find that Excel automatically highlights the cells involved in the formula. Once you press enter, the calculation is complete as shown below:

Projected P/E calculation in Excel.

Comparison of several companies

If you want to compare the price-to-earnings ratios of two companies, you can do the same thing by entering the information from row 4 for the second company named “Company B” in the table above.

However, when comparing multiple companies, you don’t need to rewrite the formula in each cell in column D. Instead, you can place your cursor in cell D3, right-click, and choose copy. Next click on cell D4, right click and select Paste. You can also select multiple cells in column D and paste the formula in all selected cells – if you are comparing multiple companies.

Once you know how the formula is formatted in Excel, you can analyze the forward P/E of different companies to compare and contrast them before deciding to invest in any of these stocks. Keep in mind that forward P/E is just a metric and shouldn’t be used solely to determine a company’s stock price valuation. There are many financial metrics and metrics out there, and it’s important to compare these metrics to those of companies in the same industry.