Bug 50409

Summary: Implement IRR function for Excel
Product: POI Reporter: Marcel May <marcel.may.de>
Component: XSSFAssignee: POI Developers List <dev>
Status: RESOLVED FIXED    
Severity: enhancement CC: marcel.may.de
Priority: P2    
Version: 3.8-dev   
Target Milestone: ---   
Hardware: PC   
OS: All   
Bug Depends on:    
Bug Blocks: 50437    
Attachments: Patch against SVN trunk
updated patch
test-data/spreadsheet/IrrNpvTestCaseData.xls

Description Marcel May 2010-12-03 14:24:23 UTC
Implements IRR, including test.
Comment 1 Marcel May 2010-12-03 14:25:43 UTC
Created attachment 26372 [details]
Patch against SVN trunk
Comment 2 Yegor Kozlov 2010-12-09 05:07:16 UTC
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
Comment 3 Yegor Kozlov 2010-12-09 05:08:01 UTC
Created attachment 26383 [details]
updated patch
Comment 4 Yegor Kozlov 2010-12-09 05:08:42 UTC
Created attachment 26384 [details]
test-data/spreadsheet/IrrNpvTestCaseData.xls
Comment 5 Yegor Kozlov 2010-12-09 12:28:00 UTC
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
Comment 6 Marcel May 2010-12-09 13:40:26 UTC
(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
Comment 7 Yegor Kozlov 2010-12-10 10:00:40 UTC
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