This is an easy-to-use Excel calculator for calculation of profits in equity stocks trading using FIFO method. It calculates and shows useful trading data for multiple equity stocks in the same sheet.
 
How to input data
Enable Macro while opening the file, if you want to use Carry Over option.
You will just have to put your trading details i.e. Transaction Type, Date/Time of Transaction, Stock Name/Symbol, Quantity, and Amount etc. in columns A:F in Trade sheet. Prepare transaction data for your trades in similar format/layout as shown in Raw Data Tab. Data must be in sorted order by (Stock Name -> Date -> Transaction Type).
Copy the raw data and paste it in columns A:F in Trade Sheet without any formatting using Paste Special (Values) option. Columns G:AO in Trade sheet contain formulas, please do not edit or delete them.
If you want to carry over the balance quantity in hand as next year's opening balance, then click on Carry Over Balance Qty button in cell AP1 in Trade sheet. It will run a macro to carry over the balance quantity in hand to the Carry Over sheet. You can use this balance quantity/data as your opening balance for the next year.
 
Raw Data sheet
Raw data sheet has no formulas. It is just to show the format/layout in which transactions data need to be prepared for processing.
Quantity and Prices for all the transactions should be written as positive numbers.
Market Price is just for your reference. Trade sheet only uses Quantity and Amount (Column D and F) for calculations.
 
Trade sheet
When you input raw data in Trade sheet, formulas will automatically calculate FIFO gains and other important data for every transaction in separate rows. You will see Gain/Loss after every sell transaction on FIFO basis in column AA. Besides this, spreadsheet will also show you other important data e.g. Cumulative Cost of Stock Purchased, Cumulative Proceeds from Sell of Stock, Number of Stocks Purchased/ Sold /In Hand after every transaction. This sheet is ready to process 3000 transactions (which can be easily extended as per your requirement).
In order to help you identify some data entry mistakes, Trade Sheet highlights relevant cell in RED in following cases:
 
If a cell in Column D (Qty) has a negative value.
If a cell in Column F (Amount) has a negative value.
If a sell transaction for any stock has a higher value of Sell quantity (column D) than it's Quantity in hand (column AB for its previous transaction).
If a date value for a buy/sell transaction for any stock is earlier than the date entered in its previous row for the same stock in column B.
 
Summary sheet
It displays stock-wise summary of all the trades for up to 100 different stocks (which can be easily extended as per your requirement). It shows Number of Stocks Purchased/Sold/In Hand, Cost of Stocks Purchased/ Sold/In Hand, Proceeds from Sale of Stock, FIFO Gain, for every stock in separate rows and Total of all trades above the table headings.
 
Carry Over sheet
Carry Over sheet is auto populated by running a macro from Trade sheet. It shows the stock in hand after all Sell transactions in Trade sheet. This data can be used as opening stock quantity for the next year.
It shows Subtotals for each equity stock. If you want to hide these Subtotals, you can do it by going to Data > Subtotal > Remove All option in Excel tool bar.
------------

Alternate buy option using Cryptos
Besides PayPal, you can also buy this calculator for any of the following cryptos. Please send the equivalent amount of crypto to any of the addresses given below and put transaction details as checkout Note. You will get the tool in your mailbox after payment confirmation.
​
LTC : LKFcSetKJY2xufP7UUNZdA8EbPwegKAxg3
BCH: qzkg4ws5s04nq95xrk09zlrk7vqu9g84wgv5jex69u
XRP: rJPceE4NMpmVaSpbiLBQSBcC1ZBJC8p7Au