Dividend payout ratio is a financial ratio that measures the percentage of net income that is distributed to shareholders as dividends. It shows how much of the company’s earnings are paid out as dividends and how much are retained for reinvestment. A high dividend payout ratio indicates that the company is sharing a large portion of its profits with its shareholders, while a low dividend payout ratio implies that the company is reinvesting more of its earnings for future growth.
Dividend payout ratio can be calculated using two different formulas:
- Formula 1: Dividend payout ratio = (Dividends per share * 100) / Earnings per share
- Formula 2: Dividend payout ratio = (Total dividends / Net income) * 100
Both formulas give the same result, but they use different inputs. Formula 1 uses dividends per share and earnings per share, which are both available on the income statement of the company. Formula 2 uses total dividends and net income, which are also found on the income statement, but they are aggregated for the whole company.
In this article, we will explain how to use both formulas in excel to calculate the dividend payout ratio of a company. We will also provide a detailed example with real numbers and an excel table to illustrate the calculation.
Formula 1: Dividend payout ratio = (Dividends per share * 100) / Earnings per share
To use formula 1 in excel, we need to have the following data:
- Dividends per share: The amount of dividends paid out for each share of common stock. It can be found on the income statement or calculated by dividing the total dividends by the number of outstanding shares.
- Earnings per share: The amount of net income earned for each share of common stock. It can be found on the income statement or calculated by subtracting the preferred dividends from the net income and dividing by the number of outstanding shares.
Once we have the data, we can enter the formula in excel as follows:
- In cell A1, enter “Dividends per share”
- In cell B1, enter the value of dividends per share (for example, 0.5)
- In cell A2, enter “Earnings per share”
- In cell B2, enter the value of earnings per share (for example, 2)
- In cell A3, enter “Dividend payout ratio”
- In cell B3, enter the formula “=B1/B2*100” (without the quotation marks)
- Press Enter
The result in cell B3 is the dividend payout ratio expressed as a percentage (for example, 25%).
Formula 2: Dividend payout ratio = (Total dividends / Net income) * 100
To use formula 2 in excel, we need to have the following data:
- Total dividends: The total amount of dividends paid out to all shareholders, including common and preferred. It can be found on the income statement or calculated by multiplying the dividends per share by the number of outstanding shares.
- Net income: The total amount of profit earned by the company after deducting all expenses, taxes, and preferred dividends. It can be found on the income statement.
Once we have the data, we can enter the formula in excel as follows:
- In cell A1, enter “Total dividends”
- In cell B1, enter the value of total dividends (for example, 50,000)
- In cell A2, enter “Net income”
- In cell B2, enter the value of net income (for example, 200,000)
- In cell A3, enter “Dividend payout ratio”
- In cell B3, enter the formula “=B1/B2*100” (without the quotation marks)
- Press Enter
The result in cell B3 is the dividend payout ratio expressed as a percentage (for example, 25%).
Example with real numbers and excel table
To illustrate the calculation of dividend payout ratio using both formulas, let us consider the following example:
- Company ABC has distributed an annual dividend payment of $0.5 per share
- Over the same period, ABC reported net earnings of $2 per share
- ABC has 100,000 shares outstanding
- ABC has no preferred stock
Using formula 1, we can calculate the dividend payout ratio as follows:
- Dividends per share = $0.5
- Earnings per share = $2
- Dividend payout ratio = ($0.5 * 100) / $2
- Dividend payout ratio = 25%
Using formula 2, we can calculate the dividend payout ratio as follows:
- Total dividends = $0.5 * 100,000
- Total dividends = $50,000
- Net income = $2 * 100,000
- Net income = $200,000
- Dividend payout ratio = ($50,000 / $200,000) * 100
- Dividend payout ratio = 25%
We can see that both formulas give the same result. We can also create an excel table to show the data and the calculation as follows:
| Data | Value |
|---|---|
| Dividends per share | $0.5 |
| Earnings per share | $2 |
| Total dividends | $50,000 |
| Net income | $200,000 |
| Dividend payout ratio | 25% |
