XNPV function returning wrong value

Aspose.Cells Product Family
1

Hi,

Aspose XNPV formula returning diffrent value than the one in Excel.

Please see attached excel file and screenshot. See the cell E16, E17, E18 and E19

Regards

Ashok

2
Ashok Nayak:

Hi,

Aspose XNPV formula returning diffrent value than the one in Excel.

Please see attached excel file and screenshot. See the cell E16, E17, E18 and E19

Regards

Ashok

I am using Aspose 7.0.2

3

Hi,


Can you please try this latest fix of Aspose.Cells for Java v7.2.1.7 and let us know your feedback? If the issue still persists, provide us with the minimum lines of your source code to reproduce the issue. It will be helpful to us for looking into the matter further. We appreciate your cooperation in this regard.
4

Hi,

Thanks for your posting and using Aspose.Cells for Java.

I was able to replicate this problem using the latest version:
Aspose.Cells for Java v7.2.1.7
with the following code.

We have logged this issue in our database. We will look into your issue and once the issue is fixed or we have some other update for you, we will let you know asap.

This issue has been logged as CELLSJAVA-40203.

I have also shown the actual and expected output below for a reference.

Java


String filePath = “F:\Demo_Calculator.xls”;


Workbook workbook = new Workbook(filePath);


workbook.calculateFormula();


Worksheet worksheet = workbook.getWorksheets().get(0);


Cells cells = worksheet.getCells();


System.out.println(cells.get(“E16”).getStringValue());

System.out.println(cells.get(“E17”).getStringValue());

System.out.println(cells.get(“E18”).getStringValue());

System.out.println(cells.get(“E19”).getStringValue());

Actual Output:
-225227917
260566258
-66683
-666164


Expected Output:
-225227920
260566254
-66686
-666168

5
Hi,

I can find the issue using the following code with your file:

Sample code:

Workbook workbook = new Workbook("Demo_Calculator.xls");
workbook.calculateFormula();
System.out.println(workbook.getWorksheets().get(0).getCells().get("E16").getStringValue());
System.out.println(workbook.getWorksheets().get(0).getCells().get("E17").getStringValue());
System.out.println(workbook.getWorksheets().get(0).getCells().get("E18").getStringValue());
System.out.println(workbook.getWorksheets().get(0).getCells().get("E19").getStringValue());


Console output:

-225227917
260566258
-66683
-666164

The difference may be there for the last three digits for the values

we have already logged a ticket with an id: CELLSJAVA-40203. We will look into it to fix it soon.

Thank you.
6

Hi,


The issue has been found replicable and even if the workbook is saved to the disk by disabling the “Auto Calculate Formula” of MS-Excel, the issue can be noticed in the resultant workbook.
7

Thanks everyone for quick reply and support.

Regards

Ashok

8

Please try to read the value from ASPOSE java example,

after calling calculateFormula method you will see the diffrence.

Definetly this is an issue.

Regards

Ashok

9

You can test by this way.

1. Disable auto calculation feature in Demo_Calculator.xls

2. Run the java example.

3. Open CalculatorOutput.xls

4. Here you will see ASPOSE calculated value.

5. Now press F9, and you will see the values are changed

regards

Ashok

10

Hi,


Yes we can notice that and as you can see that Shakeel has already created a ticket for that. We will get back to you once we have any update from our development team.
11

Hi,


I was able to notice the issue. Thanks for your further comments.
12

Hi,

After closely looking into this issue, we found it was caused by precision of Function XIRR.

Excel uses an iterative technique for calculating XIRR( http://msdn.microsoft.com/en-us/library/bb224771(v=office.12).aspx ).

Aspose.Cells uses NewtonRaphson iterative technique.

We could not get the same result of MS Excel now because the iterative technique of MS Excel is transparent for us.

But we think the result of Aspose.Cells is better than the result of Excel.

Please check the cell “H16” in the attached Demo_Calculator_1.xls, the value of the cell should be close to zero.

But the result of Excel is about -1 and the result is of Aspose.Cells is about 0.3E-07

13

Thanks for the update. You mean to say that the iterative technique of MS Excel is not transparent for calculatiing XIRR and ASPOSE using NewtonRaphson technique.

Is it possible to change ASPOSE technique in case you found the MS Excel technique to calculate XIRR?

14

Hi,

Thanks for your question and using Aspose.Cells.

The iterative technique of MS Excel is transparent for us.

We do not know how MS Excel calculate the function XIRR.