If you are an investor who trades stocks, you may want to keep track of your gains and losses for each transaction. This can help you evaluate your performance, optimize your portfolio, and report your income for tax purposes. In this article, we will show you how to use Excel formulas to calculate your stock gains and losses, based on the purchase price, sale price, quantity, and commissions.
The basic formula for calculating the gain or loss of a stock transaction is:
Gain or Loss = (Sale Price - Purchase Price) * Quantity - Commission
This formula assumes that you have already paid the commission when you bought the stock, and you will pay the same commission when you sell the stock. If the commission is different for buying and selling, you can modify the formula accordingly.
The gain or loss can be expressed in absolute terms (dollars) or in relative terms (percentage). To calculate the percentage gain or loss, you can divide the absolute gain or loss by the total cost of the transaction, which is:
Total Cost = Purchase Price * Quantity + Commission
The percentage gain or loss formula is:
Percentage Gain or Loss = (Gain or Loss / Total Cost) * 100%
Procedures
To apply the formulas in Excel, you need to have the following data for each stock transaction:
- Purchase Price: The price per share that you paid when you bought the stock.
- Sale Price: The price per share that you received when you sold the stock.
- Quantity: The number of shares that you bought or sold.
- Commission: The fee that you paid to the broker for executing the transaction.
You can enter these data in separate columns in an Excel worksheet, and label them accordingly. For example, you can use column A for Purchase Price, column B for Sale Price, column C for Quantity, and column D for Commission.
Then, you can use the formulas in the previous section to calculate the gain or loss and the percentage gain or loss for each transaction. You can enter these formulas in column E and column F, respectively. For example, the formula in cell E2 would be:
= (B2 - A2) * C2 - D2
And the formula in cell F2 would be:
= (E2 / (A2 * C2 + D2)) * 100%
You can then copy these formulas down to the rest of the rows, and format the cells as currency or percentage as needed.
Example
To illustrate the process, let’s use a hypothetical scenario where you bought and sold three different stocks in 2023. The following table shows the data for each transaction:
| Purchase Price | Sale Price | Quantity | Commission |
|---|---|---|---|
| $50.00 | $55.00 | 100 | $10.00 |
| $40.00 | $35.00 | 200 | $15.00 |
| $30.00 | $40.00 | 300 | $20.00 |
Using the formulas above, we can calculate the gain or loss and the percentage gain or loss for each transaction. The following table shows the results:
| Purchase Price | Sale Price | Quantity | Commission | Gain or Loss | Percentage Gain or Loss |
|---|---|---|---|---|---|
| $50.00 | $55.00 | 100 | $10.00 | $390.00 | 7.80% |
| $40.00 | $35.00 | 200 | $15.00 | -$1,015.00 | -12.69% |
| $30.00 | $40.00 | 300 | $20.00 | $2,980.00 | 33.11% |
The total gain or loss for all transactions is $2,355.00, and the average percentage gain or loss is 9.41%.
Other Approaches
There are other ways to calculate stock gains and losses in Excel, depending on your preferences and needs. For example, you can use the XIRR function to calculate the annualized internal rate of return for a series of cash flows, which can measure the profitability of your investments over time. You can also use the PMT function to calculate the monthly payment for a loan, which can help you plan your budget and savings goals. You can find more information and examples of these functions on the Microsoft Excel Help website.
