How to Calculate Dividend Per Share in Excel (with 3 Easy Examples)

Get FREE Advanced Excel Exercises with Solutions!

For every small or large business, if it has shares then it’s a mandatory task to calculate the Dividend Per Share. In Excel, we can do it easily also we can manage them in intelligent ways. I’ll show 3 practical and easy examples in this article to calculate dividend per share in Excel which will help you to understand the calculation effectively.


What Is Dividend Per Share?

Dividend per share (DPS) is the total amount of declared dividends a company obtains for each general share outstanding. It is calculated by dividing the total dividends by the number of outstanding shares of a business over a period, normally a year.

Dividend Per Share (DPS) = Annual Dividend / Number of Shares

How to Calculate Dividend Per Share in Excel: 3 Practical Examples

The formula is pretty much straightforward, you can see that. Here I’ll show you some basic examples so that you can relate to them easily.


Example 1: Ideal Condition

In our very first example, we’ll learn the basic and ideal calculation of finding the Dividend Per Share.

Let,  in the closing financial year, Jonathon Group declared that a total dividend of $550,000 will be paid to shareholders. They have 20000 shares outstanding on their balance sheet.

Now, let’s find the Dividend Per Share.

Steps:

  • Insert the following formula in Cell C7
=C4/C5
  • Then just hit the ENTER button and get the output.

How to Calculate Dividend Per Share in Excel


Example 2: Special One-Time Dividend

Let, Jacob Financial Company paid a total of $5,75,000 in dividends over the last year, they also paid a special one-time dividend of $75000 to the current shareholders. The company has 20000 shares outstanding. So, what will be the Dividend Per Share for this case?

How to Calculate Dividend Per Share in Excel

Steps:

  • As the company paid a special one-time dividend, it will have to deduct it from the total dividend. So, type the following formula in Cell C8
=C4-C5
  • Later, hit the ENTER button to get the net amount.

How to Calculate Dividend Per Share in Excel

  • As we got the net dividend, so now we’ll just divide it by the number of shareholders, and write the following formula in Cell C9
=C8/C6
  • Finally, just press the ENTER button for the output.


Example 3: Average Outstanding Stock Share

Assume that William Textile Company has served an annual dividend of $10,000 to its shareholders. At the start, the outstanding Stock was 4500 and at the end, it was 5500. Now we’ll have to calculate the Dividend Per Share for this company.

Steps:

  • First, we’ll have to find the average outstanding stock means the average number of shareholders. So, insert the following formula in Cell C8
=(C5+C6)/2
  • Later, hit the ENTER button to get the average.

How to Calculate Dividend Per Share in Excel

  • Finally, just divide the annual dividend by the average outstanding stock. So, use the formula in Cell C9 as follows-
=C4/C8
  • Press the ENTER button and soon you will the output like the image below.


Download Practice Workbook

You can download the free Excel workbook from here and practice independently.


Conclusion

I hope the procedures described above will be good enough to calculate dividend per share in Excel. Feel free to ask any questions in the comment section and give me feedback.


<< Go Back to Dividend | Formula List | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Sourov Hossain Mithun
Md. Sourov Hossain Mithun

Md. Sourov Hossain Mithun, an Excel and VBA Content Developer at Softeko's ExcelDemy project, joined in October 2021. Holding a Naval Architecture & Marine Engineering degree from BUET, Mithun showcases expertise during his two-year tenure. With over 200 published articles on Excel topics, he earned a promotion to Team Leader, excelling in leading diverse teams. Mithun's passion extends to Advanced Excel, Excel VBA, Data Analysis, and Python programming, contributing significantly to the innovative and dynamic environment of ExcelDemy... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo