Capital Gains Calculations Worksheet

Spreadsheet Format for Capital Gains

Let's start simple. We are going to build a worksheet to calculate capital gains. One goal is to see how the math works. A second goal is to show you how to organize your investment data for tax purposes.

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/15

1200

25

1225

 

100

01/10/16

1400

25

 

150

 

In this example, we are organizing two pieces of investment data. We have a single transaction where 100 shares of XYZ stock was purchased; and a second transaction where 100 shares of XYZ stock was sold. In this example, there are no other investment purchases or sales. So it's simple for us to match the sale with the purchase. We organize the data, which comes from records or statements provided by the brokerage firm. And then in the final column, we calculate gain or loss. Here, the positive gain is equal to the selling price minus the buy price minus the buy commission minus the sale commission = 1400 – 1200 – 25 – 25 = 150. The person made a profit (a gain) of $25 on this investment.

Now let's move on to a more complicated scenario. Here, we are organizing data from multiple buy transactions.

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

 

Gain/Loss

100

01/03/15

1200

25

1225

 

150

01/10/16

2100

25

  

100

02/03/15

1225

25

1250

 

 

 

 

 

 

 

 

Notice I have left the gain/loss column blank for now. The gain or loss is exactly what we are trying to figure out. What do we notice here? This person invested in XYZ stock, buying 100 shares in January and another 100 shares in February. The following January, the person sold off 150 shares. So what's the question?

The question is: which shares did this person sell? Did she sell all 100 of the January shares plus 50 of the February shares; or 100 of the February shares and 50 of the January shares; or 75 shares from each lot; or some other combination?

Here's what the IRS says to do:

"The basis of stocks or bonds you own generally is the purchase price plus the costs of purchase, such as commissions and recording or transfer fees...."

So far so good: we have the purchase price plus the costs of purchase, in this case commissions, organized in our worksheet.

The IRS goes on:

"Identifying stock or bonds sold.   If you can adequately identify the shares of stock or the bonds you sold, their basis is the cost or other basis of the particular shares of stock or bonds...."

And later on the IRS says:

"Identification not possible.   If you buy and sell securities at various times in varying quantities and you cannot adequately identify the shares you sell, the basis of the securities you sell is the basis of the securities you acquired first. Except for certain mutual fund shares, discussed later, you cannot use the average price per share to figure gain or loss on the sale of the shares."

Those quotations are taken from the Stocks and Bonds section under Basis of Investment Property in chapter 4 of Publication 550.

Now let's figure this out. If we had told our broker, "sell these specific shares," then those are the shares whose basis we would use to calculate our capital gains. For example, if we told the broker to sell all 100 of the shares we bought in February, and 50 of the shares we bought in January, then our gain would be 2100 – (1225/100*50) – 1250 = $225.

Do you see what I did with the math? I want to calculate the basis of 50 shares from the January purchase. I took the cost basis of (1225, which includes the commission), divided it by the number of shares purchased (this results in a cost per share), and multiplied this by 50 (the number of shares we sold).  That results in a basis of 612.50. I subtract the 612.50 and the $1250 of basis from all 100 shares we bought in February, and the resulting gain is $225.

Ok that's how we build a formula using specific identification. But what if we did not tell our broker to sell specific shares. In that situation, the IRS says, we use the first-in, first-out method: "the basis of the securities you sell is the basis of the securities you acquired first." How does this look in terms of a formula for calculating gain? Well, we notice on the right-hand side of the worksheet we sold 150 shares. Then looking to the left-hand side we see we first bought 100 shares, and then bought another 100 shares. We take the basis of the shares we acquired first: in other words all 100 shares of the January purchase, with a cost basis of $1,225. So now we have identified basis for 100 shares out of the 150 shares we sold. Then we move on to the securities purchase next. We need basis only for 50 shares, but the February purchase was for 100 shares. So we divide the February cost basis. So here's our formula for gain using the first-in, first-out method:

2100 – 1225 – (1250/100*50) = 2100 – 1225 – 625 = $250.

Implications for Building Your Own Worksheet:

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