Investment Tax Worksheet and Tutorial - Beta
The Buy Worksheet:
The Buy Worksheet is where you enter information whenever you buy a stock. For this sheet you need to enter (from right to left) the Company Name, the Date of the Stock Purchase, the number of shares bought, the Price/Share and the buy commission amount (the commission you pay to buy the stock; this does not include the commission to sell. You will input that figure on the sell worksheet). Excel then automatically calculates the total cost of your buy including commission, column F, and the price per share incorporating the commission, column G. If you don’t trust Excel to do your calculations or want to learn more about what these formulas are doing behind the scenes, you can check out the screen tips on the worksheet for more information. If you buy a stock multiple times, enter every single different transaction on a different row of the buy worksheet.
As you can see from the picture above, there is one more column that needs to be filled out, column H. This column is the amount of shares you currently own for a given company at a certain buy point. Often times, people buy a stock a number of different times. When this occurs, the IRS allows you to pick the “buy price” for that stock (this is the method of specific identification). So, if I bought 1000 shares of GE at $30 then the initial value for column H (the number of shares currently held at this buy price) would be 1000 as seen below:
If I were to sell 300 shares of GE at this buy price, I would change the number of shares currently held at this buy price to 700 (1000-300). This column becomes particularly useful when you buy a stock multiple times and then sell the stock at different buy prices. Through this column, you can ensure that you still own a stock at a certain buy price before selling. It is extremely important to keep this column up to date and double check your calculations whenever you make a change.
Also, it is imperative that check the number of shares you own at a certain buy price before you use that buy price when you sell. For, example let's say I buy 100 shares of GE at $12 and another 100 shares at $10. If I sell 100 shares at $12 then I would own zero shares with a buy price of $12 ( I would change the value for column H to zero for those those particular shares). If I were to sell any more shares I must specify a buy price of $10 because I no longer own any shares with a buy price of $12.
You can read the screen tip for this column for a little more information. Note that column H is extremely important if you are going to use the method of specific identification. It's not as important if you are going to use the "first in first out" method, but it is in your best interest to keep this column current
The default number of trades for the worksheet is set to 15. This means that if you have more than 15 trades Excel won’t use formulas to calculate the columns “Total Cost with Commission” and the “Price per Share with commission.” Don’t worry; you won’t have to break out the old slide rule to calculate these values by hand. You can easily update the worksheet to match the number of trades you have made.
If you want to add trades, you simply highlight the last two cells where dollar signs appear and move your cursor over the bottom right side of the right cell where the cursor will form a “t.” Then you will simply click and hold the left mouse button and drag until you have added the necessary number of columns:
To delete certain rows (if you have less than 15 trades) just highlight the rows you want to terminate and press the delete button, (not the backspace button).