+ Reply to Thread
Results 1 to 19 of 19

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

  1. #1
    Forum Contributor
    Join Date
    11-29-2014
    Location
    India
    MS-Off Ver
    MS Excel 2007
    Posts
    221

    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.

    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
    Attached Files Attached Files
    Last edited by sabha; 07-04-2021 at 06:43 AM. Reason: added additional info under EDIT

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,819

    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)

  3. #3
    Forum Contributor
    Join Date
    11-29-2014
    Location
    India
    MS-Off Ver
    MS Excel 2007
    Posts
    221

    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.
    Last edited by sabha; 06-25-2021 at 01:29 AM. Reason: added additional info

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,819

    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.

  5. #5
    Forum Contributor
    Join Date
    11-29-2014
    Location
    India
    MS-Off Ver
    MS Excel 2007
    Posts
    221

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

    Quote Originally Posted by JohnTopley View Post
    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.

    Quote Originally Posted by JohnTopley View Post
    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.
    Last edited by sabha; 06-25-2021 at 04:35 AM. Reason: missed out to write about current invested price

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,819

    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.
    Attached Files Attached Files

  7. #7
    Forum Contributor
    Join Date
    11-29-2014
    Location
    India
    MS-Off Ver
    MS Excel 2007
    Posts
    221

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

    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
    Attached Files Attached Files
    Last edited by sabha; 06-25-2021 at 08:40 AM. Reason: missed attachment

  8. #8
    Forum Contributor
    Join Date
    11-29-2014
    Location
    India
    MS-Off Ver
    MS Excel 2007
    Posts
    221

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

    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

  9. #9
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,819

    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!

  10. #10
    Forum Contributor
    Join Date
    11-29-2014
    Location
    India
    MS-Off Ver
    MS Excel 2007
    Posts
    221

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

    Quote Originally Posted by JohnTopley View Post
    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.

  11. #11
    Forum Contributor
    Join Date
    11-29-2014
    Location
    India
    MS-Off Ver
    MS Excel 2007
    Posts
    221

    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

  12. #12
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,819

    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.

  13. #13
    Forum Contributor
    Join Date
    11-29-2014
    Location
    India
    MS-Off Ver
    MS Excel 2007
    Posts
    221

    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
    Attached Files Attached Files

  14. #14
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,819

    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.

  15. #15
    Forum Contributor
    Join Date
    11-29-2014
    Location
    India
    MS-Off Ver
    MS Excel 2007
    Posts
    221

    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

  16. #16
    Forum Contributor
    Join Date
    11-29-2014
    Location
    India
    MS-Off Ver
    MS Excel 2007
    Posts
    221

    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

  17. #17
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,509

    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.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  18. #18
    Forum Contributor
    Join Date
    11-29-2014
    Location
    India
    MS-Off Ver
    MS Excel 2007
    Posts
    221

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

    Thanks AliGW.. have changed status of the quesion

    Quote Originally Posted by sabha View Post
    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

  19. #19
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    14,059

    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.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 2
    Last Post: 11-21-2019, 08:58 AM
  2. Copy a column worth of formulas and text
    By shannydaw in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-04-2013, 04:19 PM
  3. Formula for gain and loss within a column with a single cell running total
    By unusually_usual in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-11-2011, 10:22 AM
  4. How to calculate 1 hour is worth 1.175
    By atlthiel in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-27-2008, 05:08 PM
  5. Excel formula to calculate % gain when adding cash equity to accou
    By Anita in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 04-29-2006, 11:25 AM
  6. Truncating a columns worth of cells not just a single cell.
    By rebates in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 09-06-2005, 06:05 PM
  7. [SOLVED] Spreadsheet to do annual investment gain$, % gain, for list
    By notaspreadsheetgenius in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 01-09-2005, 04:08 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1