Unable to get the result for excel functions

[Nivas]

I tried implementing XIRR function as following, but I'm getting #VALUE! error instead of actual result.

My sample input: dates = { 3/6/2022, 7/4/2022} , values = {-150.52, 100.17}, guess = 0.1

Code:

public static double XIRR(List<DateTime> dates, List<double> values, double guess)

        {

            using (ExcelEngine excelEngine = new ExcelEngine())

            {

                IWorkbook wb = excelEngine.Excel.Workbooks.Create();

                IWorksheet irrSheet = wb.Worksheets[0];

                irrSheet.EnableSheetCalculations();

                IRange cells = irrSheet.Range;

                string dateRange;

                string valueRange;

                string guessCell;

                for (int index = 1; index <= dates.Count; index++)

                {

                    cells[index, 1].Value2 = dates[index-1].ToOADate();

                    cells[index, 2].Value2 = values[index-1];

                }

                cells[dates.Count, 3].Value2 = guess;

                dateRange = string.Format("A1:A{0}", dates.Count);

                valueRange = string.Format("B1:B{0}", dates.Count);

                guessCell = string.Format("C{0}", dates.Count);

                cells[dates.Count, 4].Formula =

                    string.Format("=XIRR({0},{1},{2})", valueRange, dateRange, guessCell);

                object result = cells[dates.Count, 4].CalculatedValue;

                irrSheet.DisableSheetCalculations();

                wb.Close();

                return result.GetType().Equals("Error") ? 0 : ((double)result) * 100;

            }

        }

[Ramya Sivakumar]

Hi Nivas,

Greetings from Syncfusion.

We are unable to reproduce the #VALUE! Error. While trying to access the calculated value we get the “#NUM!” error and we are facing the System.InvalidCastException.

We have attached the sample which was tried at our end. Kindly share a sample illustrating the issue or else modify the following sample and reproduce the issue which would help us to proceed further.

Sample link - https://www.syncfusion.com/downloads/support/directtrac/general/ze/F-176014130607128

kindly confirm the Syncfusion XlsIO Package version and the System culture which you are using at your end.

Regards,

Ramya.

[Nivas]

Hi Ramya,

For now ignore the casting exception. All I want is to get a valid result for 'result' variable. I tried even at my end with the sample solution that you have provided, it's still #NUM! for the sample I/P which I have provided above. One thing is, in the sample input it is march-6 and july-4 not june-3 and april-7 (the first date cannot be preceded by the following dates). Still, I got the #NUM! where as in excel sheet, I got the value as -0.71. So I think it is a valid input. FYI, I'm using Syncfusion.XlsIO.Net.Core version 20.1.0.60.

Thanks, 

Nivas.

[Ramya Sivakumar]

Hi Nivas,

Thank you for the update.

We can reproduce the reported scenario at our end and forward it to the concerned team for validation. We will share the validation details in 2 business days, on July 12, 2022.

Regards,

Ramya.

[Nivas]

Hi Ramya, 

Thanks for the update. Also, I want to raise an issue for one other function i.e. IRR(). When I try IRR() with the above values list and guess value of 0.1, I'm getting 5.8417238442185474E+29 as the result which is not the case when trying it in an excel sheet where I'm getting -33% as the output. So, please look into this too and provide an update ASAP

Thanks, 

Nivas.

[Ramya Sivakumar]

Hi Nivas,

We regret for the inconvenience caused.

We are unable to reproduce the IRR() function issue. If we use the IRR() for the provided code snippet, we get the value as “wrong number of arguments” and the generated output file is corrupted. So kindly share the IRR formula which you are using at your end with the code snippet which would be help us to proceed further.

Regards,

Ramya.

[Nivas]

Hi Ramya, 

The only difference b/w XIRR and IRR is IRR doesn't have date range. So, in the above code just remove the date range and in formula replace XIRR with IRR and run the same solution and let me know if it is reproducible.

Thanks,

Nivas.

[Nivas]

Hi Ramya,

Any update on this?

[Konduru Keerthi Konduru Ravichandra Raju]

Hi Nivas,

We appreciate your patience.

Regarding XIRR:

We have confirmed the issue as XIRR formula returns incorrect result when the argument total value is negative and logged a defect report. We will include the fix for this issue in our weekly NuGet release scheduled for August 2^nd, 2022. You can track the status of the defect report through the following feedback link.

Track Status: https://www.syncfusion.com/feedback/36255/xirr-formula-return-incorrect-result-when-the-argument-total-value-is-negative

Regarding IRR:

We are able to reproduce the reported issue and forwarded it to the concerned team for validation. We will share the validation details on July 14^th, 2022.

Regards,

Keerthi.

[Nivas]

Thank you. But I'm not able access the track status link. It's just showing access denied.

Regards,

Nivas.

[Ramya Sivakumar]

Hi Nivas,

You’re welcome.

Regarding XIRR:

We have added your account to access the feedback portal. Kindly clear your cache and try log off and login with Syncfusion credentials once again. Let us know if still issue persist.

Track Status: https://www.syncfusion.com/feedback/36255/xirr-formula-return-incorrect-result-when-the-argument-total-value-is-negative

Regarding IRR:

We have confirmed the issue as IRR formula returns incorrect result when the argument total value is negative
and logged a defect report. We will include the fix for this issue in our weekly NuGet release scheduled for August 2, 2022. You can track the status of the defect report through the following feedback link.

Track Status: https://www.syncfusion.com/feedback/36294/irr-formula-returns-incorrect-result-when-the-argument-total-value-is-negative

Regards,

Ramya.

[Nivas]

Hi Ramya,

Any update on this?

[Ramya Sivakumar]

Hi Nivas,

Sorry for the delay.

Regarding XIRR:

We are unable to fix the issue due to complexity. We will include this fix in our weekly NuGet release scheduled for mid of August 2022, and let you know once the package is available to download from nuget.org.

Regarding IRR:

The weekly NuGet release planned for August 2, 2022, has been called off due to the 2022 Vol2 Service Pack release. Hence, we will include the fix in the 2022 Vol2 Service Pack release planned for August 8, 2022. We will let you know once the release is rolled out.

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

Regards,

Ramya.

[Nivas]

Hi Ramya,

Do we have any update on IRR function?

[Ramya Sivakumar]

Hi Nivas,

We are glad to announce that our Essential Studio 2022 Volume 2 SP release v20.2.0.43 is rolled out and is available for download under the following link.

https://www.syncfusion.com/forums/176701/essential-studio-2022-volume-2-service-pack-release-v20-2-0-43-is-available-for-download

We have included the fix to resolve the issue IRR formula returns incorrect result when the argument total value is negative in our 2022 Volume 2 SP release.

Track Status: https://www.syncfusion.com/feedback/36294/irr-formula-returns-incorrect-result-when-the-argument-total-value-is-negative

We thank you for your support and appreciate your patience in waiting for this release. Please get in touch with us if you would require any further assistance.

Regards,

Ramya.

[Konduru Keerthi Konduru Ravichandra Raju]

Hi Nivas,

We appreciate your patience.

Regarding XIRR:

On further analysis, we could not find proper expansion and implementation for XIRR formula. Hence, we have raised a query regarding this in stack overflow requesting the code implementation. Please find the link below.

https://stackoverflow.com/questions/73329728/how-to-calculate-the-xirr-formula-in-c-sharp

We will get back to you once we get the details.

Regards,

Keerthi.

[Nivas]

Hi Ramya/Keerthi,

Do we have any update on XIRR?

[Konduru Keerthi Konduru Ravichandra Raju]

Hi Nivas,

We have requested details from the concerned team and will get back to you once we get the details.

We appreciate your patience.

Regards,

Keerthi.

[Konduru Keerthi Konduru Ravichandra Raju]

Hi Nivas,

We appreciate your patience.

We have checked the solution provided under stackoverflow query but could not find proper explanation and implementation for XIRR formula. However, we can support XIRR formula with any of the below observations.

Case – 1: Avoid passing the third argument or guess value in the formula.

Case – 2: Have the default guess value as -0.9

We request you to confirm the case that you are comfortable with. Also let us know the reason for passing the guess value as 0.1 and possibility to change the guess value while passing the argument.

Regards,

Keerthi.