Puzzled by XIRR - larger than expected?

[stacker512]

I have started to look into creating a spreadsheet for my S&S ISA, and trying to get to grips with how to calculate XIRR.

I have recorded this:

Date         Cashflows  Notes
2021-10-08    6,363.19  Initial
2021-10-18    1,485.62
2021-11-02    2,043.71
2022-01-19      487.83
2022-02-03    1,000.00
2022-02-25    1,300.00
2022-03-07    1,400.00
2022-06-15      500.00
2022-09-21   -2,256.95  Widthdrawal
2022-12-01    1,000.00
2023-01-20    1,400.00
2023-02-24    1,500.00
2023-03-24    2,000.00
2023-03-31  -24,238.00  Current

XIRR reports as 29.96%
Annualised reports as 19.43%
Time period is 1.48 years.

Is this actually correct value for the XIRR? Various places online seemed to report wildly different values.
Is the large XIRR due to a short timespan for the calculation?

Thanks

[MDW1954]

Is the large XIRR due to a short timespan for the calculation?

Thanks

Yes, I believe. This is a known problem with XIRR, according to former poster (and PhD level mathematician) Old Plodder.

There is a correction that he posted: see post viewtopic.php?p=558783#p558783

I would advise you to read more than that post, though. The overall debate at that point in the thread is quite informative.

FWIW, although I'm not a mathematician (PhD in economics), I endorse his method, which makes intuitive sense. That said, I don't use XIRR myself, but sometimes use IRR, as annual dividend "buckets" are good enough for my purposes.

MDW1954

[Alaric]

XIRR reports as 29.96%

Is the large XIRR due to a short timespan for the calculation?

You can do a reasonableness test quite easily.

Amounts "in" are 20480.35 and "out" 2256.95. Current value is 24238 which is about a net gain of 33%.

[XFool]

I have started to look into creating a spreadsheet for my S&S ISA, and trying to get to grips with how to calculate XIRR.

I have recorded this:

Date         Cashflows  Notes
2021-10-08    6,363.19  Initial
2021-10-18    1,485.62
2021-11-02    2,043.71
2022-01-19      487.83
2022-02-03    1,000.00
2022-02-25    1,300.00
2022-03-07    1,400.00
2022-06-15      500.00
2022-09-21   -2,256.95  Widthdrawal
2022-12-01    1,000.00
2023-01-20    1,400.00
2023-02-24    1,500.00
2023-03-24    2,000.00
2023-03-31  -24,238.00  Current

XIRR reports as 29.96%
Annualised reports as 19.43%
Time period is 1.48 years.

Is this actually correct value for the XIRR? Various places online seemed to report wildly different values.

It may be irrelevant, I don't know, but I do wonder a bit what is going on here.

You show a "Withdrawal", presumably a withdrawal of cash from the ISA, and "Current", presumably a current valuation of the ISA (a pseudo withdrawal)*. So presumably all the others are investments of cash into your ISA, coming from outside the ISA? It's just that cash injections of £1,485.62, £2,043.71 and £487.83 sound a little odd to me. They sound like dividend payments! If from dividends paid outside the ISA going into the ISA as cash, fair enough. But, if they were dividends coming from shares already inside the ISA - and not paid out - then, IMO, this would be a misapplication of XIRR and the figure for XIRR would be meaningless.


* BTW - I always use a positive value for these in XIRR, as they are cash flows towards you or your bank account - and negative for cash investments going in. The numerical value of XIRR is though unaffected.

[stacker512]

So presumably all the others are investments of cash into your ISA, coming from outside the ISA?

Correct.

It's just that cash injections of £1,485.62, £2,043.71 and £487.83 sound a little odd to me. They sound like dividend payments!

They are not dividend payments on their own, but my record keeping may not have been the best. They could be:
- new cash bundled with cash balance from dividends
- new cash, that was invested, minus the dealing charge and stamp duty; hence the strange unround numbers.

I will have to check! Thanks for raising this.

* BTW - I always use a positive value for these in XIRR, as they are cash flows towards you or your bank account - and negative for cash investments going in. The numerical value of XIRR is though unaffected.

I've been aware of that but not sure which direction I would like to think of the cashflows flowing. I suppose it's a matter of personal taste. Very subjective - does the cash flow to the portfolio, or to the investor?

[monabri]

I have started to look into creating a spreadsheet for my S&S ISA, and trying to get to grips with how to calculate XIRR.

I have recorded this:

Date         Cashflows  Notes
2021-10-08    6,363.19  Initial
2021-10-18    1,485.62
2021-11-02    2,043.71
2022-01-19      487.83
2022-02-03    1,000.00
2022-02-25    1,300.00
2022-03-07    1,400.00
2022-06-15      500.00
2022-09-21   -2,256.95  Widthdrawal
2022-12-01    1,000.00
2023-01-20    1,400.00
2023-02-24    1,500.00
2023-03-24    2,000.00
2023-03-31  -24,238.00  Current

If you adopt the "sign convention" above you end up with a negative total return (add up the cashflows and you get a negative number). Thus I would suggest it is better to swap your cashflow signs around.

If you manually type in a date of 31/03/2024 (four) you can see what affect it has on the XIRR. All you can say, at this point in time, your XIRR is a value of X% as on a date.

[XFool]

It's just that cash injections of £1,485.62, £2,043.71 and £487.83 sound a little odd to me. They sound like dividend payments!

They are not dividend payments on their own, but my record keeping may not have been the best. They could be:
- new cash bundled with cash balance from dividends
- new cash, that was invested, minus the dealing charge and stamp duty; hence the strange unround numbers.

I wouldn't do this myself, for two reasons:

1. I'm too lazy!

2. The costs and charges of purchasing the investment - along with the cost of the investment itself - are indeed the total costs involved in the investment.

...Which is why I prefer the traditional negative sign (cost, or monetary debt) for such investments. It is returns of cash (hopefully) resulting from the original investment over time that are positive. This matter does cause some confusion to people starting with XIRR - it did me.

* BTW - I always use a positive value for these in XIRR, as they are cash flows towards you or your bank account - and negative for cash investments going in. The numerical value of XIRR is though unaffected.

I've been aware of that but not sure which direction I would like to think of the cashflows flowing. I suppose it's a matter of personal taste. Very subjective - does the cash flow to the portfolio, or to the investor?

As above. I think XIRR was originally a tool of business finance, used to assess whether money spent out (negative) on a business project was meeting expectations.

[Alaric]

They are not dividend payments on their own, but my record keeping may not have been the best. They could be:
- new cash bundled with cash balance from dividends
- new cash, that was invested, minus the dealing charge and stamp duty; hence the strange unround numbers.

If you are content to record the IRR of the ISA including cash balances, the only records you need are periodic values of the total investments plus cash, plus the dates and amounts of amount subscribed to the ISA and the dates and amounts withdrawn from the ISA. Individual investments and the costs of doing so don't come into it as they are internal transfers between ISA cash and ISA investments.

It's only if you wanted to keep separate records of the performance of ISA investments distinct from the performance of ISA cash that you would need to track dividends and investments.

[stacker512]

You can do a reasonableness test quite easily.

Amounts "in" are 20480.35 and "out" 2256.95. Current value is 24238 which is about a net gain of 33%.

Ah, yes! That is a very helpful explanation. Thank you.
I think my mistake when looking over this, is to also include the current portfolio value as part of that, so the result didn't make sense to me.

[stacker512]

...Which is why I prefer the traditional negative sign (cost, or monetary debt) for such investments. It is returns of cash (hopefully) resulting from the original investment over time that are positive. This matter does cause some confusion to people starting with XIRR - it did me.

I've flipped the numbers around so inflows into the ISA are negative and outflows are positive.

The values that you highlighted were indeed wrong - post dealing charges. I've corrected my spreadsheet now.

2021-10-18    1,500.00
2021-11-02    2,043.00
2022-01-19      500.00

Regarding the organisation of the spreadsheet, is it best to have entries inserted at the top of a table (I've seen an example of this) or append to the bottom? Does it matter? Just trying to work out if I will have pitfalls in the future. I have oft wondered how one can "get the latest value in a table by date" sort of operation, a spreadsheet equivalent of

select * from table order by date desc;

but I am not too familiar with spreadsheets.

[gpadsa]

You could stick with SQL, it would be a good project (I did not, I learned spreadsheets)

I would use libreoffice Base but if you have excel you probably have MS Access

This would be my starting point https://blog.sqlauthority.com/2014/04/16/sql-server-calculating-xirr-in-sql-server-internal-rate-of-return-available/ (found by googleing) then anything from stackexchange etc e.g. https://stackoverflow.com/questions/20972209/xirr-calc-in-sql
gpadsa

[MDW1954]

IRR rather than XIRR is easier, because you just have one entry per year.

But you'd still have to get the signs correct, etc.

MDW1954