Bug 54349 - Rate formula inconsistency with long periods
Summary: Rate formula inconsistency with long periods
Status: RESOLVED CLOSED
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: 3.8-FINAL
Hardware: PC All
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks: 65988
  Show dependency tree
 
Reported: 2012-12-25 14:09 UTC by Sebas
Modified: 2022-08-15 15:16 UTC (History)
0 users



Attachments

Note You need to log in before you can comment on or make changes to this bug.
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!
Comment 3 PJ Fanning 2022-08-15 15:14:23 UTC
Closing basedon https://bz.apache.org/bugzilla/show_bug.cgi?id=65988