Naimul Hasan Arif
Nowadays, Investment in the share market has become very popular. Share market is actually a platform that gathers sellers and buyers to trade on publicly listed shares on fixed hours of a day. In order to calculate the dividend, a very vital factor is used named Dividend Yield. In this article, I am going to explain the process step by step how to calculate dividend yield in Excel.
For more clarification, I am going to use a dataset of two companies. They are named Dustin Paper Mills and Kevin Electronics. I have gathered data over the past few years about their price share and dividend per share and decorated them in the Current Share price and Dividend Per Share columns. I am going to use these data to explain the dividend yield. Hopefully, it will be a helpful article for everyone related to this topic.
Download Practice Workbook
What Is Dividend Yield?
The annual amount of dividends earned in relation to a security’s market value per share is measured by a financial ratio called the Dividend Yield. The dividend yield formula, in other words, determines the portion of a company’s market price per share that is distributed to shareholders in the form of dividends. Almost every mature company pays dividends.
Formula to Calculate Dividend Yield
The dividend yield measures the proportion of a company’s share price that is distributed in the form of dividends each fiscal year based on its most recent closing share price.
Basically, the annual dividend per share (DPS) is divided by the current market share price to get the dividend yield, which is then expressed as a percentage.
The formula for the Dividend Yield can be expressed as follows:
Dividend Yield = Dividend Per Share / Market Value Per ShareWhere:
Dividend Per Share is calculated by dividing the company’s total yearly dividend payment by the total number of outstanding shares.
The company’s current price (In the case of selling the whole company, the estimated value of the company) per share is considered the Market Value Per Share.
Step by Step Procedure to Calculate Dividend Yield in Excel
In this section, I am going to discuss the whole procedure to Calculate Dividend Yield in Excel step by step.
Steps:
- Create a new column for the purpose of Dividend Yield For simplification, I have named that column Dividend Yield too.
- Pick a cell in that Dividend Yield Here, I picked cell F5.
- Input the following formula in cell F5 to calculate the dividend yield.
=E5/D5Here,
E5 = Dividend Per Share
D5 = Current Share Price
- After that, press ENTER to have the outcome.
In this case, the dividend yield value will be in number format. We express dividend yield in percentage. So, click on Percentage Sign from the Home tab to convert the value into the percentage format.
Now, we have the dividend yield in percentage.
- Finally, use Fill Handle to AutoFill the rest cells of the column for the Dustin Paper Mills.
Similarly, we can calculate the dividend yield for Kevin Electronics.
Read More: Dividend Reinvestment Calculator with Monthly Contributions in Excel
Difference Between Dividend Yield and Dividend Payout Ratio
Dividend Yield is a financial factor expressed in the percentile that measures the annual amount of dividends earned in relation to a security’s market value per share.
On the other hand, the Dividend Payout Ratio is a proportional value between dividend per share (DPS) and earnings per share (EPS).
We often misjudge the Dividend Payout Ratio as a Dividend Yield. The Dividend Payout Ratio is considered as a better factor for the company’s future as it is directly connected with the company’s cash flow.
Things to Remember
- Dividend Yield will always be in a percentage form. You need to represent the value in percentage form otherwise that will not be considered as a Dividend Yield.
- Dividend per share is divided by the market value per share to calculate the Dividend Yield. If we divide the dividend per share by earnings per share then we will get the Dividend Payout Ratio.
Dividend Yield Calculator
You will get a calculator adjacent to the Dividend Yield sheet within the workbook. Insert the values into the respective fields and you will get the result.
Conclusion
That’s all for the article. In this article, I have tried to explain the whole procedure of how to calculate dividend yield in Excel. It will be a matter of great pleasure for me if this article could help any Excel user even a little. For any further queries, comment below. You can visit our Exceldemy site for more details on Excel.
