Annualised Return

redbuzzard

OK:) Sounds interesting - maybe for another day!

Edit: that was a reference to unitised value.

redbuzzard

JohnRo wrote: »

edit: I've tried XIRR using the method you mention and got a strange result but will take another look and check for errors.

Usually, when I have bogged up a cash flow calculation it is because I have got the sign wrong - I've been making that mistake for 35 years!

EDIT - I'd be surprised if it's the problem, but if you are omitting the guess or using 0.1, then try 1 or 10. It's an iterative calculation that starts with the guess and adjusts it until it works. If you still get the same silly answer, look elsewhere,

Does this help?

JohnRo

Yes looks very similar, I have the newer entries at the top and older ones shoved off the screen at the bottom but the function uses the relevant dates so it matters not.

I also keep everything aligned so there are lots of dates with a zero cash flow in the range.

bowlhead99

Not sure if you're happy with what you've got now. But basically, if you know what cash you have paid and when, and what you have received and when, and what it is worth today (so you can pretend you sold today at current Nav and got another receipt) ; then you have all the info needed to get an annualised return rate on any of your assets (or the portfolio as a whole).

The IRR calc which assumes fixed length of time between every transaction is not what is needed unless you are going to let every period be one day long and have a lot of days with zero activities.

The xirr calc lets you plug in a list of dates and cash flows. Do that and assume the initial payment and any top-ups into the fund are negative cash flows, and any receipts from the fund and the current terminal value is positive cash flows. The annualised return will drop out.

It is not a technically perfect solution and sometimes you need to give it a "guess" value as the way it works means that there can be multiple solutions. But it is certainly good enough. Maybe feed it basic numbers to see how it works before trying your real ones.

JohnRo

I had originally intended calculating a rolling annualised return on each line but really this will do, it's not critical data and won't affect the planned schedule in any way.

Here's a simplified view of the current numbers I'm working with



Thanks folks!

redbuzzard

JohnRo wrote: »

Yes looks very similar, I have the newer entries at the top and older ones shoved off the screen at the bottom but the function uses the relevant dates so it matters not.

I also keep everything aligned so there are lots of dates with a zero cash flow in the range.

I assume you have actually entered dates for the zero cash flows? It wouldn't make any difference what the date is if the calculation works, but leaving it blank implies a date of c. 1/1/1900 which might confuse it!

It does seem generally to work even when the dates are not in chronological order, though I wouldn't guarantee it.

JohnRo

redbuzzard wrote: »

I assume you have actually entered dates for the zero cash flows?

yes, the above is a distilled version

Reaper

I asked a similar question a while ago and the forum members sorted it out for me. It's here if you want to check it out:
https://forums.moneysavingexpert.com/discussion/4726342

JohnRo

I did a forum search for annualised return but if I don't spot something obvious in the first two or three pages I tend to stop looking and post.

redbuzzard

Well, it seems to work either way up which is a comfort!

The answer looks about right.

You are aware that one of your values in the middle has the opposite sign i.e. is a payout?