Skip to main content
Tracking & Reviewing

Spreadsheet Tracking Basics

Pomegra Learn

Spreadsheet Tracking Basics

A tracking spreadsheet is where data becomes knowledge. Without one, you cannot answer whether you are on track to your goals.

Key takeaways​

  • Start with four sheets: Holdings, Contributions, Monthly Snapshot, and Returns Calculation.
  • Use GOOGLEFINANCE (Sheets) or native formulas (Excel) to pull live prices so your calculations stay current.
  • Record cost basis from your broker statement; never rely on an aggregator's estimate.
  • Monthly snapshots create a chain of evidence for tax and estate purposes.
  • A good spreadsheet takes 30 minutes to set up and 5 minutes to update each month.

Why a spreadsheet matters​

You cannot improve what you do not measure, and you cannot measure without a system. Your broker shows you what you own; your aggregator shows you what you are worth; your spreadsheet shows you whether you are winning.

An aggregator like Personal Capital will tell you that your portfolio is worth $500,000 and that you have gained $120,000 in the past three years. But that number includes your contributions. Did you actually earn $120,000, or did you contribute $110,000 and earn $10,000? The aggregator does not tell you the difference. A spreadsheet does.

A spreadsheet is also your record. If you need to prove your cost basis to the IRS, your broker statement is the primary evidence. But your spreadsheet is a useful supporting document that shows your purchase history and your methodology. It is also your proof of diligence—you can show that you rebalanced on purpose, not by accident.

Finally, a spreadsheet is where you build your accountability system. You write down your target allocation, your expected return, and your rebalancing rules. Then every month you compare the reality to the plan. Over time, this comparison tells you whether your strategy is working or whether you need to adjust.

The four-sheet foundation​

A simple but complete tracking spreadsheet has four main sheets:

Sheet 1: Holdings. This is the current state. One row per position (fund, stock, or other security). Columns for:

  • Ticker symbol
  • Description (VTI = Vanguard Total Stock Market ETF)
  • Shares held (or units, for funds)
  • Cost per share (average, from your broker)
  • Total cost basis (shares × cost per share)
  • Current price per share (live, pulled via formula)
  • Current market value (shares × current price)
  • Asset class (Stock, Bond, Cash)
  • Account (Roth IRA, taxable brokerage, etc.)

At the bottom, sum the total market value and calculate the allocation (each position's value divided by total).

Sheet 2: Contributions. A historical log of all money in and out. Columns for:

  • Date (when the money arrived in the account)
  • Account type (Roth IRA, taxable, HSA, etc.)
  • Amount (positive for deposits, negative for withdrawals)
  • Purpose (annual contribution, rebalance, bonus, living expense)
  • Running balance (cumulative sum)

This sheet is your evidence of how much you have actually invested versus how much the portfolio has earned.

Sheet 3: Monthly Snapshot. A frozen record taken on the same day each month. This sheet has 12 columns (one per month) and rows for:

  • Total market value (sum from Holdings sheet)
  • Contributions during the month
  • Withdrawals during the month
  • Beginning allocation percentages (stocks, bonds, cash)
  • Ending allocation percentages

Take a snapshot on the 1st of each month (or the 1st business day). A year of snapshots shows trends: whether your allocations are drifting, whether your contributions are consistent, whether you are growing predictably.

Sheet 4: Returns Calculation. This is where you calculate how well you actually did. Columns for:

  • Period (Year 1, Year 2, Q1 2023, etc.)
  • Beginning balance (from Month Snapshot, first day of period)
  • Contributions during period (sum from Contributions sheet)
  • Ending balance (from Month Snapshot, last day of period)
  • Simple return in dollars (ending - beginning - contributions)
  • Percentage return (return dollars / beginning balance)
  • Your benchmark return (e.g., 70% VTI, 30% BND; you calculate this manually or via formula)
  • Outperformance (your return minus benchmark return)

This sheet is where you answer the question: "Did I beat my benchmark?"

Building the Holdings sheet​

Start in Google Sheets (Sheets is free and works well for this; Excel works too).

Create a header row with: Ticker | Description | Shares | Cost/Share | Total Cost Basis | Current Price | Market Value | Asset Class | Account.

For each of your positions, enter the details manually from your broker statement. Copy the tickers exactly as they appear.

Then use a formula to pull live prices. In Google Sheets:

=GOOGLEFINANCE("TICKER", "price")

For example, in the Current Price column for VTI:

=GOOGLEFINANCE("VTI", "price")

Sheets will look up the current price of VTI and insert it. The price updates automatically throughout the day.

For Market Value, multiply Shares by Current Price:

=B2 * F2

(assuming Shares is in column B and Current Price is in column F).

At the bottom of the Market Value column, sum all positions:

=SUM(G2:G100)

This total is your portfolio's current worth.

To calculate allocation, add a column for Percentage:

=G2 / $G$101

(where G101 is the total; the $ signs lock the reference so it does not change as you copy the formula down).

In Excel, GOOGLEFINANCE does not exist. Instead, use:

=STOCKHISTORY("VTI", TODAY(), TODAY(), "daily", {"open", "high", "low", "close"})

Or, if you have an older version of Excel, use an add-in like "Stocks" (built into Excel 365) or maintain a manual price list.

Alternatively, export your current holdings from your aggregator (Personal Capital, for example, has an export function) and paste them into your spreadsheet as a starting point.

Recording cost basis correctly​

The one number you cannot get from a live price feed is cost basis. You must enter this manually from your broker statement.

Cost basis is the price per share you paid, averaged across all purchases of that security. Your broker statement includes this number—it is often labeled "Cost per share" or "Avg. Cost."

Example: You bought VTI in three tranches.

  • 100 shares at $125 in January 2020
  • 50 shares at $180 in March 2022
  • 100 shares at $200 in May 2024

Your total VTI is 250 shares. Your total cost is (100 × 125) + (50 × 180) + (100 × 200) = $12,500 + $9,000 + $20,000 = $41,500.

Your cost per share is $41,500 ÷ 250 = $166.

Enter $166 in the Cost/Share column. Your broker likely calculated this already; you can copy it.

Cost basis is essential for calculating realized gains when you sell, and for estate planning (the "step-up" basis rule). Do not skip it.

The Monthly Snapshot ritual​

On the 1st business day of each month, open your Holdings sheet. Take the numbers from that day and paste them into your Monthly Snapshot sheet.

Paste values, not formulas—this freezes the numbers so they do not change if you later edit your Holdings sheet.

In your Contributions sheet, sum any deposits and withdrawals from the past month and record them in the Monthly Snapshot.

After 12 months, you have a complete record of the year: growth, contributions, allocation changes, and volatility. This record is valuable in three ways:

  1. Tax planning. If you sold a position at a loss, you can see the exact month and your entire portfolio context.
  2. Performance review. You can calculate your return accurately, accounting for the timing of contributions and withdrawals.
  3. Estate planning. If something happens to you, your heirs have a clear record of your portfolio, its cost basis, and its growth.

Calculating returns correctly​

The simplest return calculation is: (Ending Value - Beginning Value - Contributions) / Beginning Value.

Example:

  • January 1 balance: $100,000
  • December 31 balance: $125,000
  • Contributions during year: $10,000
  • Gain: $125,000 - $100,000 - $10,000 = $15,000
  • Return: $15,000 / $100,000 = 15%

This is a money-weighted return (also called the internal rate of return). It accounts for the timing of contributions.

A more precise calculation is time-weighted return, which removes the effect of contributions. This is harder to calculate by hand but is the fairest way to judge your skill as an investor, because it eliminates the luck of depositing before a rally or withdrawing before a crash.

Most spreadsheets use the simpler money-weighted formula. Write it once and copy it down for each period (month, quarter, year).

Common errors to avoid​

Confusing price and value. The current price of VTI might be $250, but if you own 100 shares, your value is $25,000. Enter both; do not mix them up.

Outdated cost basis. If you add new shares of VTI, update the cost basis. Do not use the old cost per share for new shares bought at a different price.

Forgetting to document significant events. If you harvest a loss, liquidate a position, or receive an inheritance, record it in the Contributions sheet with a note. This creates a chain of evidence.

Using the aggregator's allocation. Some aggregators miscategorize holdings. Always verify your asset class assignments manually—is this position truly 70% stocks and 30% bonds, or is it 100% stocks?

Calculating performance during a rebalance. If you rebalance by selling and buying, the transaction appears as a contribution (an outflow) and then a contribution back (an inflow). Do not be confused; rebalancing does not change your total value, only the composition.

Sharing and backing up​

Keep your spreadsheet in Google Drive or OneDrive so it is backed up automatically. You can also share it with a trusted advisor or family member (read-only) so they can see your portfolio.

Never share your spreadsheet in a public forum; it contains cost basis and holdings information, which is sensitive.

Export a copy to Excel or PDF once per year for archival. If Google Sheets or Excel goes down (unlikely but possible), you have a snapshot.

Decision flow​

Next​

Your spreadsheet now gives you monthly data. But a monthly check-in is only one cadence. Some days you will feel the urge to look, and sometimes you will not. The next article covers the two checking rituals that actually work: the monthly glance and the quarterly deep dive.