# Need a single formulas to calculate Net Worth, Overall Gain and couple of more parameters

1. ## Need a single formulas to calculate Net Worth, Overall Gain and couple of more parameters

Hello!

I have bought and sold shares of a company multiple times whenever there was a dip or rise in the price. I have made total 10 transactions and the online website shows me my networth, Quantity, Investments, Today's gain and Overall gain. I want to replicate that in excel which I can do it with helper rows/columns but my file is kind of a dashboard with lot of data in it and I do not want to add helper rows/columns. First I was not able to understand how the the above parameters are calculated so to understand the same, I downloaded data from my portfolio on the website (shown below) and put it in excel which is attached with this email. I need assistance in writing a formula in the yelllow cells (preferably without having to add additional helper rows/columns). I am showing a pic of my excel sheet below and also a pic of how it is displayed online. The complete details are in the attachemnt.

Thank you

EDIT:
If you see the online portfolio screenshot, there is one more important data that I missed.
It shows current invested price 110.13
This also needs to be factored somewhere along with the Live Price (current price) 119.70 for proper calculations
m2.png
m3.png
money.png  Register To Reply

2. ## Re: Need a single formulas to calculate Net Worth, Overall Gain and couple of more paramet

Investments: SUM(N17:N22)

Overall Gain: H2-H4

Overall Gain% : H8/H6

Today's Gain: ?? (don't know)  Register To Reply

3. ## Re: Need a single formulas to calculate Net Worth, Overall Gain and couple of more paramet

Thank you for your reply and assistance in finding the Investments and Overall Gain %
Actually I wanted to know how to get the required values in yellow cells through formula if the helper data was not available. If the data in K12:X30 was not available in the file then how can I get the desired numbers in the yellow cells throgh formula is what I am looking for. I think this needs to be done is some sort of an arrary formula or a custom function which I am not able to do.

EDIT
If you see the online portfolio screenshot, there is one more important data that I missed.
It shows current invested price 110.13
This also needs to be factored somewhere along with the Live Price (current price) 119.70 for proper calculations

It is ok if we dont get Today's Gain and Percentage but I need the other data My Networth, Investments, Overall Gain & % and current invested price 110.13 which I missed out and I need this without the helper data availalbe in range K12:X30 so that whenever I buy or sell more shares and enter below the existing transactions, the formula in yellow cells should get automatically updated.  Register To Reply

4. ## Re: Need a single formulas to calculate Net Worth, Overall Gain and couple of more paramet

K:P and R:U can be derived from B:E.

Column V ??? Not sure.  Register To Reply

5. ## Re: Need a single formulas to calculate Net Worth, Overall Gain and couple of more paramet Originally Posted by JohnTopley K:P and R:U can be derived from B:E.
Can you help me with a formula in range H4:H6 and H8:I8 on the basis of B:E only without refering to K:P and R:U? Thanks

EDIT : Also need to know the formula for calculating current invested price 110.13 (Inv. Price) shown in the screenshot which I missed out to write it in the excel sheet. Originally Posted by JohnTopley Column V ??? Not sure.
I am ok if we dont get this value. Even I could not figure out how it was calculated in the online screenshot.  Register To Reply

6. ## Re: Need a single formulas to calculate Net Worth, Overall Gain and couple of more paramet

Example

in G4

=(\$E\$4+SUMIF(\$E\$4:\$E\$13,"<0")+SUMIF(\$E\$5:\$E\$13,">0"))*\$G\$2

See F14:G18 table

You should have been able to work (some of these) out yourself.  Register To Reply

7. ## Re: Need a single formulas to calculate Net Worth, Overall Gain and couple of more paramet

I was also trying to get formula for calculating current invested price 110.13 (Inv. Price) shown in the online screenshot which I shared in the original post. I was able to do it by making a copy of the table to other place.

If you see the below pic, =SUMPRODUCT(C24:C33,E24:E33)/SUM(E24:E33) in cell E34 gives me 102.02 which is incorrect and =SUMPRODUCT(C40:C45,E40:E45)/SUM(E40:E45) in cell E46 gives me 110.13 which is correct
Can you please suggest a formula in F21 without having to make a helper duplicate table?

Thank you

m4.png  Register To Reply

8. ## Re: Need a single formulas to calculate Net Worth, Overall Gain and couple of more paramet

I feel so dumb... the answer is just divide investments by quantity to get the answer
39868/362=110.13
Sorry for the inconvenience...

The only thing which is unresolved is Today's Gain and its percentage. If it is calculated online then there may be some logic to do it in excel too. I am trying to find a logic. If you or someone else can help with the last attachment then I would really appreciate.

Thank you so much  Register To Reply

9. ## Re: Need a single formulas to calculate Net Worth, Overall Gain and couple of more paramet

Logic would suggest a comparison with the previous (active) day but as you know, there is nothing to indicate this: so end of the road for me!  Register To Reply

10. ## Re: Need a single formulas to calculate Net Worth, Overall Gain and couple of more paramet Originally Posted by JohnTopley Logic would suggest a comparison with the previous (active) day
Yes absolutely... even i was playing around with few current price and previous price. I have made a transaction today which I will post it in sometime. Lets see with this new entry if we can find a solution to this problem.  Register To Reply

11. ## Re: Need a single formulas to calculate Net Worth, Overall Gain and couple of more paramet

The mystery is solved. Actually there is a close price of a share when the market closes. The current price of active day needs to be compared with previous day close price which I did not provide and presently I cant find it becuase today's day is over and closed price value is changed. All this while, I was comparing previous day price with the price on which I bought/sold the shares. Nevertheless, all is well that ends well...

One last question about something that is driving me crazy... I had shown negative numbers for all Sell transaction for demonstration purpose and ideally that is the way it should be. So far I was copy-pasting figures from online portfolio and now I realised that when I download the csv file, it shows negative numbers also positive. For instance, -14, -38, -27 and -18 numbers on 3rd, 7th, 10th and 22nd Jun gets downloaded as positive numbers 14, 38, 27 & 18 which disturbs all the set formulas. Editing all those positive figures for every sell entry would be manual and tedious data entry work and there are scope of erros which doing it manually. Is there anything we can do in the formula to cater this problem? Pls let me know if this can be resolved easily without wasting much of a time in the last file that you sent me. Thanks  Register To Reply

12. ## Re: Need a single formulas to calculate Net Worth, Overall Gain and couple of more paramet

Simply test if TX is "SELL" and multiply value by -1.  Register To Reply

13. ## Re: Need a single formulas to calculate Net Worth, Overall Gain and couple of more paramet

Hi!

I am stuck again at one formula. I got all the green ones but the yellow one I dont know how to get it.

The logic is:
Total 22 shares bought and 18 shares sold so the difference is 4 shares
I want to work on the first IN first OUT FIFO method. First IN (5 shares bought) out of which 3 shares sold in second transaction, Next IN (17 shares bought + 2 remaining from first lot = 19 shares) out of which 15 shares sold so remaining is 4 shares and for this, the rate was 3 hence total invested amount should be 4 x 3 = 12 which is reflecting in cell E3

How can i calculate this using a formula in E3?

Thanks

port.png  Register To Reply

14. ## Re: Need a single formulas to calculate Net Worth, Overall Gain and couple of more paramet

Surely if the 3 refers to Average Price (K3) then it is simply (Buy - Sell). Why FIFO unless you want to calculate K3.  Register To Reply

15. ## Re: Need a single formulas to calculate Net Worth, Overall Gain and couple of more paramet

Sorry.. you are right. I want to calculate K3 (by mistake I filled the average price cell)
What would be the correct formula to calcualte K3 using FIFO?
Once we get K3, I can simply multiply that with quantity to get the Invested price of 12  Register To Reply

16. ## Re: Need a single formulas to calculate Net Worth, Overall Gain and couple of more paramet

I was looking at this link https://help.upstox.com/support/solu...ery-positions- to see if any logic can be worked out but I failed. Kindly help me one more time with a formula to calculate K3 with the average invested price using FIFO method. Thanks  Register To Reply

17. ## Re: Need a single formulas to calculate Net Worth, Overall Gain and couple of more paramet

You are not likely to get help having marked this thread as solved. Remove the solved tag if you still require assistance.  Register To Reply

18. ## Re: Need a single formulas to calculate Net Worth, Overall Gain and couple of more paramet

Thanks AliGW.. have changed status of the quesion Originally Posted by sabha I was looking at this link https://help.upstox.com/support/solu...ery-positions- to see if any logic can be worked out but I failed. Kindly help me one more time with a formula to calculate K3 with the average invested price using FIFO method. Thanks
Eagerly awaiting an answer to my query. Just need to know a formule to be used to to calcualte K3 using FIFO as per solution given in the above article? Thanks  Register To Reply

19. ## Re: Need a single formulas to calculate Net Worth, Overall Gain and couple of more paramet

This proposal employs a helper column which has been hidden by changing the font to white.
The helper column (K8:K20) is populated using: =IF(F8="Buy",MAX(0,MIN(G8,H\$2)-SUM(K9:K\$20)),0)
Cell K3 is populated using: =SUMPRODUCT(D8:D20,K8:K20)/SUM(K8:K20)
Let us know if you have any questions.  Register To Reply

There are currently 1 users browsing this thread. (0 members and 1 guests) 