Calculating gain/loss if holding changes?

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.

OP what is your reason for wishing to do this ?

I use the XIRR function in Excel. See forums.moneysavingexpert.com/showthread.php?t=5798035

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