Issue 95167 - XIRR() function gives Err:502
Summary: XIRR() function gives Err:502
Status: ACCEPTED
Alias: None
Product: Calc
Classification: Application
Component: code (show other issues)
Version: OOo 3.0.1
Hardware: All All
: P3 Normal (vote)
Target Milestone: ---
Assignee: AOO issues mailing list
QA Contact:
URL:
Keywords: oooqa
Depends on:
Blocks:
 
Reported: 2008-10-19 22:22 UTC by grummund
Modified: 2017-05-20 10:44 UTC (History)
6 users (show)

See Also:
Issue Type: ENHANCEMENT
Latest Confirmation in: 4.2.0-dev
Developer Difficulty: Easy


Attachments
Example spreadsheet showing XIRR() problem (13.74 KB, application/vnd.oasis.opendocument.spreadsheet)
2008-10-19 22:37 UTC, grummund
no flags Details
Example demonstrating the issue (14.17 KB, text/plain)
2009-06-18 12:46 UTC, grummund
no flags Details
Testcase example (12.73 KB, text/plain)
2009-06-18 14:26 UTC, grummund
no flags Details
Patch for finanical.cxx (1.98 KB, patch)
2015-04-04 14:06 UTC, Driss
no flags Details | Diff
New patch for the financial.cxx (2.05 KB, patch)
2015-04-05 20:26 UTC, Driss
driss.zoubeir: review?
rb.henschel: review+
Details | Diff

Note You need to log in before you can comment on or make changes to this issue.
Description grummund 2008-10-19 22:22:45 UTC
XIRR() function gives Err:502 when annualised return is < -30%.

Tested with OO 2.4.1 on WinXP, and 2.4.0 on Debian Etch.

An example spreadsheet is with the Debian bug report at:

http://bugs.debian.org/cgi-bin/bugreport.cgi?bug=502796
Comment 1 grummund 2008-10-19 22:37:34 UTC
Created attachment 57275 [details]
Example spreadsheet showing XIRR() problem
Comment 2 Regina Henschel 2008-10-22 00:29:33 UTC
Please have a look at issue 27597. Do you get a correct value if you add a guess
different from the default?
Comment 3 grummund 2009-06-18 12:46:04 UTC
Created attachment 63060 [details]
Example demonstrating the issue
Comment 4 grummund 2009-06-18 12:55:02 UTC
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.
Comment 5 grummund 2009-06-18 13:02:59 UTC
I should add that the last attachment was created using OO 3.0.1 on WinXP. 
OOO300m15 (Build:9379)
Comment 6 grummund 2009-06-18 14:26:51 UTC
Created attachment 63066 [details]
Testcase example
Comment 7 grummund 2009-06-18 14:29:42 UTC
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.
Comment 8 Rob Weir 2013-02-12 02:11:03 UTC
Confirmed in AOO 3.4.1.  Good find.  Perhaps the internal iteration count needs to be increased?
Comment 9 Driss 2015-03-28 23:27:16 UTC
Hi,
I want to work on this bug, could you assign it to me?

Regards
Driss
Comment 10 Ariel Constenla-Haile 2015-03-29 05:58:12 UTC
(In reply to Driss from comment #9)
> Hi,
> I want to work on this bug, could you assign it to me?

Done.
Comment 11 Ariel Constenla-Haile 2015-03-29 06:22:30 UTC
@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
Comment 12 Driss 2015-03-29 22:04:10 UTC
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?
Comment 13 Ariel Constenla-Haile 2015-04-04 13:47:25 UTC
(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.
Comment 14 Driss 2015-04-04 14:06:18 UTC
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 :)
Comment 15 Driss 2015-04-05 20:26:26 UTC
Created attachment 84634 [details]
New patch for the financial.cxx

here is the new patch ::rtl::math::isNan and ::rtl::math::isInf
Comment 16 Regina Henschel 2015-04-06 16:01:34 UTC
Comment on attachment 84634 [details]
New patch for the financial.cxx

Committed as http://svn.apache.org/r1671558
Comment 17 Marcus 2017-05-20 10:44:23 UTC
Reset the assignee to the default "issues@openoffice.apache.org".