excel-formula - poi 中的 IRR 返回 NaN 但 excel 中的值正确

Question

当我使用 apache/poi 计算 Irr 值时，我得到 Double.NaN，但在 excel 中输入相同的值，我得到一个负值。

那么为什么它们返回不同的值？

inputs here:

irr(-1.0601017230994111E8,19150.63,44505.08,22997.34,33936.39,27265.92,2127.66,2108.63,886.53,2482.27,4305.12,3421.58,65644.12,1020.51,2659.57,3191.49,20284508.4,1881279.27,11675415.09,7557862.28,921090.46,622104.32,289267.36,183.41,886.53, 0.1)

Answer 1

对我来说，它给出了#NUM!当前 apache poi 4.1.0 中出现错误，而不是 NaN。

问题在于你给出的猜测。在 IRR-function据称:

Guess Optional. A number that you guess is close to the result of IRR.

Microsoft Excel uses an iterative technique for calculating IRR. Starting with guess, IRR cycles through the calculation until the result is accurate within 0.00001 percent. If IRR can't find a result that works after 20 tries, the #NUM! error value is returned.

In most cases you do not need to provide guess for the IRR calculation. If guess is omitted, it is assumed to be 0.1 (10 percent).

If IRR gives the #NUM! error value, or if the result is not close to what you expected, try again with a different value for guess.

在 Excel 中，IRR 的结果将为 -0.050193141。但你的猜测是0.1。因此，使用该猜测，内部 apache poi IRR 函数在 20 次尝试后找不到准确度在 0.00001% 以内的结果。所以#NUM!返回错误值。

为什么apache poi的IRR函数与Excel中的函数不一样？因为 Excel 的这一部分不是开源的。所以没有人真正知道它是如何工作的。

使用 -0.1 的猜测对我来说是有效的。

示例:

import java.io.FileOutputStream;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

class ExcelEvaluateIRR {

 public static void main(String[] args) throws Exception {

  try (Workbook workbook = new XSSFWorkbook(); 
       FileOutputStream fileout = new FileOutputStream("Excel.xlsx") ) {

   Double[] values = new Double[] {
    -1.0601017230994111E8,
    19150.63,
    44505.08,
    22997.34,
    33936.39,
    27265.92,
    2127.66,
    2108.63,
    886.53,
    2482.27,
    4305.12,
    3421.58,
    65644.12,
    1020.51,
    2659.57,
    3191.49,
    20284508.4,
    1881279.27,
    11675415.09,
    7557862.28,
    921090.46,
    622104.32,
    289267.36,
    183.41,
    886.53
   };

   Sheet sheet = workbook.createSheet();
   Row row = null;
   Cell cell = null;
   for (int r = 0; r < values.length; r++) {
    row = sheet.createRow(r);
    cell = row.createCell(0);
    cell.setCellValue(values[r]);
   }
   row = sheet.createRow(values.length);
   cell = row.createCell(0);
   //cell.setCellFormula("IRR(A1:A" + values.length + ",0.1)"); // will not work
   cell.setCellFormula("IRR(A1:A" + values.length + ",-0.1)"); // will work
   FormulaEvaluator formulaEvaluator = workbook.getCreationHelper().createFormulaEvaluator();
   CellValue cellValue = formulaEvaluator.evaluate(cell);
   System.out.println(cellValue);

   workbook.write(fileout);
  }

 }
}