Excel Formula for Calculation of Actual Investement Amount when having multiple Buy and Sell Transactions

[kmkmahesh]

Hello everyone, I have tried to create excel for calculating Actual Investment fifo method. But the results are not coming as expected. Need your help to suggest excel formula.

I tried so many formulaes, nothing provided me the correct result. So i removed everything what i have done.

In the attached file I added the Values as below

Cell Reference	Cell Value
N9	Amount Invested Value caluclated by totalling the values of buy and sell value, which is not showing correct value of the Amount Invested due to getting Profit or Loss in Sell value
O9	Units Present after 10 Transactions
N11	Actual Amount invested which is calculated below from H13 to M15 Cells for 10 Transactions
O11	Units Present after 10 Transactions
N17	Actual Amount invested which is calculated below from H19 to M22 Cells for 9 Transactions
O17	Units Present after 9 Transactions

On row 25 I just kept the 10th Transaction for using when ever it is required to copy after deleting the 10th transaction on row 18.

Can anyone help me with the forumula to get the actual investment amount even i have done mulitple buy and sell transactions in between. so i can get the exact numbers for other calculations.

 

[vletm]

kmkmahesh​

I'm getting different result than You.
I offer something else than formulas for You.
You could add there as many rows data as needed ... and press [ Do It ]
You can see and learn my result right side of that sheet.
(( If SLOWER cell is green then You'll see ... how those result will come. Select that cell to get it smoother. ))
This is a sample file and macros have to be enabled.

 

[kmkmahesh]

Thanks for this, i checked and stopped using it for now, as I am using Chandoos initial sheet as the base excel sheet for my mutual fund tracker created before 2 years and modified that excel file only using formulas, not added any other macros till now, so you can help me on that we can make some more better excel sheet which can help everyone

 

[vletm]

kmkmahesh​

What is missing from my sample?
Did You find differences with Your give results?
Maybe sometimes it would be good to try to use something different too.
I tried to offer something, which can help everyone.
If You would like to use only formulas then Okay, please continue using formulas.