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

**sabha** · 06-24-2021, 01:25 PM

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.

I have shown it to my friends and some excel champs in my office but they could not do it. Please please please help me with this.

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

**JohnTopley** · 06-24-2021, 04:24 PM

Investments: SUM(N17:N22)

Overall Gain: H2-H4

Overall Gain% : H8/H6

Today's Gain: ?? (don't know)

**sabha** · 06-25-2021, 12:57 AM

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.

**JohnTopley** · 06-25-2021, 03:16 AM

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

Column V ??? Not sure.

**sabha** · 06-25-2021, 03:37 AM

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.

**JohnTopley** · 06-25-2021, 04:57 AM

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.

**sabha** · 06-25-2021, 08:34 AM

Thanks for your kind assistance.

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

**sabha** · 06-25-2021, 09:01 AM

Please ignore the above post.
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

**JohnTopley** · 06-25-2021, 09:12 AM

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!

**sabha** · 06-25-2021, 09:19 AM

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.

**sabha** · 06-25-2021, 11:54 AM

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

**JohnTopley** · 06-25-2021, 11:58 AM

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

**sabha** · 07-03-2021, 02:08 PM

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

**JohnTopley** · 07-03-2021, 03:06 PM

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

**sabha** · 07-03-2021, 03:16 PM

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

**sabha** · 07-04-2021, 06:06 AM

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

**AliGW** · 07-04-2021, 06:11 AM

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

**sabha** · 07-04-2021, 08:10 AM

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

**JeteMc** · 07-08-2021, 11:55 AM

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.

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

LinkBack

Thread Tools

Rate This Thread

Display

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Thread Information

Users Browsing this Thread

Similar Threads

Only calculate the formulas in a single column of a sheet

Copy a column worth of formulas and text

Formula for gain and loss within a column with a single cell running total

How to calculate 1 hour is worth 1.175

[SOLVED] Excel formula to calculate % gain when adding cash equity to accou

Truncating a columns worth of cells not just a single cell.

Spreadsheet to do annual investment gain$, % gain, for list

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions