ACB Spreadsheet Template and Instructions for Canada

An ACB spreadsheet is useful when you can keep the transaction history complete and the formulas consistent. This free workbook combines the download, instructions, and worked example in one place, so you can check the method before entering your own records. It pools matching securities across taxable brokerage accounts for one owner; it does not verify the facts behind your entries.

Download the ACB spreadsheet

Download the free Excel ACB workbook (.xlsx) — no account required.

The workbook contains Instructions, Transactions, ACB Summary, Capital Gains, and Sources & Method sheets. Save an untouched copy before replacing the example rows. The download URL is unchanged, so existing bookmarks to the file continue to work.

The file is supplied in Excel format. If you import it into Google Sheets, compare the sample results and review flags before using the converted workbook. We have not independently validated that conversion for this release. For the underlying tax-cost arithmetic rather than spreadsheet operation, see the ACB definition, formula, and worked calculation guide .

Set up one owner and consistent security identifiers

Use a separate workbook for each taxable owner. Enter all relevant history for identical securities held by that owner, including transactions at another brokerage. The Account / Broker column helps trace source records; it does not create a separate cost pool.

Use the same Security ID for the same identical property throughout the workbook. Two spellings can accidentally split one pool, while reusing a ticker for different share classes can combine properties incorrectly. List each Security ID once in the summary sheets and review the resulting holdings against your statements.

Before entering a long history, test a purchase and sale for one security. Confirm the sign conventions and compare the result with an independently calculated example. Keep original confirmations, statements, and adjustment evidence outside the workbook as well.

What each of the five sheets does

The transaction sheet labels its date column Trade Date. Retain settlement dates separately and review the applicable acquisition/disposition timing, particularly around year-end and loss-repurchase windows. A spreadsheet label does not determine the tax treatment.

Enter purchases, sales, and adjustments

The workbook applies the direction of an adjustment based on the event type. Enter the aggregate amount, not a per-unit distribution rate. For example, a supported $0.20 reduction for 100 entitled units is a $20 adjustment.

Keep input cells separate from formulas. When adding rows, preserve the relevant formula ranges and check that later rows still include the complete preceding history. Sort entire rows together; sorting only dates disconnects transactions from their quantities and amounts.

Worked spreadsheet example

Assume one Canadian-dollar security and no other holdings or adjustments. These illustrative rows use the workbook’s event names and sign conventions:

Before the sale, 105 units have $2,099 total ACB. The cost allocated to the 35 units sold is $2,099 × 35 ÷ 105 = $699.666666…. Gross proceeds are $875 and the selling commission is $5, giving a capital gain of $875 − $699.666666… − $5 = $170.33, rounded at the end.

The remaining 70 units carry $1,399.333333… total ACB, displayed as $1,399.33. ACB per unit stays unchanged by the partial sale. Do not replace the underlying cost with a rounded per-unit amount.

Understand the spreadsheet formulas

After a purchase, add purchase cost and commission to total ACB, then divide by the updated unit balance. For a sale, remove cost at the pre-sale average and calculate the gain separately. Cost adjustments change total ACB without changing units.

To check the sale example independently in Excel or Google Sheets, enter:

1

=875-(2099*35/105)-5

The result is approximately 170.333333; format it to two decimal places for display. This check is separate from the workbook’s running formulas, which also need consistent Security IDs and chronological rows.

For a deeper explanation of pooling, see ACB across multiple brokerages . For distribution evidence, see ETF return of capital and reinvested distributions .

Common input and formula mistakes

• Positive sale quantities: sales use negative quantities. A wrong sign can increase holdings instead of reducing them.
• Per-unit adjustments: multiply the supported rate by entitled units before entering an aggregate adjustment.
• Double-counted commissions: do not embed a commission in the purchase price and also enter it separately.
• Broken formula ranges: check calculations after inserting, deleting, or sorting rows.
• Missing history: transferred shares need supported acquisition history; current market value is not a substitute for missing cost.
• Ignored warnings: investigate every CHECK or REVIEW flag, including sales exceeding holdings and cost reductions exceeding the pool.

Foreign-currency transactions require supported CAD inputs. The workbook does not fetch transaction-level exchange rates. The USD capital gains calculator can help check a single conversion scenario; retain the source and date for the rate you use.

Review and retain your records

Reconcile units by security against brokerage statements before reviewing gains. Compare sale proceeds and commissions with confirmations, and check annual fund adjustments against issuer information and tax slips. Review the selected year and Security IDs on Capital Gains rather than assuming the summary contains every disposition.

Keep dated backups of the workbook and original documents. Store a source reference for each row so another person can reproduce the result. The year-round recordkeeping routine explains when to collect evidence; the pre-filing ACB checklist supports final review and accountant handoff.

When to use a saved ledger

A spreadsheet remains a valid choice when you can maintain its inputs, formulas, and evidence. More securities, recurring imports, annual adjustments, or repeated reconciliations increase the manual review work. They do not automatically invalidate the spreadsheet approach.

If that work becomes difficult to maintain, myCostBase provides account-based ACB calculation and saved history. Compare plan features for import limits, ledger details, and exports, or create a free account from the product page. Moving to software still requires complete transaction records and supported adjustments.

Scope: This template supports straightforward securities records for one taxable owner. It does not determine superficial losses, complex reorganizations, option exercises, or deemed dispositions. Review transactions outside its supported event types separately before using the totals.

ACB spreadsheet and template: common questions

Is there a free ACB spreadsheet template for Canadian investors?

Yes. Download the Excel workbook from the download section on this page without an account. It includes five sheets, pooled ACB formulas, sample transactions, and review flags. Keep your own source documents and review the instructions before replacing the sample rows.

Can I use this ACB spreadsheet in Google Sheets?

The download is an Excel workbook. You can import an Excel file into Google Sheets, but verify that the formulas, dates, and review flags behave as expected after conversion. Compare the sample results before entering your own history; Google Sheets compatibility has not been independently validated for this release.

How do I handle multiple brokerages in an ACB spreadsheet?

Use one workbook per taxable owner and a consistent Security ID for identical securities across brokerages. The workbook pools by Security ID; the account column identifies the source of each row. Do not combine different owners or different properties into one pool.

Do ETF distributions affect the ACB I track in this spreadsheet?

Supported return-of-capital amounts reduce ACB and reinvested distributions can increase it. Enter a supported aggregate Canadian-dollar amount in an ACB Reduction or ACB Increase row with zero quantity, and retain issuer and tax-slip evidence. Review any excess-reduction flag separately.

What columns does an ACB spreadsheet need?

Keep transaction date, Security ID, account, event type, quantity, Canadian-dollar price, commission, supported adjustment, and a source reference. Formula columns track running units, total ACB, ACB per unit, and sale results. Retain settlement dates separately where relevant.

Is the spreadsheet format required by the CRA?

This workbook is a recordkeeping aid, not a CRA form or an endorsement. Your records need to support the applicable calculation and the amounts you report. Preserve the original documents and verify treatment of transactions outside the workbook scope.