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
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?
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!
Closing basedon https://bz.apache.org/bugzilla/show_bug.cgi?id=65988