View | Details | Raw Unified | Return to bug 50409
Collapse All | Expand All

(-)src/java/org/apache/poi/ss/formula/eval/FunctionEval.java (+1 lines)
Lines 100-105 Link Here
100
		retval[58] = FinanceFunction.NPER;
100
		retval[58] = FinanceFunction.NPER;
101
		retval[59] = FinanceFunction.PMT;
101
		retval[59] = FinanceFunction.PMT;
102
102
103
		retval[62] = new Irr();
103
		retval[63] = NumericFunction.RAND;
104
		retval[63] = NumericFunction.RAND;
104
		retval[64] = new Match();
105
		retval[64] = new Match();
105
		retval[65] = DateFunc.instance;
106
		retval[65] = DateFunc.instance;
(-)src/java/org/apache/poi/ss/formula/functions/Npv.java (-62 / +14 lines)
Lines 17-27 Link Here
17
17
18
package org.apache.poi.ss.formula.functions;
18
package org.apache.poi.ss.formula.functions;
19
19
20
import org.apache.poi.ss.formula.TwoDEval;
20
import org.apache.poi.ss.formula.eval.ErrorEval;
21
import org.apache.poi.ss.formula.eval.ErrorEval;
21
import org.apache.poi.ss.formula.eval.EvaluationException;
22
import org.apache.poi.ss.formula.eval.EvaluationException;
22
import org.apache.poi.ss.formula.eval.NumberEval;
23
import org.apache.poi.ss.formula.eval.NumberEval;
23
import org.apache.poi.ss.formula.eval.ValueEval;
24
import org.apache.poi.ss.formula.eval.ValueEval;
24
25
26
import java.util.Arrays;
27
25
/**
28
/**
26
 * Calculates the net present value of an investment by using a discount rate
29
 * Calculates the net present value of an investment by using a discount rate
27
 * and a series of future payments (negative values) and income (positive
30
 * and a series of future payments (negative values) and income (positive
Lines 30-107 Link Here
30
 * income.
33
 * income.
31
 *
34
 *
32
 * @author SPetrakovsky
35
 * @author SPetrakovsky
36
 * @author Marcel May
33
 */
37
 */
34
public final class Npv implements Function2Arg, Function3Arg, Function4Arg {
38
public final class Npv implements Function {
35
39
36
37
	public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1) {
38
		double result;
39
		try {
40
			double rate = NumericFunction.singleOperandEvaluate(arg0, srcRowIndex, srcColumnIndex);
41
			double d1 = NumericFunction.singleOperandEvaluate(arg1, srcRowIndex, srcColumnIndex);
42
			result = evaluate(rate, d1);
43
			NumericFunction.checkValue(result);
44
		} catch (EvaluationException e) {
45
			return e.getErrorEval();
46
		}
47
		return new NumberEval(result);
48
	}
49
	public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1,
50
			ValueEval arg2) {
51
		double result;
52
		try {
53
			double rate = NumericFunction.singleOperandEvaluate(arg0, srcRowIndex, srcColumnIndex);
54
			double d1 = NumericFunction.singleOperandEvaluate(arg1, srcRowIndex, srcColumnIndex);
55
			double d2 = NumericFunction.singleOperandEvaluate(arg2, srcRowIndex, srcColumnIndex);
56
			result = evaluate(rate, d1, d2);
57
			NumericFunction.checkValue(result);
58
		} catch (EvaluationException e) {
59
			return e.getErrorEval();
60
		}
61
		return new NumberEval(result);
62
	}
63
	public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1,
64
			ValueEval arg2, ValueEval arg3) {
65
		double result;
66
		try {
67
			double rate = NumericFunction.singleOperandEvaluate(arg0, srcRowIndex, srcColumnIndex);
68
			double d1 = NumericFunction.singleOperandEvaluate(arg1, srcRowIndex, srcColumnIndex);
69
			double d2 = NumericFunction.singleOperandEvaluate(arg2, srcRowIndex, srcColumnIndex);
70
			double d3 = NumericFunction.singleOperandEvaluate(arg3, srcRowIndex, srcColumnIndex);
71
			result = evaluate(rate, d1, d2, d3);
72
			NumericFunction.checkValue(result);
73
		} catch (EvaluationException e) {
74
			return e.getErrorEval();
75
		}
76
		return new NumberEval(result);
77
	}
78
79
	public ValueEval evaluate(ValueEval[] args, int srcRowIndex, int srcColumnIndex) {
40
	public ValueEval evaluate(ValueEval[] args, int srcRowIndex, int srcColumnIndex) {
80
		int nArgs = args.length;
41
		int nArgs = args.length;
81
		if (nArgs<2) {
42
		if (nArgs < 2) {
82
			return ErrorEval.VALUE_INVALID;
43
			return ErrorEval.VALUE_INVALID;
83
		}
44
		}
84
		int np = nArgs-1;
45
85
		double[] ds = new double[np];
46
        try {
86
		double result;
87
		try {
88
			double rate = NumericFunction.singleOperandEvaluate(args[0], srcRowIndex, srcColumnIndex);
47
			double rate = NumericFunction.singleOperandEvaluate(args[0], srcRowIndex, srcColumnIndex);
89
			for (int i = 0; i < ds.length; i++) {
48
            // convert tail arguments into an array of doubles
90
				ds[i] =  NumericFunction.singleOperandEvaluate(args[i+1], srcRowIndex, srcColumnIndex);
49
            ValueEval[] vargs = Arrays.copyOfRange(args, 1 , args.length);
91
			}
50
            double[] values = AggregateFunction.ValueCollector.collectValues(vargs);
92
			result = evaluate(rate, ds);
51
52
            double result = FinanceLib.npv(rate, values);
93
			NumericFunction.checkValue(result);
53
			NumericFunction.checkValue(result);
54
            return new NumberEval(result);
94
		} catch (EvaluationException e) {
55
		} catch (EvaluationException e) {
95
			return e.getErrorEval();
56
			return e.getErrorEval();
96
		}
57
		}
97
		return new NumberEval(result);
98
	}
58
	}
99
100
	private static double evaluate(double rate, double...ds) {
101
		double sum = 0;
102
		for (int i = 0; i < ds.length; i++) {
103
			sum += ds[i] / Math.pow(rate + 1, i);
104
		}
105
		return sum;
106
	}
107
}
59
}
(-)src/java/org/apache/poi/ss/formula/functions/Irr.java (+92 lines)
Line 0 Link Here
1
/* ====================================================================
2
   Licensed to the Apache Software Foundation (ASF) under one or more
3
   contributor license agreements.  See the NOTICE file distributed with
4
   this work for additional information regarding copyright ownership.
5
   The ASF licenses this file to You under the Apache License, Version 2.0
6
   (the "License"); you may not use this file except in compliance with
7
   the License.  You may obtain a copy of the License at
8
9
       http://www.apache.org/licenses/LICENSE-2.0
10
11
   Unless required by applicable law or agreed to in writing, software
12
   distributed under the License is distributed on an "AS IS" BASIS,
13
   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
14
   See the License for the specific language governing permissions and
15
   limitations under the License.
16
==================================================================== */
17
18
package org.apache.poi.ss.formula.functions;
19
20
import org.apache.poi.ss.formula.eval.*;
21
22
/**
23
 * Calculates the internal rate of return.
24
 *
25
 * Syntax is IRR(values) or IRR(values,guess)
26
 *
27
 * @author Marcel May
28
 * @see <a href="http://en.wikipedia.org/wiki/Internal_rate_of_return#Numerical_solution">Wikipedia on IRR</a>
29
 * @see <a href="http://office.microsoft.com/en-us/excel-help/irr-HP005209146.aspx">Excel IRR</a>
30
 */
31
public final class Irr implements Function {
32
33
34
    public ValueEval evaluate(final ValueEval[] args, final int srcRowIndex, final int srcColumnIndex) {
35
        if(args.length == 0 || args.length > 2) {
36
            // Wrong number of arguments
37
            return ErrorEval.VALUE_INVALID;
38
        }
39
40
        try {
41
            double[] values = AggregateFunction.ValueCollector.collectValues(args[0]);
42
            double guess;
43
            if(args.length == 2) {
44
                guess = NumericFunction.singleOperandEvaluate(args[1], srcRowIndex, srcColumnIndex);
45
            } else {
46
                guess = 0.1d;
47
            }
48
            double result = irr(values, guess);
49
            NumericFunction.checkValue(result);
50
            return new NumberEval(result);
51
        } catch (EvaluationException e){
52
            return e.getErrorEval();
53
        }
54
    }
55
56
    /**
57
     * Computes the internal rate of return using an estimated irr of 10 percent.
58
     *
59
     * @param income the income values.
60
     * @return the irr.
61
     */
62
    public static double irr(double[] income) {
63
        return irr(income, 0.1d);
64
    }
65
66
    /**
67
     * Computes the internal rate of return using an estimated irr.
68
     *
69
     * @param income       the income values.
70
     * @param estimatedIrr the optional estimated irr.
71
     * @return the irr.
72
     * @see {http://en.wikipedia.org/wiki/Internal_rate_of_return#Numerical_solution}
73
     */
74
    public static double irr(double[] income, double estimatedIrr) {
75
        double r_prev = estimatedIrr / 2d;
76
        double npv_prev = FinanceLib.npv(r_prev, income);
77
        double r = estimatedIrr;
78
79
        // Stop iteration by iteration count or delta
80
        int iter = 0;
81
        while (Math.abs(r - r_prev) > 0.00001d && iter <= 20) {
82
            double npv =  FinanceLib.npv(r, income);
83
            double old_r = r;
84
            r -= npv * (r - r_prev) / (npv - npv_prev);
85
            npv_prev = npv;
86
            r_prev = old_r;
87
            iter++;
88
        }
89
        return r;
90
    }
91
92
}
(-)src/java/org/apache/poi/ss/formula/functions/AggregateFunction.java (-1 / +1 lines)
Lines 67-73 Link Here
67
			return new NumberEval(result);
67
			return new NumberEval(result);
68
		}
68
		}
69
	}
69
	}
70
	private static final class ValueCollector extends MultiOperandNumericFunction {
70
	static final class ValueCollector extends MultiOperandNumericFunction {
71
		private static final ValueCollector instance = new ValueCollector();
71
		private static final ValueCollector instance = new ValueCollector();
72
		public ValueCollector() {
72
		public ValueCollector() {
73
			super(false, false);
73
			super(false, false);
(-)src/testcases/org/apache/poi/ss/formula/functions/TestNpv.java (+104 lines)
Line 0 Link Here
1
/* ====================================================================
2
   Licensed to the Apache Software Foundation (ASF) under one or more
3
   contributor license agreements.  See the NOTICE file distributed with
4
   this work for additional information regarding copyright ownership.
5
   The ASF licenses this file to You under the Apache License, Version 2.0
6
   (the "License"); you may not use this file except in compliance with
7
   the License.  You may obtain a copy of the License at
8
9
       http://www.apache.org/licenses/LICENSE-2.0
10
11
   Unless required by applicable law or agreed to in writing, software
12
   distributed under the License is distributed on an "AS IS" BASIS,
13
   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
14
   See the License for the specific language governing permissions and
15
   limitations under the License.
16
==================================================================== */
17
18
package org.apache.poi.ss.formula.functions;
19
20
import junit.framework.TestCase;
21
import junit.framework.AssertionFailedError;
22
import org.apache.poi.hssf.usermodel.*;
23
import org.apache.poi.hssf.HSSFTestDataSamples;
24
import org.apache.poi.ss.usermodel.Cell;
25
import org.apache.poi.ss.usermodel.Row;
26
import org.apache.poi.ss.usermodel.CellValue;
27
28
/**
29
 * Tests for {@link Npv}
30
 *
31
 * @author Marcel May
32
 * @see <a href="http://office.microsoft.com/en-us/excel-help/npv-HP005209199.aspx">Excel Help</a>
33
 */
34
public final class TestNpv extends TestCase {
35
36
    public void testEvaluateInSheetExample2() {
37
        HSSFWorkbook wb = new HSSFWorkbook();
38
        HSSFSheet sheet = wb.createSheet("Sheet1");
39
        HSSFRow row = sheet.createRow(0);
40
41
        sheet.createRow(1).createCell(0).setCellValue(0.08d);
42
        sheet.createRow(2).createCell(0).setCellValue(-40000d);
43
        sheet.createRow(3).createCell(0).setCellValue(8000d);
44
        sheet.createRow(4).createCell(0).setCellValue(9200d);
45
        sheet.createRow(5).createCell(0).setCellValue(10000d);
46
        sheet.createRow(6).createCell(0).setCellValue(12000d);
47
        sheet.createRow(7).createCell(0).setCellValue(14500d);
48
49
        HSSFCell cell = row.createCell(8);
50
        HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
51
52
        // Enumeration
53
        cell.setCellFormula("NPV(A2, A4,A5,A6,A7,A8)+A3");
54
        fe.clearAllCachedResultValues();
55
        fe.evaluateFormulaCell(cell);
56
        double res = cell.getNumericCellValue();
57
        assertEquals(1922.06d, Math.round(res * 100d) / 100d);
58
59
        // Range
60
        cell.setCellFormula("NPV(A2, A4:A8)+A3");
61
62
        fe.clearAllCachedResultValues();
63
        fe.evaluateFormulaCell(cell);
64
        res = cell.getNumericCellValue();
65
        assertEquals(1922.06d, Math.round(res * 100d) / 100d);
66
    }
67
68
    /**
69
     * evaluate formulas with NPV and compare the result with
70
     * the cached formula result pre-calculated by Excel
71
     */
72
    public void testNpvFromSpreadsheet(){
73
        HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("IrrNpvTestCaseData.xls");
74
        HSSFSheet sheet = wb.getSheet("IRR-NPV");
75
        HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
76
        StringBuffer failures = new StringBuffer();
77
        int failureCount = 0;
78
        // TODO YK: Formulas in rows 16 and 17 operate with ArrayPtg which isn't yet supported
79
        // FormulaEvaluator as of r1041407 throws "Unexpected ptg class (org.apache.poi.ss.formula.ptg.ArrayPtg)"
80
        for(int rownum = 9; rownum <= 15; rownum++){
81
            HSSFRow row = sheet.getRow(rownum);
82
            HSSFCell cellB = row.getCell(1);
83
            try {
84
                CellValue cv = fe.evaluate(cellB);
85
                assertFormulaResult(cv, cellB);
86
            } catch (Throwable e){
87
                if(failures.length() > 0) failures.append('\n');
88
                failures.append("Row[" + (cellB.getRowIndex() + 1)+ "]: " + cellB.getCellFormula() + " ");
89
                failures.append(e.getMessage());
90
                failureCount++;
91
            }
92
        }
93
94
        if(failures.length() > 0) {
95
            throw new AssertionFailedError(failureCount + " IRR evaluations failed:\n" + failures.toString());
96
        }
97
    }
98
99
    private static void assertFormulaResult(CellValue cv, HSSFCell cell){
100
        double actualValue = cv.getNumberValue();
101
        double expectedValue = cell.getNumericCellValue(); // cached formula result calculated by Excel
102
        assertEquals(expectedValue, actualValue, 1E-4); // should agree within 0.01%
103
    }
104
}
(-)src/testcases/org/apache/poi/ss/formula/functions/TestIrr.java (+118 lines)
Line 0 Link Here
1
/* ====================================================================
2
   Licensed to the Apache Software Foundation (ASF) under one or more
3
   contributor license agreements.  See the NOTICE file distributed with
4
   this work for additional information regarding copyright ownership.
5
   The ASF licenses this file to You under the Apache License, Version 2.0
6
   (the "License"); you may not use this file except in compliance with
7
   the License.  You may obtain a copy of the License at
8
9
       http://www.apache.org/licenses/LICENSE-2.0
10
11
   Unless required by applicable law or agreed to in writing, software
12
   distributed under the License is distributed on an "AS IS" BASIS,
13
   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
14
   See the License for the specific language governing permissions and
15
   limitations under the License.
16
==================================================================== */
17
18
package org.apache.poi.ss.formula.functions;
19
20
import junit.framework.TestCase;
21
import junit.framework.AssertionFailedError;
22
import org.apache.poi.hssf.usermodel.*;
23
import org.apache.poi.hssf.HSSFTestDataSamples;
24
import org.apache.poi.ss.usermodel.CellValue;
25
26
/**
27
 * Tests for {@link Irr}
28
 *
29
 * @author Marcel May
30
 */
31
public final class TestIrr extends TestCase {
32
33
    public void testIrr() {
34
        // http://en.wikipedia.org/wiki/Internal_rate_of_return#Example
35
        double[] incomes = {-4000d, 1200d, 1410d, 1875d, 1050d};
36
        double irr = Irr.irr(incomes);
37
        double irrRounded = Math.round(irr * 1000d) / 1000d;
38
        assertEquals("irr", 0.143d, irrRounded);
39
40
        // http://www.techonthenet.com/excel/formulas/irr.php
41
        incomes = new double[]{-7500d, 3000d, 5000d, 1200d, 4000d};
42
        irr = Irr.irr(incomes);
43
        irrRounded = Math.round(irr * 100d) / 100d;
44
        assertEquals("irr", 0.28d, irrRounded);
45
46
        incomes = new double[]{-10000d, 3400d, 6500d, 1000d};
47
        irr = Irr.irr(incomes);
48
        irrRounded = Math.round(irr * 100d) / 100d;
49
        assertEquals("irr", 0.05, irrRounded);
50
    }
51
52
    public void testEvaluateInSheet() {
53
        HSSFWorkbook wb = new HSSFWorkbook();
54
        HSSFSheet sheet = wb.createSheet("Sheet1");
55
        HSSFRow row = sheet.createRow(0);
56
57
        row.createCell(0).setCellValue(-4000d);
58
        row.createCell(1).setCellValue(1200d);
59
        row.createCell(2).setCellValue(1410d);
60
        row.createCell(3).setCellValue(1875d);
61
        row.createCell(4).setCellValue(1050d);
62
63
        HSSFCell cell = row.createCell(5);
64
        cell.setCellFormula("IRR(A1:E1)");
65
66
        HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
67
        fe.clearAllCachedResultValues();
68
        fe.evaluateFormulaCell(cell);
69
        double res = cell.getNumericCellValue();
70
        assertEquals(0.143d, Math.round(res * 1000d) / 1000d);
71
    }
72
73
    public void testIrrFromSpreadsheet(){
74
        HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("IrrNpvTestCaseData.xls");
75
        HSSFSheet sheet = wb.getSheet("IRR-NPV");
76
        HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
77
        StringBuffer failures = new StringBuffer();
78
        int failureCount = 0;
79
        // TODO YK: Formulas in rows 16 and 17 operate with ArrayPtg which isn't yet supported
80
        // FormulaEvaluator as of r1041407 throws "Unexpected ptg class (org.apache.poi.ss.formula.ptg.ArrayPtg)"
81
        for(int rownum = 9; rownum <= 15; rownum++){
82
            HSSFRow row = sheet.getRow(rownum);
83
            HSSFCell cellA = row.getCell(0);
84
            try {
85
                CellValue cv = fe.evaluate(cellA);
86
                assertFormulaResult(cv, cellA);
87
            } catch (Throwable e){
88
                if(failures.length() > 0) failures.append('\n');
89
                failures.append("Row[" + (cellA.getRowIndex() + 1)+ "]: " + cellA.getCellFormula() + " ");
90
                failures.append(e.getMessage());
91
                failureCount++;
92
            }
93
94
            HSSFCell cellC = row.getCell(2); //IRR-NPV relationship: NPV(IRR(values), values) = 0
95
            try {
96
                CellValue cv = fe.evaluate(cellC);
97
                assertEquals(0, cv.getNumberValue(), 1E-4);
98
            } catch (Throwable e){
99
                if(failures.length() > 0) failures.append('\n');
100
                failures.append("Row[" + (cellC.getRowIndex() + 1)+ "]: " + cellC.getCellFormula() + " ");
101
                failures.append(e.getMessage());
102
                failureCount++;
103
            }
104
        }
105
106
        if(failures.length() > 0) {
107
            throw new AssertionFailedError(failureCount + " IRR assertions failed:\n" + failures.toString());
108
        }
109
110
    }
111
112
    private static void assertFormulaResult(CellValue cv, HSSFCell cell){
113
        double actualValue = cv.getNumberValue();
114
        double expectedValue = cell.getNumericCellValue(); // cached formula result calculated by Excel
115
        assertEquals("Invalid formula result: " + cv.toString(), HSSFCell.CELL_TYPE_NUMERIC, cv.getCellType());
116
        assertEquals(expectedValue, actualValue, 1E-4); // should agree within 0.01%
117
    }
118
}

Return to bug 50409