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

Get FREE Advanced Excel Exercises with Solutions!

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


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.


How to Calculate Dividend Yield in Excel: Step-by-Step Procedure

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.

  • Finally, use Fill Handle to AutoFill the rest cells of the column for the Dustin Paper Mills.

How to Calculate Dividend Yield in Excel

Similarly, we can calculate the dividend yield for Kevin Electronics.

Read More: How to Calculate Percentage Share 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.


Download Practice Workbook


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.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel projects.
Naimul Hasan Arif
Naimul Hasan Arif

Hello everyone, I am Naimul Hasan Arif, graduated from Bangladesh University of Engineering and Technology (BUET). I am working as an Excel and VBA Content Developer. I try to remain dedicated to my duties and give my best with my skills & knowledge.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo