MATLAB XIRR gives -37.1% while excel gives 4.2%
5 views (last 30 days)
Show older comments
Christine Fesler on 12 Oct 2017
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.
Find more on Data Export to MATLAB in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!Start Hunting!