Calculating gain/loss if holding changes?
Options
baj25
Posts: 48 Forumite
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
0
Comments
-
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.0
-
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
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/0 -
OP what is your reason for wishing to do this ?0
-
Average book cost:
https://en-us.janushenderson.com/retail/calculate-average-cost/0 -
To be able to compare performance of different funds AJoe.0
-
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 2021 - #027 £15,268 (76%)0 -
I use the XIRR function in Excel. See forums.moneysavingexpert.com/showthread.php?t=57980350
-
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/
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.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%)0 -
"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.0 -
TrustyOven wrote: »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.
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%)0
This discussion has been closed.
Categories
- All Categories
- 343.2K Banking & Borrowing
- 250.1K Reduce Debt & Boost Income
- 449.7K Spending & Discounts
- 235.3K Work, Benefits & Business
- 608K Mortgages, Homes & Bills
- 173.1K Life & Family
- 247.9K Travel & Transport
- 1.5M Hobbies & Leisure
- 15.9K Discuss & Feedback
- 15.1K Coronavirus Support Boards