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

The forward price to earnings (P/E) is a valuation metric for measuring and comparing a company’s earnings–using expected earnings per share–to the current stock price. Companies forecast their earnings for each quarter using earnings per share (EPS)which is a company’s profit divided by the number of outstanding common stock shares.

If a company’s EPS is expected to rise, it typically means that the profit or net income should increase relative to the number of shares outstanding. The forward P/E ratio measures the relationship of the current stock price to the forecasted EPS figures. Investors can calculate a company’s forward P/E ratio for the next quarter or year using Microsoft Excel.

Understanding the Forward P/E Ratio

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

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

• Forward P/E ratio: Current Share Price / Expected EPS for the period.

The forward P/E ratio is helpful because it can signal to investors that a company’s stock price is high or low when compared to the expected EPS in the upcoming quarters. Investors 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–either up or down–throughout the year. Investors who follow a company’s forward P/E 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 be a more accurate reflection of a company’s valuation versus using the historical P/E ratio.

How to Calculate a Forward P/E Ratio in Excel

In Microsoft Excel, first, increase the widths of column 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 (their labeled A, B, C) and a dropdown menu will appear. Left-click on “Column Width” from the dropdown and change the value to 30.

Before calculating the ratio, in Excel, we must first establish the column and row heading names.

Row 1:

Write the title of the sheet; “Calculating the Forward P/E Ratio.”

Row 2:

Write the headings, including Company, the data in the formula, and the calculation results. The headings should be labeled and located as follows:

• A2 = Company
• B2 = Stock Price (or Market Price)
• C2 = EPS (expected)
• D2 = Forward P/E
• A3, A4, and so on will be the locations of the company names.

Your headings should be aligned similar to the screenshot below:

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

Row 3

We can write in the data for Company A into our spreadsheet:

• Cell A3 = Write Company A’s Name
• Cell B3 = $50
• Cell C3 = $2.60

Please see image below for how your spreadsheet should look:

Calculate the Forward P/E in Excel:

• As a reminder, the formula to calculate the forward P/E Ratio is as follows: Market Share Price / Expected EPS.
• 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

See the screenshot below for how the formula should look in cell D3:

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

Comparing Multiple Companies

If you want to compare the forward P/E ratios of two companies, you can follow the same process, inputting the information in Row 4 for the second company name–labeled “Company B Name” on the spreadsheet above.

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 cell D3, right-click, and choose copy. Next, click on cell D4, right-click, and select paste. You can also highlight multiple cells in column D and paste the formula in all of the cells that you selected–if you’re comparing several companies.

Once you know how to format the formula in Excel, you can analyze the forward P/E ratios of various companies to compare and contrast before choosing to invest in one of those stocks. Please bear in mind that the forward P/E ratio is only one ratio and shouldn’t be used exclusively for determining a company’s stock price valuation. There are many financial ratios and metrics available, and it’s important to compare those metrics to companies within the same industry.