You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@poi.apache.org by ce...@apache.org on 2013/11/04 23:52:06 UTC
svn commit: r1538795 - in /poi/trunk:
src/java/org/apache/poi/ss/formula/eval/
src/java/org/apache/poi/ss/formula/functions/
src/testcases/org/apache/poi/ss/formula/functions/ test-data/spreadsheet/
Author: cedricwalter
Date: Mon Nov 4 22:52:06 2013
New Revision: 1538795
URL: http://svn.apache.org/r1538795
Log:
Bug 55024: MIRR Formula implementation review, added error handling and FormulaEvalTest.xls
Added:
poi/trunk/src/java/org/apache/poi/ss/formula/functions/Mirr.java
poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestMirr.java
poi/trunk/test-data/spreadsheet/mirrTest.xls (with props)
Modified:
poi/trunk/src/java/org/apache/poi/ss/formula/eval/FunctionEval.java
poi/trunk/test-data/spreadsheet/FormulaEvalTestData.xls
Modified: poi/trunk/src/java/org/apache/poi/ss/formula/eval/FunctionEval.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/eval/FunctionEval.java?rev=1538795&r1=1538794&r2=1538795&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/formula/eval/FunctionEval.java (original)
+++ poi/trunk/src/java/org/apache/poi/ss/formula/eval/FunctionEval.java Mon Nov 4 22:52:06 2013
@@ -110,6 +110,8 @@ public final class FunctionEval {
retval[59] = FinanceFunction.PMT;
retval[60] = new Rate();
+ retval[61] = new Mirr();
+
retval[62] = new Irr();
retval[63] = NumericFunction.RAND;
retval[64] = new Match();
Added: poi/trunk/src/java/org/apache/poi/ss/formula/functions/Mirr.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/functions/Mirr.java?rev=1538795&view=auto
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/formula/functions/Mirr.java (added)
+++ poi/trunk/src/java/org/apache/poi/ss/formula/functions/Mirr.java Mon Nov 4 22:52:06 2013
@@ -0,0 +1,109 @@
+/* ====================================================================
+ Licensed to the Apache Software Foundation (ASF) under one or more
+ contributor license agreements. See the NOTICE file distributed with
+ this work for additional information regarding copyright ownership.
+ The ASF licenses this file to You under the Apache License, Version 2.0
+ (the "License"); you may not use this file except in compliance with
+ the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+==================================================================== */
+
+
+package org.apache.poi.ss.formula.functions;
+
+import org.apache.poi.ss.formula.eval.ErrorEval;
+import org.apache.poi.ss.formula.eval.EvaluationException;
+
+/**
+ * Calculates Modified internal rate of return. Syntax is MIRR(cash_flow_values, finance_rate, reinvest_rate)
+ *
+ * <p>Returns the modified internal rate of return for a series of periodic cash flows. MIRR considers both the cost
+ * of the investment and the interest received on reinvestment of cash.</p>
+ *
+ * Values is an array or a reference to cells that contain numbers. These numbers represent a series of payments (negative values) and income (positive values) occurring at regular periods.
+ * <ul>
+ * <li>Values must contain at least one positive value and one negative value to calculate the modified internal rate of return. Otherwise, MIRR returns the #DIV/0! error value.</li>
+ * <li>If an array or reference argument contains text, logical values, or empty cells, those values are ignored; however, cells with the value zero are included.</li>
+ * </ul>
+ *
+ * Finance_rate is the interest rate you pay on the money used in the cash flows.
+ * Reinvest_rate is the interest rate you receive on the cash flows as you reinvest them.
+ *
+ * @author Carlos Delgado (carlos dot del dot est at gmail dot com)
+ * @author Cédric Walter (cedric dot walter at gmail dot com)
+ *
+ * @see <a href="http://en.wikipedia.org/wiki/MIRR">Wikipedia on MIRR</a>
+ * @see <a href="http://office.microsoft.com/en-001/excel-help/mirr-HP005209180.aspx">Excel MIRR</a>
+ * @see {@link Irr}
+ */
+public class Mirr extends MultiOperandNumericFunction {
+
+ public Mirr() {
+ super(false, false);
+ }
+
+ @Override
+ protected int getMaxNumOperands() {
+ return 3;
+ }
+
+ @Override
+ protected double evaluate(double[] values) throws EvaluationException {
+
+ double financeRate = values[values.length-1];
+ double reinvestRate = values[values.length-2];
+
+ double[] mirrValues = new double[values.length - 2];
+ System.arraycopy(values, 0, mirrValues, 0, mirrValues.length);
+
+ boolean mirrValuesAreAllNegatives = true;
+ for (double mirrValue : mirrValues) {
+ mirrValuesAreAllNegatives &= mirrValue < 0;
+ }
+ if (mirrValuesAreAllNegatives) {
+ return -1.0d;
+ }
+
+ boolean mirrValuesAreAllPositives = true;
+ for (double mirrValue : mirrValues) {
+ mirrValuesAreAllPositives &= mirrValue > 0;
+ }
+ if (mirrValuesAreAllPositives) {
+ throw new EvaluationException(ErrorEval.DIV_ZERO);
+ }
+
+ return mirr(mirrValues, financeRate, reinvestRate);
+ }
+
+ private static double mirr(double[] in, double financeRate, double reinvestRate) {
+ double value = 0;
+ int numOfYears = in.length - 1;
+ double pv = 0;
+ double fv = 0;
+
+ 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++);
+ }
+ }
+
+ if (fv != 0 && pv != 0) {
+ value = Math.pow(-fv / pv, 1d / numOfYears) - 1;
+ }
+ return value;
+ }
+}
Added: poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestMirr.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestMirr.java?rev=1538795&view=auto
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestMirr.java (added)
+++ poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestMirr.java Mon Nov 4 22:52:06 2013
@@ -0,0 +1,162 @@
+/* ====================================================================
+ Licensed to the Apache Software Foundation (ASF) under one or more
+ contributor license agreements. See the NOTICE file distributed with
+ this work for additional information regarding copyright ownership.
+ The ASF licenses this file to You under the Apache License, Version 2.0
+ (the "License"); you may not use this file except in compliance with
+ the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+==================================================================== */
+
+package org.apache.poi.ss.formula.functions;
+
+import junit.framework.AssertionFailedError;
+import junit.framework.TestCase;
+import org.apache.poi.hssf.HSSFTestDataSamples;
+import org.apache.poi.hssf.usermodel.*;
+import org.apache.poi.ss.formula.eval.ErrorEval;
+import org.apache.poi.ss.formula.eval.EvaluationException;
+import org.apache.poi.ss.usermodel.CellValue;
+
+/**
+ * Tests for {@link org.apache.poi.ss.formula.functions.Mirr}
+ *
+ * @author Carlos Delgado (carlos dot del dot est at gmail dot com)
+ * @author Cédric Walter (cedric dot walter at gmail dot com)
+ * @see {@link org.apache.poi.ss.formula.functions.TestIrr}
+ */
+public final class TestMirr extends TestCase {
+
+ public void 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};
+ try {
+ mirrValue = mirr.evaluate(values);
+ } catch (EvaluationException e) {
+ throw new AssertionFailedError("MIRR should not failed with these parameters" + e);
+ }
+ assertEquals("mirr", 0.126094130366, mirrValue, 0.0000000001);
+
+ reinvestRate = 0.05;
+ financeRate = 0.08;
+ values = new double[]{-7500d, 3000d, 5000d, 1200d, 4000d, reinvestRate, financeRate};
+ try {
+ mirrValue = mirr.evaluate(values);
+ } catch (EvaluationException e) {
+ throw new AssertionFailedError("MIRR should not failed with these parameters" + e);
+ }
+ assertEquals("mirr", 0.18736225093, mirrValue, 0.0000000001);
+
+ reinvestRate = 0.065;
+ financeRate = 0.1;
+ values = new double[]{-10000, 3400d, 6500d, 1000d, reinvestRate, financeRate};
+ try {
+ mirrValue = mirr.evaluate(values);
+ } catch (EvaluationException e) {
+ throw new AssertionFailedError("MIRR should not failed with these parameters" + e);
+ }
+ assertEquals("mirr", 0.07039493966, mirrValue, 0.0000000001);
+
+ reinvestRate = 0.07;
+ financeRate = 0.01;
+ values = new double[]{-10000d, -3400d, -6500d, -1000d, reinvestRate, financeRate};
+ try {
+ mirrValue = mirr.evaluate(values);
+ } catch (EvaluationException e) {
+ throw new AssertionFailedError("MIRR should not failed with these parameters" + e);
+ }
+ assertEquals("mirr", -1, mirrValue, 0.0);
+
+ }
+
+ public 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};
+ try {
+ mirr.evaluate(incomes);
+ } catch (EvaluationException e) {
+ assertEquals(ErrorEval.DIV_ZERO, e.getErrorEval());
+ return;
+ }
+ throw new AssertionFailedError("MIRR should failed with all these positives values");
+ }
+
+
+ public void testEvaluateInSheet() {
+ HSSFWorkbook wb = new HSSFWorkbook();
+ HSSFSheet sheet = wb.createSheet("Sheet1");
+ HSSFRow row = sheet.createRow(0);
+
+ row.createCell(0).setCellValue(-7500d);
+ row.createCell(1).setCellValue(3000d);
+ row.createCell(2).setCellValue(5000d);
+ row.createCell(3).setCellValue(1200d);
+ row.createCell(4).setCellValue(4000d);
+
+ row.createCell(5).setCellValue(0.05d);
+ row.createCell(6).setCellValue(0.08d);
+
+ HSSFCell cell = row.createCell(7);
+ cell.setCellFormula("MIRR(A1:E1, F1, G1)");
+
+ HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
+ fe.clearAllCachedResultValues();
+ fe.evaluateFormulaCell(cell);
+ double res = cell.getNumericCellValue();
+ assertEquals(0.18736225093, res, 0.00000001);
+ }
+
+ public void testMirrFromSpreadsheet() {
+ HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("mirrTest.xls");
+ HSSFSheet sheet = wb.getSheet("Mirr");
+ HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
+ StringBuilder failures = new StringBuilder();
+ int failureCount = 0;
+ int[] resultRows = {9, 19, 29, 45};
+
+ for (int rowNum : resultRows) {
+ HSSFRow row = sheet.getRow(rowNum);
+ HSSFCell cellA = row.getCell(0);
+ try {
+ CellValue cv = fe.evaluate(cellA);
+ assertFormulaResult(cv, cellA);
+ } catch (Throwable e) {
+ if (failures.length() > 0) failures.append('\n');
+ failures.append("Row[").append(cellA.getRowIndex() + 1).append("]: ").append(cellA.getCellFormula()).append(" ");
+ failures.append(e.getMessage());
+ failureCount++;
+ }
+ }
+
+ HSSFRow row = sheet.getRow(37);
+ HSSFCell cellA = row.getCell(0);
+ CellValue cv = fe.evaluate(cellA);
+ assertEquals(ErrorEval.DIV_ZERO.getErrorCode(), cv.getErrorValue());
+
+ if (failures.length() > 0) {
+ throw new AssertionFailedError(failureCount + " IRR assertions failed:\n" + failures.toString());
+ }
+
+ }
+
+ private static void assertFormulaResult(CellValue cv, HSSFCell cell) {
+ double actualValue = cv.getNumberValue();
+ double expectedValue = cell.getNumericCellValue(); // cached formula result calculated by Excel
+ assertEquals("Invalid formula result: " + cv.toString(), HSSFCell.CELL_TYPE_NUMERIC, cv.getCellType());
+ assertEquals(expectedValue, actualValue, 1E-8);
+ }
+}
Modified: poi/trunk/test-data/spreadsheet/FormulaEvalTestData.xls
URL: http://svn.apache.org/viewvc/poi/trunk/test-data/spreadsheet/FormulaEvalTestData.xls?rev=1538795&r1=1538794&r2=1538795&view=diff
==============================================================================
Binary files - no diff available.
Added: poi/trunk/test-data/spreadsheet/mirrTest.xls
URL: http://svn.apache.org/viewvc/poi/trunk/test-data/spreadsheet/mirrTest.xls?rev=1538795&view=auto
==============================================================================
Binary file - no diff available.
Propchange: poi/trunk/test-data/spreadsheet/mirrTest.xls
------------------------------------------------------------------------------
svn:mime-type = application/octet-stream
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org