If you purchase and sell investments such as stocks, exchange-traded funds (ETFs), or other assets, you may be required to pay capital gains taxes. You can use a worksheet you create in Excel, Google Sheets, or another program to calculate your capital gains or losses. This also helps you organize your investment data when it comes time to file your tax return.
Worksheet 1: Simple Capital Gains Worksheet
Capital Gains Worksheet for Stock XYZ
Number of Shares | Purchase Date | Purchase Price | Purchase Commission | Cost Basis | Number of Shares | Sale Date | Sale Price | Sale Commission | Profit/Loss
100 | 01/03/21 | $1,200 | $25 | $1,225 | 100 | 03/10/22 | $1,400 | $25 | $150
Let’s say you purchased 100 shares of Stock XYZ on January 3, 2021. You bought the shares at a price of $12 per share, for a total cost of $1,200. Your broker surprised you with a commission of $25.
After one year, on March 10, 2022, you decided to sell 100 shares at a price of $14 per share, for a total of $1,400. You did not make any other purchases or sales. You had to pay another commission of $25 for the sale.
As you can see in the table above, you can enter all this information as you have it. The first row should contain a description of what is in the cell below (date, shares, etc.). So when buying shares, the first five columns should be filled with information. Once you sell those shares, the next five cells should be filled.
The last cell “Profit/Loss” can be calculated by subtracting the cost basis from the sale price and then subtracting the final commission:
$1,400 – $1,225 – $25 = $150
You can use a function in the sheet (if numeric) to call this information and calculate the profit/loss automatically. From this cell “Profit/Loss,” we can see that you made a profit of $150 on this investment. There may be capital gains taxes owed depending on the rest of the investments and capital gains or losses and income for the tax year.
Worksheet 2: Capital Gains Worksheet for Multiple Purchases and Sales
Capital Gains Worksheet for Stock XYZ
Number of Shares | Purchase Date | Purchase Price | Purchase Commission | Cost Basis | Number of Shares | Sale Date | Sale Price | Sale Commission
100 | 01/03/21 | $1,200 | $25 | $1,225 | 150 | 01/20/22 | $2,100 | $25
100 | 02/03/21 | $1,225 | $25 | $1,250
Here we are organizing data from multiple purchases. Let’s assume you invested in Stock XYZ, purchasing 100 shares on January 3, 2021, for a total cost of $1,200 ($12 per share). You bought another 100 shares on February 3, 2021, for a total cost of $1,225 ($12.25 per share). This means you have a total of 200 shares after all transactions.
In January 2022, you sold 150 shares. This leaves you with 50 shares remaining.
The question is, which shares did you sell? Did you sell all 100 shares you purchased in January plus 50 shares from the February purchase? Were they 100 shares from the February purchase and 50 shares from the January purchase, or did you sell 75 shares from each batch?
The Internal Revenue Service (IRS) states that the basis of the shares consists of the purchase price plus the purchase costs. Costs may include transfer fees and commissions.
So
We have the purchase price plus the commission for both batches of stocks ($1,225 for January and $1,250 for February). The Internal Revenue Service indicates that the basis is the cost of the specific stocks if you can identify which ones you sold. Otherwise, its basis will be the basis of the stocks you acquired first (more on that below). Let’s assume you told your broker, “Sell these specific stocks,” and instructed them to sell all 100 shares you bought in February and 50 shares of the stocks you bought in January.
We want to calculate the basis for the 50 shares from January. We will take the basis cost of $1,225, which includes the commission, and then divide it by the number of shares purchased. This results in a cost per share. Then we will multiply this by 50. This is the number of shares we sold, resulting in a basis of $612.50.
$1,225/100 = $12.25 x 50 = $612.50
Then we subtract $612.50 from the selling price of $2,100:
$2,100 – $612.50 = $1,487.50
Next, we subtract the cost of the February stocks (100 shares) from $1,487.50:
$1,487.50 – $1,250 = $237.50
Your profit is $237.50 before paying the commission ($212.50 after taking into account the commission of $25) if you sold these specific stocks.
FAQs
What is the capital gains tax rate?
The capital gains tax you pay depends on how long you owned the investment. If you held it for less than one year, your capital gains tax rate equals your ordinary income tax rate. If you kept the investment for more than a year before selling, your capital gains tax rate is either 0%, 15%, or 20%, depending on your income. Certain investments, like artwork, are subject to different capital gains tax rates.
How can you offset capital gains?
You can offset capital gains by calculating your losses. Capital losses can offset gains up to a maximum of $3,000 per year. Any losses exceeding that can be carried forward to offset gains in future tax years.
Source: https://www.thebalancemoney.com/capital-gains-worksheet-3192968
Leave a Reply