How To Calculate Capital Gains or Losses With a Worksheet

If you bought and sold investments like stocks, exchange-traded funds (ETFs), or other assets, you may owe capital gains taxes. You can use a worksheet that you build in Excel, Google Sheets, or another program, to calculate your capital gains or losses. This also helps you organize your investment data for when it comes time to file your taxes.

When you're ready to build a worksheet to calculate your capital gains or losses, try to do the following:

• Make one worksheet for each stock, bond, or other investment you have
• Keep all the purchases on the left side
• Order the purchases in chronological order from first to last
• Keep all the sales transactions on the right side
• Use formulas to calculate gains or losses using the data in the other cells if you use spreadsheet software

Below, we'll dive into two examples of how to build a worksheet to calculate capital gains or losses on your investments.

Worksheet 1: Simple Capital Gains Worksheet

Let's say you bought 100 shares of Company XYZ stock on Jan. 3, 2021. You bought the 100 shares at $12 per share, for a total cost of $1,200. Your broker charged you a commission of $25.

Over a year later, on March 10, 2022, you decided to sell your 100 shares at $14 per share, for a total of $1,400. You made no other investment purchases or sales. You had to pay your broker another $25 for the sale.

As you can see in the table above, you can input all of this information as you have it. The first row should have a description of what is in the cell below (date, shares, etc.). So when you buy shares, you would fill in the first five columns with information. As soon as you sell those shares, you would fill in the next five cells.

The last cell, "Gain/Loss" can be figured out by subtracting the cost basis from the sell price and then subtracting the final commission cost:

$1,400 – $1,225 – $25 = $150

You can use a function in the worksheet (if it's digital) to automatically pull in this info and calculate the gain/loss. From this Gain/Loss cell, we can see that you made a profit of $150 on this investment. Depending on the rest of the investments, capital gains or losses, and income for the tax year, capital gains taxes may be owed.

Now let's move on to a more complicated scenario.

Worksheet 2: Capital Gains Worksheet for Multiple Purchases and Sales

Here we're organizing data from multiple buy transactions. Let's say you invested in Company XYZ stock, buying 100 shares on Jan. 3, 2021, for a total of $1,200 ($12 per share). You bought another 100 shares on Feb. 3, 2021, for a total of $1,225 ($12.25 per share). This means you owned a total of 200 shares after both transactions.

In January 2022, you sold off 150 shares. This leaves you with 50 shares left.

The question is, Which shares did you sell? Did you sell all 100 of the January shares plus 50 of the February shares? Was it 100 of the February shares and 50 of the January shares, or did you sell 75 shares from each lot?

The Internal Revenue Service (IRS) says that the basis of the shares works out to the purchase price plus the costs of purchase. Costs might include transfer fees and commissions.

So we have the purchase price plus the commission for both lots of shares ($1,225 for January and $1,250 for February). The IRS indicates that the basis is the cost of the particular shares if you can identify those you sold. Otherwise, their basis would be the basis of those shares that you acquired first (more on that below). Let's say you told your broker, "Sell these specific shares," and you said to sell all 100 shares you bought in February and 50 of the shares you bought in January.

We want to calculate the basis of 50 shares from the January purchase. We would take the cost basis of $1,225, which includes the commission, then divide it by the number of shares purchased. This results in a cost per share. We would then multiply this by 50. This is the number of shares we sold, and it results in a basis of $612.50.

$1,225/100 = $12.25 x 50 = $612.50

Then subtract the $612.50 from the sell price of $2,100:

$2,100 - $612.50 = $1,487.50

Then subtract the cost basis for the 100 February shares from $1,487.50:

$1,487.50 – $1,250 = $237.50

Your gain is $237.50 before paying the commission ($212.50 after you account for the $25 commission on the sell) if you sold these specific shares.

But what if you didn't tell your broker to sell specific shares? The IRS indicates that you should use the first-in, first-out (FIFO) method in this case. Notice on the right side of the worksheet that we sold 150 shares. Then look to the left side. We first bought 100 shares in January, then we bought another 100 shares in February. We take the basis of the shares we acquired first, all 100 shares of the January purchase, with a cost basis of $1,225.

So now we've identified the basis for 100 shares out of the 150 shares we sold. We need the basis for only 50 shares bought in February now. So we'll divide the February cost basis.

Find the cost per share and then the total cost for 50 shares:

$1,250/100 = $12.50 x 50 = $625

Then subtract that plus January's shares cost basis from the total sell price:

$2,100 – $1,225 – $625 = $250

Based on the first-in, first-out method, your gain would be $250 before paying the commission of $25, and $225 after.

Frequently Asked Questions (FAQs)