• 
• 
• 
• 
• 
• 
• 

Send to a friend

• linkedin
• twitter
• facebook
• code

Originally published: 11/12/2019 13:24
Last version published: 22/05/2020 19:17
Publication number: ELQ-62427-2
View all versions & Certificate

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.

by Soni Excel Solutions
Complex Data Processing made easy with ExcelFollow 106

4 reviews3,365 views|13 comments|Bookmark Add to cart - $22.00

equity stock trading calculatorstock trading calculatorfifo calculatorshare trading calculatorshares trading excel sheetshares trading fifo calculatorfifo equity calculatorfifo excel sheetfifo profit calculator

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 $22.00

Add to cart

Add to bookmarks

Discuss

Further information

Reviews

• Rate this Downloadable Best Practice
  

  Write a review

• Pete123(last updated: 26/04/2021 13:59)
  
• Partha Mishra(last updated: 17/03/2021 12:32)
  
• Rafael Mata Ordóñez(last updated: 20/12/2020 10:10)
  
• Faisal Foulad,ACA(last updated: 21/05/2020 00:59)
  

Post Comment

• 

  Soni Excel Solutions 8 months ago
  Authorstar

  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 8 months ago
    User

    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

    • 

      Soni Excel Solutions 8 months ago
      Authorstar

      You can play around it. If you find any issues, I will be happy to help.

      arrow_drop_uparrow_drop_downReply reply
• 

  Partha Mishra 8 months ago
  User

  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 7 months ago
  User

  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 7 months ago
    Authorstar

    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 7 months ago
      User

      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 7 months ago
        Authorstar

        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 1 year ago
  User

  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 4 months ago
  User

  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 4 months ago
    Authorstar

    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 1 year ago
  User

  does it matter if i delete the sample data in trade sheet ?

  arrow_drop_uparrow_drop_downReply reply

  • 

    Soni Excel Solutions 1 year ago
    Authorstar

    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