Capital Gains Tax Calculation Worksheet

Man discussing a stock buyback with his colleagues.
•••

Thomas Barwick / Getty Images

Building a worksheet to calculate capital gains shows how the math works. It also illustrates how you can organize your investment data for tax purposes. Here's what you'll need to get started:

  • 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 gain or loss using the data in the other cells if you use spreadsheet software.

Worksheet 1. Simple Capital Gains Worksheet

Capital gains worksheet for XYZ stock
# shares Date Bought Buy Price Commission Cost Basis # shares Date Sold Sell Price Commission Gain/Loss
100 01/03/20 1,200 25 1,225 100 01/10/21 1,400 25 150

Here we have a single transaction where 100 shares of XYZ stock were purchased, and a second transaction where 100 shares of XYZ stock were sold. There are no other investment purchases or sales, so it's simple to match the sale with the purchase. We must organize the data, which comes from records or statements provided by the brokerage firm. Then, in the final column, we calculate the gain or loss.

The positive gain here is equal to the selling price, minus the buy price, minus the buy commission, minus the sale commission: $1,400 – $1,200 – $25 – $25 = $150. The investor made a profit of $150 on this investment. 

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

Worksheet 2. Capital Gains Worksheet: Multiple Purchases

Capital Gains Worksheet for XYZ Stock
 # Shares  Date
Bought
 Buy
Price
 Commission Cost
Basis
 #
Shares
Date
Sold
Sell
Price 
 Commission
 100  01/03/20  1,200 25   1,225 150   01/20/21  2,100 25 
 100  02/03/20  1,225 25  1,250        

Here, we're organizing data from multiple buy transactions. This individual invested XYZ stock, buying 100 shares in January and another 100 shares in February. They sold off 150 shares the following January.

The question is, which shares did this person sell? Did they sell all 100 of the January shares plus 50 of the February shares, 100 of the February shares and 50 of the January shares, or 75 shares from each lot? The 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 costs of purchase—the commissions organized in our worksheet in this case. The IRS indicates that their 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.

We would use these shares to calculate our capital gains if we had told our broker, "Sell these specific shares." For example, our gain would look like this if we were to tell the broker to sell all 100 shares we bought in February, and 50 of the shares we bought in January: $2,100 – ($1,225/100*50) – $1,250 = $225.

We want to calculate the basis of 50 shares from the January purchase, so 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, the number of shares we sold. This results in a basis of $612.50.

Subtract the $612.50 and the $1,250 of basis from all 100 shares we bought in February, and the resulting gain is $225.

But what if we didn't tell our broker to sell specific shares? The IRS indicates that we should use the first-in, first-out method. Notice on the right side of the worksheet that we sold 150 shares. Then look to the left side. We first bought 100 shares, then bought another 100 shares. 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'd move on to the securities purchase next. We need basis only for 50 shares, but the February purchase was for 100 shares. So we'll divide the February cost basis. Here's our formula for gain using the first-in, first-out method: $2,100 – $1,225 – ($1,250/100*50) = $2,100 – $1,225 – $625 = $250

It's really just a matter of some simple spreadsheets and basic math.