What is the formula for calculating earnings per share (EPS) in Excel?

The earnings per share (EPS) ratio is an important metric used by analysts and traders to establish the financial strength of a company. Essentially, the EPS ratio indicates how much of a company's net income would be earned per share if all profits were paid out to shareholders.

While it is more likely that the company reinvests its profits to grow the business, investors still look to EPS to gauge a company's profitability. A higher ratio means a company is profitable enough to pay out large sums to its shareholders. Typically, investors look at the change in a company's EPS over time compared to others in the same industry. Establishing trends within EPS growth gives a better idea of how profitable a company has been in the past and may be in the future. A company with a steadily increasing EPS is considered to be a more reliable investment than one whose EPS is on the decline or varies substantially.

Calculating the EPS ratio requires only three data points: net income, preferred stock dividends and number of common shares outstanding. The total amount of preferred stock dividends is subtracted from the net income and the result is divided by the number of common shares outstanding. It is important to note that this equation differentiates between common and preferred shares. This is because preferred stock earns a fixed dividend percentage that must be paid before common share dividends. (For related reading, see "A Primer on Preferred Stocks.")

A number of online financial spreadsheet templates calculate the EPS ratio and other financial metrics. The EPS ratio is also often found on stock trading websites since it is so commonly used in investment analysis. However, companies typically calculate and publish the EPS ratio at the end of the fiscal year using a weighted average for the number of common shares outstanding. This is because companies typically sell and buy back stock throughout the year, so the number of shares outstanding varies from day to day. For a more up-to-date figure, a company's current EPS ratio can easily be calculated using Microsoft Excel.

After collecting the necessary data, input the net income, preferred dividends and number of common shares outstanding into three adjacent cells, say B3 through B5. In cell B6, input the formula "=B3-B4" to subtract preferred dividends from net income. In cell B7, input the formula "=B6/B5" to render the EPS ratio.

Dan wants to be sure he diversifies his investments sufficiently as he plans for retirement, so he begins researching stocks that look like they have growth potential. After researching company XYZ extensively, Dan wants to see how its EPS ratio stacks up to similar businesses in the industry before moving forward. His research shows XYZ has a net income of $5 million, preferred share dividends of $1.5 million and 700,000 total common shares outstanding. Using Excel, Dan calculates that XYZ has an EPS ratio of $5. Since this ratio has been steadily on the rise in recent years and compares favorably to others in the industry, Dan decides XYZ is a sound investment.

(For related reading, see "5 Types of Earnings Per Share.")