Home › Templates › Dividend tracker
Dividend Tracker Spreadsheet
A free dividend tracker spreadsheet that shows your forward annual income, yield on cost and current yield, logs every payment you receive, totals income by month with a chart, and includes a simple DRIP (reinvestment) projection.
Use it in Google Sheets: choose File → Import → Upload in Sheets and pick the .xlsx, or upload it to Google Drive and open it with Google Sheets. No sign-up, no add-on, no email needed. The formulas are plain SUMIFS/IF/SUM.
| Ticker | Shares | Annual div/share | Frequency | Forward income | Per payment | Yield on cost | Current yield |
|---|---|---|---|---|---|---|---|
| CTSO | 60 | $3.20 | Quarterly | $192.00 | $48.00 | 2.21% | 1.97% |
| BRDX | 50 | $3.60 | Quarterly | $180.00 | $45.00 | 1.43% | 1.25% |
| FABR | 200 | $1.80 | Monthly | $360.00 | $30.00 | 6.00% | 5.37% |
| NWND | 40 | $2.50 | Semi-annual | $100.00 | $50.00 | 3.33% | 3.08% |
| Total | $832.00 | 2.75% | 2.44% |
What's inside the spreadsheet
| Tab | What you type | What it calculates |
|---|---|---|
| Start here | Nothing | Steps, definitions, colour key, disclaimer |
| Holdings | Ticker, shares, total cost basis, annual dividend per share, frequency (dropdown: Monthly, Quarterly, Semi-annual, Annual), current price. 50 rows. | Payments per year, forward annual income, income per payment, yield on cost, current yield, market value, received to date (from the log), share of income |
| Payments | Payment date, ticker, amount received, reinvested? (Y/N dropdown), notes. 1,000 rows. | Feeds the monthly summary and "received to date" |
| Monthly income | The year (one cell) | Received, reinvested and cash income for each month (SUMIFS on dates), totals, portfolio yield, stacked column chart |
| DRIP projection | Starting value, starting yield, dividend growth, price growth, yearly contribution, tax withheld | 20-year table: dividends, reinvested amount, end value, and share value if dividends were taken as cash |
| Example Holdings / Payments / Monthly income | Already filled | A worked example with 4 fictional holdings and 19 payments |
How to use it
- On Holdings, add each stock or fund: shares, total cost basis (everything you paid, fees included) and the annual dividend per share.
- Annual dividend per share = the latest declared payment × payments per year. A $0.80 quarterly dividend is $3.20 a year. Pick the frequency from the dropdown.
- Type a current price to get current yield and market value. Update it when you review.
- When a dividend lands in your account, add a row to Payments: the payment date, ticker, amount and Y if it was reinvested.
- On Monthly income, type the year in B3. The table and chart fill from your log.
- Optional: copy your portfolio value and current yield into DRIP projection and try different assumptions.
Ex-dividend, record and payment dates
A dividend has several dates. Only one decides whether you get it. Investor.gov (SEC), "Ex-Dividend Dates: When Are You Entitled to Stock and Cash Dividends", explains:
- Record date: the company "sets a record date when you must be on the company's books as a shareholder to receive the dividend."
- Ex-dividend date: "usually set as the record date or one business day before if the record date is not a business day." "If you purchase a stock on its ex-dividend date or after, you will not receive the next dividend payment. Instead, the seller gets the dividend."
- Payment (payable) date: the day the cash is paid. This is the date to use in the Payments log.
Investor.gov's own example: declared Monday, March 2, 2026; record date Monday, March 16, 2026; ex-dividend date Monday, March 16, 2026; payable Tuesday, March 17, 2026. In a second example the record date is Sunday, March 15, 2026, so the ex-dividend date moves back to Friday, March 13, 2026.
Settlement timing explains why these dates now line up. The SEC's T+1 investor bulletin says the standard settlement cycle moved from "T+2" to "T+1", and "The new 'T+1' settlement cycle will apply to all applicable securities transactions occurring on or after May 28, 2024." Under T+1, "if you sell shares of ABC stock on Monday, the transaction will settle on Tuesday." So a buy made the business day before the record date settles on the record date, in time to be on the books. Investor.gov also notes special rules: if a dividend is 25% or more of the stock's value, the ex-dividend date is deferred until one business day after the dividend is paid.
The formulas, explained
Exact formulas from the file (row 5 shown). Payments data is in rows 5–1004: A = date, B = ticker, C = amount, D = reinvested.
| Field | Formula |
|---|---|
| Payments / year (G) | =IF(E5="Monthly",12,IF(E5="Quarterly",4,IF(E5="Semi-annual",2,IF(E5="Annual",1,0)))) |
| Forward annual income (H) | =B5*D5 (shares × annual dividend per share) |
| Income per payment (I) | =IF(G5>0,H5/G5,"") |
| Yield on cost (J) | =IF(C5>0,H5/C5,"") (forward income ÷ total cost basis) |
| Current yield (K) | =IF(F5>0,D5/F5,"") (annual dividend per share ÷ price) |
| Received to date (M) | =SUMIFS(Payments!$C$5:$C$1004,Payments!$B$5:$B$1004,$A5) |
| Month start (Monthly A6) | =DATE($B$3,1,1) … =DATE($B$3,12,1) |
| Received in month (B6) | =SUMIFS(Payments!$C$5:$C$1004,Payments!$A$5:$A$1004,">="&A6,Payments!$A$5:$A$1004,"<"&DATE($B$3,2,1)) |
| Reinvested in month (C6) | Same SUMIFS plus Payments!$D$5:$D$1004,"Y" |
| DRIP: dividends in year | =B15*C15 (start value × yield); reinvested =D15*(1-$B$9) |
| DRIP: next year's yield | =C15*(1+$B$6)/(1+$B$7) |
| DRIP: end value | =B15+E15+F15+G15 (start + reinvested dividends + price change + contribution) |
Using DATE(year, month+1, 1) as the upper bound means December's bound is DATE(2026,13,1), which Excel, Google Sheets and LibreOffice all roll over to 1 January of the next year.
Worked example from the sample data
Forward income and yields: CTSO
60 shares × $3.20 a year = $192.00 forward annual income, paid quarterly as $48.00. Total cost basis is $8,700.00, so yield on cost is $192.00 ÷ $8,700.00 = 2.21%. At a price of $162.40 the current yield is $3.20 ÷ $162.40 = 1.97%. Yield on cost is higher because the price has risen since purchase.
A monthly payer: FABR
200 shares × $1.80 = $360.00 a year, or $30.00 a month. That is why it shows up in every bar of the chart.
Monthly income for 2026
The log holds 19 fictional payments. One is dated 11 Dec 2025, so the 2026 summary skips it. Received in 2026 so far: $679.00, of which $435.00 was reinvested and $244.00 was taken as cash. March, June and September are the biggest months ($123.00) because the two quarterly payers pay then. The whole portfolio's forward income is $832.00 a year, about $69.33 a month on average, with yield on cost 2.75% and current yield 2.44%.
DRIP projection (assumptions, not a forecast)
Default inputs: $10,000 start, 3.5% yield, 4% dividend growth, 4% price growth, no contributions, no tax. Year 1: dividends $350.00, end value $10,750.00. Year 10 end value: $20,610.32; year 20: $42,478.51. If the dividends were taken as cash instead, the shares alone would be worth $14,802.44 after 10 years and $21,911.23 after 20 (the cash you received is not counted in that column). Change any input and the table updates. Real dividends can be cut and prices can fall.
Reinvested dividends are usually still taxable. IRS Publication 550 says: "If you use your dividends to buy more stock at a price equal to its fair market value, you must still report the dividends as income." Use the tax input in the DRIP tab if tax is withheld in your account.
Common mistakes
- Typing the quarterly amount as the annual dividend. Annual dividend per share = payment × payments per year.
- Logging by ex-date instead of payment date. The monthly summary is about cash received, so use the date the money arrived.
- Using the price you paid as "current price". Then current yield equals yield on cost and tells you nothing new.
- Treating forward income as guaranteed. It assumes the current rate continues. Companies can reduce or suspend dividends.
- Forgetting reinvested shares. When a DRIP buys shares, increase the share count and cost basis on Holdings so forward income stays accurate.
- Chasing yield. A very high current yield can mean the price has dropped because the market expects a cut.
FAQ
How do I track dividends in a spreadsheet?
Keep two tables: holdings (ticker, shares, annual dividend per share) to estimate forward income, and a payments log (date, ticker, amount) for what you actually received. A SUMIFS on the payment dates totals income by month. The free template on this page has both, plus a chart.
What is yield on cost?
Yield on cost is your forward annual dividend income divided by what you paid for the shares. If you paid $8,700 for shares that now pay $192 a year, yield on cost is 2.21%. Current yield uses today's price instead of your cost.
Do I get the dividend if I buy on the ex-dividend date?
No. Investor.gov says that if you purchase a stock on its ex-dividend date or after, you will not receive the next dividend payment; the seller gets it. You need to buy before the ex-dividend date.
Is the ex-dividend date the same as the record date?
Usually, now. Investor.gov says the ex-dividend date is usually set as the record date, or one business day before if the record date is not a business day. The US moved to T+1 settlement for trades on or after May 28, 2024.
Are reinvested dividends taxed?
In the US, generally yes. IRS Publication 550 says that if you use your dividends to buy more stock at fair market value, you must still report the dividends as income.
How do I calculate monthly dividend income?
For a forecast, divide forward annual income by 12. For actual income, add up the payments received in each month. The template does both: the average on the summary block and the real totals in the month table.
Related tools and templates
- DCA calculatorRegular investing over time
- Compound interest calculatorGrowth of reinvested returns
- Stock portfolio trackerTrades, cost basis, P/L, allocation
- Net worth trackerAssets minus liabilities, monthly
- Capital gains tax calculatorEstimate tax on a sale
- Trading journalReview each trade
- Trading planRules before you trade
- All templatesFree spreadsheets
Educational template only, not financial, tax or investment advice. Example data is fictional: tickers CTSO, BRDX, FABR and NWND, prices and dividend amounts are made up. The DRIP projection uses assumptions you choose and is not a forecast. Quotes from Investor.gov and the IRS were retrieved on 23 October 2026.