Canadian Capitalist Logo Dark
No Result
View All Result
Saturday, January 28, 2023
  • Login
  • Register
  • Home
  • Economy
  • Investing
  • Markets
  • Real Estate
  • Retirement
  • Tax Savings
  • Trivia
  • Resources
Subscribe
Canadian Capitalist Logo Light
  • Home
  • Economy
  • Investing
  • Markets
  • Real Estate
  • Retirement
  • Tax Savings
  • Trivia
  • Resources
No Result
View All Result
Canadian Capitalist Logo Mobile
No Result
View All Result
Home Uncategorised

Free ACB & Capital Gains Tracker in Excel

by Ram Balakrishnan
August 24, 2010
Reading Time: 2 mins read
174 13
0
what is a life insurance policy dividend
212
SHARES
2.7k
VIEWS
Share on FacebookShare on TwitterShare on Linkedin

In response to yesterday’s post, Reader Kevin asked me to share the Excel spreadsheet that I use for tracking adjusted cost base (ACB) and capital gains. Since I mostly make buy transactions and rarely do a sell transaction these days, my set up is rather simple. I simply input the date, type of transaction (buy, sell or ROC), no. of shares, price / share and commission and compute the ACB and share balance. Each stock goes into a separate sheet and US stocks have an additional column for the exchange rate.

If you haven’t calculated ACB before, it is quite simple. When you make a buy transaction, you should add the total value of the current transaction to the previous ACB. For instance, let’s say you own 300 shares of XYZ Corp. at an ACB of $3,000. If you buy another 100 shares of XYZ Corp. for a total of $1,500, you’ll own 400 shares of XYZ Corp. at an ACB of $4,500.

When you make a sell transaction, your ACB decreases by the number of shares sold times the average ACB per share. For instance, if you had instead sold 100 shares of XYZ Corp., you’ll now own 200 shares at an ACB of $2,000. You’ll also have a capital gains of $500.

Return of Capital simply reduces the ACB by the number of shares times ROC per share. For instance, if XYZ Corp. had a return of capital of $1 per share, your ACB would be $2,700 ($3,000 – 300 shares * $1). If you need more detailed explanation of ACB, check out Gummy Stuff’s Adjusted Cost Base for Canadians. DRIP Primer also has a handy explanation of ACBs for those who reinvest their dividends.

To make Kevin happy, I cleaned up my Excel spreadsheet and added some code to automatically handle buy, sell and Return Of Capital transactions. All you need to do to track your ACB, is copy the cells into a new sheet and enter the Date, Transaction, # Shares, Price / Share, Exchange rate (if applicable) and Commission columns. The spreadsheet should calculate ACB, Share Balance and Capital Gains columns. Just copy and paste the previous row to create a new transaction. You can download the spreadsheet here. Comments are welcome.

Related posts:

  1. Finding a Financial Advisor, Part 1
  2. Carnival of Debt Reduction # 19
  3. The Income Tax Cut is Better
  4. This and That
Share85Tweet53Share15

Get real time update about this post categories directly on your device, subscribe now.

Unsubscribe
Previous Post

A Free Adjusted-Cost Base (ACB) & Capital Gains Tracker

Next Post

This and That: Burton Malkiel and more…

Ram Balakrishnan

Ram Balakrishnan

Related Posts

Why you cant afford a house in Canada

Why You Can’t Afford A Home In Canada?

January 24, 2022
358
investing benefits
Investing

Finding a Financial Advisor, Part 1

June 19, 2021
2.1k
investing in bitcoin

Is it time to invest in Bitcoins again?

May 13, 2019
2k
when do reits liquidate
Uncategorised

Performance of Currency-Neutral S&P 500 Index Funds

January 19, 2014
2k
is mortgage interest tax deductible
Uncategorised

The 2013 Sleepy Portfolio Report Card

January 12, 2014
2k
how to buy individual stocks in canada
Uncategorised

Asset Class Returns for 2013

January 5, 2014
1.9k
Next Post
how to reinvest dividends

This and That: Burton Malkiel and more...

Please login to join discussion
Canadian Capitalist

© 2022 Canadian Capitalist

Navigate Site

  • Home
  • Disclaimer
  • Privacy Policy
  • Advertisement
  • Contact Us

Follow Us

No Result
View All Result
  • Home
  • Economy
  • Investing
  • Markets
  • Real Estate
  • Retirement
  • Tax Savings
  • Trivia
  • Resources

© 2022 Canadian Capitalist

Welcome Back!

Sign In with Facebook
Sign In with Google
Sign In with Linked In
OR

Login to your account below

Forgotten Password? Sign Up

Create New Account!

Sign Up with Facebook
Sign Up with Google
Sign Up with Linked In
OR

Fill the forms below to register

*By registering into our website, you agree to the Terms & Conditions and Privacy Policy.
All fields are required. Log In

Retrieve your password

Please enter your username or email address to reset your password.

Log In
This website uses cookies. By continuing to use this website you are giving consent to cookies being used. Visit our Privacy and Cookie Policy.
two man and woman standing on doorway
The man who does not read has no advantage over the man who cannot read - Mark Twain