Workbook.CalculateFormula() doesn't compute IRR correctly for SECOND IRR calculation

Aspose.Cells Product Family
1

Support Team,


I think I have found an additional issue with the IRR function. We had an issue earlier this year and a fix was made that corrected the problem. Now our business has updated their source spreadsheet to contain several IRR calculations. The first row of calculations seem to work correctly, but on the second the calculations stop generating results partway across the row.

See the original post here:
Workbook.CalculateFormula() doesn't compute IRR correctly

I have attached an updated sample application that demonstrates the problem. If you run the project and open the created output.xls file you will see that starting at cell Q6 the calculated values are #NUM. I have automatic formula calculation turned off so Excel will not fix the problem before you see it. If you trigger the formula calculation the values calculate correctly.

My production application has been running under version 7.1.1. I have tried all the version up through 7.3.3.1 with the same results. Also starting with version 7.3.1, my full application which uses an XSLM source spreadsheet fails and reports an invalid or corrupted file. Saving the spreadsheet to the disk creates a 0 byte file. I don’t know if these two issues are related or not. One post indicated that 7.3.1 fixed the issue with XSLM files.

Thanks for your help,
Doug Webb

2

Hi,

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

After initial investigation, we were able to notice this issue using the latest version:
Aspose.Cells
for .NET v7.3.3.4


We have logged it in our database and we will look into it further. Once the issue is resolved or we have some other update for you, we will let you know asap.

This issue has been logged as CELLSNET-41203.

C#


void TestRun()

{

var inputs1 = new[] { -3529.68, 267.38, 264.66, 262.32, 260.32, 258.64, 257.26, 256.16, 257.56, 259.47, 261.44, 263.44, 265.50, 267.59, 269.75, 271.94, 274.19, 276.48, 278.84, 281.23, 265.86, 250.53, 253.09, 255.70, 258.37, 261.09, 260.96, 260.82, 260.69, 260.55, 260.41, 260.27, 260.13, 259.99, 259.85, 259.70, 259.56, 259.41, 259.26, 259.11, 258.95 };

var inputs2 = new[] { -3096.88, 267.38, 264.66, 262.32, 260.32, 258.64, 257.26, 256.16, 257.56, 259.47, 261.44, 263.44, 265.50, 267.59, 269.75, 271.94, 274.19, 276.48, 278.84, 281.23, 265.86, 250.53, 253.09, 255.70, 258.37, 261.09, 260.96, 260.82, 260.69, 260.55, 260.41, 260.27, 260.13, 259.99, 259.85, 259.70, 259.56, 259.41, 259.26, 259.11, 258.95 };


var workbook = new Workbook(“IRRTest.xlsx”);


var worksheet = workbook.Worksheets[“Test”];


worksheet.Cells.ImportArray(inputs1, 1, 3, false);

worksheet.Cells.ImportArray(inputs2, 4, 3, false);


workbook.CalculateFormula();


CheckIrr(worksheet, “N3”);

CheckIrr(worksheet, “AR3”);


CheckIrr(worksheet, “N6”);

// This next line will error out when uncommented

CheckIrr(worksheet, “AR6”);


workbook.Save(“output.xlsx”);

Console.WriteLine(“Done”);


}


static private void CheckIrr(Worksheet worksheet, string cell)

{

var irr = worksheet.Cells[cell].DoubleValue;

Console.WriteLine(cell + " = " + irr + “%”);

}




3

Hi,

Thanks for using Aspose.Cells for .NET

We have fixed this issue.

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

4
Shakeel,

I have been unable to verify this fix in my application because my source file is an XSLM file, which is still giving an file format invalid error message. In my testing of various versions, this error started again with 7.3.1 and was reportedly fixed (www.aspose.comhttps://forum.aspose.com/t/103082) in 7.3.3.1 but this version did not work for me.

Please advise what the next step that needs to be taken.

Thanks,
Doug
5

Hi,

Thanks for your input and using Aspose.Cells.

This error is quite a generic error and Excel can throw it because of many know/unknown reasons. We are constantly working on this.

By the way, I have tested your file (IRRTest.xlsx) with the given code in my above post and the new version works fine without any problem.

Please provide us your test code and the source xlsm file which is throwing error. We will look into it further and fix it.

6

Ok, that makes sense. I was unable to reproduce the problem within the context of my test project, but after some trial and error I figured out what has changed. I am doing something like this in my application:



var workbookAsStream = new MemoryStream();
workbook.Save(workbookAsStream, SaveFormat.Xlsm);
…
…
return new FileStreamResult(workbookAsStream, results.MimeType()) { FileDownloadName = fileName }; 

This returned a file with 0 bytes.  But when I added this line after the workbook.Save line it works as expected.
workbookAsStream.Position = 0; 
I will keep testing.
Doug

7

Hi,

Thanks for your feedback.

I think, you have found the root of your problem. The stream was already at the end of position but once you set it at the beginning, it worked fine.

If you encounter any other issue, please feel free to post, we will be glad to help you further.

8

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

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