IRR/XIRR Help

[techfreak]

Hi all,

I've been trying to calculate the overall investment performance of my investment portfolio over a given time period.

The portfolio has been funded via regular monthly amounts. My ultimate aim is to be able to compare the investment performance of any given portfolio over a set period of time whether yearly or since inception, that also takes into account capital in/out at various time points. The capital in/out should be applicable to take into account any deposit/withdrawals, tax/charges paid, dividends reinvested or withdrawn or any other money in/out that could affect the overall investment performance.

I use an investment platform that automatically calculates the IRR since inception (doesn't tell you how though) and it tells you the IRR before tax and charges and after tax and charges.

In this particular instance, there have been no withdrawals and dividends have been reinvested.

I've created a spreadsheet that works out my XIRR as 8.25% which I understand is the annualised return since inception.
I've tried to work out the overall XIRR and it calculates this as 28.09%.

However, the platform is calculating the IRR as 27.97% since inception and I can't seem to figure out why the two are different.

The basics are £200 per month invested since 06/09/2011. No withdrawals. Value as at today is £8,636.90. Dividends have been reinvested. I haven't put specific withdrawal of monthly charges into the spreadsheet as I believe these should be reflected in the end value? - Could this be affecting the calculation? Would I need to put in figures that show each and every monthly charge?

The data on the investment platform (since inception) is as follows.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Capital in[/TD]
[TD]£7,600[/TD]
[/TR]
[TR]
[TD]Capital out[/TD]
[TD]£0.00[/TD]
[/TR]
[TR]
[TD]Realised Gain/Loss[/TD]
[TD]£843.00[/TD]
[/TR]
[TR]
[TD]Unrealised Gain/Loss[/TD]
[TD]£284.16[/TD]
[/TR]
[TR]
[TD]Gross Interest[/TD]
[TD]£3.59[/TD]
[/TR]
[TR]
[TD]Gross Dividends[/TD]
[TD]£32.57[/TD]
[/TR]
[TR]
[TD]Tax[/TD]
[TD]£-3.96[/TD]
[/TR]
[TR]
[TD]Charges[/TD]
[TD]£-155.36[/TD]
[/TR]
[TR]
[TD]Rebates[/TD]
[TD]£32.91[/TD]
[/TR]
[TR]
[TD]Portfolio Value[/TD]
[TD]£8,636.90[/TD]
[/TR]
[TR]
[TD]Net Gain/Loss[/TD]
[TD]£1,036.90[/TD]
[/TR]
[TR]
[TD]Return Before Tax and Charges[/TD]
[TD]32.57%[/TD]
[/TR]
[TR]
[TD]Return After Tax and Charges[/TD]
[TD]27.97%[/TD]
[/TR]
</tbody>[/TABLE]

The data I have in put in the spreadsheet is as follows:

A1:A45 = £200 (I have also included end of year value as a minus and then plus figure to calculate yearly XIRR)
B1:B45 = monthly dates

XIRR formula which returns 8.25% is =XIRR(A1:A45,B1:B45,10%)

Overall IRR formula which returns 28.09% is =SUM((1+XIRR(A1:A45,B1:B45))^((DATE(2014,10,20)-DATE(2011,9,6))/365)-1)

Help is very much appreciated...

 

[BiocideJ]

Most sites will provide a disclaimer that their calculation for IRR may not match other valid calculations for IRR. Subtle differences regarding timing of money in/out flow through to the final percentage and will likely be impossible to reconcile unless the site will provide their exact method for calculating their RSI.

28.09% and 27.97% seem very close and I would tend to believe your calculations are solid.

 

[techfreak]

Thanks for the reply Biocide.

I've ran it again on another portfolio and the values match exactly, so confirms that it should be as accurate as can be to give a good overall picture.

 

[techfreak]

Strange - I've just ran it again on another account and my annualised figures for each year match up exactly - bar one - the first year -8.76% calculated vs -8.81% reported on site. Not a million miles off so ok.

But, the overall IRR since inception is exactly 3% out... my calculations work it out as 12.31% and the site reports 15.31%

I've asked to see if they can give me some clues themselves as only they know how they've reached this figure... will report back if I get any joy..

 

[BiocideJ]

Another difference I've encountered which can make a big difference is not all calculations consider the actual number of days in the year but rather a set number like 365 for the annualization. I believe the XIRR function uses actual days and this can potentially make a difference as well although 3% seems a bit extreme.

 

[techfreak]

I've figured it out, I think....


The site was using a start date of 06/10/2010 (I believe this was the date the account was opened)- however, no money was actually invested until 08/07/2011.


If I try to amend my spreadsheet to include a start date of 06/10/2010 and a value of £0.00 - it doesn't work - the XIRR inputs 0.0% - I'm guessing it needs to start with a positive value.


If I change the start date on the site to the day the actual money was invested it matches the values produced on the spreadsheet.


I think the site needs to be able to work it out from the day the actual money was invested and not the day the account was opened.

 

[BiocideJ]

Can you add a starting value and a withdrawal on 6/10/2010 in offsetting amounts (two line items) perhaps and then continue with your normal dates/values?

I haven't used XIRR in a while, but that seems like a possible work-around.

 

[techfreak]

Yes that works - even putting in a nominal starting value of £0.01 calculates the same IRR they have. Point being - I can't believe that's the correct figure to use?

Surely IRR should be calculated from the actual date money was invested and put to work?

 

[BiocideJ]

Surely IRR should be calculated from the actual date money was invested and put to work?

Oh, definitely. I was just making a suggestion so you could match their RSI number.