Check for divide by 0 in Calc function NPER.

Plus improve efficiency and use correct prefixes for variable names.

Change-Id: I2ba0863a57aad4c89bc930fa69b08ed90b070002
Reviewed-on: https://gerrit.libreoffice.org/34902
Tested-by: Jenkins <ci@libreoffice.org>
Reviewed-by: Eike Rathke <erack@redhat.com>

2 files changed, 49 insertions, 17 deletions

diff --git a/sc/qa/unit/data/functions/financial/fods/nper.fods b/sc/qa/unit/data/functions/financial/fods/nper.fods
index 3911ba9292e7..22c06f914a25 100644
--- a/sc/qa/unit/data/functions/financial/fods/nper.fods
+++ b/sc/qa/unit/data/functions/financial/fods/nper.fods
@@ -1528,16 +1528,42 @@
      <table:table-cell table:number-columns-repeated="9"/>
     </table:table-row>
     <table:table-row table:style-name="ro6">
-     <table:table-cell table:number-columns-repeated="2"/>
-     <table:table-cell table:style-name="ce25"/>
-     <table:table-cell table:number-columns-repeated="6"/>
+     <table:table-cell table:style-name="ce48" table:formula="of:=NPER(0.06/52; 0; 8000; 0 ;2)" office:value-type="string" office:string-value="" calcext:value-type="error">
+      <text:p>Err:502</text:p>
+     </table:table-cell>
+     <table:table-cell table:formula="of:#ERR502!" office:value-type="string" office:string-value="" calcext:value-type="error">
+      <text:p>Err:502</text:p>
+     </table:table-cell>
+     <table:table-cell table:style-name="ce53" table:formula="of:=ORG.OPENOFFICE.ERRORTYPE([.A12])=502" office:value-type="boolean" office:boolean-value="true" calcext:value-type="boolean">
+      <text:p>TRUE</text:p>
+     </table:table-cell>
+     <table:table-cell table:formula="of:=FORMULA([.A12])" office:value-type="string" office:string-value="=NPER(0.06/52, 0, 8000, 0 ,2)" calcext:value-type="string">
+      <text:p>=NPER(0.06/52, 0, 8000, 0 ,2)</text:p>
+     </table:table-cell>
+     <table:table-cell office:value-type="string" calcext:value-type="string">
+      <text:p>No payment, no result</text:p>
+     </table:table-cell>
+     <table:table-cell table:number-columns-repeated="4"/>
      <table:table-cell table:style-name="ce20" table:number-columns-repeated="2"/>
      <table:table-cell table:number-columns-repeated="9"/>
     </table:table-row>
     <table:table-row table:style-name="ro6">
-     <table:table-cell table:number-columns-repeated="2"/>
-     <table:table-cell table:style-name="ce26"/>
-     <table:table-cell table:number-columns-repeated="6"/>
+     <table:table-cell table:formula="of:=NPER(0.06/52; -175;8000;-8000;1)" office:value-type="float" office:value="0" calcext:value-type="float">
+      <text:p>0</text:p>
+     </table:table-cell>
+     <table:table-cell office:value-type="float" office:value="0" calcext:value-type="float">
+      <text:p>0</text:p>
+     </table:table-cell>
+     <table:table-cell table:style-name="ce56" table:formula="of:=ROUND([.A13];12)=ROUND([.B13];12)" office:value-type="boolean" office:boolean-value="true" calcext:value-type="boolean">
+      <text:p>TRUE</text:p>
+     </table:table-cell>
+     <table:table-cell table:formula="of:=FORMULA([.A13])" office:value-type="string" office:string-value="=NPER(0.06/52, -175,8000,-8000,1)" calcext:value-type="string">
+      <text:p>=NPER(0.06/52, -175,8000,-8000,1)</text:p>
+     </table:table-cell>
+     <table:table-cell office:value-type="string" calcext:value-type="string">
+      <text:p>&apos;present value = future value’</text:p>
+     </table:table-cell>
+     <table:table-cell table:number-columns-repeated="4"/>
      <table:table-cell table:style-name="ce41"/>
      <table:table-cell table:style-name="ce43"/>
      <table:table-cell table:style-name="ce41" table:number-columns-repeated="3"/>
@@ -1760,4 +1786,4 @@
    </table:named-expressions>
   </office:spreadsheet>
  </office:body>
-</office:document>
\ No newline at end of file
+</office:document>
diff --git a/sc/source/core/tool/interpr2.cxx b/sc/source/core/tool/interpr2.cxx
index cf305836b3a2..a25da2993368 100644
--- a/sc/source/core/tool/interpr2.cxx
+++ b/sc/source/core/tool/interpr2.cxx
@@ -1995,7 +1995,7 @@ void ScInterpreter::ScFV()
 
 void ScInterpreter::ScNper()
 {
-    double nInterest, nRmz, nBw, nZw = 0;
+    double fInterest, fPmt, fPV, fFV = 0;
     bool bPayInAdvance = false;
     sal_uInt8 nParamCount = GetByte();
     if ( !MustHaveParamCount( nParamCount, 3, 5 ) )
@@ -2003,17 +2003,23 @@ void ScInterpreter::ScNper()
     if (nParamCount == 5)
         bPayInAdvance = GetBool();
     if (nParamCount >= 4)
-        nZw   = GetDouble();
-    nBw   = GetDouble();
-    nRmz  = GetDouble();
-    nInterest = GetDouble();
-    if (nInterest == 0.0)
-        PushDouble(-(nBw + nZw)/nRmz);
+        fFV   = GetDouble();  // Future Value
+    fPV   = GetDouble();      // Present Value
+    fPmt  = GetDouble();      // Payment
+    fInterest = GetDouble();
+    // Note that due to the function specification in ODFF1.2 (and Excel) the
+    // amount to be paid to get from fPV to fFV is fFV_+_fPV.
+    if ( fPV + fFV == 0.0 )
+        PushDouble( 0.0 );
+    else if ( fPmt == 0.0 )
+        PushIllegalArgument();  // No payment, future value can never be reached
+    else if (fInterest == 0.0)
+        PushDouble(-(fPV + fFV)/fPmt);
     else if (bPayInAdvance)
-        PushDouble(log(-(nInterest*nZw-nRmz*(1.0+nInterest))/(nInterest*nBw+nRmz*(1.0+nInterest)))
-                  / rtl::math::log1p(nInterest));
+        PushDouble(log(-(fInterest*fFV-fPmt*(1.0+fInterest))/(fInterest*fPV+fPmt*(1.0+fInterest)))
+                  / rtl::math::log1p(fInterest));
     else
-        PushDouble(log(-(nInterest*nZw-nRmz)/(nInterest*nBw+nRmz)) / rtl::math::log1p(nInterest));
+        PushDouble(log(-(fInterest*fFV-fPmt)/(fInterest*fPV+fPmt)) / rtl::math::log1p(fInterest));
 }
 
 bool ScInterpreter::RateIteration( double fNper, double fPayment, double fPv,