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