• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Calculation of profit from multiple transaction data

khelgadi

khelgadi

Member
Hello Excel Masters!!

I want to calculate profit from buying and selling transactions by using FIFO method and matching the quantity.
the formula for profit is as simple as = (sell rate- buy rate) * qty, but the qty needs to extracted from the list of transactions. I have attached a file which contains data sheet and a sample report ( i have made it manually).

Requesting for your help, thanks in advance.
 

Attachments

  • capital gain.xlsx
    15.1 KB · Views: 10
Using simple sort and filter functions you can generate the table you want to have buying transactions sorted by date and selling transactions sorted by date side by side.
To my knowledge, the only way for you to tie which transaction is for which lot purchased / sold, would be to define a lot number at time of purchase and then link the sale using the same lot number.
Once you have this additional column of data in your raw data, you can surely link up the transactions based on the lot number.
Are you generating this data manually or getting it from some system? I assume the system is not keeping track of the lot number which is where your struggle begins.

See my file which shows you how to at least split this data up into a table with bought and sold transactions sorted by date side by side.
I don't know if this helps you any at all.
 

Attachments

  • capital gain.xlsx
    18.8 KB · Views: 5
Also this means that when you sell your lots, if you sold 83.71, it would have to be broken into two transactions of 50.04 on the 13th of May at 420.22 for lot 1 and 33.67 on the 13th of May at 420.22 for lot 2.
So then you would be able to use formulae or a pivot table to link all transactions for lot 1, lot 2, etc and get your report.

Does anyone else have any bright ideas?
 
Hi @adityakapoor ,
Thanks for the input. I had been struggling and waiting for my consultant to get any other possible solution. Your method sure helps to place the transactions in front of each other. Main issue is to spilt the qty as per transactions.
I am still not sure how everyone is doing this as this is a common need.
Can we do it with the help of MACROs?
 
You must log in or register to reply here.
Back
Top