Multiple Equity Stocks Trading FIFO Gain Excel Calculator
Originally published: 11/12/2019 13:24
Last version published: 22/05/2020 19:17
Publication number: ELQ-62427-2
View all versions & Certificate
certified

Multiple Equity Stocks Trading FIFO Gain Excel Calculator

Easy-to-use Excel calculator for calculation of profits in equity stocks trading using FIFO method for multiple stocks.

Description
Easy-to-use Excel calculator for calculation of profits in equity stocks trading using FIFO method.

How to Use:
1. Prepare raw data for your trades in 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 stock-wise summary of all trades will be displayed in 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 Trade sheet. If you trade in non-USD currency please put USD Conversion Rate for that transaction in column H. For USD transaction 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, 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.

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.

Please let me know if you have any question or suggestion to improve this template.

This Best Practice includes
1 Excel Sheet

Acquire business license for $19.00

Add to cart

Add to bookmarks

Discuss

Further information

Calculation of trading profits for multiple equity stocks using FIFO method

Reviews


More Best Practices from Soni Excel Solutions

keyboard_arrow_leftkeyboard_arrow_right

Discussion feed for Multiple Equity Stocks Trading FIFO Gain Excel Calculator

The user community and author are here to help. Go ahead!

  • Soni Excel Solutions
    Thanks for your words of appreciation. If you trade using single base currency and have a fixed percentage of trading fee then you can set all the cells in column G as the base currency e.g. USD and then change the column H to show Conversion Rate= (1-percetnage commission). For example if commission is 0.5% then you can set value in column H as 1- 0.005 or 0.995.

    Please let me know if you have any questions or problems.
    arrow_drop_uparrow_drop_downReply reply
    • Partha Mishra
      Brilliant! It works for the time being. Unfortunately I need the multiple currencies soon enough, and since I am also dealing with multiple brokers, they charge different rates. I think I can manage by adding an additional column, say beside column H, and then tweak column I (price in USD) to factor that in. Will it break the other formulae?
      arrow_drop_uparrow_drop_downReply reply
  • Partha Mishra
    Hi there! Great work with the tool. I find it very useful and informative. Is there an easy way to factor in trade commissions/fees in the workbook? Thanks Partha
    -1arrow_drop_uparrow_drop_downReply reply
    • Dovydas Tarulis
      Hi,

      I having trouble to isolate the data in "summary" by Date.
      My raw data is from 2019 till now. I select to only show 2020 in the "trade" sheet. But the summary still shows the total gains as well as taxes from the 2019 till the end. No mater if I select in the "Trade" sheet only the year I need the end result is the same. What am I doing wrong?
      I also tied to copy raw data form 2020 only, but then it misses some buys from 2019 which I sold in 2020.
      can you help me with that?
      arrow_drop_uparrow_drop_downReply reply
      • Soni Excel Solutions
        HI,

        Summary sheet will show summarized data from all the transactions shown in Trade tab. So you need to put only those transactions for which you want to find P/L. If you don't want to use transactions for 2019, then please don't put them in Trade tab.
        1arrow_drop_uparrow_drop_downReply reply
        • Dovydas Tarulis
          Thanks for quick reply. However as i said before I tried that. The problem is that If I copy only 2020 data the summary misses some Buy data which I made in 2019 and sold in 2020.
          arrow_drop_uparrow_drop_downReply reply
          • Soni Excel Solutions
            This is a simple FIFO calculator. Please use only those transactions for which you want calculation. You can have file financial year wise.
            arrow_drop_uparrow_drop_downReply reply
      • Torben Riis
        Hello, since I don't use Excel, have you tested if your sheet can be loaded into Numbers or Google Sheets without losing functionality? Thanks, Torben
        arrow_drop_uparrow_drop_downReply reply
        • SJV
          Hello, can an update or fix be made to handle short selling? My first transaction is a short, and the error says Sold holdings cannot be greater than quantity on hand.
          arrow_drop_uparrow_drop_downReply reply
          • Soni Excel Solutions
            In FIFO method, it cannot handle short sell. You can put Buy transaction before the Sell transaction for that short trade.
            arrow_drop_uparrow_drop_downReply reply
          • David Itzkowic
            does it matter if i delete the sample data in trade sheet ?
            arrow_drop_uparrow_drop_downReply reply
            • Soni Excel Solutions
              Dear Sir,

              Thanks for your purchase.

              Sample data is just to show the layout, it has no link or effect to your actual calculations, so you may delete it.

              Regards
              Soni
              arrow_drop_uparrow_drop_downReply reply


            4.3 / 5 (4 votes)

            please wait...