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!