ASF Bugzilla – Full Text Bug Listing

- Home
- | New
- | Browse
- | Search
- | [?]
- | Reports
- | Help
- | New Account
- | Log In
- | Forgot Password

Summary: | Implement IRR function for Excel | ||
---|---|---|---|

Product: | POI | Reporter: | Marcel May <marcel.may.de> |

Component: | XSSF | Assignee: | 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
```
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 |