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.

 

[kmkmahesh]

Can you please help adding the Summary of each line in the vba, for understanding aa = 23 it took me a day, as the I4 cell contains the times we just need to start from the first row of funded to that last row adding the I4 value to firstrow.
Excel sheet can have many rows also, as one of my sheet has more than 300 rows, which i daily funded Rs.20/- as SIP, so can you please help me with that vba which i literally not able to understand it.
and i dont want to add any click event to generate the output, the event must be triggered when the Value in Cell "i4" is changed.


I am asking this because i am not familiar with vba, i usually copy the syntax after searching from google and do changes in required things. If i get what line doing what, i can make my sheet better

 

[vletm]

kmkmahesh​

Over three weeks later ... hmm?
Did You answer any of my questions?

Some comments about Your writings:
You can hide columns from H to O ... I have not used those at all!
Did You explain/write something in Your original writing about ... cell I4 ... where?
My sample use C & D-column values from row 9 and You could see results (now) from Q-column and after that.
You can add there as many rows as it needs.
So far, where do You need to know something about - how do it works?
You should know - do it give correct results?
( Expected results could be different than correct results. )

 

[kmkmahesh]

Late because, my laptop is not working due to HDD failure, i am not going to open my financial data sheet on my office devices. Just two days back i installed new SSD in my laptop, so it is three weeks.

For you questions, it is giving the exact number what i need in the Q7,R7, S7 is bonus from you which is the Realised Gain/Loss which is very helpful.

As the other T7, U7, V7 and W7 values are also much useful for further fund investement details.

The sheet i shared above is modified for that specific requirement, my actual sheet looks like below attached. I want to modify that sheet and i want to add more useful things that i requested here. And i want to make all the entries in a single sheet and all the Investment summary of each fund should be in another table, but i am not good with vba so i keep using seperate sheets for each fund

 

[vletm]

Over three weeks ...
... You could only wait to write something here.
... like Your 2nd and 3rd sentences.
Your original file
... why did You offer something, which You don't want or need?
If I remember ... I wondered, why it's as it is?
My opinion is that from Your above layout - You're missing those my given outputs.
It should be possible to have 'all' funds in same way in same table as I've done.
... and show something in other table/sheet.
But, without Your given needed details - I should guess over 90%.
> If You're interesting to get something,which You need?
... You should able to give useful sample data as well as an idea of outputs.

 

[kmkmahesh]

as i said in previous post, i never open personal things in my office devices, and i keep all my passwords in a Keepass database, which normally syncs to my dropbox, so i dont have password with me to login.

I am just trying to optimizing the https://chandoo.org/wp/mutual-fund-tracker-excel/ so the new file can help much better with full Mutual Fund portfolio

Yes, the screenshot i shared above is the excel file i am using from 2 years which contains seperate sheet for each fund. Yes my actual plan to have all the mutual fund transaction on single sheet and showing the summary on another sheet.

I need to modify and remove the data and only keep the few data for the sample, so working on it will be easy.

On monday i will provide the sheet.

 

[vletm]

kmkmahesh​

If You can do something only few days a week - then please name those days.
Are You sure that link covers fifo method too?
Did You answer - why You sent something?
Term easy ... means double work.