XIRR function not working if last row is greater than -62 in the range

Aspose.Cells Product Family
1

Hi,

XIRR function gives numeric error when if last row is greater than -62.
But if last row is lower than -62,for example -63,-64, XIRR function is working properly.

Strange but true :frowning:

Sample Code :

var workbook = new Aspose.Cells.Workbook(runningPath + "\\IRR_Calculation.xlsx"); 
var wsXIRR = workbook.Worksheets[0];

wsXIRR.Cells["A11"].PutValue("31.12.2014");
wsXIRR.Cells["B11"].PutValue(100);
wsXIRR.Cells["A12"].PutValue("04.02.2015");
wsXIRR.Cells["B12"].PutValue(100);
wsXIRR.Cells["A13"].PutValue("30.05.2019"); 

wsXIRR.Cells["B13"].PutValue(-62);

wsXIRR.Cells["B3"].Formula = "=XIRR(B11:B13,A11:A13)";

CalculationOptions options = new CalculationOptions();
options.PrecisionStrategy = CalculationPrecisionStrategy.None; 
workbook.CalculateFormula(options);

string xirr = wsXIRR.Cells["B3"].Value;

the result is SUCCESSFULL

but, if B13 cell is -61,

the result become #NUM!

I’m working Aspose.Cells for .NET on versions 18.02.

Please Help,

2

@enis.gultekin,
I have tried this scenario using Excel 2016 from scratch and entered similar values which you have mentioned above. I never got a success message using Excel and always get error value. Could you please send us a sample workbook created by Excel which shows the success value as per your expectations? We will try to provide assistance by performing the same task using Aspose.Cells.

3

@ahsaniqbalsidiqui

Thanks for your help.
I have prepared a project. I hope, you can try and see the issue.

TestProject.zip (3.7 MB)

4

@enis.gultekin,
I have tested the sample project with different values but afraid to share that I always get #value! as shown in the attached images. Please share which Excel version are you using and also share the images of the output. We will try to investigate the issue further once feedback is provided by you.
Capture.PNG (897 Bytes)
Capture2.PNG (588 Bytes)

5

@enis.gultekin,

After further evaluation, I am able to reproduce the issue as you mentioned by using the following sample code with your template file. I found that XIRR function gives numeric error “#NUM!” when the last row is greater than -62. But if last row is lower than -62,for example -63,-64, XIRR function is working properly.
e.g
Sample code:

CalculationOptions options = new CalculationOptions();
            options.PrecisionStrategy = CalculationPrecisionStrategy.None;

            var workbook = new Aspose.Cells.Workbook("e:\\test2\\TestExcel.xlsx");
            var wsXIRR = workbook.Worksheets[0];

            wsXIRR.Cells["A11"].PutValue(Convert.ToDateTime("31.12.2014"));
            wsXIRR.Cells["B11"].PutValue(100);
            wsXIRR.Cells["A12"].PutValue(Convert.ToDateTime("04.02.2015"));
            wsXIRR.Cells["B12"].PutValue(100);
            wsXIRR.Cells["A13"].PutValue(Convert.ToDateTime("30.05.2019"));
            wsXIRR.Cells["B13"].PutValue(-62);

            wsXIRR.Cells["B3"].Formula = "=XIRR(B11:B13,A11:A13)";

            workbook.CalculateFormula(options);

            string xirr = wsXIRR.Cells["B3"].Value.ToString();

            Console.WriteLine("When the last row is -62, the result is : ");
            Console.WriteLine(xirr);

            wsXIRR.Cells["B13"].PutValue(-61);
            workbook.CalculateFormula(options);

            xirr = wsXIRR.Cells["B3"].Value.ToString();

            Console.WriteLine("When the last row is -61, the result is : ");
            Console.WriteLine(xirr);

            workbook.Save("e:\\test2\\TestExcelResult1.xlsx");

            Console.ReadLine();

output:
When the last row is -62, the result is :
-0.23531010768766
When the last row is -61, the result is :
#NUM!

I have logged a ticket with an id “CELLSNET-46766” for your issue. We will look into it soon.

Once we have an update on it, we will let you know.

6

@enis.gultekin,

This is to inform you that we have fixed your issue (logged earlier as “CELLSNET-46766”) now. We will soon provide you the fixed version after performing QA and incorporating other enhancements and fixes.

7

Hi @Amjad_Sahi,

Thanks for your help,
I’m looking forward :slight_smile:

8

@enis.gultekin,

Please try our latest version/fix: Aspose.Cells v19.5.6 (attached)

Your issue should be fixed in it.

Let us know your feedback.
Aspose.Cells19.5.6 For .Net2_AuthenticodeSigned.Zip (4.9 MB)
Aspose.Cells19.5.6 For .Net4.0.Zip (4.9 MB)
Aspose.Cells_Java_v19.5.6.zip (6.5 MB)

9

@Amjad_Sahi

Thanks, I’ve tried, it works.

10

@enis.gultekin,

Good to know that your issue is sorted out by the new fix/version. Feel free to contact us any time if you need further help or have some other issue or queries, we will be happy to assist you soon.

11

The issues you have found earlier (filed as CELLSNET-46766) have been fixed in Aspose.Cells for .NET v19.6. This message was posted using BugNotificationTool from Downloads module by Amjad_Sahi