To calculate this, lets use the same example of UWTI shares and delete the rows of the S shares. Note: this is the method for if you bought more shares than you sold – if you bought shares at different prices, then sell them later, you’ll need to calculate your Average Cost to use in your calculation. Now you have your profit or loss for this trade. Second: add this number to the “Total Amount” from when you sold your shares. Different Buy/Sell Calculationsįirst: multiply your purchase price times the number of shares you sold: This means that I need to first calculate the total cost of the shares I sold, then I can use that to determine my profit. This does not work for UWTI, because I sold a different number of shares than I bought. This means if we just add the “Total Amount”, it will tell us the exact profit or loss we made on the trade. This one is easy because the shares I sold equal the shares I bought. Now we’re ready to calculate! Lets start with the trade for S. Use the “Sort” tool to sort first by “Ticker”, next by “Date” (oldest to newest).įor DWTI and SPY, we haven’t ever “closed” our positions (selling a stock you bought, or covering a stock you short), so we cannot calculate a profit or loss. Simple Calculationsįirst, we want to change how the data is sorted so we can group all the trades of the same symbol together. Tip: If you have not bought and then sold a stock, you can’t calculate how much profit you’ve made on the trade. To do this, open the spreadsheet with your transaction history. The most important reason you would want to use excel to track your stock portfolio is trying to calculate your profit and loss from each trade.