Issue 95167

Created attachment 57275 [details]
Example spreadsheet showing XIRR() problem

Please have a look at issue 27597. Do you get a correct value if you add a guess
different from the default?

Created attachment 63060 [details]
Example demonstrating the issue

regina,

I attached a file to demonstrate this problem using the guess field.

http://www.openoffice.org/nonav/issues/showattachment.cgi/63060/OO_XIRR.ods

As you can see, entering a guess value does allow XIRR() to compute a result. 
However, it's not easy to manually find a guess value that works.

The issue seems most accute for negative values.

HTH.

I should add that the last attachment was created using OO 3.0.1 on WinXP. 
OOO300m15 (Build:9379)

Created attachment 63066 [details]
Testcase example

Please ignore the previous attachments and use the latest testcase instead:

http://www.openoffice.org/nonav/issues/showattachment.cgi/63066/OO_XIRR.ods

This was created with OO 3.1.0, OOO310m11 (Build 9399), WinXP.

Thanks.

Confirmed in AOO 3.4.1.  Good find.  Perhaps the internal iteration count needs to be increased?

Hi,
I want to work on this bug, could you assign it to me?

Regards
Driss

(In reply to Driss from comment #9)
> Hi,
> I want to work on this bug, could you assign it to me?

Done.

@Driss: once you decide to start working in it, change the status to ACCEPTED.
You can start by reading https://svn.apache.org/viewvc/openoffice/trunk/main/scaddins/source/analysis/financial.cxx?revision=1413471&view=markup#l461

In the function Code the Newton's Method was used. The convergence of this Method depends amongst others on the start value fResultRate, which is the third argument of XIRR (guessed rate with default Value= 0,1). In case of divergence one get the err502. Changing the guessed rate can help to get convergence and avoid err502.

I will try to improve the code. If you have any idea or hints how to avoid divergence of Newton's method it would be great.

May be the third argument for XIRR  should not be given by the user but calculated in the code in a manner that divergence will be avoid as far as possible?

(In reply to Driss from comment #12)
> In the function Code the Newton's Method was used. The convergence of this
> Method depends amongst others on the start value fResultRate, which is the
> third argument of XIRR (guessed rate with default Value= 0,1). In case of
> divergence one get the err502. Changing the guessed rate can help to get
> convergence and avoid err502.
> 
> I will try to improve the code. If you have any idea or hints how to avoid
> divergence of Newton's method it would be great.
> 
> May be the third argument for XIRR  should not be given by the user but
> calculated in the code in a manner that divergence will be avoid as far as
> possible?

While searching for a solution try to keep compatibility with the current use; if the function is defined in the standard, follow its specification; try to see what MS Office does, for example https://support.office.com/en-nz/article/XIRR-function-c3cdd4d8-359c-482a-a8ba-7a0052f5c053

Regina is on Cc, sure she can give you a better advice.

Created attachment 84632 [details]
Patch for finanical.cxx

Hi!

please find in the attachment the patch!
The idea behind the Improvement is:
1- Use the guess value given by the user or the default value of 0.1 to calculate the XIRR
2- If those do not lead to solution then try to find a guess between -0.99 and 0.99 (using 0.01 as step value) which can deliver a solution.

If you have any remarks to the solution or to the programming style, please let me know :)

Created attachment 84634 [details]
New patch for the financial.cxx

here is the new patch ::rtl::math::isNan and ::rtl::math::isInf

Comment on attachment 84634 [details]
New patch for the financial.cxx

Committed as http://svn.apache.org/r1671558

Reset the assignee to the default "issues@openoffice.apache.org".