EXCEL CLOSED POSITIONS WORKSHEET

 

 

One row is filled in for each trade, or more if the original acquisition involved multiple purchases. This is to preserve all details of the position component that was closed. For purchases, in addition to the purchase data from the open positions section, the calculated initial amount and amount plus commission are entered. Entries are in date sequence as positions are closed. All details of the sale are entered as well, such as sale price, date sold, sales commission, gross amount, net amount realized, and gain or loss. Show a loss as a negative number. Next, the dividend columns are completed, with dividends allocated between the open and closed sections for partial sales. Finally, the total return is computed as total dividends plus gain/loss.  

An option that was originally sold is entered in the sale data section, and the buy back values are shown in the buy section. Of course, if it expired, the buy back price and commission are zero. While I seldom short stocks, opening short positions and closing by buying back can be easily recorded by entering the short position initiation data in the sales section, and the closing data in the purchase section.  

The only column totals are the current month dividends, and the current month gain/loss total. This starting row to be included in the gain/loss total must be adjusted each month, to be the first line for any position closed this month.