Google Sheets Dividend Tracker: Monitor Income, Yield, and Ex-Dates

Building a dividend tracker in Google Sheets is one of the best ways for income investors to monitor their dividend stocks, track upcoming payments, and project annual income. The problem is that GOOGLEFINANCE does not provide any dividend data. No dividend yield, no dividend per share, no ex-dividend dates, no payout ratios. Nothing.

MarketXLS solves this completely. With MarketXLS installed in Google Sheets, you get every dividend metric you need as a simple formula.

Why GOOGLEFINANCE Cannot Track Dividends

GOOGLEFINANCE supports about 18 attributes for stocks. None of them are dividend-related. There is no:

• =GOOGLEFINANCE("AAPL", "dividendyield") (does not exist)
• =GOOGLEFINANCE("AAPL", "dividendpershare") (does not exist)
• =GOOGLEFINANCE("AAPL", "exdividenddate") (does not exist)

This means income investors who use Google Sheets have no built-in way to track dividends. They end up manually looking up dividend data on other websites and copy-pasting it into their spreadsheet. That is slow, error-prone, and outdated by the time they finish.

Dividend Data Functions in Google Sheets with MarketXLS

Install MarketXLS from the Google Workspace Marketplace and you get instant access to dividend data:

=DividendYield("AAPL")       // Current dividend yield (%)
=DividendPerShare("AAPL")    // Annual dividend per share ($)
=Ex_DividendDate("AAPL")     // Next ex-dividend date
=PayoutRatio("AAPL")         // Payout ratio (%)

These formulas work for any US or Canadian stock that pays dividends. The data updates automatically so your dividend tracker always shows current information.

Build a Dividend Tracker in Google Sheets: Step by Step

Step 1: Set Up the Tracker

Create a new Google Sheet with these column headers in Row 1:

Step 2: Enter Your Dividend Stocks

In column A starting at row 2, enter your dividend stock symbols:

AAPL
MSFT
JNJ
PG
KO
PEP
T
VZ
XOM
ABBV

Step 3: Add MarketXLS Formulas

Column B (Name):

=Name(A2)

Column C (Price):

=Last(A2)

Column D (Shares): Enter the number of shares you own manually.

Column E (Dividend Yield):

=DividendYield(A2)

Column F (Dividend Per Share):

=DividendPerShare(A2)

Column G (Annual Dividend Income):

=D2*DividendPerShare(A2)

Column H (Ex-Dividend Date):

=Ex_DividendDate(A2)

Column I (Payout Ratio):

=PayoutRatio(A2)

Column J (Sector):

=Sector(A2)

Copy all formulas down for each stock.

Step 4: Add Summary Section

At the top or bottom of your sheet, add these summary calculations:

Total Annual Dividend Income:   =SUM(G2:G100)
Monthly Dividend Income:        =SUM(G2:G100)/12
Portfolio Yield on Cost:        =SUM(G2:G100)/SUMPRODUCT(D2:D100, cost_basis_column)
Portfolio Current Yield:        =SUM(G2:G100)/SUMPRODUCT(D2:D100, C2:C100)
Number of Dividend Stocks:      =COUNTA(A2:A100)
Average Payout Ratio:           =AVERAGE(I2:I100)

Advanced Dividend Analysis in Google Sheets

Dividend Safety Analysis

The payout ratio tells you what percentage of earnings a company pays out as dividends. A lower payout ratio generally means the dividend is safer. Use conditional formatting to flag stocks with concerning payout ratios:

• Below 50%: Green (healthy, room to grow)
• 50% to 75%: Yellow (moderate)
• Above 75%: Red (may be unsustainable)

Historical Fundamental Data for Dividend Analysis

Use MarketXLS historical fundamental functions to analyze dividend sustainability:

=hf_revenue("AAPL", 2024)                    // Revenue trend
=hf_revenue("AAPL", 2023)
=hf_revenue("AAPL", 2022)
=hf_net_income("AAPL", 2024)                 // Earnings trend
=hf_free_cash_flow("AAPL", 2024)             // Cash flow to support dividends
=hf_dividends_per_basic_common_share("AAPL", 2024) // Historical dividends paid

A stock with growing revenue, growing earnings, and growing free cash flow is more likely to maintain and increase its dividend.

Yield on Cost Tracking

Yield on cost is the dividend yield based on your original purchase price, not the current stock price. This is a key metric for long-term dividend investors:

Yield on Cost = Annual Dividend Per Share / Your Average Cost Per Share

If you bought a stock at $50 and it now pays $3 per year in dividends, your yield on cost is 6%, even if the current stock price is $100 (which would show a 3% current yield).

Upcoming Ex-Dividend Dates

Sort your dividend tracker by the Ex-Date column to see which stocks have upcoming ex-dividend dates. You must own the stock before the ex-dividend date to receive the next payment. Use conditional formatting to highlight dates within the next 30 days.

Dividend Screening in Google Sheets

MarketXLS includes stock screening capabilities that let you find new dividend stocks. Combined with Google Sheets filtering, you can build a powerful dividend stock screener:

Screen for High Yield

Use MarketXLS =DividendYield() across a list of stocks and filter for yields above a certain threshold.

Screen for Low Payout Ratio

Use =PayoutRatio() to find stocks with sustainable dividends (payout ratio under 60%).

Screen for Dividend Growth

Use historical fundamental data to find stocks that have increased their dividend over multiple years:

=hf_dividends_per_basic_common_share("AAPL", 2024)
=hf_dividends_per_basic_common_share("AAPL", 2023)
=hf_dividends_per_basic_common_share("AAPL", 2022)

Companies with consistently growing dividends are often called "dividend aristocrats" or "dividend growth stocks."

Dividend Tracker for Financial Advisors

Financial advisors managing income-focused client portfolios can use this Google Sheets dividend tracker to:

• Project annual income for each client based on their holdings
• Monitor ex-dividend dates to ensure clients own stocks before the record date
• Identify high-risk dividends using payout ratio analysis
• Share reports with clients through Google Sheets sharing
• Compare yield on cost across different client accounts
• Screen for new income opportunities using MarketXLS dividend data

GOOGLEFINANCE vs MarketXLS for Dividend Tracking

Frequently Asked Questions

Can I track dividends in Google Sheets?

Not with GOOGLEFINANCE. GOOGLEFINANCE has no dividend data at all. Install MarketXLS from the Google Workspace Marketplace and use =DividendYield(), =DividendPerShare(), =Ex_DividendDate(), and =PayoutRatio() to track dividends in Google Sheets.

How do I calculate dividend income in Google Sheets?

Multiply your shares by the dividend per share: =shares * DividendPerShare("AAPL"). This gives you the projected annual dividend income for that holding.

How do I find ex-dividend dates in Google Sheets?

Use =Ex_DividendDate("AAPL") with MarketXLS. This returns the next ex-dividend date for any US or Canadian stock.

What is a good dividend yield?

It depends on the sector and your goals. Generally, yields between 2% and 5% are considered healthy for large-cap stocks. Yields above 6% to 8% may signal risk. Always check the payout ratio with =PayoutRatio() to assess dividend sustainability.

Can I screen for dividend stocks in Google Sheets?

Yes. Enter a list of stock symbols, use =DividendYield() for each, and filter for your target yield. Combine with =PayoutRatio() and =hf_free_cash_flow() to find stocks with sustainable, high-yield dividends.

Summary

GOOGLEFINANCE provides zero dividend data. MarketXLS gives you everything: dividend yield, dividend per share, ex-dividend dates, payout ratios, and historical dividend data. Build a complete dividend tracker in Google Sheets that projects your annual income, flags upcoming payments, and helps you find new income opportunities.

Get MarketXLS for Google Sheets | View Pricing | Install from Google Workspace