diff options
| -rw-r--r-- | sc/qa/unit/data/functions/financial/fods/nper.fods | 40 | ||||
| -rw-r--r-- | sc/source/core/tool/interpr2.cxx | 26 |
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>'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, |