You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@poi.apache.org by bu...@apache.org on 2022/03/31 13:44:28 UTC
[Bug 65988] New: Rate function giving incorrect results
https://bz.apache.org/bugzilla/show_bug.cgi?id=65988
Bug ID: 65988
Summary: Rate function giving incorrect results
Product: POI
Version: unspecified
Hardware: PC
OS: Mac OS X 10.1
Status: NEW
Severity: major
Priority: P2
Component: POI Overall
Assignee: dev@poi.apache.org
Reporter: pranaybsankpal2050@gmail.com
Target Milestone: ---
Hi Team,
Please find the code below
double nper = 360.0;
double pmt = 6.56;
double pv = -2000.0;
XSSFWorkbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet();
XSSFRow row = (XSSFRow) sheet.createRow(1);
XSSFCell cell = row.createCell(1);
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
cell.setCellFormula("RATE(" + nper + ", " + pmt + ", " + pv + ")");
FormulaEvaluator evaluator =
workbook.getCreationHelper().createFormulaEvaluator();
evaluator.evaluateInCell(cell);
double rate = cell.getNumericCellValue();
Result : 0.0
whereas in excel I get: 0.0009480170844060
This is currently affecting one of our critical flows in production. Could you
please suggest how to fix this?
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 65988] Rate function giving incorrect results
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=65988
--- Comment #3 from Pranay <pr...@gmail.com> ---
Edit: output library giving is 4.6918646695350337E-17
Whereas output excel giving is 0.0009480170844060
Please find the demo project: https://github.com/pranayhere/poi-bug
Dependencies :
poi: 5.2.2
poi-ooxml: 5.2.2
poi-ooxml-schemas: 4.1.2
openjdk version: 1.8.0_302
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 65988] Rate function giving incorrect results
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=65988
PJ Fanning <fa...@yahoo.com> changed:
What |Removed |Added
----------------------------------------------------------------------------
Depends on| |54349
Referenced Bugs:
https://bz.apache.org/bugzilla/show_bug.cgi?id=54349
[Bug 54349] Rate formula inconsistency with long periods
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 65988] Rate function giving incorrect results
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=65988
--- Comment #5 from Pranay <pr...@gmail.com> ---
tried changing it, getting the same results.
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 65988] Rate function giving incorrect results
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=65988
--- Comment #11 from Yegor Kozlov <ye...@gmail.com> ---
Yes, I think we should borrow this code.
NumPy is BSD 3 which is permissive and compatible with the Apache licence.
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 65988] Rate function giving incorrect results
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=65988
--- Comment #1 from PJ Fanning <fa...@yahoo.com> ---
One workaround is to call setForceFormulaRecalculation(true) on the
XSSFWorkbook before writing it out. Excel will then recalculate the value when
it loads the xlsx.
The functions support in POI is of variable quality. The project is maintained
by volunteers and some of the function implementations are incomplete.
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 65988] Rate function giving incorrect results
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=65988
--- Comment #6 from PJ Fanning <fa...@yahoo.com> ---
this function was added in 2011 based on
https://bz.apache.org/bugzilla/show_bug.cgi?id=52050 - hasn't been touched
since (except for general code tidy up)
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 65988] Rate function giving incorrect results
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=65988
Yegor Kozlov <ye...@gmail.com> changed:
What |Removed |Added
----------------------------------------------------------------------------
Resolution|--- |FIXED
Status|NEW |RESOLVED
--- Comment #13 from Yegor Kozlov <ye...@gmail.com> ---
Patch applied in r1899668
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 65988] Rate function giving incorrect results
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=65988
--- Comment #14 from Pranay <pr...@gmail.com> ---
Thanks Yegor Kozlov, in which version of POI these changes will be available?
Any release date you have?
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 65988] Rate function giving incorrect results
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=65988
--- Comment #7 from PJ Fanning <fa...@yahoo.com> ---
I added r1899452 with a test case that shows
https://support.microsoft.com/en-us/office/rate-function-9f665657-4a7e-4bb7-a030-83fc59e748ce
example works but that Pranay's example does not.
I have not attempted to rewrite the rate function to fix the issue. It's
possible that the signs of the pmt and pv are confusing the existing POI impl.
The MS example has negative pmt and positive pv.
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 65988] Rate function giving incorrect results
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=65988
--- Comment #10 from PJ Fanning <fa...@yahoo.com> ---
Thanks Yegor - do you think we should just make your change then?
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 65988] Rate function giving incorrect results
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=65988
Bug 65988 depends on bug 54349, which changed state.
Bug 54349 Summary: Rate formula inconsistency with long periods
https://bz.apache.org/bugzilla/show_bug.cgi?id=54349
What |Removed |Added
----------------------------------------------------------------------------
Status|NEEDINFO |RESOLVED
Resolution|--- |CLOSED
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 65988] Rate function giving incorrect results
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=65988
--- Comment #15 from PJ Fanning <fa...@yahoo.com> ---
See https://poi.apache.org/changes.html -- no date agreed for the POI 5.2.3.
Possibly September but could be later.
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 65988] Rate function giving incorrect results
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=65988
--- Comment #4 from PJ Fanning <fa...@yahoo.com> ---
poi-ooxml-schemas: 4.1.2 is not meant to be used with POI 5 - use
poi-ooxml-lite 5.2.2 instead
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 65988] Rate function giving incorrect results
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=65988
PJ Fanning <fa...@yahoo.com> changed:
What |Removed |Added
----------------------------------------------------------------------------
OS|Mac OS X 10.1 |All
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 65988] Rate function giving incorrect results
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=65988
--- Comment #12 from Yegor Kozlov <ye...@gmail.com> ---
Created attachment 38240
--> https://bz.apache.org/bugzilla/attachment.cgi?id=38240&action=edit
RATE function fixes
Use the implementation of RATE ported from the NumPy library:
https://github.com/numpy/numpy/blob/648fe68f79dfbad21947db4b55873703db0675c4/numpy/lib/financial.py#L580-L650
I added a few more unit tests from the LibreOffice documentation
(https://wiki.documentfoundation.org/Documentation/Calc_Functions/RATE) and
NumPy tests
(https://github.com/numpy/numpy-financial/blob/master/numpy_financial/tests/test_financial.py)
One test case is still disabled: RATE(3,10,900,1,0,0.5) does not converge in
Excel and LibreOffice , but POI and NumPy return a result. Other examples of
infeasible solutions pass, only this one fails.
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 65988] Rate function giving incorrect results
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=65988
--- Comment #9 from Yegor Kozlov <ye...@gmail.com> ---
Its a nuance of the implementation. In some cases it solves the equation wrong.
Fixing the existing code is possible, but may take time as it requires fluency
in numerical recipes.
The NumPy library provides a nice implementation of the RATE function, see
https://github.com/numpy/numpy/blob/v1.17.5/numpy/lib/financial.py#L580-L650
Replacing the existing implementation with the ported code below fixes the bug.
In my test POI returned 0.0009480267492294 which is within the 1E-8 accuracy
with what Excel returns ( 0.0009480170844060).
private static double _g_div_gp(double r, double n, double p, double x,
double y, double w) {
double t1 = Math.pow(r+1, n);
double t2 = Math.pow(r+1, n-1);
return (y + t1*x + p*(t1 - 1)*(r*w + 1)/r) /
(n*t2*x - p*(t1 - 1)*(r*w + 1)/(Math.pow(r, 2) + n*p*t2*(r*w +
1)/r +
p*(t1 - 1)*w/r));
}
static double calculateRate(double nper, double pmt, double pv, double fv,
double type, double guess){
double tol = 1e-8;
double maxiter = 100;
double rn = guess;
int iter = 0;
boolean close = false;
while (iter < maxiter && !close){
double rnp1 = rn - _g_div_gp(rn, nper, pmt, pv, fv, type);
double diff = Math.abs(rnp1 - rn);
close = diff < tol;
iter += 1;
rn = rnp1;
}
if(!close)
return Double.NaN;
else {
return rn;
}
}
Yegor
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 65988] Rate function giving incorrect results
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=65988
--- Comment #2 from PJ Fanning <fa...@yahoo.com> ---
Can you fill in the POI version that you are seeing the issue in? It's possible
that newer versions of POI have better implementations of some functions.
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 65988] Rate function giving incorrect results
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=65988
--- Comment #8 from PJ Fanning <fa...@yahoo.com> ---
according the stackoverflow answer on
https://bz.apache.org/bugzilla/show_bug.cgi?id=54349 - the convergence criteria
in the existing code is wrong
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 65988] Rate function giving incorrect results
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=65988
--- Comment #16 from Pranay <pr...@gmail.com> ---
Thanks PJ Fanning
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org