Capital Gains Excel - Free Template

1. Open the Transactions sheet and enter each sale or disposition on its own row. Use the actual dates, cost base, and selling price, not rounded estimates.
2. Check the Inclusion_Rules sheet before entering rows with mixed asset types. It gives you the reference table used to classify the rate.
3. Fill in the asset description, province or territory, and holding period details. This helps you keep a clean audit trail if you review the file later.
4. Review the calculated capital gain/loss and taxable capital gain/loss columns. If the gain looks wrong, the usual cause is a bad acquisition date or an incorrect cost base.
5. Use the Summary sheet to check totals by category and compare gains against losses. This is the quickest way to see whether the workbook matches your tax records.
6. Read the Instructions sheet once before your first entry and then again at year-end. It explains the workbook layout so you do not have to guess where each item belongs.

You use this workbook when you need a clean record of each disposition, not just a rough list of trades. A sole investor with 8 share sales, a bookkeeper at an incorporated company disposing of equipment, or a family reporting a cottage sale can all use the same structure to capture cost base, proceeds, and holding period.

When the workbook becomes useful

The pressure usually shows up at year-end or when you are preparing a T1 return and need to reconcile what was sold. If you sold 6 positions at $4,500 each and one of them has a $1,200 gain, you want the taxable portion visible immediately instead of reworking it in a calculator.

Why the sheet structure matters

The Transactions sheet gives you one line per event, which is the right way to handle a portfolio with mixed dates and asset types. The Summary sheet is there for the quick check: if your gains total $18,000 and losses total $7,500, you can see the net exposure before you hand the file to your accountant or file yourself.

Once the net exposure is clear, the next year’s filing is easier to line up with your RRSP contribution room tracker so the deduction side and the capital gains side stay in sync.

The CRA expects you to keep enough detail to support the adjusted cost base, proceeds of disposition, and the resulting gain or loss. For Canadian tax reporting, capital gains are generally reported at the inclusion rate in effect for the year, and the workbook’s rule table is there so you can apply that rate consistently instead of guessing row by row.

Records and retention

You should keep supporting records for at least the CRA’s six-year retention period. That means acquisition confirmations, sale confirmations, broker statements, and any notes showing why a row was included at a specific rate.

Why the inclusion rate matters

If a transaction has a $10,000 capital gain and the applicable inclusion rate is 50%, the taxable capital gain is $5,000. If the rate changes and you fail to update the workbook, the error flows straight into your return and can distort both tax payable and instalment planning.

This is why the Inclusion_Rules sheet is more than a reference tab: it keeps the rate visible when you are working through several sales in the same month. A small investor with 15 rows can stay accurate without rebuilding formulas in a separate tax calculator.

That same trail of sales also belongs in an ACB tracker, so the gain calculation and cost base stay aligned as each disposition is added.

The biggest mistake is using the wrong cost base, especially when fees and commissions are left out. If you bought shares for $12,000 and paid $45 in trading fees, then sold for $14,000, that $45 changes the gain and should not be ignored.

Dates and classification mistakes

Another common problem is mixing up disposition date and settlement date. A sale that closed on 2026-03-10 but settled on 2026-03-13 may not change the economics, but it can affect the way you document the event and the order in which rows appear in your records.

What bad entries cost

One wrong row can distort the whole summary. If you understate gains by $3,000 and the marginal tax impact is roughly 25% to 35% in your bracket, that is $750 to more than $1,000 in tax error before interest or reassessment work.

People also forget to separate capital and income treatment. A frequent trader with 40+ short-term sales may not be dealing with capital gains at all, so the spreadsheet should be used with a clear tax position rather than as a blanket substitute for proper reporting.

The easiest way to keep this workbook useful is to update it on the same day you get the trade confirmation or sale notice. If you wait until March, you will spend an afternoon chasing down missing acquisition dates and broker statements.

Simple habits that keep it current

• Enter each sale once it settles, while the numbers are still in front of you.
• Use the same naming style for assets so the Summary sheet stays readable.
• Check the Inclusion_Rules tab before adding a row with an unusual holding period or asset type.

When a spreadsheet is no longer enough

If you are handling hundreds of trades, multiple accounts, or complex corporate reorganizations, the workbook becomes a review tool rather than the main system. At that point you usually need brokerage exports, a proper tax package, or accountant-reviewed schedules to keep the data reliable.