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 foreign-currency transactions to CAD using a documented rate and date consistent with CRA guidance
• 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, convert every purchase and disposition to Canadian dollars using a documented exchange rate applicable to that transaction. CRA generally points to the Bank of Canada rate for the day of the transaction, but also accepts qualifying alternative sources and permits averages in certain circumstances.

A spreadsheet without a rate lookup requires you to find the applicable rate, record its source and observation date, and calculate the Canadian-dollar amount. Retain both trade and settlement dates; CRA’s T5008 guide uses the transaction-completion or settlement date in Box 14. For investors with many USD transactions, inconsistent sources or missing date records can make reconciliation difficult.

The USD capital gains calculator handles the Bank of Canada rate lookup automatically for individual transactions. For the full procedure, see Bank of Canada FX rates for capital gains.

Brokerage transfers

When you transfer shares between taxable brokerages without a disposition, the supported ACB generally carries through. The receiving broker may receive the correct book cost, an estimate, or incomplete history. A spreadsheet that replaces the historical ACB with an unsupported transfer value will misstate later capital gains or losses.

The T5008 reconciliation problem

At tax time, you may receive a T5008 with Box 20 cost or book value and Box 21 proceeds, as described in the CRA T5008 guide. CRA says Box 20 may or may not reflect the investor’s ACB. Cross-account holdings, transfers, and ETF adjustments are reasons to reconcile it, not proof that every broker figure is wrong.

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 can reduce manual work when the volume or complexity of adjustments becomes difficult to maintain reliably.

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 supports pooled ACB across accounts, ETF distribution adjustments from provider data, documented FX rates per transaction, and T5008 reconciliation in 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
• Income Tax Act, section 40(2)(g)(i) — Superficial loss rule
• CRA: T5008 Slip Guide
• CRA: Calculating and Reporting Capital Gains and Losses

Frequently asked questions

What columns does an ACB spreadsheet need?

A complete ACB spreadsheet needs at minimum: transaction date, security identifier, brokerage account, transaction type, number of shares, price per share in CAD, commission, running shares, running ACB, and ACB per share. Keep both trade and settlement dates where relevant, plus notes linking each entry to its supporting record.

Can I use Excel to calculate adjusted cost base in Canada?

Yes, for simple portfolios. Excel works well when you hold securities at one brokerage, do not have ETF distributions or DRIP, and have a straightforward buy-and-sell history. Spreadsheets become harder to maintain when you add cross-brokerage pooling, annual ETF adjustments, and documented transaction-level FX conversions.

Is there a free ACB spreadsheet template for Canadian investors?

Yes. myCostBase offers a free ACB spreadsheet template with the correct column structure, the weighted average formula, and example rows for a buy, a DRIP reinvestment, and a partial sale. It is available in Excel format and can be opened in Google Sheets. Download it at mycostbase.ca/tools/adjusted-cost-base-spreadsheet-template/.

How do I handle multiple brokerages in an ACB spreadsheet?

Add a brokerage column and sort or filter by security ticker — not by account. CRA requires you to pool all identical securities across all taxable accounts. Every purchase, DRIP, and ROC adjustment must go into the same pool, regardless of which broker holds the shares. If you have 100 shares at Wealthsimple and 50 shares at Questrade, the ACB is calculated across all 150 shares.

General information only — not tax, legal, or financial advice. Consult a qualified professional for advice specific to your situation.

myCostBase tracks your ACB across brokerages, ETF distributions, and USD trades. Create your free myCostBase account →