How to calculate profits from S&S ISA

jainraghav74

Hello

I started investing under S&S ISA from Mar 2018 onwards and I have made random purchase over since.

I know dates on which money was paid, and the money that was paid.
I know the current value of my funds.
I have not sold anything yet.

I have all this data in Microsoft Excel.

1. How can I calculate what is my return? Is there a formula where dates & amount are random.

2. At what point is it best to sell? I know this might be difficult to answer but looking for thoughts, opinions, guidance, comments. For example, if based on formula above, if I am getting 5 times my mortgage interest rate, should I sell and "lock" some profit. Or should I wait for like 5 years and sell at that point.

harshitguptaiitr

1. How can I calculate what is my return? Is there a formula where dates & amount are random.

=XIRR(AMOUNT, DATES)
Here, AMOUNT is the column where amount is stored and DATES in the column with corresponding dates.
Last row of these columns should be current amount (in negative) and current date

dales1

Apparently XIRR gives a money-weighted return – hence time periods in which more money is invested have more impact on the overall return than equivalent time periods in which less money is invested.

Alternatively, but harder work to set up, you can use Unitisation, see https://monevator.com/how-to-unitize-your-portfolio/

Apparently Unitisation gives a time-weighted return – all time periods are weighted equally, irrespective of how much money is invested and when.
So Unitisation tells you the underlying investment performance, and strips out the impact of money flowing in and out.


Dales

bowlhead99

dales1 wrote: »

Apparently XIRR gives a money-weighted return – hence time periods in which more money is invested have more impact on the overall return than equivalent time periods in which less money is invested.

Alternatively, but harder work to set up, you can use Unitisation, see https://monevator.com/how-to-unitize-your-portfolio/

Apparently Unitisation gives a time-weighted return – all time periods are weighted equally, irrespective of how much money is invested and when.
So Unitisation tells you the underlying investment performance, and strips out the impact of money flowing in and out.

Unitisation is what you might want to use for portfolio monitoring if you wanted to track the return of how the 'portfolio' (choice of assets) performed over different periods, like a an investment manager would do when telling the public how his fund had performed and what returns had been available from it.

For example the holdings in total went down in value by 20% over a few months after you put some more money in during September, but then after you put some more money in during December, the total ISA holdings then went up in value by 10% in a month.

If you have lots of data about what each of your holdings was worth at a point in time (or at least, what the total ISA was worth each time you introduce new money to buy something in the ISA), you can imagine that you are offering an 'investment fund' to investors, give them units in the fund at a fair value each time new money is put in, and track the value of the units going up or down in value over time since you started investing. You could compare that to what performance was available from a real investment fund or a stock market index, or a bank account.

However, the IRR (using Excel XIRR) does something different, which may be more useful. While the investment values will go up and down by different percentages of different time periods, your actual overall performance of your investment in pounds is going to depend on how much money you had invested when, which IRR will reflect.

For example, using the earlier figures where holdings fell 20% and later quickly recovered 10%: if you did not have very much money in your ISA in September because you thought some stock markets were overvalued, you would have not have lost very much when the ISA holdings crashed in value last autumn. And then maybe you thought it was a great time to invest, so you piled thousands into your ISA and into exciting companies, and all that new money plus they old money went up quickly by 5%. Your ISA might be in profit overall.

Or the other way round, if you had loads of money in your ISA last September and it crashed 20%, and you got disappointed and cashed a lot of your money out, then the market went back up 10% with only some of your money still being invested, the net loss would still be close to 20% despite the partial recovery that a 'unitised' version would have shown.

So generally IRR can be preferred because it tells you what you made on an annualised basis including the effects of how much you chose to put in when. But unitizing it and creating a virtual 'investment fund' and fund units, allows you to map out how your portfolio mix did against other fund products without regard to when you were heavily invested or uninvested in your portfolio.

One way of looking at it is to say that ignoring time weighting, a stock market index might have gone up (e.g.) 8% annualised over a couple of decades, so it sounds like a good thing to be investing in; but some investors were heavily invested at different times and less invested at other times; with extreme examples, based on when their money was at work, someone might personally have a negative annualised return while someone else might have an incredibly positive one. IRR will give you broadly the right positive or negative numbers for those people whereas unitisation will track what happened to the first unit of money invested.

That perhaps gives you an answer to OP question 1. But to the second question, OP is right, it's difficult or impossible to answer. If you can prove you are making 5x your mortgage rate through investments, why not wait for another five years after growing your money before paying down the mortgage? The lender is financing a lucrative investment program - why pay it off ever! (ie don't pay more than the bare minimum). But in reality you know that markets have their ups and downs and we haven't had a serious 30-50% crash for over a decade now, and we will at some point, so it's not a bad thing to use some investment profits to pay down debt.

jainraghav74

For Q1 - I have now made everything into XIRR function as suggested and this is EXACTLY what I was looking for.

For Q2 - I understand that the growth in my funds *S&S ISA" have no real meaning unless I sell them and get proceeds in my bank account. Also I understand that mortgage amount will automatically reduce with time - for example a loan of 100k in 2017 will sound more scary than a loan of 100k in 2019 - due to inflation and theory of time value of money.

Thanks for the valuable insights though...

Alexland

jainraghav74 wrote: »

I started investing under S&S ISA from Mar 2018 onwards and I have made random purchase over since.

Buying random stuff rarely goes well. Might be worth reconsidering your approach and going for something more structured such as a single global tracker fund (for high risk) or multi asset fund (for balanced and adventurous risk). This strategy will work better for most investors. If you only hold one fund per investment account then you can look at publicly available performance data. You only sell when you need to use money and plan to derisk in advance.

Alex

Aminatidi

I'd be interested if there are any templates out there that I can download that will help with this.

Personally I've done similar, not "random" in terms of fund but random in so much as the timing, and I've kind of got to the point where I'm looking more at total return than every last detail.

msallen

Aminatidi wrote: »

I'd be interested if there are any templates out there that I can download that will help with this.

Personally I've done similar, not "random" in terms of fund but random in so much as the timing, and I've kind of got to the point where I'm looking more at total return than every last detail.

Not a template, but my post at https://forums.moneysavingexpert.com/discussion/comment/73916621#Comment_73916621 gives an example of using XIRR() in Excel. Its quite straightforward.

dales1

Many thanks bowlhead99 !

I had read the afore-mentioned Monevator article some time ago, and I had followed his conclusion that "you might as well go the whole hog and unitize your portfolio" [even though he didn't adequately explain the difference as against XIRR].

Accordingly I then dug out all my data and I fully unitised my portfolio - using Fire-v-London's (partially Bogle-based) spreadsheet but heavily modified by me.

I have now looked on the Bogleheads wiki and that is fairly explanatory.

But your explanation (above) beats all these, and I am now confident I have understood the merits of unitisation v XIRR.

You said that "unitisation will track what happened to the first unit of money invested" (a concept which I haven't read before) and I can now see that is what is happening in my spreadsheet's formulae. Bingo !

Accordingly I aim to spend some time tomorrow adding XIRR-based returns (over various timescales) to my spreadsheet, in preference to (and in comparison with) the unitised returns.

Purely out of interest and spreadsheet practice !

Dales

quirkydeptless

dales1 wrote: »

Alternatively, but harder work to set up, you can use Unitisation, see https://monevator.com/how-to-unitize-your-portfolio/

"For starters, if stock picking is your hobby, then finding out you’re in the ‘D’ League – less Neil Woodford, more Woody Woodpecker – will be thoroughly depressing." - The Investor on April 9, 2015

:think:

jainraghav74

Buying random stuff rarely goes well. Might be worth reconsidering your approach and going for something more structured such as a single global tracker fund (for high risk) or multi asset fund (for balanced and adventurous risk).

I have chosen 5 funds in which I invest. But random is when I invest, which of these 5 do I invest in and how much I put in...