Investment Tax Worksheet and Tutorial - Beta
The Sell Worksheet is the heart of the file. You are going to use this sell worksheet to determine your long term gains and losses and calculate your capital gains tax. For this sheet you need to enter the Company Symbol and the Buy Date and the Buy Price Including Commission from Buy Worksheet (which can be found in columns B and F, respectively). Next you will need to enter the Date of the Sale, the Number of Shares sold, the Sell Commission Amount, and the Price per Share not including the commission.
Excel will then calculate the Gross Sale Amount, Price per Share at the time of the sell including the commission, the Net Sale Amount, and the Short Term and Long Term Gains and losses. Note that the Net Sale Amount is the total revenue from the sale of the stock minus the total cost including all commissions. This Net Sale Amount is what gets reported to the IRS on the Form 1099B as net proceeds. Additionally, this worksheet is set up to automatically place the proceeds into proper long term and short term categories to save you as much effort as possible.
Excel will represent negative numbers by using parentheses. This is standard practice in the accounting world
Similar to the Buy Worksheet, the Sell Worksheet has a default setting of 15 trades. You can adjust the number of trades in the sell worksheet the same way you did in the Buy Worksheet (see above).
Once you have your worksheet completed you can use Excel’s auto sum feature to add up your short term and long term gains and losses. The final step is applying your tax rates to the short term and long term gains respectively. Remember, that your short term gains are taxed at normal income and long term gains are taxed at either 5 or 15 percent depending income level. If you are subject to Alternative Minimum Tax, do forget to incorporate it in your calculations.
Sometimes when you enter information into the worksheet to have Excel execute a formula, a weird error arises. When this happens just double check to make sure all information has been entered properly. If you enter information in the wrong cell or an invalid date (such as 2/31/2007) Excel won’t understand and will notify you with an error.
We suggest that you wait until the end of the year to calculate your actual taxes. This way you will have all necessary data and you will know what tax bracket you are in. However, we STRONGLY RECOMMEND that you update the worksheet whenever you make a trade or receive a dividend to keep it as current as possible. This will save a big headache when tax season rolls around.
Your total tax paid to the IRS will be the tax paid for dividends plus/minus the tax paid for short term gains plus/minus the tax paid for long term gains.
You can print the Buy and Dividend Worksheets by using the normal letter size paper and just changing the setup to "Landscape." The file should already be set up to print in the Landscape format. If it is not just go to Fileà Page Setup and click the radio button for "Landscape." You will need to print the Sell Worksheet on legal paper (since there are so many columns) in the Landscape format. Again, the sheet should already be set up for this format. If it is not go to Fileà Page Setup and select the Landscape radio button and then on the "Paper Size" drop down menu make the change to legal.
Thanks for using StockBoxFinancial’s Investment Tax Worksheet. If you have any questions, ideas, or comments please email us at support@stockboxfinancial.com.
Chandler Lutz does not own shares in any of the companies mentioned. The data provided in relation to these companies in not accurate and is for example purposes only.