Multiple Equity Stocks Trading FIFO Gain Google Sheet Calculator
  • Multiple Equity Stocks Trading FIFO Gain Google Sheet Calculator
  • Multiple Equity Stocks Trading FIFO Gain Google Sheet Calculator
  • Multiple Equity Stocks Trading FIFO Gain Google Sheet Calculator
  • Multiple Equity Stocks Trading FIFO Gain Google Sheet Calculator
  • Multiple Equity Stocks Trading FIFO Gain Google Sheet Calculator
  • Multiple Equity Stocks Trading FIFO Gain Google Sheet Calculator
  • 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

Any questions on Multiple Equity Stocks Trading FIFO Gain Google Sheet Calculator?

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


5.0 / 5 (1 votes)

please wait...