Index: src/java/org/apache/poi/ss/formula/eval/FunctionEval.java
===================================================================
--- src/java/org/apache/poi/ss/formula/eval/FunctionEval.java (revision 1041942)
+++ src/java/org/apache/poi/ss/formula/eval/FunctionEval.java (working copy)
@@ -100,6 +100,7 @@
retval[58] = FinanceFunction.NPER;
retval[59] = FinanceFunction.PMT;
+ retval[62] = new Irr();
retval[63] = NumericFunction.RAND;
retval[64] = new Match();
retval[65] = DateFunc.instance;
Index: src/java/org/apache/poi/ss/formula/functions/Irr.java
===================================================================
--- src/java/org/apache/poi/ss/formula/functions/Irr.java (revision 0)
+++ src/java/org/apache/poi/ss/formula/functions/Irr.java (revision 0)
@@ -0,0 +1,111 @@
+/* ====================================================================
+ 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 org.apache.poi.ss.formula.TwoDEval;
+import org.apache.poi.ss.formula.eval.ErrorEval;
+import org.apache.poi.ss.formula.eval.NumberEval;
+import org.apache.poi.ss.formula.eval.ValueEval;
+
+/**
+ * Calculates the internal rate of return.
+ *
+ * Syntax is IRR(values) or IRR(values,guess)
+ *
+ * @author Marcel May
+ * @see Wikipedia on IRR
+ * @see Excel IRR
+ */
+public final class Irr implements Function {
+
+ public ValueEval evaluate(final ValueEval[] args, final int srcRowIndex, final int srcColumnIndex) {
+ if (args.length == 1 && args[0] instanceof TwoDEval) {
+ // Use default estimate
+ double[] numbers = extractNumbers((TwoDEval) args[0]);
+ return new NumberEval( irr(numbers));
+ } else if (args.length == 2 && args[0] instanceof TwoDEval && args[1] instanceof NumberEval ) {
+ double[] numbers = extractNumbers((TwoDEval) args[0]);
+ return new NumberEval( irr(numbers, extractDouble(args[1])));
+ } else {
+ // Wrong number of arguments
+ return ErrorEval.NA;
+ }
+ }
+
+ private double[] extractNumbers(final TwoDEval pArg) {
+ // Validate
+ if (!pArg.isColumn() && !pArg.isRow()) {
+ throw new RuntimeException("area" + pArg.getWidth() + "x" + pArg.getHeight() + " must be either row or column");
+ }
+ double[] res = new double[Math.max(pArg.getHeight(), pArg.getWidth())];
+ if (pArg.isRow()) {
+ for (int i = 0; i < res.length; i++) {
+ res[i] = extractDouble(pArg.getValue(i, 0));
+ }
+ } else {
+ for (int i = 0; i < res.length; i++) {
+ res[i] = extractDouble(pArg.getValue(0, 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);
+ }
+
+
+ /**
+ * Computes the internal rate of return using an estimated irr of 10 percent.
+ *
+ * @param income the income values.
+ * @return the irr.
+ */
+ public static double irr(double[] income) {
+ return irr(income, 0.1d);
+ }
+
+ /**
+ * Computes the internal rate of return using an estimated irr.
+ *
+ * @param income the income values.
+ * @param estimatedIrr the optional estimated irr.
+ * @return the irr.
+ * @see {http://en.wikipedia.org/wiki/Internal_rate_of_return#Numerical_solution}
+ */
+ public static double irr(double[] income, double estimatedIrr) {
+ double r_prev = estimatedIrr / 2d;
+ double npv_prev = FinanceLib.npv(r_prev, income);
+ double r = estimatedIrr;
+
+ // Stop iteration by iteration count or delta
+ int iter = 0;
+ while (Math.abs(r - r_prev) > 0.00001d && iter <= 20) {
+ double npv = FinanceLib.npv(r, income);
+ double old_r = r;
+ r -= npv * (r - r_prev) / (npv - npv_prev);
+ npv_prev = npv;
+ r_prev = old_r;
+ iter++;
+ }
+ return r;
+ }
+}
Property changes on: src/java/org/apache/poi/ss/formula/functions/Irr.java
___________________________________________________________________
Added: svn:mime-type
+ text/plain
Added: svn:keywords
+ Date Revision
Added: svn:eol-style
+ native
Index: src/testcases/org/apache/poi/ss/formula/functions/TestIrr.java
===================================================================
--- src/testcases/org/apache/poi/ss/formula/functions/TestIrr.java (revision 0)
+++ src/testcases/org/apache/poi/ss/formula/functions/TestIrr.java (revision 0)
@@ -0,0 +1,69 @@
+/* ====================================================================
+ 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.*;
+
+/**
+ * Tests for {@link Irr}
+ *
+ * @author Marcel May
+ */
+public final class TestIrr extends TestCase {
+
+ public void testIrr() {
+ // http://en.wikipedia.org/wiki/Internal_rate_of_return#Example
+ double[] incomes = {-4000d, 1200d, 1410d, 1875d, 1050d};
+ double irr = Irr.irr(incomes);
+ double irrRounded = Math.round(irr * 1000d) / 1000d;
+ assertEquals("irr", 0.143d, irrRounded);
+
+ // http://www.techonthenet.com/excel/formulas/irr.php
+ incomes = new double[]{-7500d, 3000d, 5000d, 1200d, 4000d};
+ irr = Irr.irr(incomes);
+ irrRounded = Math.round(irr * 100d) / 100d;
+ assertEquals("irr", 0.28d, irrRounded);
+
+ incomes = new double[]{-10000d, 3400d, 6500d, 1000d};
+ irr = Irr.irr(incomes);
+ irrRounded = Math.round(irr * 100d) / 100d;
+ assertEquals("irr", 0.05, irrRounded);
+ }
+
+ public void testEvaluateInSheet() {
+ HSSFWorkbook wb = new HSSFWorkbook();
+ HSSFSheet sheet = wb.createSheet("Sheet1");
+ HSSFRow row = sheet.createRow(0);
+
+ row.createCell(0).setCellValue(-4000d);
+ row.createCell(1).setCellValue(1200d);
+ row.createCell(2).setCellValue(1410d);
+ row.createCell(3).setCellValue(1875d);
+ row.createCell(4).setCellValue(1050d);
+
+ HSSFCell cell = row.createCell(5);
+ cell.setCellFormula("IRR(A1:A5)");
+
+ HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
+ fe.clearAllCachedResultValues();
+ fe.evaluateFormulaCell(cell);
+ double res = cell.getNumericCellValue();
+ assertEquals(0.143d, Math.round(res * 1000d) / 1000d);
+ }
+}
Property changes on: src/testcases/org/apache/poi/ss/formula/functions/TestIrr.java
___________________________________________________________________
Added: svn:mime-type
+ text/plain
Added: svn:keywords
+ Date Revision
Added: svn:eol-style
+ native