Quick Summary
An investment portfolio tracker spreadsheet logs holdings, dividends, and returns across accounts. Includes Excel template, ROI formula, and broker CSV import workflow.
Quick answer. An investment portfolio tracker spreadsheet has three sheets: Holdings (one row per position with shares, cost basis, current value), Dividends (one row per payment with date, ticker, amount), and Performance (cumulative ROI by holding and portfolio total). Our Investment Portfolio Tracker Excel template is $12 and ships with these three sheets plus broker CSV import compatibility.
The portfolio tracker is the spreadsheet that long-term investors actually use most often. Brokerage statements show you the current snapshot; the spreadsheet shows you the history, the dividend pattern, and the performance attribution. This post covers the column structure, the formulas that matter, and the workflow for keeping it current.
What the tracker needs to do
Five jobs.
- List every position with cost basis and current value. This is the table.
- Log every dividend received. For dividend tracking and total return calculation.
- Calculate cumulative ROI per position. Both price appreciation and dividend yield.
- Aggregate portfolio totals. Total cost, total value, total return.
- Make broker statements importable without manual re-typing. CSV paste workflow.
That’s it. Charting, asset allocation, sector breakdown are all extras that can sit on a dashboard but aren’t required for the core function.
The Holdings sheet
One row per position. Columns:
| Column | Example | Notes |
|---|---|---|
| Ticker | VTI | Or fund name |
| Account | Vanguard taxable | Helps when you have multiple accounts |
| Shares | 124.5 | Including fractional |
| Cost basis (total) | 22,800 | What you paid for all shares combined |
| Avg cost per share | =Cost basis / Shares | Calculated |
| Current price | 245.30 | Manual update or formula |
| Current value | =Shares * Current price | Calculated |
| Unrealized gain/loss | =Current value - Cost basis | Calculated |
| Percent return (price only) | =(Current value - Cost basis) / Cost basis | Calculated |
| Annualized return | XIRR-based | Optional |
The Current price column is where most trackers handle either manual updates (you type in a price weekly) or formula-based pulls. Google Sheets has GOOGLEFINANCE which can pull live prices: =GOOGLEFINANCE("VTI","price"). Excel doesn’t have a built-in equivalent (some workarounds exist via Power Query or Stock data type).
For most users, manual updates weekly or monthly are easier than wrestling with live data feeds. The portfolio doesn’t change second-to-second; weekly is sufficient for tracking.
The Dividends sheet
One row per dividend received. Columns:
| Column | Example |
|---|---|
| Date paid | 2026-03-31 |
| Ticker | VTI |
| Account | Vanguard taxable |
| Amount | 87.20 |
| Per-share | 0.70 |
| DRIP? | Yes |
| Notes | Q1 2026 distribution |
Why log dividends separately instead of relying on broker statements? Two reasons. First, dividend totals across accounts are tedious to assemble at tax time without a single log. Second, total return (price appreciation plus dividend yield) is the right measure of investment performance, and you can’t calculate it without the dividend log.
Most brokers export dividends as a CSV for any date range. A monthly paste into the Dividends sheet keeps it current.
The Performance sheet
Read-only. Pulls from Holdings and Dividends and produces:
- Total portfolio value (sum of Current value column)
- Total cost basis (sum of Cost basis column)
- Unrealized gain/loss (total value minus total cost)
- Total dividends YTD (sum of Dividends sheet for current year)
- Total dividends LTM (last twelve months)
- Total return YTD = (current value plus YTD dividends - prior year-end value) / prior year-end value
- Total return since inception (XIRR across all cash flows)
The XIRR calculation is the gold-standard performance measure because it accounts for the timing of contributions and dividends. The formula in Google Sheets:
=XIRR(cash_flows_range, dates_range)Where cash flows are negative for contributions, negative for dividends reinvested, and positive for current value (as if you sold today). Dates are the corresponding transaction dates.
This is more accurate than simple “current value minus cost basis divided by cost basis” because it factors in when you put money in.
A worked example
Dana has a small taxable portfolio. Her Holdings sheet:
| Ticker | Account | Shares | Cost basis | Current price | Current value | Gain/loss | Percent |
|---|---|---|---|---|---|---|---|
| VTI | Vanguard | 78.4 | 18,200 | 245.30 | 19,231 | 1,031 | 5.7% |
| VXUS | Vanguard | 102.1 | 6,400 | 64.80 | 6,616 | 216 | 3.4% |
| VTEB | Vanguard | 156.7 | 8,300 | 51.10 | 8,007 | -293 | -3.5% |
| Total | 32,900 | 33,854 | 954 | 2.9% |
Dividends YTD: $412. Total return YTD (price plus dividends): $954 + $412 = $1,366. Against a starting balance of $32,900, that’s 4.1 percent YTD.
She updates current prices weekly, dividends monthly, and reviews the Performance dashboard quarterly. Total time: about 90 minutes a year for a small portfolio.
Where the spreadsheet helps that the broker doesn’t
Cross-broker view. If you have positions at Vanguard, Fidelity, and a 401(k) at Schwab, no single broker shows the consolidated view. The spreadsheet does.
Historical dividend yield. Brokers often show last 12 months. A spreadsheet shows whatever range you’ve logged.
Total return vs price-only return. Brokers often default to price-only. The spreadsheet calculates true total return including dividends.
Tax-lot detail for selling decisions. The spreadsheet can hold tax-lot rows (date acquired, shares, cost) to identify which lots to sell for tax-loss harvesting. Some brokers expose this; many don’t, especially for old positions.
Where the spreadsheet doesn’t help
Live prices. Brokers show live; spreadsheets show last-updated. For long-term investors this doesn’t matter; for active traders it’s a deal-breaker.
Tax document generation. Brokers send 1099-DIV, 1099-INT, 1099-B at year end. The spreadsheet doesn’t produce these; you still need broker docs for tax filing. The spreadsheet helps cross-check and pull deduction-relevant numbers but doesn’t replace.
Cost basis for old positions. If you have positions purchased before 2011, brokers may not have cost basis. You may need to reconstruct it. The spreadsheet is a place to record what you find, not a way to find it.
The broker CSV import workflow
Most brokers let you download a transaction CSV. The workflow:
- In the broker portal, open Transaction History or Account Activity.
- Filter to the date range you want (typically last month or YTD).
- Export as CSV.
- Open the CSV in Excel or Sheets.
- Filter to dividend rows (transaction type = “DIV” or similar).
- Copy the relevant columns (date, ticker, amount).
- Paste into the Dividends sheet of the tracker.
- For new buy/sell transactions, update the Holdings sheet with new share counts and adjusted cost basis.
10 minutes a month if you do it consistently. Two hours if you wait until December. Consistency wins.
What our paid template adds
The Investment Portfolio Tracker Excel template is $12 and includes:
- Pre-built Holdings, Dividends, and Performance sheets with the columns above
- Asset allocation pie chart (auto-categorizes by asset class if you tag holdings)
- Multi-account totals
- Currency support (for international holdings)
- Excel format that opens in Sheets, Numbers, LibreOffice
- XIRR formulas pre-built
If you’d rather build it from scratch, the structure above is reproducible in about 90 minutes. Most people who track manually for 6 months end up wanting a template anyway, so the $12 saves the rebuilding effort.
What I’d add after a year of using one
Two things I added to my own copy:
A “tax efficiency” tag per holding. Lets me note which positions are in taxable vs tax-advantaged accounts. Useful for asset location decisions (bonds belong in tax-advantaged; stocks can sit in either; international has different optimal placement).
A “rebalance check” cell. If my target allocation is 70 stocks / 25 bonds / 5 cash, the cell calculates current drift. When drift exceeds 5 percent in any bucket, I rebalance. The cell turns red as a visual cue.
Five extra cells, real change in usefulness.
Get the template
- Financial Planning Spreadsheet — 40-year life projection with net worth, cash flow, and FIRE in one file.
- Net Worth Tracker — Monthly asset and liability log with a 12-month trend chart.
- Net Worth Tracker — Monthly asset and liability log with a 12-month trend chart.
- Investment Portfolio Tracker Ultimate ($19) — Up to 50 holdings, dividend tracker, sector allocation targets, and rebalancing suggestions.