I have this code which is calculating ACCRINT using excel in-built formula.
public static void ACCRINTExcelFile_Aspose()
{
// 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 cash flow
int couponFrequency = 1; // Semi-annual payments
int accrualId = 1; // Example ID
// Manually calculate the ACCRINT value based on your inputs
int daysBetweenSettlementAndPrevCoupon = (settlementDate - previousCouponDate).Days;
int daysBetweenCoupons = (nextCouponDate - previousCouponDate).Days;
decimal accrintValue = (decimal)daysBetweenSettlementAndPrevCoupon / daysBetweenCoupons * coupon * resultantCF;
// Print the manually calculated ACCRINT value to the console
Console.WriteLine($"Manually Calculated ACCRINT value: {accrintValue}");
// Get the current directory and set the file path
string currentDirectory = Directory.GetCurrentDirectory();
string filePath = Path.Combine(currentDirectory, "AccrualCalculation_Aspose.xlsx");
// Create a new workbook and worksheet
Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();
Aspose.Cells.Worksheet worksheet = workbook.Worksheets[0];
worksheet.Name = "Accrual Calculation";
// Insert headers
worksheet.Cells["A1"].PutValue("Previous Coupon Date");
worksheet.Cells["B1"].PutValue("Next Coupon Date");
worksheet.Cells["C1"].PutValue("Settlement Date");
worksheet.Cells["D1"].PutValue("Coupon Rate");
worksheet.Cells["E1"].PutValue("Resultant CF");
worksheet.Cells["F1"].PutValue("Coupon Frequency");
worksheet.Cells["G1"].PutValue("Accrual ID");
worksheet.Cells["H1"].PutValue("ACCRINT Formula");
// Insert values into cells
worksheet.Cells["A2"].PutValue(previousCouponDate);
worksheet.Cells["B2"].PutValue(nextCouponDate);
worksheet.Cells["C2"].PutValue(settlementDate);
worksheet.Cells["D2"].PutValue(coupon);
worksheet.Cells["E2"].PutValue(resultantCF);
worksheet.Cells["F2"].PutValue(couponFrequency);
worksheet.Cells["G2"].PutValue(accrualId);
// Insert the ACCRINT formula (Aspose.Cells supports this but it will only work if the formula is correctly recognized by Excel)
worksheet.Cells["H2"].Formula = "ACCRINT(A2, B2, C2, D2, E2, F2, G2)";
// Auto fit columns for better readability
workbook.CalculateFormula();
// Retrieve the calculated value
Aspose.Cells.Cell accrintCell = worksheet.Cells["H2"];
double accrintValuenew = accrintCell.DoubleValue;
// Print the calculated ACCRINT value to the console
Console.WriteLine($"Calculated ACCRINT value: {accrintValuenew}");
// Save the workbook to the specified path
workbook.Save(filePath);
// Notify the user of the file's creation and location
Console.WriteLine($"Excel file generated at: {filePath}");
}
But the result returned by it is wrong in some cases as comapred to excel