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

本文介绍了poi 中的 IRR 返回 NaN 但在 excel 中返回正确的值的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

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

When i calculate Irr value use apache/poi i get Double.NaN, but the same inputs in excel i got a negative value.

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

So why they return different value?

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)

推荐答案

对我来说，它给出了 #NUM！当前 apache poi 4.1.0 中的错误，而不是 NaN.

For me it gives the #NUM! error in current apache poi 4.1.0, not NaN.

问题在于您给出的猜测.在 IRR-function声明如下:

The problem is about your given guess. In IRR-function it is stated:

猜测可选.您猜测的数字接近 IRR 的结果.

Microsoft Excel 使用迭代技术来计算 IRR.从猜测开始，IRR 循环计算，直到结果准确度在 0.00001% 以内.如果 IRR 找不到结果在 20 次尝试后有效，#NUM！返回错误值.

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.

在大多数情况下，您不需要为 IRR 计算提供猜测.如果省略guess，则假定为0.1 (10百分比).

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

如果 IRR 给出 #NUM!错误值，或者如果结果与您预期的不接近，请使用不同的猜测值重试.

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！返回错误值.

The result of your IRR would be -0.050193141 in Excel. But your guess is 0.1. So using that guess the internal apache poi IRR function does not find a result which is accurate within 0.00001 percent after 20 tries. So the #NUM! error value is returned.

为什么apache poi 的IRR 功能与Excel 中的不一样?好吧，因为 Excel 的那部分不是开源的.所以没有人真正知道它是如何工作的.

Why apache poi's IRR function is not the same as in Excel? Well because that part of Excel is not open source. So nobody really knows how it works.

使用 -0.1 的猜测对我有用.

Using a guess of -0.1 works for me.

示例:

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

 }
}

这篇关于poi 中的 IRR 返回 NaN 但在 excel 中返回正确的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！

IRR