You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@poi.apache.org by bu...@apache.org on 2010/12/03 20:24:25 UTC
DO NOT REPLY [Bug 50409] New: Implement IRR function for Excel
https://issues.apache.org/bugzilla/show_bug.cgi?id=50409
Summary: Implement IRR function for Excel
Product: POI
Version: 3.8-dev
Platform: PC
Status: NEW
Severity: enhancement
Priority: P2
Component: XSSF
AssignedTo: dev@poi.apache.org
ReportedBy: marcel.may.de@gmail.com
Implements IRR, including test.
--
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
DO NOT REPLY [Bug 50409] Implement IRR function for Excel
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=50409
--- Comment #2 from Yegor Kozlov <ye...@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
DO NOT REPLY [Bug 50409] Implement IRR function for Excel
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=50409
Marcel May <ma...@gmail.com> changed:
What |Removed |Added
----------------------------------------------------------------------------
CC| |marcel.may.de@gmail.com
OS/Version| |All
--
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
DO NOT REPLY [Bug 50409] Implement IRR function for Excel
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=50409
--- Comment #4 from Yegor Kozlov <ye...@dinom.ru> 2010-12-09 05:08:42 EST ---
Created an attachment (id=26384)
--> (https://issues.apache.org/bugzilla/attachment.cgi?id=26384)
test-data/spreadsheet/IrrNpvTestCaseData.xls
--
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
DO NOT REPLY [Bug 50409] Implement IRR function for Excel
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=50409
--- Comment #6 from Marcel May <ma...@gmail.com> 2010-12-09 13:40:26 EST ---
(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
--
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
DO NOT REPLY [Bug 50409] Implement IRR function for Excel
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=50409
Yegor Kozlov <ye...@dinom.ru> changed:
What |Removed |Added
----------------------------------------------------------------------------
Status|NEW |RESOLVED
Resolution| |FIXED
--- Comment #7 from Yegor Kozlov <ye...@dinom.ru> 2010-12-10 10:00:40 EST ---
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
--
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
DO NOT REPLY [Bug 50409] Implement IRR function for Excel
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=50409
Yegor Kozlov <ye...@dinom.ru> changed:
What |Removed |Added
----------------------------------------------------------------------------
Blocks| |50437
--
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
DO NOT REPLY [Bug 50409] Implement IRR function for Excel
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=50409
--- Comment #5 from Yegor Kozlov <ye...@dinom.ru> 2010-12-09 12:28:00 EST ---
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
--
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
DO NOT REPLY [Bug 50409] Implement IRR function for Excel
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=50409
--- Comment #1 from Marcel May <ma...@gmail.com> 2010-12-03 14:25:43 EST ---
Created an attachment (id=26372)
--> (https://issues.apache.org/bugzilla/attachment.cgi?id=26372)
Patch against SVN trunk
--
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
DO NOT REPLY [Bug 50409] Implement IRR function for Excel
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=50409
Yegor Kozlov <ye...@dinom.ru> changed:
What |Removed |Added
----------------------------------------------------------------------------
Attachment #26372|0 |1
is obsolete| |
--- Comment #3 from Yegor Kozlov <ye...@dinom.ru> 2010-12-09 05:08:01 EST ---
Created an attachment (id=26383)
--> (https://issues.apache.org/bugzilla/attachment.cgi?id=26383)
updated patch
--
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