How to Calculate Dividend Yield in Excel (with Easy Steps)

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.

How to Calculate Dividend Yield in Excel


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 Share

Where:

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/D5

Here,
E5 = Dividend Per Share
D5 = Current Share Price

How to Calculate Dividend Yield in Excel

  • 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.

How to Calculate Dividend Yield in Excel

Now, we have the dividend yield in percentage.

How to Calculate Dividend Yield in Excel

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.


Related Articles

Naimul Hasan Arif

Naimul Hasan Arif

Hello, I am Arif. I am an Engineer who graduated from BUET. I want to pursue an enterprising career in a progressive environment where my skills & knowledge can be enhanced to their maximum potential to contribute to the overall success and growth of the organization.

We will be happy to hear your thoughts

Leave a reply

5 Excel Hacks You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo