retval[62] = new Irr();

/* ====================================================================

   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.TwoDEval;

import org.apache.poi.ss.formula.eval.ErrorEval;

import org.apache.poi.ss.formula.eval.NumberEval;

import org.apache.poi.ss.formula.eval.ValueEval;

/**

 * Calculates the internal rate of return.

 *

 * Syntax is IRR(values) or IRR(values,guess)

 *

 * @author Marcel May

 * @see <a href="http://en.wikipedia.org/wiki/Internal_rate_of_return#Numerical_solution">Wikipedia on IRR</a>

 * @see <a href="http://office.microsoft.com/en-us/excel-help/irr-HP005209146.aspx">Excel IRR</a>

 */

public final class Irr implements Function {

    public ValueEval evaluate(final ValueEval[] args, final int srcRowIndex, final int srcColumnIndex) {

        if (args.length == 1 && args[0] instanceof TwoDEval) {

            // Use default estimate

            double[] numbers = extractNumbers((TwoDEval) args[0]);

            return new NumberEval( irr(numbers));

        } else if (args.length == 2  && args[0] instanceof TwoDEval && args[1] instanceof NumberEval ) {

            double[] numbers = extractNumbers((TwoDEval) args[0]);

            return new NumberEval( irr(numbers, extractDouble(args[1])));

        } else {

            // Wrong number of arguments

            return ErrorEval.NA;

        }

    }

    private double[] extractNumbers(final TwoDEval pArg) {

        // Validate

        if (!pArg.isColumn() && !pArg.isRow()) {

            throw new RuntimeException("area" + pArg.getWidth() + "x" + pArg.getHeight() + " must be either row or column");

        }

        double[] res = new double[Math.max(pArg.getHeight(), pArg.getWidth())];

        if (pArg.isRow()) {

            for (int i = 0; i < res.length; i++) {

                res[i] = extractDouble(pArg.getValue(i, 0));

            }

        } else {

            for (int i = 0; i < res.length; i++) {

                res[i] = extractDouble(pArg.getValue(0, i));

            }

        }

        return res;

    }

    private double extractDouble(final ValueEval pValue) {

        if (pValue instanceof NumberEval) {

            return ((NumberEval) pValue).getNumberValue();

        }

        throw new RuntimeException("Can not convert to number: " + pValue);

    }

    /**

     * Computes the internal rate of return using an estimated irr of 10 percent.

     *

     * @param income the income values.

     * @return the irr.

     */

    public static double irr(double[] income) {

        return irr(income, 0.1d);

    }

    /**

     * Computes the internal rate of return using an estimated irr.

     *

     * @param income       the income values.

     * @param estimatedIrr the optional estimated irr.

     * @return the irr.

     * @see {http://en.wikipedia.org/wiki/Internal_rate_of_return#Numerical_solution}

     */

    public static double irr(double[] income, double estimatedIrr) {

        double r_prev = estimatedIrr / 2d;

        double npv_prev = FinanceLib.npv(r_prev, income);

        double r = estimatedIrr;

        // Stop iteration by iteration count or delta

        int iter = 0;

        while (Math.abs(r - r_prev) > 0.00001d && iter <= 20) {

            double npv =  FinanceLib.npv(r, income);

            double old_r = r;

            r -= npv * (r - r_prev) / (npv - npv_prev);

            npv_prev = npv;

            r_prev = old_r;

            iter++;

        }

        return r;

    }

}

/* ====================================================================

   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.TestCase;

import org.apache.poi.hssf.usermodel.*;

/**

 * Tests for {@link Irr}

 *

 * @author Marcel May

 */

public final class TestIrr extends TestCase {

    public void testIrr() {

        // http://en.wikipedia.org/wiki/Internal_rate_of_return#Example

        double[] incomes = {-4000d, 1200d, 1410d, 1875d, 1050d};

        double irr = Irr.irr(incomes);

        double irrRounded = Math.round(irr * 1000d) / 1000d;

        assertEquals("irr", 0.143d, irrRounded);

        // http://www.techonthenet.com/excel/formulas/irr.php

        incomes = new double[]{-7500d, 3000d, 5000d, 1200d, 4000d};

        irr = Irr.irr(incomes);

        irrRounded = Math.round(irr * 100d) / 100d;

        assertEquals("irr", 0.28d, irrRounded);

        incomes = new double[]{-10000d, 3400d, 6500d, 1000d};

        irr = Irr.irr(incomes);

        irrRounded = Math.round(irr * 100d) / 100d;

        assertEquals("irr", 0.05, irrRounded);

    }

    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);

    }

}