The dividend tracking spreadsheet, and where it breaks
A spreadsheet is a perfectly good place to start. Here is a layout that works, the formulas worth adding, and the five failure points that quietly corrupt most dividend spreadsheets after a year.
Two sheets is enough
One for holdings, one row per payout. Everything else derives.
Store the ex-date share count
Today's share count is the single most common error.
Know the ceiling
Around 10–15 holdings, reconciliation costs more than it returns.
A layout that actually works
Use two tabs. Holdings:
- Ticker, exchange, currency, shares owned, average cost, sector.
Payouts · one row per dividend, never edited retroactively:
- Pay date, ex-dividend date, ticker, shares held on ex-date, dividend per share.
- Gross amount (
shares × per share), withholding tax, net received, currency, FX rate used.
From those two tabs you can pivot income by month, by ticker and by sector, and calculate yield-on-cost as annual income divided by total cost. Keep the payouts tab append-only, the moment you start editing old rows to "fix" a total, the audit trail is gone.
The five things that break it
- Share counts drift. The payout depends on shares held on the ex-date. If you top up between the ex-date and pay date and your formula reads the current holding, every affected row overstates income.
- Estimated dates masquerade as confirmed ones. Most free data sources extrapolate next quarter's ex-date from last year's. A spreadsheet has no way to show you which dates are real and which are guesses.
- Cuts and specials go unnoticed. A dividend cut only shows up when the smaller payment arrives, by which point the forecast has been wrong for months.
- Currency handling is inconsistent. Mixing spot rates, pay-date rates and year-end rates in the same column makes year-on-year comparison meaningless.
- One typo poisons everything. A misplaced decimal in a per-share figure flows into totals, yield, forecast and FIRE projections with no validation to catch it.
What to use instead once it stops scaling
A dedicated dividend tracker keeps the same underlying record · one row per payout, but fills it in for you, labels estimated dates separately from confirmed ones, converts currencies consistently, and warns you when an amount looks implausible. You can still export everything to CSV. If you want the calculations without the account, the free yield calculator and DRIP calculator do the maths on their own.
Frequently asked questions
Can I track dividends in Excel or Google Sheets?
Yes. A single transaction sheet with one row per payout, plus a holdings sheet, covers most portfolios. Google Sheets can pull live prices with GOOGLEFINANCE, though its dividend coverage is limited and does not include announced future payouts.
What columns should a dividend spreadsheet have?
Date paid, ex-dividend date, ticker, shares held on the ex-date, dividend per share, gross amount, withholding tax, net received, and currency. Everything else can be calculated from those columns.
Why do dividend spreadsheets become inaccurate?
Share counts change between the ex-date and the pay date, special dividends and dividend cuts are missed, currency rates are applied inconsistently, ex-dates are estimated rather than confirmed, and a single mistyped row silently corrupts every total downstream.
Does GOOGLEFINANCE track dividends?
Only partially. It can return historical prices and some dividend data for major US tickers, but it does not reliably provide upcoming ex-dividend dates, pay dates, or international coverage, which is exactly what forward planning needs.
When should I move off a spreadsheet?
Usually around ten to fifteen holdings, or as soon as you hold shares in more than one currency. That is the point where reconciliation takes longer than reviewing the portfolio itself.