Financial and DateTime Formula calculation issues

Aspose.Cells Product Family
1

Hello,

We’ve encountered several issues with Excel formula calculation, when performing Workbook.CalculateFormula(…), we’ve noticed some differences between Aspose calculation and Excel calculation.

We’ve a couple of tests files with the formulas being tested. In the test files there are three columns:
- Formula (formula being tested)
- Expected Value - the value as given by Excel
- Aspose Value - the value as given by Aspose when performing Workbook.CalculateFormula() over the excel file

The following functions from the TestsFinancial.xls seems to not be calculated correctly by Aspose:
Formula Excel Value Aspose Value
DURATION 1.311592665 1.356791808
MDURATION 1.272709892 1.279615326
PPMT 0 NaN.
YIELDMAT 30.11203354 30.61390077
XIRR 0.885516822 0.885516825
ODDFYIELD 26.62813649 0

The following functions from the TestsDateTime.xls seems to not be calculated correctly by Aspose (note: For the Excel 2010 functions, Aspose returns #NAME? as the result):
Formula Excel Value Aspose Value
WORKDAY 39681 39680
NETWORKDAYS.INTL 261 #NAME?
WORKDAY.INTL 40081 #NAME?

Please investigate these issues and provide us some feedback.

Thanks in advance,
Mihai Andrei,
Sr. Software Engineer,
IBM Romania

2

Hi,


Thank you for using Aspose.Cells.

We will look into your sample files for the issues you have mentioned, and provide our feedback.
3

Hi,

Thank you for being patient.

After an initial test using your sample excel files and the following lines of code, I can notice the issues as you have mentioned.

I have forwarded these details to our development team for further investigation and rectification of these. We will update you here once we have any information about the resolution of this issue from our development team. The issue has been logged as: CELLSNET-40879.

Workbook w = new Workbook(“TestsDateTime.xls”);

w.CalculateFormula();

w.Settings.CalcMode = CalcModeType.Manual;

w.Save(“TestsDateTimeOut.xls”);

4

Hi,

Thanks for using Aspose.Cells.

We have fixed this issue.

Please download and try this fix: Aspose.Cells for .NET v7.3.0.5 and let us know your feedback.

5

Hi!

The following formulas are not working using the latest version 7.3.2.0:

NETWORKDAYS.INTL, NOW, TODAY, WORKDAY, WORKDAY.INTL.

Please use the initial attachments with the latest version to reproduce the issues!

Thank you

6

These formulas are not working either using 7.3.2.0 version:

DURATION, INTRATE, MDURATION, PPMT, YIELD, YIELDMAT, XIRR, ODDFYIELD

Thank you.

7

Hi,

Thanks for your feedback.

We have reopened this issue in our database. We will fix the above mentioned functions and update you asap.

Also, we have logged your comments in our database against the issue id: CELLSNET-40879

8

The issues you have found earlier (filed as CELLSNET-40879) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by aspose.notifier.
9

Hello,

Tested against Aspose.Cells 8.1.0.0.

XIRR formula still produces a different value - 0.885516825, Excel calculates it as 0.885516822.

Any feedback will be highly appreciated.

Thank you,
Mihai Andrei
Sr Software Engineer
IBM Romania

10

Hi Mihai,


Thank you for reverting back.

We have evaluated the XIRR formula calculation against latest build of Aspose.Cells for .NET 8.0.1.3, and found some minor variation in the results produced by MS Excel & Aspose.Cells API. In order to further investigate the matter, we have logged a separate ticket CELLSNET-42593 in our bug tracking system. We will soon evaluate the presented scenario in more detail, and will keep you posted with updates in this regard.
11
Hi,

For the precision of those formulas, we are afraid we can only calculate them as current values. We will make further investigation for it, but we are afraid we cannot give a solution (if there is any) soon.

We are sorry for any inconvenience caused!