Multiple Equity Stocks Trading FIFO Gain Google Sheet Calculator
Originally published: 03/08/2020 20:09
Publication number: ELQ-10170-1
View all versions & Certificate
certified

Multiple Equity Stocks Trading FIFO Gain Google Sheet Calculator

Very handy Google Sheet Calculator to calculate trading profits for multiple stocks in single sheet using FIFO method.

Description
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:AF 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 PDF file with with Google Sheet URL

Acquire business license for $22.00

Add to cart

Add to bookmarks

Discuss

Further information

To calculate trading profit using FIFO method for various stocks together in a single sheet

To calculate stocks valuation using FIFO method and calculate profit or loss in trading

Reviews

  • Rate this Downloadable Best Practice

    Write a review

  • Marko Alexander(last updated: 29/10/2020 07:29)

People using this Best Practice also downloaded


keyboard_arrow_leftkeyboard_arrow_right

More Best Practices from Soni Excel Solutions


keyboard_arrow_leftkeyboard_arrow_right

Discussion feed for Multiple Equity Stocks Trading FIFO Gain Google Sheet Calculator

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

  • laxman rao
    hi there, i recently purchased multiple equity stock trading fifo gain, i followed the instructions but there are errors in my stock sheet and couldnt figure out why. could you explain me, what this part one cost is and what part 2 cost is for as i am getting my Fifo wrong on my spreadsheet.
    the part one cost, part 2 cost and temp what does it calculate? and also my stock in hand column does not calculate. it shows for example, for multiple buys of the same stocks in different rows example 15, 15, 45,25 and for sell it shows -50, -50 although if i add the buys is 100 and my sell is 100 it should show 0 as in your sheet but it doesn. help me out here.
    arrow_drop_uparrow_drop_downReply reply
    • Soni Excel Solutions
      Hi,

      Thanks for your purchase. This sheet has been tested thoroughly and being used by many users without any problems. It calculates 100% correctly, so be assured of correct results if you have not altered any formulas in it.

      Part1 cost and Part2 cost are used for intermediate calculations, you are not supposed to understand or worry about it. You just have to put your transactions details in specified columns and it shows the results correctly.

      You can try with a fresh sheet without changing anything in it. However if you are unable to figure out the mistakes in populating your data, please share your sheet with me. I will try to identify the issue.

      Regards
      Soni
      arrow_drop_uparrow_drop_downReply reply
    • 偉恩 藍982
      Hello?!
      arrow_drop_uparrow_drop_downReply reply
      • 偉恩 藍982
        waynelan828@gmail.com
        need the admission
        arrow_drop_uparrow_drop_downReply reply
        • Soni Excel Solutions
          I have shared the sheet with you in the morning Please check your mail box. Thanks!
          arrow_drop_uparrow_drop_downReply reply
        • shanson16
          How to account for a stock split?
          arrow_drop_uparrow_drop_downReply reply
          • Soni Excel Solutions
            Hi,

            In case of stock split I can suggest to add 2 extra virtual transactions: first to Sell all existing shares at current price and then next is to Buy total shares available after stock split at reduced/new stock price.

            e.g. Initially you bought 10 shares @ $100. After some time its price reaches to $200. So before split, total value of stock is $2000. Then company splits it into 1:2 and price is dropped to $105 after split. So you will now have 20 shares @ $105 and total value of stock is $2100.

            In this case you transactions would be as follows:

            Actual transaction (initial investment)
            1. Buy 10 shares @ $100

            Virtual transactions (just to account stock split)
            1. Sell 10 shares @ $200
            2.. Buy 20 shares @ $105

            I hope it makes sense.

            Regards
            Soni
            arrow_drop_uparrow_drop_downReply reply


          5.0 / 5 (1 votes)

          please wait...