Implements IRR, including test.
Created attachment 26372 [details] Patch against SVN trunk
There were a few problems with the code as originally submitted: (1) Irr incorectly ineterpeted columns and rows. Look at your test case, you populate cells A1:E1, but evaluate A1:A5 + 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); + } (2) Avoid implicit casting to TwoDEval or NumberEval. Input arguments can be numbers, strings, booleans, arrays, named ranges or formula results: =NPV(10%,A2:A6) =NPV(C2,A2:A6) // where C2=10% =NPV(C2,A2:A6) // where C2=TRUE which evaluates to 1! =NPV(named_range1,named_range1) =NPV(COUNT(A1,A2),INDIRECT("A2:A6")) =NPV(COUNT(A1,A2),{100, 200, 300}) The best candidate to convert input ValueEvals into an array of doubles is AggregateFunction.ValueCollector, I raised visibility of this class from private to default and changed Irr and Npv to use it. (3) The implementation of Irr does not properly work for short data series and a negative guess. I created a sample .xls file based on the data from Excel online documentation and the calculated bresult for =IRR(A2:A4,-10%) is wrong. See failing TestIrr#testIrrFromSpreadsheet(). Row[14]: IRR(A2:A4,-10%) expected:<-0.443506941334654> but was:<-25833.516857587092> Row[15]: IRR(A2:A4,irr_guess) expected:<-0.44350694133474056> but was:<-100630.10400193676> Please take a look at the changes. The updated patch includes both Irr nd Npv implementations. Regards, Yegor
Created attachment 26383 [details] updated patch
Created attachment 26384 [details] test-data/spreadsheet/IrrNpvTestCaseData.xls
The proposed implementation is based on the Secant solver which relies on the initial guesses. You start with the bracket [estimatedIrr / 2, estimatedIrr] and it seems not to work if estimatedIrr is negative or the sum of cash flows is negative (that's what I concluded from debugging). BTW, the Commons-Math's Secant solver seems to handle it OK, at least, I'm getting the same result as Excel. Here is my code (requires commons-math-2.1.jar in the classpath): import org.apache.commons.math.analysis.UnivariateRealFunction; import org.apache.commons.math.analysis.solvers.UnivariateRealSolverFactory; import org.apache.commons.math.analysis.solvers.UnivariateRealSolver; import org.apache.commons.math.FunctionEvaluationException; import org.apache.poi.ss.formula.functions.FinanceLib; import junit.framework.TestCase; /** * @author Yegor Kozlov */ public class TestCommonsMathSolvers extends TestCase { public static void main(String[] args) throws Exception { double[] values = new double[]{-70000d, 12000, 15000}; double guess = -0.1; // not used by the Commons-Math's secant solver double irr = irr_secant(values, guess); assertEquals(-0.44, Math.round(irr * 100d) / 100d); } public static double irr_secant(final double[] values, final double guess) throws Exception {https://issues.apache.org/bugzilla/show_bug.cgi?id=50409 UnivariateRealFunction function = new UnivariateRealFunction(){ public double value(double x) throws FunctionEvaluationException { return FinanceLib.npv(x, values); } }; UnivariateRealSolverFactory factory = UnivariateRealSolverFactory.newInstance(); UnivariateRealSolver solver = factory.newSecantSolver(); return solver.solve(function, values[0], values[values.length - 1], guess); } } Do you have a clue what solver is used by Excel? My guess it is Secant or Newton or a combination to assure convergence. Yegor
(In reply to comment #5) ... > > Do you have a clue what solver is used by Excel? My guess it is Secant or > Newton or a combination to assure convergence. > > Yegor Not really, sorry. There's a hint here: "Excel's IRR function uses "plain vanilla" Newton-Raphson root finding algorithm" http://one-nomads-blog.blogspot.com/2006/07/excels-npv-and-irr-function.html
I re-wrote your implementation of IRR to use the Newton-Raphson Method. Now all tests pass and the patch can be checked-in. My implementation was inspired by the Commons-Math's NewtonSolver: https://svn.apache.org/repos/asf/commons/proper/math/tags/MATH_2_1/src/main/java/org/apache/commons/math/analysis/solvers/NewtonSolver.java Applied in r1044370 Yegor