How Do You Calculate a Payout Ratio Using Excel?

Financial ratios are an important tool in measuring the health of a company. They can tell if a company has enough assets to meet its obligations, if they are operating efficiently, how liquid their balance sheet is, and a host of other insights. One of the important ratios that specifically affects shareholders is the payout ratio.

Payout Ratio

The payout ratio measures a company's proportion of earnings paid out to shareholders as dividends. It is calculated by using a ratio of the dividends a company pays out per share and its earnings per share (EPS). The formula to calculate the payout ratio is:

Payout Ratio = Dividends Per Share / Earnings Per Share

You can calculate a payout ratio using Microsoft Excel.

Calculating the Payout Ratio in Excel

Dividends Per Share Calculation

The dividends per share ratio looks at the amount of dividends paid out in a specific period for each individual share. It is a simple ratio that takes dividends and divides them by the current outstanding ordinary shares.

Dividends Per Share = Dividends / Outstanding Ordinary Shares

It's important to note that if there are any one-off dividends paid in the period being analyzed, they should be subtracted from the calculation. Suppose you are invested in a company that paid a total of $5 million in dividends last year and it has five million shares outstanding. In Microsoft Excel, enter "Dividends Per Share" in cell A1. Next, enter "=5000000/5000000" in cell B1; the dividends per share for this company is $1 per share.

Earnings Per Share Calculation

Earnings per share (EPS) is an indicator of a company's profitability. It seeks to value profits per number of shares outstanding. The higher the EPS, the more profitable a company is. If EPS is not provided, it can be calculated by dividing the profits by outstanding ordinary shares.

Earnings Per Share = (Net Income - Preferred Dividends) / Ordinary Shares Outstanding

To arrive at earnings per share in this exercise in Excel, enter "Earnings Per Share" into cell A2. Suppose the company had a net income of $50 million last year. The calculation for earnings per share in this scenario would be entered into cell B2 as "=(50000000 - 5000000)/5000000" and the EPS would be $9.

Payout Ratio Calculation

Once you have the dividends per share and earnings per share calculated in Excel, it is straightforward to calculate the payout ratio. Enter "Payout Ratio" into cell A3. Next, in cell B3, enter "=B1/B2"; the payout ratio is 11.11%.

The Bottom Line

Investors use the payout ratio to gauge whether dividends are appropriate and sustainable. Each sector will have its own specific ratio that is appropriate for the type of business. Companies that have a sustainable business year-round, typically pay out higher dividends, as their revenues and profits are stable. Cyclical businesses, on the other hand, usually pay out smaller dividends, as their earnings are not as predictable. Dividends are seen as a return on investment for investors and the payout ratio expresses that return and can easily be calculated in Excel.

Open a New Bank Account
The offers that appear in this table are from partnerships from which Investopedia receives compensation. This compensation may impact how and where listings appear. Investopedia does not include all offers available in the marketplace.