MATLAB XIRR gives -37.1% while excel gives 4.2%
2 views (last 30 days)
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.
Duncan Lilley on 19 Oct 2017
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)
Shao Shao on 8 Mar 2021
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.