Attachment 30339 Details for Bug 55024 – Patch with source code implementation for IRR function and test case

Patch with source code implementation for IRR function and test case

mirrImplementation.patch (text/plain), 10.07 KB, created by Carlos Delgado on 2013-05-29 08:31:01 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: Carlos Delgado

Created: 2013-05-29 08:31:01 UTC

Size: 10.07 KB

patch

obsolete

>Index: src/java/org/apache/poi/ss/formula/eval/FunctionEval.java
>===================================================================
>--- src/java/org/apache/poi/ss/formula/eval/FunctionEval.java	(revision 1486969)
>+++ src/java/org/apache/poi/ss/formula/eval/FunctionEval.java	(working copy)
>@@ -56,6 +56,7 @@
> 	 */
> 	protected static final Function[] functions = produceFunctions();
> 
>+    // Associates function indexes with functions, see functionMetadata.txt for details
> 	private static Function[] produceFunctions() {
> 		Function[] retval = new Function[368];
> 
>@@ -108,7 +109,8 @@
> 		retval[58] = FinanceFunction.NPER;
> 		retval[59] = FinanceFunction.PMT;
> 
>-      retval[60] = new Rate();
>+        retval[60] = new Rate();
>+        retval[61] = new Mirr();
> 		retval[62] = new Irr();
> 		retval[63] = NumericFunction.RAND;
> 		retval[64] = new Match();
>@@ -160,7 +162,7 @@
> 		retval[130] = new T();
> 
> 		retval[ID.INDIRECT] = null; // Indirect.evaluate has different signature
>-        retval[162] = TextFunction.CLEAN;  //Aniket Banerjee    
>+        retval[162] = TextFunction.CLEAN;  //Aniket Banerjee
>         retval[167] = new IPMT();
>         retval[168] = new PPMT();
> 		retval[169] = new Counta();
>Index: src/java/org/apache/poi/ss/formula/functions/Mirr.java
>===================================================================
>--- src/java/org/apache/poi/ss/formula/functions/Mirr.java	(revision 0)
>+++ src/java/org/apache/poi/ss/formula/functions/Mirr.java	(working copy)
>@@ -0,0 +1,78 @@
>+/* ====================================================================
>+   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.EvaluationException;
>+
>+/**
>+ * Calculates Modified internal rate of return. Syntax is MIRR(cash_flow_values, finance_rate, reinvest_rate)
>+ *
>+ * @author Carlos Delgado (carlos dot del dot est 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);
>+
>+        return mirr(mirrValues, financeRate, reinvestRate);
>+    }
>+
>+    public static double mirr(double[] in, double financeRate, double reinvestRate) {
>+        double mirr = 0;
>+        int numOfYears = in.length - 1;
>+        double pv = 0;
>+        double fv = 0;
>+
>+        int indexN = 0;
>+        for (int index = 0; index < in.length; index++) {
>+            if (in[index] < 0) {
>+                pv += in[index] / Math.pow(1 + financeRate + reinvestRate, indexN++);
>+            }
>+        }
>+
>+        for (int index = 0; index < in.length; index++) {
>+            if (in[index] > 0) {
>+                fv += in[index] * Math.pow(1 + financeRate, numOfYears - indexN++);
>+            }
>+        }
>+
>+        if (fv != 0 && pv != 0) {
>+            mirr = Math.pow(-fv / pv, 1d / numOfYears) - 1;
>+        }
>+        return mirr;
>+    }
>+}
>Index: src/testcases/org/apache/poi/ss/formula/functions/TestMirr.java
>===================================================================
>--- src/testcases/org/apache/poi/ss/formula/functions/TestMirr.java	(revision 0)
>+++ src/testcases/org/apache/poi/ss/formula/functions/TestMirr.java	(working copy)
>@@ -0,0 +1,117 @@
>+/* ====================================================================
>+   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.usermodel.CellValue;
>+
>+/**
>+ * Tests for {@link org.apache.poi.ss.formula.functions.Mirr}
>+ *
>+ * @author Carlos Delgado (carlos dot del dot est at gmail dot com)
>+ * @see {@link org.apache.poi.ss.formula.functions.TestIrr}
>+ */
>+public final class TestMirr extends TestCase {
>+
>+    public void testMirr() {
>+        // http://office.microsoft.com/en-001/excel-help/mirr-HP005209180.aspx
>+        double[] incomes = {-120000d, 39000d, 30000d, 21000d, 37000d, 46000d};
>+        double financeRate = 0.12;
>+        double reinvestRate = 0.1;
>+        double mirr = Mirr.mirr(incomes, financeRate, reinvestRate);
>+        assertEquals("mirr", 0.126094130366
>+                , mirr, 0.0000000001);
>+
>+        http://www.techonthenet.com/excel/formulas/mirr.php
>+        incomes = new double[]{-7500d, 3000d, 5000d, 1200d, 4000d};
>+        reinvestRate = 0.05;
>+        financeRate = 0.08;
>+        mirr = Mirr.mirr(incomes, financeRate, reinvestRate);
>+        assertEquals("mirr", 0.18736225093, mirr, 0.0000000001);
>+
>+        incomes = new double[]{-10000, 3400d, 6500d, 1000d};
>+        reinvestRate = 0.065;
>+        financeRate = 0.1;
>+        mirr = Mirr.mirr(incomes, financeRate, reinvestRate);
>+        assertEquals("mirr", 0.07039493966 , mirr, 0.0000000001);
>+
>+    }
>+
>+    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);
>+        StringBuffer failures = new StringBuffer();
>+        int failureCount = 0;
>+        int[] resultRows = {9, 19, 29};
>+
>+        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[" + (cellA.getRowIndex() + 1)+ "]: " + cellA.getCellFormula() + " ");
>+                failures.append(e.getMessage());
>+                failureCount++;
>+            }
>+
>+        }
>+
>+        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);
>+    }
>+}
>Index: test-data/spreadsheet/mirrTest.xls
>===================================================================
>Cannot display: file marked as a binary type.
>svn:mime-type = application/octet-stream
>Index: test-data/spreadsheet/mirrTest.xls
>===================================================================
>--- test-data/spreadsheet/mirrTest.xls	(revision 0)
>+++ test-data/spreadsheet/mirrTest.xls	(working copy)
>
>Property changes on: test-data/spreadsheet/mirrTest.xls
>___________________________________________________________________
>Added: svn:mime-type
>## -0,0 +1 ##
>+application/octet-stream
>\ No newline at end of property

Actions: View

Attachments on bug 55024: 30339 | 30340