How to Calculate Dividend Per Share in Excel [Guide & Template]

How to Calculate Dividend Per Share in Excel

When it comes to dividends, there is a lot of important information that investors need to know in order to make informed decisions. One key metric is the dividend per share. In this blog post, we will show you how to calculate the dividend per share in Excel. We will also provide an example so that you can see how it is done. Let's get started!

What is a dividend per share (DPS)?

Dividend per share is a term used to describe the amount of money a shareholder will receive for each share of stock they own. This metric is calculated by taking the total amount of money the stock or company wishes to pay as dividends and then dividing that by the number of shares outstanding.

Why it's important to know how to calculate DPS?

Dividend per share is important because it lets investors know how much they will be paid out in dividends if they own a certain number of shares. It also provides insight into the company's financial health, profitability, and even valuation.

It is important to note that not all companies pay dividends, and the dividend amount can vary from company to company.

How to calculate dividend per share in Excel?

There are a few different ways that you can calculate DPS in Excel. We will show you one method below.

First, you will need to create a spreadsheet with the following columns: Date, Dividends Paid, and Weighted Average Shares Outstanding.

Next, you will need to enter the relevant information into each column. For example, for the Date column, you will need to enter the dividend payment date. For the Dividends Paid column, you will need to enter the dividends paid for that year or quarter. And for the Weighted Average Shares Outstanding column, you will need to enter the weighted average number of shares outstanding.

Once you have all of the information above, you can calculate the DPS. To do this, you will need to divide the Dividends Paid by the Weighted Average Shares Outstanding.

Apple Dividend Per Share
Dividends paid are negative because they are a cash outflow

For example, Apple paid 14,467,000,000 in dividends and its last fiscal year, and the weighted average shares outstanding were 16,701,272,000.

Therefore the DPS for that quarter is calculated as follows:

DPS = 14,467,000,000/16,701,272,000

DPS = 0.87

As you can see, the DPS is simply the total dividends paid divided by the weighted average shares outstanding. Now you know how to calculate dividend per share in Excel.

Dividend per share Excel free template

If you would like to simplify this process, you can use this free template that automatically provides the dividend per share of 50,000+ companies and ETFs worldwide by changing the company ticker.

Excel stock dividend template

This template automatically fetches the data from Wisesheets, so you don't have to waste time doing manual calculations or copy-pasting the data.

To get the template, sign up for a free trial account at Wisesheets, download the Excel or Google Sheets add-in and download the template there.

Examples of how to use DPS calculations

Now that you know how to calculate dividend per share in Excel, let's take a look at some examples of how this information can be used.

As we mentioned earlier, the dividend per share is one metric that can be used to measure a company's profitability and financial health. For example, if a company has a high DPS, it may be because it is doing well and can pay out more money in dividends. Alternatively, a low DPS may indicate that the company is not doing as well financially (assuming the number of shares remains equal).

Another way that DPS can be used is in valuing a company. For example, if you are trying to value a company that does not have a lot of historical data, you can use the DPS to help you come up with a valuation. You can check out this guide on valuing companies based on the dividend discount model.

You can also use the DPS to help you make investment decisions. For example, if you are trying to decide whether or not to invest in a company, you can look at the company's DPS to see how much money you would receive in dividends if you owned shares in the company. You can also compare this metric with the stock price so you can calculate the dividend yield and asses if it's satisfactory for the risk you are taking.

This information can be helpful in making your decision as it will allow you to see how much income you would receive from your investment.

Factors that can affect DPS

Now that you know how to calculate DPS in Excel and some of the ways it can be used, let's take a look at some of the factors that can affect DPS.

One factor that can affect DPS is the number of outstanding shares. For example, if a company has a lot of outstanding shares, the DPS will be lower because it will be divided among more shares. Alternatively, if a company has fewer outstanding shares, the DPS will be higher because it will be divided among fewer shares.

Another factor that can affect DPS is how well the company is doing. For example, if a company is doing well, it may be able to pay out more money in dividends. However, if a company is not doing well, it may have to cut back on its dividend payments.

Finally, the DPS can also be affected by how much money the company has available to pay out in dividends. For example, if a company has a lot of cash on hand, it may be able to pay out more in dividends. However, if a company does not have a lot of cash on hand, it may have to cut back on its dividend payments.

As you can see, a few factors can affect the DPS. However, by understanding how to calculate DPS in Excel, you will be able to better understand a company's financial health and make more informed investment decisions.

Tips for maximizing your DPS payout

If you're looking to maximize your DPS payout, there are a few things you can do.

First, you can screen and look for companies that have a high DPS. As we mentioned earlier, a high DPS usually indicates that the company is doing well and can pay out more money in dividends.

Second, you can invest in companies that have a lot of cash on hand and limited growth opportunities. As we mentioned earlier, if a company has a lot of cash on hand, it may be able to pay out more in dividends, especially if it doesn't have better ways to invest the money.

Finally, you can invest in companies that have a history of paying out high dividends. By investing in companies that have a history of paying out high dividends, you can have more certainty that the companies have a track record and, thus, are likely to continue paying strong dividends to shareholders.

By following these tips, you can help maximize your DPS payout and make better dividend stock investments.

Conclusion

In conclusion, DPS can be a helpful metric in understanding a company's financial health and making investment decisions. By understanding how to calculate DPS in Excel, you will be able to better assess a company's dividend payments and make more informed investment decisions.

Do you have any tips on how to maximize your DPS payout? Share them with us in the comments below!

Guillermo Valles
 | Website

Hello! I'm a finance enthusiast who fell in love with the world of finance at 15, devouring Warren Buffet's books and streaming Berkshire Hathaway meetings like a true fan.

I started my career in the industry at one of Canada's largest REITs, where I honed my skills analyzing and facilitating over a billion dollars in commercial real estate deals.

My passion led me to the stock market, but I quickly found myself spending more time gathering data than analyzing companies.

That's when my team and I created Wisesheets, a tool designed to automate the stock data gathering process, with the ultimate goal of helping anyone quickly find good investment opportunities.

Today, I juggle improving Wisesheets and tending to my stock portfolio, which I like to think of as a garden of assets and dividends. My journey from a finance-loving teenager to a tech entrepreneur has been a thrilling ride, full of surprises and lessons.

I'm excited for what's next and look forward to sharing my passion for finance and investing with others!

Related Posts