MATLAB XIRR gives -37.1% while excel gives 4.2%

3 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.

Answers (2)

Duncan Lilley
Duncan Lilley on 19 Oct 2017
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)
  1 Comment
Christine Fesler
Christine Fesler on 14 Nov 2017
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. >>

Sign in to comment.


Shao Shao
Shao Shao on 8 Mar 2021
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.

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!