Discrepancy in Excel Formula calculation

[Saloni Goyal]

I was working on Accrued Interest Calculation using Excel. Below is my code

 public double CalculateAccruedInterest(CalculateAccruedInterestInput input)

 {

   string currentDirectory = Directory.GetCurrentDirectory();

   string filePath = Path.Combine(currentDirectory, "ExcelFinancialFunctions.xlsx");

   double accruedInterest = 0;

   // Create a new instance of Excel Engine

   using (var excelEngine = new ExcelEngine())

   {

     // Create a new instance of Excel application

     IApplication application = excelEngine.Excel;

     // Open the workbook using a FileStream

     using (var fileStream = new FileStream(filePath, FileMode.Open, FileAccess.Read))

     {

       IWorkbook workbook = application.Workbooks.Open(fileStream);

       IWorksheet worksheet = workbook.Worksheets[0];

       worksheet.EnableSheetCalculations();

       application.EnableIncrementalFormula = true;

       // Set values in cells using input data (1-based index)

       worksheet.Range["A1"].Text = input.PreviousCouponDate.ToString("yyyy-MM-dd"); // PreviousCouponDate

       worksheet.Range["B1"].Text = input.NextCouponDate.ToString("yyyy-MM-dd"); // NextCouponDate

       worksheet.Range["C1"].Text = input.SettlementDate.ToString("yyyy-MM-dd"); // SettlementDate

       worksheet.Range["D1"].Number = input.Coupon; // Coupon

       worksheet.Range["E1"].Number = input.ResultantCF; // ResultantCF

       worksheet.Range["F1"].Number = input.CouponFrequency; // CouponFrequency

       worksheet.Range["G1"].Number = input.AccrualId; // AccrualId

       // worksheet.CalcEngine.UpdateCalcID();

       worksheet.Calculate();

       // Force Excel to recalculate all formulas

       // Fetch the result from cell H1 (1-based index)

       var result = worksheet["H1"].CalculatedValue; // H1

       if (result != null && double.TryParse(result.ToString(), out double parsedResult))

       {

         accruedInterest = parsedResult;

       }

       else

       {

         throw new Exception($"The result in H1 is not a valid number. Value: {result}");

       }

       // Save and close the workbook

       workbook.Close();

     }

   }

   return accruedInterest;

 }


I pass data as 

 previousCouponDate: "2024-05-30T00:00:00Z"

    nextCouponDate: "2027-12-30T00:00:00Z"

    settlementDate: "2028-12-12T00:00:00Z"

    coupon: 0.0564

    resultantCF: 300200033

    couponFrequency: 1

    accrualId: 1


And it returns

      "accruedInterest": 76779239.31036784


But result by Excel is

76836258

Why there is difference in results. If I enter any value till current year 2024, it give correct result but for future years, it is not working.

What I am missing?

Please check this on urgent basis.

[Atchaya Sekar]

Hi Saloni,

 

Could you please share the formula used in the H1 cell, or as requested, share the input Excel file so we can validate the reported issue from our end? It will help us proceed further and provide assistance.

 

 

Regards,

Atchaya S.

[Saloni Goyal]

Hi,

Below is the formula I am using 

ACCRINT(A1, B1, C1, D1, E1, F1, G1)

Below is the code if I try to directly call from the code. But on entering same values in excel and on applying formula in H1, the results are different.

  public static void ACCRINTExcelFile_Syncfusion()

  {

    // Define parameters

    DateTime previousCouponDate = new DateTime(2024, 5, 30);

    DateTime nextCouponDate = new DateTime(2027, 12, 30);

    DateTime settlementDate = new DateTime(2028, 12, 12);

    decimal coupon = 0.0564m; // Example coupon rate

    decimal resultantCF = 300200033m; // Example resultant flow

    int couponFrequency = 1; // Semi-annual payments

    int accrualId = 1; // Example ID

    // Get the current directory and set the file path

    string currentDirectory = Directory.GetCurrentDirectory();

    string filePath = Path.Combine(currentDirectory, "AccrualCalculation_Syncfusion.xlsx");

    // Create a new Excel engine and application

    using (ExcelEngine excelEngine = new ExcelEngine())

    {

      IApplication application = excelEngine.Excel;

      application.DefaultVersion = ExcelVersion.Excel2016;

      // Create a workbook with one worksheet

      Syncfusion.XlsIO.IWorkbook workbook = application.Workbooks.Create(1);

      IWorksheet worksheet = workbook.Worksheets[0];

      worksheet.EnableSheetCalculations();

      worksheet.Name = "Accrual Calculation";

      // Insert headers

      worksheet.Range["A1"].Text = "Previous Coupon Date";

      worksheet.Range["B1"].Text = "Next Coupon Date";

      worksheet.Range["C1"].Text = "Settlement Date";

      worksheet.Range["D1"].Text = "Coupon Rate";

      worksheet.Range["E1"].Text = "Resultant CF";

      worksheet.Range["F1"].Text = "Coupon Frequency";

      worksheet.Range["G1"].Text = "Accrual ID";

      worksheet.Range["H1"].Text = "ACCRINT Formula";

      // Insert values into cells

      worksheet.Range["A2"].DateTime = previousCouponDate;

      worksheet.Range["B2"].DateTime = nextCouponDate;

      worksheet.Range["C2"].DateTime = settlementDate;

      worksheet.Range["D2"].Number = (double)coupon;

      worksheet.Range["E2"].Number = (double)resultantCF;

      worksheet.Range["F2"].Number = couponFrequency;

      worksheet.Range["G2"].Number = accrualId;

      // Insert the ACCRINT formula (Syncfusion doesn't support ACCRINT natively, so you can insert a custom formula or calculate externally)

      // Example: You can write a placeholder here to calculate manually or insert the formula for Excel

      worksheet.Range["H2"].Formula = "ACCRINT(A2, B2, C2, D2, E2, F2, G2)"; // Excel will calculate this formula when opened

      worksheet.CalcEngine.UpdateCalcID();

      // Auto fit the columns for better readability

      worksheet.UsedRange.AutofitColumns();

      var resultCell = worksheet["H2"].CalculatedValue;

      Console.WriteLine(resultCell);

      // Save the workbook to the specified path

      using (FileStream fileStream = new FileStream(filePath, FileMode.Create, FileAccess.ReadWrite))

      {

        workbook.SaveAs(fileStream);

      }

      // If you want to retrieve the calculated value from the ACCRINT formula, you must open the file in Excel or pre-calculate it in code

      Console.WriteLine("Excel file generated at: " + filePath);

    }

  }

Can you please check this.

Thanks

[Atchaya Sekar]

Hi Saloni goyal ,

We have reproduced the reported issue on our end and are currently validating it. We will share the validation details on September 23, 2024.

Regards,

Atchaya S.

[Atchaya Sekar]

Hi Saloni goyal,

 

We have investigated the reported issue and identified that the calculated value returns incorrect results. Consequently, the reported issue, "ACCRINT formula gives incorrect calculated value compared to MS Excel" has been confirmed as a defect. We have logged a bug report for this issue, and we will fix it and include the solution in our NuGet release scheduled on October 15, 2024. 

 

Feedback link - Calculated value returns incorrect results while loading in WinForms | Feedback Portal (syncfusion.com)

We will let you know once it is released. We appreciate your patience until then.

 

Disclaimer: Inclusion of this solution in the weekly release may change due to other factors including but not limited to QA checks and works reprioritization.

 

Regards,

Atchaya S.

[Yaavann Vignesh Sethuraman]

Hi Saloni goyal,

We apologize for the inconvenience. Unfortunately, we are unable to include the fix for the issue "ACCRINT formula gives incorrect calculated value compared to MS Excel" in our NuGet package released as promised today. We are currently experiencing challenges with the year fraction between the issue date and the settlement date, resulting in discrepancies in the Excel output. We are actively working on it. We will fix this issue and include it in our weekly NuGet release which is scheduled for October 22, 2024.

Regards,
Yaavann.

[Yaavann Vignesh Sethuraman]

Hi Saloni Goyal,

We apologize for any inconvenience this may have caused. After the detailed analysis of the reported issue with the ACCRINT formula returning different values than Microsoft Excel, we attempted to reproduce Excel’s calculation logic in our CalcEngine. 

Despite our efforts, we were unable to pinpoint the exact method Excel uses to derive its results.

Additionally, we verified this case in Google Sheets, which also returned a different result from Excel. Other third-party products we reviewed also did not produce the same value as Excel. Given these findings, we have concluded that we cannot replicate Excel’s exact ACCRINT behavior at this time. Thank you for your understanding.

Regards,
Yaavann.