MATLAB XIRR gives -37.1% while excel gives 4.2%

1 次查看（过去 30 天）
Christine Fesler
Christine Fesler 2017-10-12
回答： Shao Shao 2021-3-8
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.

请先登录，再回答此问题。

回答（2 个）

Duncan Lilley
Duncan Lilley 2017-10-19
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 个评论
Christine Fesler
Christine Fesler 2017-11-14
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. >>

请先登录，再进行评论。

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

类别

在 Help Center 和 File Exchange 中查找有关 Data Import from MATLAB 的更多信息

标签

Community Treasure Hunt

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

Start Hunting!

Translated by