How to Calculate a Company’s Forward P/E in Excel

The forward price-to-earnings ratio (P/E) is a valuation metric that measures and compares a company’s earnings using expected earnings per share and the current stock price.

The forward P/E ratio measures the relationship of the current stock price to the forecasted EPS figures. Here’s how to calculate a company’s forward P/E ratio for the next period using Microsoft Excel.

Understanding the Forward P/E Ratio

The forward P/E is similar to the price-to-earnings ratio, which measures the relationship of the current stock price to the current or historical EPS, except it forecasts P/E. You can calculate a company’s earnings per share using the data provided from its financial statements, but companies will typically estimate future EPS for you in their forecasts.

Companies generally provide you with the expected earnings per share for each of the upcoming quarters. From there, you can calculate the forward P/E ratio using the formula:

Forward P/E ratio = Current Share Price ÷ Expected EPS for a period.

The forward P/E ratio is helpful because it can signal whether a company’s stock price is high or low compared with the expected EPS in the upcoming quarters. You can also compare the forward P/E of a company to other companies within the same industry to get a sense of whether the stock price is overvalued or undervalued.

Company executives often adjust their EPS forecasts (up or down) throughout the year. If you follow a company’s forward P/E over long periods, you can determine whether the stock price is accurately valued relative to the newly adjusted EPS forecasts. As a result, the forward P/E ratio can more accurately reflect a company’s valuation vs. using the historical P/E ratio.

Here are the steps to calculate forward P/E in Excel.

#1 Format Your Worksheet

In Microsoft Excel, first, increase the widths of columns A, B, and C by highlighting the entire sheet. Click on the corner of the worksheet (to the left of column A and above the numeral 1 in row one). Once the sheet is highlighted, right-click on the top of any column (labeled A, B, C), and a dropdown menu will appear. Left-click on “Column Width” from the dropdown and change the value to 30.

It helps to first establish the column heading names. You can label these however it works best for you, but this example follows this format:

• A1 = Merge cells A–D and enter a label
• A2 = Company
• B2 = Stock Price (or Market Price)
• C2 = EPS (expected)
• D2 = Forward P/E

Once you have that done, you can begin entering your data.

As an example, assume Company A has a current stock price of $50 and an expected EPS of $2.60 for a particular quarter.

#2 Enter Your Data

Next, enter the data for your first company into your spreadsheet:

• Cell A3 = Company name
• Cell B3 = $50
• Cell C3 = $2.60

#3 Calculate the Forward P/E

As a reminder, the formula to calculate the forward P/E Ratio is:

Market Share Price / Expected EPS

So, to calculate the ratio:

• Place your cursor in cell D3.
• Please note that all formulas in Excel begin with the equal sign.
• Type the forward P/E formula in cell D3 as follows: =B3/C3
• Press Enter or Return on your keyboard

You’ll notice that Excel highlights the cells involved in the formula automatically. Once you press Enter, the calculation will be completed:

#4 Compare Multiple Companies

If you want to compare the forward P/E ratios of multiple companies, you can follow the same process, inputting the information in each row for the companies you’re analyzing.

However, when comparing multiple companies, you don’t have to rewrite the formula in each cell within Column D. Instead, you can place your cursor in the results cell, right-click, and choose copy. Next, click on the next cell in the column, right-click, and select paste. You can also click the first cell with the formula, move your mouse pointer to the bottom right corner of the cell, click and hold, and then drag the pointer down to the row desired. The cells should autofill with the appropriate results.

The Bottom Line

Once you know how to format the formula in Excel, you can analyze the forward P/E ratios of various companies before choosing to invest. Remember that the forward P/E ratio is only one ratio and shouldn’t be used exclusively for determining a company’s stock price valuation. Many financial ratios and metrics should be used along with forward P/E, and it’s important to compare those metrics to companies with similar companies in the same industry.