Resolves: #i95167# XIRR() function gives Err:502

Patch by: Driss Ben Zoubeir <driss.zoubeir@gmail.com>
Review by: Regina Henschel <rb.henschel@t-online.de>
(cherry picked from commit 85cd072198d9024c20b741c04a20ef99fc9b5910)

Conflicts:
scaddins/source/analysis/financial.cxx

Change-Id: I385d5f9bfa840fe609cb5723f2d4d57d26fd0d6c

1 files changed, 28 insertions, 7 deletions

diff --git a/scaddins/source/analysis/financial.cxx b/scaddins/source/analysis/financial.cxx
index 309f044da2d3..c51f4e984de8 100644
--- a/scaddins/source/analysis/financial.cxx
+++ b/scaddins/source/analysis/financial.cxx
@@ -541,16 +541,37 @@ double SAL_CALL AnalysisAddIn::getXirr(
 
     // Newton's method - try to find a fResultRate, so that lcl_sca_XirrResult() returns 0.
     sal_Int32 nIter = 0;
-    bool bContLoop;
+    double fResultValue;
+    sal_Int32 nIterScan = 0;
+    bool bContLoop = false;
+    bool bResultRateScanEnd = false;
+
+    // First the inner while-loop will be executed using the default Value fResultRate
+    // or the user guessed fResultRate if those do not deliver a solution for the
+    // Newton's method then the range from -0.99 to +0.99 will be scanned with a
+    // step size of 0.01 to find fResultRate's value which can deliver a solution
     do
     {
-        double fResultValue = lcl_sca_XirrResult( aValues, aDates, fResultRate );
-        double fNewRate = fResultRate - fResultValue / lcl_sca_XirrResult_Deriv1( aValues, aDates, fResultRate );
-        double fRateEps = fabs( fNewRate - fResultRate );
-        fResultRate = fNewRate;
-        bContLoop = (fRateEps > fMaxEps) && (fabs( fResultValue ) > fMaxEps);
+        if (nIterScan >=1)
+            fResultRate = -0.99 + (nIterScan -1)* 0.01;
+        do
+        {
+            fResultValue = lcl_sca_XirrResult( aValues, aDates, fResultRate );
+            double fNewRate = fResultRate - fResultValue / lcl_sca_XirrResult_Deriv1( aValues, aDates, fResultRate );
+            double fRateEps = fabs( fNewRate - fResultRate );
+            fResultRate = fNewRate;
+            bContLoop = (fRateEps > fMaxEps) && (fabs( fResultValue ) > fMaxEps);
+        }
+        while( bContLoop && (++nIter < nMaxIter) );
+        nIter = 0;
+        if (  ::rtl::math::isNan(fResultRate)  || ::rtl::math::isInf(fResultRate)
+            ||::rtl::math::isNan(fResultValue) || ::rtl::math::isInf(fResultValue))
+            bContLoop = true;
+
+        ++nIterScan;
+        bResultRateScanEnd = (nIterScan >= 200);
     }
-    while( bContLoop && (++nIter < nMaxIter) );
+    while(bContLoop && !bResultRateScanEnd);
 
     if( bContLoop )
         throw css::lang::IllegalArgumentException();