MATLAB XIRR gives -37.1% while excel gives 4.2%

Hi, I am wondering why I am getting a widely different calculation between excel and MATLAB when using the XIRR function. I know the day calculation is different for MATLAB due to the leap year; however, my other calculations between MATLAB and Excel are not this different and generally equal at 0.1 significance level. I have attached the excel file that shows the vector of data and dates I am referring to. In excel, using the XIRR command, I get a calculation of 4.2%; however, in MATLAB when I use the formula: result = xirr(MATLABCashFlow(:,:),date(:,:)) I get -37.1%. I am not sure if the negative ending value for this vector is really throwing off MATLAB's guess calculation; however, my other data vectors that also have negative ending values have similar results to excel. Please, I would really appreciate if someone import this file into MATLAB and tried the xirr calculation to see if they also get a similar return of -37.1%. I am using MATLAB R2016B 64 bit and Excel 2010 version 32bit on Windows 7.

Answer 1

[Duncan Lilley]

Hello,

It appears that the algorithm is converging to a different solution. Here are some workarounds which achieve the same answer as Excel:

1) Provide an initial guess

result = xirr(MATLABCashFlow(:,:), date(:,:), 0.01)

2) Use "pvvar" and "fzero" to solve the problem

fun = @(r)pvvar(MATLABCashFlow(:,:), r, date(:,:));
result = fzero(fun, 0)

[Christine Fesler]

Hi, Thanks for your comment; however, the solution above does not work. guess = @(r)pvvar(cashflowC(6).PME_MSCIWrld(:,n+2),r,cashflowC(6).PME_MSCIWrld(:,1)); result = fzero(guess,0); Exiting fzero: aborting search for an interval containing a sign change because complex function value encountered during search. (Function value at -1.28 is -6.740246541120336e+22-1.640052906094859e+22i.) Check function or try again with a different starting value. >>

Answer 2

[Shao Shao]

Hello,

I found your question quite interesting and tried to confirm this issue. The conclusion is, matlab has a bug here and the result by EXCEL is correct.

As you can see, there are two solutions which meet your data (where y = 0), one is 0.041991999745369 and the other is -0.3709338902. Matlab select the latter but it seems to be incorrect, because sum(cf) > 0. The EXCEL result is correct.

Hope this answer is not too late.