Your browser isn't supported
It looks like you're using an old web browser. To get the most out of the site and to ensure guides display correctly, we suggest upgrading your browser now. Download the latest:

Welcome to the MSE Forums

We're home to a fantastic community of MoneySavers but anyone can post. Please exercise caution & report spam, illegal, offensive or libellous posts/messages: click "report" or email forumteam@. Skimlinks & other affiliated links are turned on

Search
  • FIRST POST
    • baj25
    • By baj25 14th May 18, 8:51 PM
    • 37Posts
    • 11Thanks
    baj25
    Calculating gain/loss if holding changes?
    • #1
    • 14th May 18, 8:51 PM
    Calculating gain/loss if holding changes? 14th May 18 at 8:51 PM
    How do people calculate % gain/loss over a time period, if you increase/decrease holding during the period. Treat them as two separate sub periods, pro rata? e.g. 100 units bought at £1 on jan 1st, 20 more units bought 1st jul at £1.10, what is % gain on dec 31st if unit price is £1.20 on that day? Thanks, Brian
Page 1
    • Terry Towelling
    • By Terry Towelling 14th May 18, 9:27 PM
    • 212 Posts
    • 107 Thanks
    Terry Towelling
    • #2
    • 14th May 18, 9:27 PM
    • #2
    • 14th May 18, 9:27 PM
    I tried to do this - and was only partially successful. I created a spreadsheet to do it but it really only works well if you plan not to sell any of your units (although I did build a bit in for this) and it really only works when you create percentages for each individual purchase. That said, the percentages were meaningless when I tried to annualise them and they were less than a year old. The spreadsheet became horrendous with all sorts of 'IF' statements in it. I then struggled with the reinvested dividends because, in theory, they haven't cost you anything so your percentage gains will have to be based on the change in value of the units and not the actual gain you have made (i.e. 100% of it). In the end I abandoned percentages and my spreadsheet just shows me the financial value of any gains/losses for individual purchases together with any realised profits and retained gains for that purchase. If you do find a way to do it meaningfully, let me know.
    • Asghar
    • By Asghar 14th May 18, 9:58 PM
    • 181 Posts
    • 102 Thanks
    Asghar
    • #3
    • 14th May 18, 9:58 PM
    • #3
    • 14th May 18, 9:58 PM
    How do people calculate % gain/loss over a time period, if you increase/decrease holding during the period. Treat them as two separate sub periods, pro rata? e.g. 100 units bought at £1 on jan 1st, 20 more units bought 1st jul at £1.10, what is % gain on dec 31st if unit price is £1.20 on that day? Thanks, Brian
    Originally posted by baj25
    The total purchase cost is £122 (100 x £1 + 20 x £1.10)
    The end of year value is £144 ( 120 units x £1.20)

    The gain for the year is £22 or 18.03%.
    Or is that too simple?

    Have a read here.
    http://monevator.com/how-to-unitize-your-portfolio/
    Last edited by Asghar; 14-05-2018 at 10:01 PM.
    • AnotherJoe
    • By AnotherJoe 14th May 18, 10:02 PM
    • 9,880 Posts
    • 11,036 Thanks
    AnotherJoe
    • #4
    • 14th May 18, 10:02 PM
    • #4
    • 14th May 18, 10:02 PM
    OP what is your reason for wishing to do this ?
    • EdGasketTheSecond
    • By EdGasketTheSecond 14th May 18, 10:04 PM
    • 625 Posts
    • 331 Thanks
    EdGasketTheSecond
    • #5
    • 14th May 18, 10:04 PM
    • #5
    • 14th May 18, 10:04 PM
    Average book cost:
    https://en-us.janushenderson.com/retail/calculate-average-cost/
    • baj25
    • By baj25 14th May 18, 10:15 PM
    • 37 Posts
    • 11 Thanks
    baj25
    • #6
    • 14th May 18, 10:15 PM
    • #6
    • 14th May 18, 10:15 PM
    To be able to compare performance of different funds AJoe.
    • george4064
    • By george4064 14th May 18, 10:18 PM
    • 995 Posts
    • 1,043 Thanks
    george4064
    • #7
    • 14th May 18, 10:18 PM
    • #7
    • 14th May 18, 10:18 PM
    Look up unitisation.

    I unitise my portfolio, which gives overall gain/losses and performance of my portfolio as a whole (and it is capable of handling new money and withdrawals without skewing the performance).

    Sound like you!!!8217;re looking to unitise individual holdings, which is same concept just a lot more work as you!!!8217;re doing it X many times. (X being the number of holdings you have).
    "If you arenít willing to own a stock for ten years, donít even think about owning it for ten minutesĒ Warren Buffett

    Save £12k in 2016 - #045 £10,358.81/£12,000 (86%)
    Save £12k in 2017 - #003 £12,427.51/£12,000 (104%)
    Save £12k in 2018 - #004 £4,457.62/£12,000 (37%)
    • msallen
    • By msallen 15th May 18, 4:25 AM
    • 853 Posts
    • 937 Thanks
    msallen
    • #8
    • 15th May 18, 4:25 AM
    • #8
    • 15th May 18, 4:25 AM
    I use the XIRR function in Excel. See forums.moneysavingexpert.com/showthread.php?t=5798035
    • TrustyOven
    • By TrustyOven 15th May 18, 5:28 PM
    • 711 Posts
    • 745 Thanks
    TrustyOven
    • #9
    • 15th May 18, 5:28 PM
    • #9
    • 15th May 18, 5:28 PM
    The total purchase cost is £122 (100 x £1 + 20 x £1.10)
    The end of year value is £144 ( 120 units x £1.20)

    The gain for the year is £22 or 18.03%.
    Or is that too simple?

    Have a read here.
    http://monevator.com/how-to-unitize-your-portfolio/
    Originally posted by Asghar

    How does this work out in practice in a spreadsheet?


    I tried doing this in the past but I could never get the hang of updating unit values each time a purchase or sale occurs, and that you have to get the latest value in the spreadsheet. It's the keeping-track-of-the-history data that seems to befuddle me.


    Edit: My bad. Looks like I had forgotten unitisation having glanced at the monevator link. My question still stands I think - how to use spreadsheets to manage the temporal data.
    Last edited by TrustyOven; 15-05-2018 at 5:36 PM.
    Goals
    Save £12k in 2017 #016 (£4212.06 / £10k) (42.12%)
    Save £12k in 2016 #041 (£4558.28 / £6k) (75.97%)
    Save £12k in 2014 #192 (£4115.62 / £5k) (82.3%)
    • Terry Towelling
    • By Terry Towelling 15th May 18, 7:41 PM
    • 212 Posts
    • 107 Thanks
    Terry Towelling
    "The total purchase cost is £122 (100 x £1 + 20 x £1.10)
    The end of year value is £144 ( 120 units x £1.20)

    The gain for the year is £22 or 18.03%.
    Or is that too simple?"



    Could be too simple. Certainly it's a way to say 'The current value of my units is X% more than I paid for them' but it takes no account of the timings of the unit purchases. The first purchase may have been made in January and a subsequent purchase might have been much later in the year. So you have a whole year's gain on the 1st purchase but only a month or two on the later one. It gets worse when you sell some units during the year.
    Last edited by Terry Towelling; 15-05-2018 at 7:42 PM. Reason: Clarity
    • TrustyOven
    • By TrustyOven 15th May 18, 9:27 PM
    • 711 Posts
    • 745 Thanks
    TrustyOven
    How does this work out in practice in a spreadsheet?


    I tried doing this in the past but I could never get the hang of updating unit values each time a purchase or sale occurs, and that you have to get the latest value in the spreadsheet. It's the keeping-track-of-the-history data that seems to befuddle me.


    Edit: My bad. Looks like I had forgotten unitisation having glanced at the monevator link. My question still stands I think - how to use spreadsheets to manage the temporal data.
    Originally posted by TrustyOven

    After hours of faffing about, I think I got it.
    Goals
    Save £12k in 2017 #016 (£4212.06 / £10k) (42.12%)
    Save £12k in 2016 #041 (£4558.28 / £6k) (75.97%)
    Save £12k in 2014 #192 (£4115.62 / £5k) (82.3%)
    • baj25
    • By baj25 20th May 18, 8:40 PM
    • 37 Posts
    • 11 Thanks
    baj25
    Thanks for replies, will try that.
    • AnotherJoe
    • By AnotherJoe 20th May 18, 8:56 PM
    • 9,880 Posts
    • 11,036 Thanks
    AnotherJoe
    To be able to compare performance of different funds AJoe.
    Originally posted by baj25
    That doesn't make sense. You can compare the performance of different funds without having bought or sold.
Welcome to our new Forum!

Our aim is to save you money quickly and easily. We hope you like it!

Forum Team Contact us

Live Stats

108Posts Today

1,094Users online

Martin's Twitter