Dividend Portfolio Tracker: Build a Complete Monitoring System in Excel

A dividend portfolio tracker in Excel is a holdings sheet where you enter each ticker, share count, and cost basis, and formulas fill in the current price, dividend per share, yield, and payment frequency. Annual income is then shares times dividend per share, and yield on cost is dividend per share divided by your cost per share. With the MarketXLS add-in, those data columns come from =Last(A2), =DividendPerShare(A2), =DividendYield(A2), and =DividendFrequency(A2).

See it in actionMarketXLS video

The full MarketXLS demo

Watch full demoBook a demo

Try it in your own spreadsheet

Get the free MarketXLS sample workbook (.xlsx).

Email address

Download free workbook

No account needed. MarketXLS live data requires a paid plan. Preview workbook

Explore more videos +

IntroductionHistorical DataOptions DataUse-cases

This guide builds the tracker in five sheets: a holdings dashboard, monthly income projections, dividend growth tracking, a DRIP (dividend reinvestment plan) log, and sector diversification.

Why Track Your Dividend Portfolio in Excel

An Excel tracker adds what brokerage statements usually leave out, and it can combine holdings from several brokers in one sheet:

• Projected income: expected dividend income over the next 12 months.
• Yield on cost: yield based on what you paid, not just the current market yield.
• Dividend growth: whether each holding is raising its dividend over time.
• Ex-dividend calendar: when each holding goes ex-dividend.
• Sector diversification: whether your income is concentrated in one sector.
• DRIP tracking: how reinvested dividends grow your share count and income.
• Tax planning: qualified vs non-qualified dividends.

Essential MarketXLS Formulas for Dividend Tracking

The tracker uses five MarketXLS formulas. They are shown in Windows desktop syntax. In the Microsoft 365 add-in (Excel for Mac and Excel for the web), add the mxls. prefix, for example =mxls.DividendYield("AAPL").

=DividendYield("AAPL")

Returns the trailing twelve month (TTM) dividend yield as a decimal: dividends paid over the last 12 months divided by the current price. Multiply by 100 or format the cell as a percentage.

=DividendYield("AAPL")    → 0.0049 (0.49%)
=DividendYield("JNJ")     → 0.0321
=DividendYield("T")       → 0.0645

Formula documentation: DividendYield

=DividendPerShare("AAPL")

Returns the annual dividend amount per share in dollars.

=DividendPerShare("AAPL")  → $0.96
=DividendPerShare("JNJ")   → $4.96
=DividendPerShare("T")     → $1.11

Formula documentation: DividendPerShare

=DividendFrequency("AAPL")

Returns how often the company pays dividends (e.g., quarterly, monthly, semi-annually).

=DividendFrequency("AAPL") → Quarterly
=DividendFrequency("O")    → Monthly
=DividendFrequency("UL")   → Semi-Annually

Formula documentation: DividendFrequency

=Last("AAPL")

Returns the current stock price. Essential for calculating current market value and current yield.

=Last("AAPL")   → 187.44
=Last("JNJ")    → 154.32

Formula documentation: Last

=QM_GetHistory("AAPL")

Returns historical prices for the symbol as a table. Use it to track price performance and, with dividends added back, total return. See the formula reference for the date-range and period parameters.

=QM_GetHistory("AAPL")

Formula documentation: QM_GetHistory

---

Building the Dividend Portfolio Tracker

Sheet 1: Holdings Dashboard

This is the main sheet where you see your entire dividend portfolio at a glance.

Column Layout

Example Data

| Ticker | Company        | Sector      | Shares | Cost/Share | Total Cost | Price   | Mkt Value |
|--------|---------------|-------------|--------|------------|------------|---------|-----------|
| AAPL   | Apple         | Technology  | 200    | $142.00    | $28,400    | =Last() | =D*G      |
| JNJ    | Johnson & J.  | Healthcare  | 100    | $158.00    | $15,800    | =Last() | =D*G      |
| PG     | Procter&Gamble| Consumer    | 150    | $138.00    | $20,700    | =Last() | =D*G      |
| O      | Realty Income | REIT        | 300    | $52.00     | $15,600    | =Last() | =D*G      |
| T      | AT&T          | Telecom     | 500    | $18.50     | $9,250     | =Last() | =D*G      |
| KO     | Coca-Cola     | Consumer    | 200    | $55.00     | $11,000    | =Last() | =D*G      |

Formula documentation: Last

Portfolio Summary Section

At the top of your holdings sheet, add a summary area:

---

Sheet 2: Income Projections

This sheet projects your dividend income across the year, broken down by month and by holding.

Monthly Income Matrix

Create a 12-month projection matrix:

Mark each month with the expected payment amount based on the dividend frequency. For quarterly payers, research the typical payment months (e.g., AAPL pays in Feb, May, Aug, Nov).

Income Summary by Month

Create a bar chart from this data to visualize your monthly income stream. This shows whether your income is evenly distributed or lumpy.

---

Sheet 3: Dividend Growth Tracking

Track how your holdings' dividends change over time to identify dividend growers and potential cutters.

Year-over-Year Dividend Growth

Enter historical dividend per share data manually or from annual reports. The current year comes from =DividendPerShare().

Dividend Growth Categories

Classify each holding:

• Dividend Aristocrat: 25+ consecutive years of dividend increases
• Dividend Achiever: 10-24 consecutive years of increases
• Stable Payer: Consistent dividends without regular increases
• Caution: Recent dividend cut or freeze

Add conditional formatting:

• Green for 5%+ annual growth
• Yellow for 0-5% growth
• Red for negative growth (dividend cut)

---

Sheet 4: DRIP Tracking

If you reinvest dividends, tracking DRIP effects shows the power of compounding.

DRIP Log

New Shares = Dividend Received / Share Price at Reinvestment

Over time, the share count grows, which increases the next dividend payment, which buys more shares.

DRIP Impact Analysis

The DRIP boost seems small in year one but compounds significantly over decades.

---

Sheet 5: Sector Diversification

Sector diversification limits the damage when one sector cuts dividends, as energy companies did in 2020.

Sector Allocation

Create a pie chart for visual sector allocation. Compare your value weights versus your income weights: REITs and telecom often contribute disproportionately to income.

Diversification Guidelines

• No single sector should exceed 25% of total income.
• No single stock should exceed 10% of total income.
• Aim for at least 5 sectors represented.

---

Using Historical Data for Dividend Analysis

Price and Total Return Charts

Use =QM_GetHistory() to build charts showing your holdings' performance over time:

=QM_GetHistory("JNJ")

Formula documentation: QM_GetHistory

This returns historical price data that you can chart. For total return (including dividends), you would add back dividends received to the price appreciation.

Yield History

Track how a stock's yield has changed over time by combining historical prices with known dividend amounts:

Historical Yield = Annual Dividend / Historical Price

If yield is unusually high compared to historical norms, it may indicate either an attractive buying opportunity or a signal that the market expects a dividend cut.

---

Comparison of Dividend Tracking Methods

---

Advanced Dividend Portfolio Strategies in Excel

Forward Yield vs. Trailing Yield

• Trailing yield (=DividendYield()) uses the last 12 months of actual dividends paid.
• Forward yield (=ForwardAnnualDividendYield()) uses the expected dividends for the next 12 months.

Track both to spot divergences. If forward yield drops below trailing yield, the company may have cut or frozen its dividend.

Income Goal Tracking

Set an annual income goal and track progress:

Use a progress bar (conditional formatting on a cell) to visualize how close you are to your income goal.

Dividend Safety Analysis

Assess whether each holding's dividend is sustainable:

High payout ratios (above 80-90%) may indicate the dividend is at risk, especially during economic downturns. REITs are an exception because they are required to distribute most of their taxable income.

Dividend Reinvestment Calculator

Build a forward projection showing how DRIP compounds your income over 5, 10, and 20 years:

• Start with current shares and current dividend per share.
• Each year, add DRIP shares based on dividend received / average price.
• Apply an assumed dividend growth rate.
• Project future income and share counts.

The projection shows how reinvestment and dividend growth together change income over time. It depends entirely on the growth rate you assume.

---

Frequently Asked Questions

How do I calculate yield on cost for my dividend portfolio?

Yield on cost = Annual dividend per share / Your cost basis per share. Use =DividendPerShare("AAPL") / E2 where E2 is your cost basis. This metric shows the effective yield based on what you actually paid, which grows over time as companies raise dividends.

How often should I update my dividend portfolio tracker?

MarketXLS data (prices, yields, dividends per share) updates when you refresh or recalculate the workbook. Stock prices are 15-minute delayed on the Standard plan and real-time on Advanced and Business. Update your manual entries (shares held, cost basis) whenever you make a trade. Review your income projections and DRIP tracking monthly.

Can I track dividends from international stocks?

Yes, for supported markets. MarketXLS covers US, Canadian, UK, German, and Australian markets; enter the exchange-specific ticker format for each holding. Be aware that international dividends may have different tax withholding rates and payment frequencies.

How do I handle dividend reinvestment in my tracker?

Create a DRIP log that records each reinvestment: date, ticker, dividend amount, reinvestment price, and new shares acquired. Update your shares held in the main holdings sheet to reflect the additional DRIP shares. This keeps your market value and income projections accurate.

What is a good dividend yield to target for a dividend portfolio?

This depends on your goals and risk tolerance. Higher yields (5%+) provide more current income but may indicate higher risk or slower growth. Lower yields (1-3%) from quality growth companies may produce better total returns over time. Track both yield and dividend growth to find the right balance.

How do I consolidate dividends from multiple brokerage accounts?

Enter all holdings in your Excel tracker regardless of which broker holds them. MarketXLS formulas work with ticker symbols, not broker accounts. Your tracker becomes the single source of truth for your entire dividend portfolio across all accounts.

---

Getting Started with MarketXLS for Dividend Tracking

To build this tracker you need the MarketXLS add-in, which supplies the yield, dividend per share, payment frequency, price, and historical data columns in Excel. It runs on Windows desktop Excel and, through the Microsoft 365 add-in, on Excel for Mac and Excel for the web.

1. Visit MarketXLS.com and explore the pricing plans.
2. Download and install the add-in.
3. Build the holdings sheet with =DividendYield(), =DividendPerShare(), =DividendFrequency(), =Last(), and =QM_GetHistory().
4. Update shares and cost basis after each trade and review income projections monthly.

Disclaimer: None of the content published on marketxls.com constitutes a recommendation that any particular security, portfolio of securities, transaction, or investment strategy is suitable for any specific person. The author is not offering any professional advice of any kind. The reader should consult a professional financial advisor to determine their suitability for any strategies discussed herein.