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

  • Rate this Downloadable Tool

    Write a review

  • Faisal Foulad,ACA(last updated: 21/05/2020 00:59)

More Best Practices from Tejasvita Soni

keyboard_arrow_leftkeyboard_arrow_right

Any questions on Multiple Equity Stocks Trading FIFO Gain Excel Calculator?

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


5.0 / 5 (1 votes)

please wait...