Non matching results for IRR

[geoffles]

Description

I have some IRR calculations with wildly different results compared with Excel.

I have included some example C# with some cases of my mismatching data. Note that for some examples, Excel returns a NUM! error, in which cases I have supplied a double.NaN as the expected value. I'm not sure what the expected behaviour of the library in cases where Excel returns this error.

Repro steps

Run the code below:

var nums = new double[][] {
    //Control from example: https://support.microsoft.com/en-us/office/irr-function-64925eaa-9988-495b-b290-3ad0c163c1bc
    new double[] { -70000,12000,15000,18000,21000,26000 },
    //My examples
    new double[] {0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,-264.4016315,188.9045976,543.716092,530.2272019,604.528632,534.241247,542.0029841,479.1036244,418.0742638,357.6408251,343.7344172,-81.33203134,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0},
    new double[] {0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,-43.161,-430.389,-501.96,937.3308129,1779.965058,1908.268878,1502.484889,1058.628376,1210.615764,1322.572013,1294.614828,1040.674621,888.9149407,876.4030213,905.8048006,1040.772848,1101.466178,1030.015334,812.3926072,884.0664324},
    new double[] {0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,-28.704,-75.504,-23.02606893,1.185710897,13.92458582,27.67665869,10.03008021,11.19371069,9.084245582,5.21520105,-6.686665091,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0},
    new double[] {0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,-91.08311155,642.6094366,1190.794681,1242.259461,1099.434363,959.1485074,822.4738264,805.0219758,772.0441847,688.4331595,526.7929387,148.1610195,0,0,0,0,0,0,0,0,0,0,0,0},
    new double[] {0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,-43.6746279,295.7212113,327.4711176,292.1510936,257.2684481,221.3013225,230.8746166,240.5736588,246.3812007,252.6002921,259.2265278,265.9834272,379.167634,389.1791145,399.2526461,409.3782695,419.6130945,429.8745746,699.2714037,710.7886706,722.4094988,734.1335718,745.9547144},
    new double[] {0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,-50.544,-18.70128,-5,-5,-20.81759411,-1.770116165,0.503289685,0.314113056,0.771195008,-6.760070328,-1.148506289,-5.468591741,-5.463712308,-5.961486897,-77.9481861,0,0,0,0,0,0,0,0,0,0,0,0,0,0},
    new double[] {0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,386.7071313,358.8012443,412.1902946,406.1510305,575.7373662,434.2232677,482.4196839,326.8542996,148.1786251,166.3984516,165.1862579,299.0548516,433.2516591,274.6848952,-57.7222301,-77.64704325,-74.51874336,-72.47051425,-175.5111645,0,0,0,0,0,0,0,0},
    new double[] {0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,-86,-48,-48,-14.44851464,21.91564362,43.38578906,52.94575179,-12.91690729,1.592347847,-59.42631116,-51.25464708,4.561745518,-58.86849133,-41.74956104,-48.2415346,-50.71649086,0,0,0,0,0,0,0,0,0,0,0,0},
    new double[] {0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,188.2654506,258.853252,248.190251,415.1319939,352.9407094,313.9594741,245.2171731,169.7541284,152.8011487,-10.12116163,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0},
    new double[] {0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,-11.72006017,48.20251141,90.2234982,110.7320377,97.53736511,84.90423777,72.61183825,59.93543912,47.21437118,47.50882332,47.59134869,47.99650759,51.13068874,51.59539354,49.42200214,0,0,0,0,0,0,0,0,0,0,0},
    new double[] {0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,-100,-100,-200,187.100265,253.9146864,147.551264,34.92061992,-64.04128041,-208.5295737,-300.0898697,-341.5536358,-386.6206075,-429.4765567,-468.1390525,-520.189004,-10.04343545,-85.70649279,0,0,0,0,0,0,0,0,0},
    //This example does not contain a positive and negative number and so is not expected to return a result
    new double[] {0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,259.4212097,590.9890307,659.3935863,746.5125155,868.4792077,724.4262136,651.6032203,570.292362,482.2883783,374.9513019,374.8559331,355.5442713,504.803386,498.9507628,493.491597,487.9682785,482.3713156,474.1003335,465.5522816,456.6731588,447.4077869,437.6865137,427.4187315,416.6237142,405.2395334,393.1984566,380.2299029},
};

var expected = new double[]
{
    //Control
    0.086631,
    //My examples
    1.299489258,
    0.900688878,
    -0.1201593755,
    7.747939368,
    6.848641279,
    double.NaN,
    double.NaN,
    double.NaN,
    double.NaN,
    4.778677157,
    double.NaN,
    //No positive and negative number
    double.NaN
};

var actual = nums.Select(n => Excel.FinancialFunctions.Financial.Irr(n)).ToArray();


//Compare up to a precision of 6 decimal places
var matches = actual.Select((a,index) => Math.Round(expected[index], 6) == Math.Round(a,6)).ToArray();

Expected behavior

It is expected that the matches array should be true for all indices except where expected contains double.NaN (Note that by design double.NaN == will return false in C#).

Note that it is not known what the expected behaviour is where Excel would return NUM! error.

Actual behavior

Only matches[0] provides a true result

Known workarounds

No known work arounds

Related information

• Operating system: Windows 11
• Branch: Versions 3.1.0 and 3.2.0
• .NET Runtime, CoreCLR or Mono Version: .Net 6.0
• Performance information, links to performance testing scripts

[geoffles]

Bumping this issue - is this an issue that will be looked at?

[jcoliz]

@geoffles Thanks for contributing this!

[geoffles]

I've managed to get to the bottom of this issue. The TLDR is that excel will ignore leading zeros in the cashflows. My specific use case resulted in the leading zeros which we are now actually going to ignore.

@jcoliz I presume you'd like to close this issue as not a bug?

[geoffles]

Ok, so plot twist...

Because the definition of IRR is the rate of return that leads to an NPV of zero (from what I understand), it means that the leading zeros should have no effect on the NPV. So looks like maybe there is still a bug on the table?