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 $19.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)

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!

  • 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...