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.
- Capital gains are short-term or long-term, depending on how long you owned the assets before selling them.
- Long-term tax rates are lower in most cases, set at 0%, 15%, or 20% as of 2022. Short-term gains are taxed according to your tax bracket for your ordinary income.
- You can offset capital gains with capital losses, which can provide another nice tax break, although certain rules apply.
- Preparing and using a worksheet to calculate your gains and losses can help you identify them at tax time and use them to your best advantage.
Worksheet 1: Simple Capital Gains Worksheet
|Capital gains worksheet for XYZ stock|
|Number of Shares||Date Bought||Buy Price||Commission||Cost Basis||Number of Shares||Date Sold||Sell Price||Commission||Gain/Loss|
Here we have a single transaction where 100 shares of XYZ stock were purchased. A second transaction then sold 100 shares of XYZ stock. There are no other investment purchases or sales. It's simple to match the sale with the purchase. We must organize the data. This 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|
|Number of Shares||Date Bought||Buy Price||Commission||Cost Basis||Number of Shares||Date Sold||Sell Price||Commission||Commission|
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? Was it 100 of the February shares and 50 of the January shares, or did they sell 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. These are 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."
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. 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.
Subtract the $612.50 and the $1,250 of basis from all 100 shares we bought in February. The resulting gain is $225.
It's worksheet is just a matter of some simple spreadsheets and basic math.
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 (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, then we 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 for only 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
Frequently Asked Questions (FAQs)
How much is the capital gains tax?
There are two types of capital gains taxes: long and short. Short-term gains from investments held for one year or less are taxed at your income tax rate. Long-term gains from investments held for more than a year receive a more favorable tax rate of either 0% (individuals earning up to $40,400), 15% (individuals earning up to $445,850), or 20% (individuals earning more than $445,850) as of tax year 2021. These capital gains bracket thresholds increase to $80,800 and $501,600 for married couples filing jointly.
There are some investments, such as collectibles, that are taxed at different capital gains rates. But most exchange-traded investments will be taxed at either 0%, 15%, or 20% if you meet long-term holding requirements.
How can you offset capital gains?
You'll add together all the numbers in the gain/loss column of your worksheet when it comes time to calculate your capital gains tax liability. This allows you to offset your gains with your losses. It reduces your total taxable amount. You can offset all of your gains as well as up to $3,000 of your earned income if your losses are greater than your gains. Any losses beyond that can be rolled forward to offset gains in future years.