poi-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From bugzi...@apache.org
Subject DO NOT REPLY [Bug 50409] Implement IRR function for Excel
Date Thu, 09 Dec 2010 10:07:19 GMT
https://issues.apache.org/bugzilla/show_bug.cgi?id=50409

--- Comment #2 from Yegor Kozlov <yegor@dinom.ru> 2010-12-09 05:07:16 EST ---

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

-- 
Configure bugmail: https://issues.apache.org/bugzilla/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org


Mime
View raw message