Author: fanningpj
Date: Mon Nov 25 20:48:45 2024
New Revision: 1922095
URL: http://svn.apache.org/viewvc?rev=1922095&view=rev
Log:
[github-733] Fix rate order in Mirr function. Thanks to Aleksandrs Jansons.
This closes #733
Modified:
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/Mirr.java
poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestMirr.java
poi/trunk/test-data/spreadsheet/mirrTest.xls
Modified:
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/Mirr.java
URL:
http://svn.apache.org/viewvc/poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/Mirr.java?rev=1922095&r1=1922094&r2=1922095&view=diff
==============================================================================
--- poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/Mirr.java
(original)
+++ poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/Mirr.java
Mon Nov 25 20:48:45 2024
@@ -56,23 +56,20 @@ public class Mirr extends MultiOperandNu
@Override
protected double evaluate(double[] values) throws EvaluationException {
- double financeRate = values[values.length-1];
- double reinvestRate = values[values.length-2];
+ double financeRate = values[values.length-2];
+ double reinvestRate = values[values.length-1];
double[] mirrValues = Arrays.copyOf(values, values.length - 2);
boolean mirrValuesAreAllNegatives = true;
- for (double mirrValue : mirrValues) {
- mirrValuesAreAllNegatives &= mirrValue < 0;
- }
- if (mirrValuesAreAllNegatives) {
- return -1.0d;
- }
-
boolean mirrValuesAreAllPositives = true;
for (double mirrValue : mirrValues) {
+ mirrValuesAreAllNegatives &= mirrValue < 0;
mirrValuesAreAllPositives &= mirrValue > 0;
}
+ if (mirrValuesAreAllNegatives) {
+ return -1.0d;
+ }
if (mirrValuesAreAllPositives) {
throw new EvaluationException(ErrorEval.DIV_ZERO);
}
@@ -88,14 +85,10 @@ public class Mirr extends MultiOperandNu
int indexN = 0;
for (double anIn : in) {
- if (anIn < 0) {
- pv += anIn / Math.pow(1 + financeRate + reinvestRate,
indexN++);
- }
- }
-
- for (double anIn : in) {
if (anIn > 0) {
- fv += anIn * Math.pow(1 + financeRate, numOfYears - indexN++);
+ fv += anIn * Math.pow(1 + reinvestRate, numOfYears - indexN++);
+ } else if (anIn < 0) {
+ pv += anIn / Math.pow(1 + financeRate, indexN++);
}
}
Modified:
poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestMirr.java
URL:
http://svn.apache.org/viewvc/poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestMirr.java?rev=1922095&r1=1922094&r2=1922095&view=diff
==============================================================================
---
poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestMirr.java
(original)
+++
poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestMirr.java
Mon Nov 25 20:48:45 2024
@@ -44,43 +44,44 @@ final class TestMirr {
Mirr mirr = new Mirr();
double mirrValue;
- double financeRate = 0.12;
- double reinvestRate = 0.1;
- double[] values = {-120000d, 39000d, 30000d, 21000d, 37000d, 46000d,
reinvestRate, financeRate};
- // MIRR should not failed with these parameters
+ double financeRate = 0.1;
+ double reinvestRate = 0.12;
+ double[] values = {-120000d, 39000d, 30000d, 21000d, 37000d, 46000d,
financeRate, reinvestRate};
mirrValue = mirr.evaluate(values);
assertEquals(0.126094130366, mirrValue, 0.0000000001);
- reinvestRate = 0.05;
- financeRate = 0.08;
- values = new double[]{-7500d, 3000d, 5000d, 1200d, 4000d,
reinvestRate, financeRate};
- // MIRR should not failed with these parameters
+ financeRate = 0.05;
+ reinvestRate = 0.08;
+ values = new double[]{-7500d, 3000d, 5000d, 1200d, 4000d,
financeRate, reinvestRate};
mirrValue = mirr.evaluate(values);
assertEquals(0.18736225093, mirrValue, 0.0000000001);
- reinvestRate = 0.065;
- financeRate = 0.1;
- values = new double[]{-10000, 3400d, 6500d, 1000d, reinvestRate,
financeRate};
- // MIRR should not failed with these parameters
+ financeRate = 0.065;
+ reinvestRate = 0.1;
+ values = new double[]{-10000, 3400d, 6500d, 1000d, financeRate,
reinvestRate};
mirrValue = mirr.evaluate(values);
assertEquals(0.07039493966, mirrValue, 0.0000000001);
- reinvestRate = 0.07;
- financeRate = 0.01;
- values = new double[]{-10000d, -3400d, -6500d, -1000d, reinvestRate,
financeRate};
- // MIRR should not failed with these parameters
+ financeRate = 0.07;
+ reinvestRate = 0.01;
+ values = new double[]{-10000d, -3400d, -6500d, -1000d, financeRate,
reinvestRate};
mirrValue = mirr.evaluate(values);
assertEquals(-1, mirrValue, 0.0);
+ financeRate = 0.1;
+ reinvestRate = 0.12;
+ values = new double[]{-1000d, -4000d, 5000d, 2000d, financeRate,
reinvestRate};
+ mirrValue = mirr.evaluate(values);
+ assertEquals(0.179085686035, mirrValue, 0.0000000001);
}
@Test
void testMirrErrors_expectDIV0() {
Mirr mirr = new Mirr();
- double reinvestRate = 0.05;
double financeRate = 0.08;
- double[] incomes = {120000d, 39000d, 30000d, 21000d, 37000d, 46000d,
reinvestRate, financeRate};
+ double reinvestRate = 0.05;
+ double[] incomes = {120000d, 39000d, 30000d, 21000d, 37000d, 46000d,
financeRate, reinvestRate};
EvaluationException e = assertThrows(EvaluationException.class, () ->
mirr.evaluate(incomes));
assertEquals(ErrorEval.DIV_ZERO, e.getErrorEval());
@@ -117,7 +118,7 @@ final class TestMirr {
HSSFSheet sheet = wb.getSheet("Mirr");
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
int failureCount = 0;
- int[] resultRows = {9, 19, 29, 45};
+ int[] resultRows = {9, 19, 29, 45, 53};
for (int rowNum : resultRows) {
HSSFRow row = sheet.getRow(rowNum);
Modified: poi/trunk/test-data/spreadsheet/mirrTest.xls
URL:
http://svn.apache.org/viewvc/poi/trunk/test-data/spreadsheet/mirrTest.xls?rev=1922095&r1=1922094&r2=1922095&view=diff
==============================================================================
Binary files - no diff available.
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
