Md. Sourov Hossain Mithun
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.
Download Practice Workbook
You can download the free Excel workbook from here and practice independently.
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 SharesThe 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.
Read More: How to Calculate Dividend Yield in Excel (with Easy Steps)
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?
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.
- 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.
Read More: How to Calculate Market Share in Excel (4 Related Examples)
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.
- 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.
Read More: How to Calculate Share Price Volatility in Excel (2 Easy Methods)
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. Visit ExcelDemy to explore more.
