Adjusted Cost Base ACB Excel - Free Template

1. Open ACB_Transactions and enter each trade on its own line. Use a new row for every buy, sell, dividend reinvestment, split, or adjustment.
2. Fill in the security details first: trade date, settlement date, name, ticker, security type, and account type. That gives you a clean audit trail if you review the file later.
3. Enter quantity, price per unit, commission, and other fees. The template is set up to show the full cost or proceeds instead of hiding fees inside the trade value.
4. Check the running units and running ACB after each entry. If a sale happens, the sheet helps you see the per-unit cost base before you calculate the gain or loss.
5. Review ACB_Summary to see the current position by security. Use it as your quick check before year-end or before you prepare your tax return.
6. Use Reference_Prices when you want a market value comparison. This is useful when you are reviewing an account, checking concentration risk, or updating records after a busy month.
7. Read Instructions before your first entry. If you trade often, copy the last clean row style so your dates, notes, and number formats stay consistent.

This template is for the person who actually has to prove the numbers later: a self-directed investor, a bookkeeper helping a shareholder, or a family tracking a non-registered account before tax season. If you buy 100 ETF units at $22.50, then sell 40 units three months later, you need the original purchase price, the commission, and the reduced unit balance in one place.

When the file earns its keep

You use it after each trade, not just at year-end. A month with 12 buys and 3 sells can get messy fast, especially when one account reinvests distributions and another has foreign securities with separate fees.

What image 1 shows

Image 1 shows the ACB_Transactions sheet with columns for Txn ID, trade date, settlement date, security name, ticker, transaction type, quantity, price per unit, gross amount, commission, other fees, total cost or proceeds, running units, running ACB, ACB per unit, capital gain/loss, and notes. That is the working ledger you update row by row.

For a practical example, 250 shares bought at $18.00 plus a $9.95 commission give a cost base of $4,509.95. If you later sell 80 shares, the sheet lets you see the remaining units and the per-unit base instead of guessing from an old confirmation slip.

The CRA expects you to keep enough detail to support every capital gain and capital loss you report. In Canada, that means trade confirmations, dates, quantities, fees, corporate actions, and an ACB history you can explain if a review comes up years later.

Recordkeeping and retention

Keep the support for at least six years after the end of the tax year it relates to. If you sold shares on 2026-11-15, keep the trade slip, your ACB worksheet, and any adjustment notes until the end of 2032.

Why this beats a loose notes app

A notes app will not reliably separate gross proceeds from fees, and that is where people go wrong. If you sell $12,000 of stock and pay $14.95 in commission, that fee changes the gain; ignore it and you overstate taxable income.

If you hold more than one account, the same security must be tracked consistently across each one. A taxable account, a spousal account, and a corporate account are not interchangeable, so a single workbook with clear account type fields is the safer choice than scattered tabs or paper files.

The most common error is treating the purchase price as the whole cost base and forgetting fees. On a $25,000 ETF purchase with a $9.95 commission, that seems small, but across 20 trades it can shift your gain by hundreds of dollars.

Where the numbers break

Another problem is not adjusting units after a partial sale. If you bought 1,000 units and later sold 300, the remaining 700 units must carry the correct remaining ACB; if they do not, every later sale is wrong.

Corporate actions are where people get burned

Splits, consolidations, return of capital, and reinvested distributions are the places where a casual spreadsheet falls apart. A 2-for-1 split doubles the units and halves the per-unit ACB; if you miss that step, the next sale can show a false gain or loss by a wide margin.

I have seen investors discover in March that a December sale was based on the wrong average cost because they mixed up settlement date and trade date. That can mean a tax slip correction, extra bookwork, and a return that needs to be amended before the numbers line up.

That same kind of mistake can also distort a registered drawdown plan when withdrawals, withholding tax, and income timing are not tracked against the right dates.

The file works best when you enter trades as they settle instead of waiting for a pile of confirmations in December. A 10-minute weekly update is usually enough for a small portfolio with 5 to 15 trades a month.

Make it part of your month-end

• Update the ledger on the same day you reconcile your broker statement.
• Use the same row structure for every trade so formulas stay intact.
• Copy the prior month’s blank lines and keep formatting consistent.
• Check the summary sheet before you file your tax return or review unrealized gains.

When a spreadsheet is no longer enough

If you are entering dozens of trades a day, handling options, or managing several related accounts, a spreadsheet becomes slow and error-prone. At that point, a portfolio system or a broker export with stricter controls is the better tool, but for a normal Canadian investor this workbook is a solid working file.

The habit that keeps it useful is simple: record the trade, confirm the fee, and update the ACB before the next purchase changes the average again.