Bug 54349

Summary: Rate formula inconsistency with long periods
Product: POI Reporter: Sebas <sebas.lillo>
Component: HSSFAssignee: POI Developers List <dev>
Status: NEEDINFO ---    
Severity: normal    
Priority: P2    
Version: 3.8-FINAL   
Target Milestone: ---   
Hardware: PC   
OS: All   

Description Sebas 2012-12-25 14:09:28 UTC
in order to emulate excel's rate function, I'm using apache poi v3.8. My code is:

public static void main(String[] args) {

    int nper = 120;
    double pmt = 28.1;
    double pv = 2400;

    HSSFWorkbook workbook = new HSSFWorkbook();
    HSSFSheet sheet = workbook.createSheet();

    FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();

    Row row = sheet.createRow(0);

    Cell np = row.createCell(0);
    np.setCellValue(nper);

    Cell pt = row.createCell(1);
    pt.setCellValue(pmt);

    Cell cpv = row.createCell(2);
    cpv.setCellValue(-pv);

    Cell rate = row.createCell(3); 
    rate.setCellFormula("RATE(A1,B1,C1)");

    evaluator.evaluateFormulaCell(rate);
    double drate = rate.getNumericCellValue() * 100;
    System.out.println("RATE: " + drate);

}

which output is the same as excel: 0.599

But if I try the same calculation, this time with the values:

int nper = 360;
double pmt = 15.9;
double pv = 2400;


In excel I get 0.580, and the program returns 3.722909655790338E-14. Is this a known bug ? Any hints ? Thanks
Comment 1 Nick Burch 2012-12-26 08:37:30 UTC
Are you able to run some tests on intermediate values? Specifically, can you identify if the accuracy drops off gradually as you increase the values, or if there's a sudden change? And if you increase even more, does the accuracy return, or does it stay wrong?
Comment 2 Nick Burch 2012-12-30 07:47:28 UTC
Someone on StackOverflow has identified some problems with the code - http://stackoverflow.com/a/14087532/685641 - looks like it needs some more unit tests and some fixing up!