A spreadsheet is a perfectly good dividend tracker if you set it up with care. It is free, you control it, and you learn how your income works by building it. This guide shows what to put in it, and where spreadsheets tend to struggle.
Two sheets, not one
Keep what you own separate from what you were paid. Mixing them is the most common reason a tracker becomes unmanageable.
Sheet one: Holdings
| Column | What it holds |
|---|---|
| Ticker | The symbol |
| Account | Which account holds it (TFSA, RRSP, 401(k), brokerage and so on) |
| Shares | Current number of shares |
| Average cost | What you paid per share on average |
| Annual dividend per share | The latest payment multiplied by payments per year |
| Frequency | Monthly, quarterly, semi-annual or annual |
| Expected annual income | Shares × annual dividend per share |
| Yield on cost | Annual dividend per share ÷ average cost |
Sheet two: Payments received
One row per payment: date, ticker, account, amount, and whether it was paid in cash or reinvested. Never overwrite old rows; add new ones. A pivot table or SUMIFS by month and year then gives you income received over time.
Formulas worth having
- Expected annual income per holding: =shares × annual dividend per share
- Income received this year: =SUMIFS(amount, date, ">="&DATE(YEAR(TODAY()),1,1))
- Yield on cost: =annual dividend ÷ average cost
- Share of income by holding: each holding's expected income ÷ total
Habits that keep it accurate
- Update share counts whenever you trade, including reinvested dividends.
- Record the date the money was paid, not the ex-dividend date.
- Note any withholding. A 15% US withholding on a $100 dividend means $85 arrives.
- Reconcile with your statement once a month.
When a spreadsheet stops being worth it
- You hold several accounts at different brokers and updates take longer than the investing.
- You reinvest dividends, so share counts change on every payment.
- You want a forward calendar, not just a record of what has been paid.
- You find mistakes in old months and no longer trust the totals.
If that sounds familiar, Nimblewit reads your statements and exports, updates prices and dividends after each market close and recalculates income whenever you change a trade. You can still export everything to a spreadsheet at any time. See the dividend tracker.