How to Calculate Profit Loss of security transactions in MS Excel

Many of us trade in securities or stocks and shares and need a quick and easy method to calculate our profit or loss so that we can plan our taxes. Calculating the gross receipts or payments, total brokerage, service tax, security transaction tax and the final profit or loss is a breeze in MS Excel using simple formulas. We can then calculate the profit or loss percentage.
The gross payments are calculated using the quantity of share purchased (C2) and the purchase price (D2). The brokerage depends on your broker and the purchase or sale price is multiplied by the brokerage percentage. In our case the brokerage is ‘=E2*0.25%’. The service tax and the security transaction tax or any such tax is prescribed by the respective governments and are calculated as shown. Finally the calculated net purchase payments are subtracted from the calculated net receipts to arrive at a net profit. Actually depending on long you have held the stock before sale determines whether you you pay capital gains tax (0% in India) or 10% as short term capital gains. Only after deducting such a tax you finally get the profit. In case of a loss you can offset it from other sources of income on which you might have to pay a tax.
If you enter the date of purchase and sale then you can find out how much you gained in case of a profit situation on an annual basis and compare it to the interest gain in a fixed deposit of a bank. Such calculations help compare Return On Investment (ROI) or Internal Rate of Return (IRR).

Watch the video below to learn how to simple formulas and functions help to perform calculations in security transactions:

Leave a Reply

Your email address will not be published. Required fields are marked *