Index: src/java/org/apache/poi/ss/formula/functions/Npv.java
===================================================================
--- src/java/org/apache/poi/ss/formula/functions/Npv.java (revision
1043675)
+++ src/java/org/apache/poi/ss/formula/functions/Npv.java (working copy)
@@ -17,6 +17,7 @@
package org.apache.poi.ss.formula.functions;
+import org.apache.poi.ss.formula.TwoDEval;
import org.apache.poi.ss.formula.eval.ErrorEval;
import org.apache.poi.ss.formula.eval.EvaluationException;
import org.apache.poi.ss.formula.eval.NumberEval;
@@ -30,66 +31,30 @@
* income.
*
* @author SPetrakovsky
+ * @author Marcel May
*/
-public final class Npv implements Function2Arg, Function3Arg, Function4Arg {
+public final class Npv implements Function {
-
- public ValueEval evaluate(int srcRowIndex, int srcColumnIndex,
ValueEval arg0, ValueEval arg1) {
- double result;
- try {
- double rate =
NumericFunction.singleOperandEvaluate(arg0, srcRowIndex, srcColumnIndex);
- double d1 = NumericFunction.singleOperandEvaluate(arg1,
srcRowIndex, srcColumnIndex);
- result = evaluate(rate, d1);
- NumericFunction.checkValue(result);
- } catch (EvaluationException e) {
- return e.getErrorEval();
- }
- return new NumberEval(result);
- }
- public ValueEval evaluate(int srcRowIndex, int srcColumnIndex,
ValueEval arg0, ValueEval arg1,
- ValueEval arg2) {
- double result;
- try {
- double rate =
NumericFunction.singleOperandEvaluate(arg0, srcRowIndex, srcColumnIndex);
- double d1 = NumericFunction.singleOperandEvaluate(arg1,
srcRowIndex, srcColumnIndex);
- double d2 = NumericFunction.singleOperandEvaluate(arg2,
srcRowIndex, srcColumnIndex);
- result = evaluate(rate, d1, d2);
- NumericFunction.checkValue(result);
- } catch (EvaluationException e) {
- return e.getErrorEval();
- }
- return new NumberEval(result);
- }
- public ValueEval evaluate(int srcRowIndex, int srcColumnIndex,
ValueEval arg0, ValueEval arg1,
- ValueEval arg2, ValueEval arg3) {
- double result;
- try {
- double rate =
NumericFunction.singleOperandEvaluate(arg0, srcRowIndex, srcColumnIndex);
- double d1 = NumericFunction.singleOperandEvaluate(arg1,
srcRowIndex, srcColumnIndex);
- double d2 = NumericFunction.singleOperandEvaluate(arg2,
srcRowIndex, srcColumnIndex);
- double d3 = NumericFunction.singleOperandEvaluate(arg3,
srcRowIndex, srcColumnIndex);
- result = evaluate(rate, d1, d2, d3);
- NumericFunction.checkValue(result);
- } catch (EvaluationException e) {
- return e.getErrorEval();
- }
- return new NumberEval(result);
- }
-
public ValueEval evaluate(ValueEval[] args, int srcRowIndex, int
srcColumnIndex) {
int nArgs = args.length;
if (nArgs<2) {
return ErrorEval.VALUE_INVALID;
}
- int np = nArgs-1;
- double[] ds = new double[np];
- double result;
- try {
+ double result;
+ try {
+ double[] ds;
+ if (2==nArgs && args[1] instanceof TwoDEval) {
+ // eg A4:A10
+ ds = extractNumbers((TwoDEval) args[1]);
+ } else {
+ // eg A4,A5,...
+ ds = new double[args.length-1];
+ for (int i = 0; i < ds.length; i++) {
+ ds[i] =
NumericFunction.singleOperandEvaluate(args[i+1], srcRowIndex, srcColumnIndex);
+ }
+ }
double rate =
NumericFunction.singleOperandEvaluate(args[0], srcRowIndex, srcColumnIndex);
- for (int i = 0; i < ds.length; i++) {
- ds[i] =
NumericFunction.singleOperandEvaluate(args[i+1], srcRowIndex, srcColumnIndex);
- }
- result = evaluate(rate, ds);
+ result = FinanceLib.npv(rate, ds);
NumericFunction.checkValue(result);
} catch (EvaluationException e) {
return e.getErrorEval();
@@ -97,11 +62,23 @@
return new NumberEval(result);
}
- private static double evaluate(double rate, double...ds) {
- double sum = 0;
- for (int i = 0; i < ds.length; i++) {
- sum += ds[i] / Math.pow(rate + 1, i);
- }
- return sum;
- }
+ private double[] extractNumbers(final TwoDEval pArg) {
+ // Validate
+ LookupUtils.ValueVector vector = LookupUtils.createVector(pArg);
+ if (null == vector) {
+ throw new RuntimeException("area" + pArg.getWidth() + "x" +
pArg.getHeight() + " must be either row or column");
+ }
+ double[] res = new double[vector.getSize()];
+ for (int i = 0; i < res.length; i++) {
+ res[i] = extractDouble(vector.getItem(i));
+ }
+ return res;
+ }
+
+ private double extractDouble(final ValueEval pValue) {
+ if (pValue instanceof NumberEval) {
+ return ((NumberEval) pValue).getNumberValue();
+ }
+ throw new RuntimeException("Can not convert to number: " + pValue);
+ }
}
Index: src/testcases/org/apache/poi/ss/formula/functions/TestNpv.java
===================================================================
--- src/testcases/org/apache/poi/ss/formula/functions/TestNpv.java
(revision 0)
+++ src/testcases/org/apache/poi/ss/formula/functions/TestNpv.java
(revision 0)
@@ -0,0 +1,64 @@
+/* ====================================================================
+ Licensed to the Apache Software Foundation (ASF) under one or more
+ contributor license agreements. See the NOTICE file distributed with
+ this work for additional information regarding copyright ownership.
+ The ASF licenses this file to You under the Apache License, Version 2.0
+ (the "License"); you may not use this file except in compliance with
+ the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+==================================================================== */
+
+package org.apache.poi.ss.formula.functions;
+
+import junit.framework.TestCase;
+import org.apache.poi.hssf.usermodel.*;
+import org.apache.poi.ss.usermodel.Cell;
+import org.apache.poi.ss.usermodel.Row;
+
+/**
+ * Tests for {...@link Npv}
+ *
+ * @author Marcel May
+ * @see <a
href="http://office.microsoft.com/en-us/excel-help/npv-HP005209199.aspx";>Excel
Help</a>
+ */
+public final class TestNpv extends TestCase {
+
+ public void testEvaluateInSheetExample2() {
+ HSSFWorkbook wb = new HSSFWorkbook();
+ HSSFSheet sheet = wb.createSheet("Sheet1");
+ HSSFRow row = sheet.createRow(0);
+
+ sheet.createRow(1).createCell(0).setCellValue(0.08d);
+ sheet.createRow(2).createCell(0).setCellValue(-40000d);
+ sheet.createRow(3).createCell(0).setCellValue(8000d);
+ sheet.createRow(4).createCell(0).setCellValue(9200d);
+ sheet.createRow(5).createCell(0).setCellValue(10000d);
+ sheet.createRow(6).createCell(0).setCellValue(12000d);
+ sheet.createRow(7).createCell(0).setCellValue(14500d);
+
+ HSSFCell cell = row.createCell(8);
+ HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
+
+ // Enumeration
+ cell.setCellFormula("NPV(A2, A4,A5,A6,A7,A8)+A3");
+ fe.clearAllCachedResultValues();
+ fe.evaluateFormulaCell(cell);
+ double res = cell.getNumericCellValue();
+ assertEquals(1922.06d, Math.round(res * 100d) / 100d);
+
+ // Range
+ cell.setCellFormula("NPV(A2, A4:A8)+A3");
+
+ fe.clearAllCachedResultValues();
+ fe.evaluateFormulaCell(cell);
+ res = cell.getNumericCellValue();
+ assertEquals(1922.06d, Math.round(res * 100d) / 100d);
+ }
+}
Property changes on:
src/testcases/org/apache/poi/ss/formula/functions/TestNpv.java
___________________________________________________________________
Added: svn:mime-type
+ text/plain
Added: svn:keywords
+ Date Revision
Added: svn:eol-style
+ native
Oops, the patch got swallowed.
On Dec 9, 2010, at 2:33 AM, Marcel May wrote:
> I can confirm this is a bug - just had the same problem.
>
> a) the NPV computation is wrong
> - sum += ds[i] / Math.pow(rate + 1, i);
> + sum += ds[i] / Math.pow(rate + 1, i+1);
>
> I think the computation should actually use the FinanceLib#npv method
> (which is correct).
>
> b) The impl does not support ranges
>
> I got a patch fix a) and b), including a test case:
>
>
>
> Jon, do you want to create the bug ticket? Then I'll add my patch for it.
>
> Cheers,
> Marcel
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
