ACB Spreadsheet for Canadian Investors

Many Canadian investors start tracking adjusted cost base in a spreadsheet. It works — for a while. A well-structured Excel or Google Sheets workbook handles simple buy-and-sell histories cleanly, and the weighted average formula is not complicated. The problem is that most portfolios eventually grow beyond what a spreadsheet handles reliably, and the failure modes are invisible until tax time.

This ACB spreadsheet guide for Canadian investors explains what a complete spreadsheet needs to track, where the formula breaks down, and when switching to dedicated ACB software becomes the right call.

What adjusted cost base tracking requires

Before building or downloading a spreadsheet, it helps to understand exactly what ACB tracking requires. Adjusted cost base is itself a defined term in the Income Tax Act (section 54), and CRA uses the weighted average pooling method required under the Act’s identical property rules (section 47): every share of the same security you hold in any taxable (non-registered) account belongs to a single cost pool, and every new purchase recalculates the average cost per share across the entire pool.

This means your spreadsheet must:

• Pool identical securities across all taxable accounts (not just one brokerage)
• Recalculate ACB per share after every buy and every DRIP reinvestment
• Apply annual return-of-capital reductions from ETF distributions
• Add phantom income distributions to ACB in years where reinvested gains increase cost
• Convert USD transactions to CAD at the Bank of Canada daily rate on each trade date
• Carry original cost through brokerage transfers (no cost reset on transfer)
• Track the superficial loss ACB adjustment if shares were repurchased within 30 days of a loss, per the Income Tax Act’s superficial loss rule (paragraph 40(2)(g)(i)) and the definition of “superficial loss” in section 54

For a full explanation of the mechanics, see How to Calculate Adjusted Cost Base in Canada.

What columns an ACB spreadsheet needs

A minimum viable ACB spreadsheet needs these columns:

The notes column is often omitted but becomes important at tax time: when your T5008 Box 20 does not match your ACB (which is common), a clear record of every adjustment and its source makes the reconciliation defensible. Use the T5008 ACB reconciliation checker to compare your ledger against the broker’s figures before filing.

The weighted average formula in Excel

The ACB per share after a new purchase is:

1

New ACB per share = (Previous ACB Total + (New Shares × Price) + Commission) ÷ New Total Shares

In a spreadsheet with running columns, this is straightforward to implement. Example rows for XEQT.TO:

After the partial sale, the ACB per share stays at $25.70 — the share count drops but the cost per share is unchanged until the next buy or adjustment.

The ROC Reduction row on 2022-12-10 reduces the running ACB Total by 150 × $0.18 = $27.00. The per-share ACB drops from $25.83 to $25.65. This adjustment must be applied every year a distribution includes a return-of-capital component.

Excel/Google Sheets formula for ACB per share after a buy:

1

=IF(Action="Buy", (Prev_ACB_Total + Shares * Price + Commission) / New_Total_Shares, Prev_ACB_Per_Share)

For sells, the ACB per share does not change — you only reduce the running share count and calculate the capital gain separately.

Adjusted cost base Excel template

A free ACB spreadsheet template is available at /tools/adjusted-cost-base-spreadsheet-template/. It includes the column structure above, the weighted average formula pre-built, and three sample rows (a buy, a DRIP reinvestment, and a partial sale) so you can see the pattern before adding your own data. The template is in Excel format and opens in Google Sheets without modification.

For the template to work correctly, you need to add all transactions — from all taxable accounts — for each security into the same sheet, sorted by date. Keeping separate sheets per brokerage and then trying to calculate pooled ACB manually is a common source of error.

When an ACB spreadsheet is enough

A spreadsheet is adequate when your situation is genuinely simple:

• You hold securities at one brokerage only
• You hold individual stocks or broadly diversified ETFs that pay little or no return of capital
• You trade in Canadian dollars (no USD conversions)
• You do not participate in DRIP
• You have fewer than a dozen securities
• You have not transferred positions between brokerages

In this scenario, the weighted average formula handles everything. You buy, you sell, you calculate the gain. The spreadsheet accurately reflects your legal ACB.

The break-even point — where the spreadsheet is still manageable but has become error-prone — is usually when one or more of the following applies: you hold the same ETF at two brokerages, you’ve been receiving annual ROC distributions for several years, or you’ve made DRIP reinvestments across dozens of months.

Where ACB spreadsheets break down

Cross-brokerage pooling

CRA requires you to pool all identical securities across all your taxable accounts. A spreadsheet structured per-brokerage cannot do this without manual consolidation. The manual step is where errors happen: investors forget to merge one account’s entries, use the wrong sort order, or apply a sale to the wrong cost pool.

If you hold XEQT at both Wealthsimple and Questrade, your spreadsheet must show a single pool of all units. The brokerage column is for your records — the ACB calculation ignores which account held which shares.

Return of capital from ETFs

Annual ROC adjustments must be subtracted from ACB each year the distribution occurs. This information appears on your T3 tax slip or in the ETF provider’s annual distribution tax breakdown. Many investors who track buys and sells in a spreadsheet miss the ROC rows entirely because the adjustment does not appear in a transaction history — it has to be looked up separately and entered manually.

Miss five years of ROC on a position with $0.30/unit annual ROC and 200 units, and your ACB is overstated by $300.00. When you sell, you report a capital gain $300.00 smaller than the legally correct amount — and if CRA reassesses, you owe the tax on that amount plus interest.

For the full walkthrough on ROC adjustments, see ETF Return of Capital and Adjusted Cost Base.

DRIP reinvestments

Dividend reinvestment plans generate small purchases — sometimes fractional shares — on the dividend payment date. Each reinvestment adds new shares at cost to the ACB pool. Over years of monthly DRIP participation, this means dozens or hundreds of rows in the spreadsheet, each requiring a correct price (the reinvestment price, which may differ slightly from the market close), a correct share count (including fractional shares), and zero commission.

Brokers often show DRIP in the transaction history, but the entries may be inconsistent in format or timing. Some brokers record the full-year DRIP as a single year-end entry rather than individual monthly purchases. For ACB purposes, each reinvestment should be recorded separately at the actual reinvestment date and price.

USD trades and FX conversions

For USD-denominated securities, every purchase and every sale must be converted to CAD at the Bank of Canada daily rate on the trade date. This means looking up the rate for each transaction — not using an annual average, not using the broker’s conversion, and not using the settlement-date rate.

A spreadsheet that does not embed Bank of Canada rate lookups cannot do this automatically. You must manually find the rate for each date, enter it, and verify it. For investors with dozens of USD transactions per year, this is the single most error-prone part of the manual process.

The USD capital gains calculator handles the Bank of Canada rate lookup automatically for individual transactions. For the full procedure, see How to Use Bank of Canada FX Rates for Canadian Capital Gains.

Brokerage transfers

When you transfer shares from one taxable brokerage to another, the receiving broker may book the shares at current market value — not at your original cost. For ACB purposes, your original cost carries through unchanged. A spreadsheet that uses the broker’s transfer price as the cost will have the wrong ACB from that point forward, overstating or understating all future capital gains on that position.

The T5008 reconciliation problem

At tax time, your broker sends a T5008 slip with Box 20 (cost or book value) and Box 21 (proceeds of disposition), per the CRA’s T5008 slip guide. Box 20 is the broker’s book value — not your legal ACB — and it is wrong whenever you’ve held the same security across multiple accounts, transferred shares, or received ETF distribution adjustments. A spreadsheet that doesn’t systematically reconcile against Box 20 leaves this discrepancy invisible.

For more on why Box 20 is often different from your correct ACB, see T5008 Box 20 and Adjusted Cost Base.

Spreadsheet vs ACB tracking software

The right tool depends on your situation. A spreadsheet is a reasonable starting point. Dedicated software becomes necessary when the volume or complexity of adjustments exceeds what you can reliably maintain manually.

When to move from a spreadsheet to myCostBase

The spreadsheet has served its purpose when any of the following is true:

• You hold the same ETF or stock at more than one taxable brokerage and are manually merging pools
• You’ve received multiple years of ETF distribution adjustments (ROC or phantom income) that you may not have applied consistently
• You trade USD-denominated securities and are looking up Bank of Canada rates manually for each transaction
• You’ve transferred positions between brokerages and are unsure whether the cost carried through correctly
• You’re spending more than a few hours at tax time reconstructing what your ACB should be

myCostBase handles all of these automatically: pooled ACB across accounts, ETF distribution adjustments applied from provider data, Bank of Canada FX rates per transaction, and T5008 reconciliation built into the year-end workflow. Start with manual entry on the free plan — no CSV import required.

For a detailed breakdown of what a complete ACB tracker needs to maintain — including every transaction type that affects the ledger — see the ACB Tracker for Canadian Investors.

References

• Income Tax Act, section 47 — Identical Properties
• Income Tax Act, section 54 — Definitions (“adjusted cost base”, “superficial loss”)
• Income Tax Act, section 40(2)(g)(i) — Superficial loss rule
• CRA: T5008 Slip Guide
• CRA: Calculating and Reporting Capital Gains and Losses

This article is for general educational purposes only and is not tax, legal, or financial advice. Adjusted cost base calculations depend on your complete transaction history and personal circumstances. Consult a qualified tax professional for advice specific to your situation.

Use myCostBase to track your ACB across brokerages, ETF distributions, and USD trades — start free →