How To Calculate Dividend Per Share in Excel (3 Simple Ways)

If you bought shares of a company and heard that it is providing a certain amount of dividend, you might be interested to know how much dividend you are entitled to. In order to know that, you must learn how to calculate dividend per share in excel. In this article, we will be learning a couple of methods to do so in Excel.

What is Dividend Per Share?

When you buy shares from a company, you become a part-owner of the company. As an owner, you are entitled to the profits of the company. The dividend per share is the amount of profit the company is willing to distribute to its owners for each share of the company.

The most straightforward way to calculate DPS is to divide the total dividends paid by the outstanding shares. We have the total dividends paid and the outstanding shares for this example. Let’s calculate the dividend per share:

➤ In the cell where you want the dividends per share to show up, write this formula:

➤ Press Enter to perform the calculation.

Instead of the calculation being that simple, you might have to calculate the actual total dividends paid and the average outstanding shares yourself. In that case, you have to follow the steps below:

➤ Create some more cells for calculating the Annual Dividend and Average Outstanding Shares. We are going to use the C column for this.
➤ Write Annual Dividend on C1 cell, and Average Outstanding Shares on C3 cell.
➤ Select A1:A4 cells, and select the Format Painter icon from the Clipboard section of the Home tab.
➤ Click to select the C1:C4 cells and copy the formats.

➤ Calculate the annual dividend using this formula in the C2 cell:

➤ Put this formula in the C4 cell.

➤ Write the dividend per share formula like the method before in the target cell (B6 in this case)

In some cases, you might not have the total dividends paid and the outstanding shares. But if you have the net income, dividends paid, and the average outstanding shares, you can calculate the dividend per share. Here is how to do it:

➤ Create separate cells for Earnings Per Share, Dividend Payout Ratio (%), and Dividend Per Share.
➤ Calculate the Earnings Per Share using this formula:

➤ Calculate the Dividend Payout Ratio (%) using this formula:

➤ Finally, use the following formula to calculate the Dividend Per Share

Frequently Asked Questions

What is 20% dividend payout?

That means that the company is giving the shareholders 20% of the profit that the company earned in that year. However, this 20% is divided among all of the shareholders.

What is the formula for dividend coverage?

Here is the formula for dividend coverage:
=A1/B1
Here, A1 refers to the earnings per share, and B1 refers to the dividend per share

How to calculate yield?

In order to calculate yield, you need to divide the DPS that you received by the value you paid for each share. Write the formula like the following:
=A2/B2
Replace A2 with the cell where you have the DPS, and B2 with the cell where you have the share price.

How do I calculate dividends?

To calculate the dividends you are entitled to, you need to multiply the DPS announced by the company and the number of shares you have. Here is the formula for that:
=A1*B1
The DPS amount is A1, and your share count is B1.

Who pays dividends?

Companies pay dividends to the shareholders from their profits.

Wrapping Up

In this article, we have learned how to calculate dividend per share in Excel. We hope that you were able to understand all the methods that we taught here. If you still have any confusion, put it in the comments, and we will be happy to review it. Download the workbook to try the methods for yourself using our examples, and we will see you in another day, in another tutorial.