Easy-to-use Google Sheet calculator for calculation of profits in equity stocks trading using FIFO method.
How to Use:
1. Prepare raw data for your trades in a similar format/layout as shown in Raw Data Tab.
2. Copy/Paste Special (Values) the raw data in columns A:H in Trade Sheet.
3. Formulas in columns I:AB in Trade sheet will calculate and show important data after every transaction and a stock-wise summary of all trades will be displayed in the Summary sheet.
You will just have to put your trading details i.e. Transaction Type (buy/sale), Date & Time of Transaction, Stock Ticker, Quantity, Price etc. In columns A:H in the Trade sheet. If you trade in non-USD currency please put USD Conversion Rate for that transaction in column H. For USD transactions the value in column H could be 1 or leave the cell empty.
Excel formulas will automatically calculate FIFO gains and other important data after every transaction in separate rows. You will see Gain/Loss after every sell transaction on FIFO basis in column AA. Besides this, the sheet 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.
Summary sheet displays stock-wise summary of all trades. It shows Number of Stocks Purchased/Sold/In Hand, Cost of Stocks Purchased/ Sold/In Hand, Proceeds from Sale of Stock, FIFO Gain, Applicable Tax (@33%), and Percentage Return for every stock. It also shows Total of all trades in 1st row.
Chart sheet displays 4 different charts for graphical representations of important statistics:
1. Top Gainers: To display top 10 stocks with the best returns in descending order.
2. Top Losers: To display top 10 stocks with the worst returns in ascending order.
3. Ratio of Balance Stock: To display cost of balance stock (in percentage) for the top 10 most weighted stocks in user’s portfolio.
4. Cost of Balance Stock: To display cost of balance stock for the top 10 most weighted stocks in user’s portfolio.
Other things to note:
1. Columns T:X contain formulas for calculating intermediate results. You may show or hide them as per your choice.
2. Column AC shows Error message if user inputs (by mistake) a higher value of Sell quantity for any stock than its Buy quantity.
3. Sheet highlights Sell transactions in Orange and Buy Transaction in Green. Similarly Summary highlights Stocks with Loss in Orange and Stocks with Profit in Green.
------------
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