Skip to content
The title slide and first slides of the Capital Gains Tracker template

Capital Gains Tracker

Spreadsheet template for Google Sheets and Excel

Make a copy in Google Sheets
Price
Free
Slides
5
Difficulty
Beginner
Updated
November 15, 2026

Every slide (5)

Overview

Investors who sell shares, funds or ETFs during the year use this capital gains tracker to keep every sale in one list. The Sales tab gives each sale a row with dates, proceeds, fees and cost basis, and it calculates the gain or loss and whether the holding period was longer than a year. The Open lots tab records what you still own. The Dashboard totals gains and losses and shows results by holding period and by investment type.

Fill in the cost basis from your broker's statement, and add a note when you inherited or were gifted an asset, because the basis may follow different rules. Tick the 1099-B box when the form arrives so you can see which sales are still missing from it. Treat the sheet as a record, then ask a tax professional how gains and losses combine on your return.

Useful for

  • Total short-term and long-term gains before filing
  • Check each sale against the broker's 1099-B
  • Keep track of lots you still hold and their cost

What's included

  • 4 tabs: Dashboard, Sales, Open lots, How to use
  • Dashboard: a dashboard with 5 summary figures
  • Sales: a table
  • Open lots: a table
  • 2 charts that update as you type
  • Formulas, dropdowns and colour rules already set up
  • Sample rows that show how to fill it in

How to use it

  1. 1

    Click Make a copy in Google Sheets, then confirm on the Google page. The copy is saved to your own Google Drive.

  2. 2

    Start on the Sales tab: replace the sample rows with your own.

  3. 3

    Keep the formula columns as they are; they fill in as you type.

  4. 4

    Read the How to use tab for tips. For Excel, download the .xlsx file instead.

What you need

Requirements

  • A Google account, or Microsoft Excel for the download

Works with

  • Google Sheets
  • Microsoft Excel (download the .xlsx)

Questions

How do I calculate a capital gain on shares?

Subtract the cost basis and any selling fees from the proceeds. A positive number is a gain and a negative one a loss. The Gain or loss column does this for each row and the Dashboard adds the rows.

What is the difference between short-term and long-term?

It depends on how long you held the investment before selling. The sheet counts the days between the two dates and labels the sale Short-term or Long-term. Read the latest rules to see how each is taxed, or consult a tax professional.

What should I keep for stock sales?

Keep trade confirmations, year-end broker statements and the 1099-B. Note the purchase date and cost of each lot. This sheet holds the figures in one place beside those documents.

  • #tax-tracker
  • #capital-gains-tracker
  • #google-sheets
  • #excel
  • #spreadsheet-template
  • #free-template

Also in Finance

All finance templates
Get this template

Preparing your template…