POI中的IRR返回NaN，但Excel中的正确值 [英] IRR in poi return NaN but correct value in 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功能它说:

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

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

Guess Optional. A number that you guess is close to the result of 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计算提供猜测.如果省略猜测，则假定为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.

您的 IRR 的结果在 Excel 中为-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中的正确值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！