Excel Queries For Dividends: Fidelity Investments Explained

Investors can use Excel to track dividends and gain a better understanding of their portfolio's performance and potential. While there is no easy, free method to obtain dividend data, investors can use specific formulas and functions in Excel to calculate and analyse dividend yields and payments. This includes using platforms such as Wisesheets, an Excel add-on that provides access to dividend data for thousands of stocks.

Fidelity Investments also offers resources to help investors assess dividend payments and provides comprehensive dividend figures and charts to evaluate the growth and sustainability of dividend income streams. Additionally, investors can access various account documents, such as monthly and quarterly statements, year-end investment reports, and tax forms, on the Fidelity website.

Dividend yield and dividend payment amount on Excel Stocks

Fidelity.com offers comprehensive dividend figures and charts to help evaluate the growth and sustainability of this potential income stream. Dividend yield measures the immediate cash return an investor receives from an equity investment. It is calculated by dividing the annual cash dividend amount by the current stock price. Dividend yield can be based on the sum of all cash dividends paid during the prior 12 months or expected dividends for the coming 12 months.

To get dividend yield and dividend payment data in Excel, a common method is to manually copy-paste data from financial websites. However, this approach is inefficient due to frequent changes in stock prices and dividend yields. A more efficient method is to use an API to programmatically retrieve and update data, but this requires coding knowledge and can be expensive.

An alternative is to use Wisesheets, an Excel and Google Sheets add-on that provides dividend yield and dividend payment data for thousands of stocks through simple function calls. For example, to get the real-time dividend yield of a stock, the formula =WISE("ticker", "dividend yield", "ttm") can be used, where "ticker" is replaced with the desired stock ticker symbol.

Wisesheets also allows access to historical dividend yield and historical stock dividend payment data. For example, to get the historical dividend yield for a specific year, the formula =WISE("ticker", "dividend yield", year) can be used, where "year" is replaced with the desired year. To get historical dividend payment data, the formula =WISEPRICE("ticker", "dividend") can be used.

By utilizing Wisesheets, investors can efficiently access and analyze dividend data for a large number of stocks, making more informed decisions about dividend-paying stocks.

Historical dividend yield in Excel

To get historical dividend yield data in Excel, you can use the Wisesheets Excel and Google Sheets add-in. This add-in allows you to access historical dividend yield data for any stock or ETF. To do this, simply enter the following formula into any cell of your spreadsheet:

=WISE("ticker", "dividend yield", year) or =WISE("ticker", "dividend yield", year, "quarter")

For example, to get the historical dividend yield of Apple (AAPL) for the second quarter of 2021, you would use the following formula:

=WISE("AAPL", "dividend yield", 2021, "Q2")

This formula will return the dividend yield for Apple during the specified time period, based on the dividend payments made and the closing stock price when the financial statements were published.

Alternatively, you can use the Excel Stock History function to access historical price data. The function syntax is as follows:

=STOCKHISTORY(stock, start_date, [end_date], [interval], [headers], [property0], [property1], [property2], [property3], [property4], [property5])

However, it's important to note that this method does not include historical dividend payments or historical financials.

Historical stock dividend payments in Excel

Historical stock dividend payments can be tracked in Excel using various methods, each with its own advantages and limitations. One common approach is to manually copy-paste data from financial websites like Yahoo Finance. However, this method is time-consuming and inefficient, especially when dealing with a large number of stocks, as dividend yields change frequently.

A more advanced technique involves utilising Application Programming Interfaces (APIs) to programmatically retrieve and update dividend data in Excel. While APIs provide greater flexibility, they often come with a cost and require a solid understanding of coding.

An alternative solution is to use Wisesheets, an Excel and Google Sheets add-on that simplifies the process of obtaining dividend data. With Wisesheets, you can use the formula "=WISEPRICE("ticker", "dividend")" to fetch historical dividend payments for a specific stock. For example, entering "=WISEPRICE("COKE", "dividend") will provide access to Coca-Cola's dividend payments, including the date, ex-date, dividend amount, and declaration date, going back to 1985. This data can then be used for further calculations and visualisations, such as analysing dividend payment growth over different periods.

Wisesheets also offers a free dividend payment analysis template that provides valuable information such as the current dividend yield, expected dividend payment, and dividend payment growth for any given company. This template empowers users to make informed decisions about dividend stocks and saves significant time in data retrieval and analysis.

Fidelity.com also provides comprehensive dividend figures and charts to help evaluate the growth and sustainability of dividend income. Their platform enables investors to utilise this data according to their individual investment styles.

Dividend payback matrix

A dividend payback matrix is a tool that helps determine payback times (in years) based on dividend yields and dividend-growth assumptions. It is a way to compare different investment options and determine their relative investment merits.

The dividend payback period is a measure of how long it will take for the annual dividends per share to pay back the original investment. This calculation is based on the initial dividend yield and the subsequent annual growth in the dividend payment. It does not consider stock price movement over time or the time value of money.

For example, assume a $100 stock is paying a $5 annual dividend (5% dividend yield) at the time of purchase, but no growth in the annual dividend payment is expected. It will take 20 years for the original investment to be paid back through dividends alone. If the dividend is expected to grow, the payback period will be shorter. For instance, a stock with a 2% current dividend yield that is expected to increase annually by 5% will have a payback period of 26 years.

The dividend payback matrix can be used to determine the payback times for different stocks based on their dividend yields and growth rates. This helps investors make informed decisions about their investments, especially when comparing high-quality dividend growth stocks.

It is important to note that the dividend payback matrix assumes a constant dividend growth rate and does not take into account the reinvestment of dividend payments. Additionally, it does not consider the gains from the sale of a stock but focuses solely on the income produced from the investment.

The relationship between dividends and market value

Dividends and market value are closely related concepts that can be used to evaluate a company's financial health. Dividends are a distribution of a company's earnings to its shareholders, while market value represents the ownership stake held by shareholders. Here are some key points regarding their relationship:

• Impact on Stock Price: The declaration of dividends can boost stock prices in the short and long term. In the short term, investors' excitement and increased demand for the stock may lead to a price increase. In the long term, companies that consistently pay out dividends are often seen as financially stable and well-run, making them more attractive to investors.
• Dividend Yield: Dividend yield is calculated by dividing the annual dividend per share by the current market price per share. A higher dividend yield indicates a higher return on investment for shareholders, potentially attracting more investors and increasing market value.
• Dividend Stability: Companies with a track record of stable dividend payments are viewed as less risky and more attractive to investors, leading to an increase in market value.
• Dividend Reinvestment: Some companies offer dividend reinvestment plans (DRIPs) that allow shareholders to automatically reinvest their dividends into additional shares. This can lead to increased demand for the company's shares and potentially drive up market value.
• Dividend Taxation: The tax treatment of dividends can also impact market value. In some jurisdictions, dividends are taxed at lower rates than ordinary income, making them more attractive to investors.
• Financial Health Indicator: Dividends are often seen as a signal of a company's financial health and future prospects. A company that consistently pays dividends or increases dividend payments is generally viewed as financially stable and generating sufficient cash flows.
• Reinvestment vs Dividends: Some investors may prefer companies that reinvest profits into the business rather than paying out dividends. They believe that retained earnings can be used more effectively to fund growth opportunities, such as research and development.
• Impact on Book Value: Dividends can impact a company's book value, which is the total value of a company's assets minus any liabilities. When dividends are paid out, the company's book value is reduced as the cash distributed to shareholders is no longer part of the company's assets.
• Company Growth: Dividends signal that a company may be lacking in growth opportunities. Companies that reinvest profits into the business are typically seen as having greater potential for future growth, making them more attractive to investors.
• Shareholder Value: Dividends can increase shareholder value by providing a return on investment and increasing demand for shares, leading to capital gains. However, companies need to balance dividend payments with reinvesting profits to support future growth.

Frequently asked questions