Created attachment 6528 [details]
Calc spreadsheet that shows mistakes in calculations

changed component to spreadsheet

reassigned to oc

Hi Eike,

the result of calculation is widely the same as in Excel. Excel just
show a value Error if it would run out of scope.

I would like it earlier, possible ?

Frank

Maybe for OOo2.0, but no promise.

I'll work on it.

Created attachment 51351 [details]
version for constrain Nper to integer

Created attachment 51352 [details]
version if Nper is allowed to be non integer

Both patches are based on a m240.
In both cases rates that a lower than -1 are rejected. That will give error
cases mostly same to that of excel with the RATE_problem.sxc document.
In addition the oddf constraint Nper>0 is integrated and it works correct in
cases, where the Guess or the intermediate result value is at a place with zero
slope, where the current version gives an error.

Please have a look, if you like one of them in content or whether I should
change something in the algorithm.

After you decide, I'll try to transport the solution to a version which is based
on CWS odff. Discussion should be continued on dev@sc.openoffice.org, I think.

Created attachment 51357 [details]
Now the correct one, hopefully :)

I had a closer look at the problem. Gnumeric 1.6.3 (Windows) returns the value 0
if the algorithm doesn't converge. That is not correct. Excel returns an error,
if the calculated value is smaller than -1. I don't think we should follow
Excel, because those values are still solutions to the equation, which is given
in the ODFF spec. Gnumeric shows such values.

What I have done:
(1)
Introduce a test for Nper<=0 because ODFF constraints it that way.
(2)
Made a case distinction for Nper integer or not. In ODFF spec it is not yet
decided whether non inter Nper are allowed. If the  non integer Nper will be
forbidden, the case can easily be removed. With this distinction, we get
additional values as they are calculated in Gnumeric.
(3)
Catch special case "solution on an extreme".
(4)
Remove setting non zero PayType to 1, because other values than 0 or 1 are
meaningful and can easily be handled. ODFF spec is not precise in that area.

The text includes comments, so that you can enable the statements, if you don't
like to follow my decision concerning PayType or validity of results <-1.

Additional remark to ODFF spec: The given equation is not defined for a solution
RATE=0. But such situations are possible. Changing the equation to a version
with geometric series instead of the version with fraction would solve that
problem and force Nper to be integer.

The patches are based on m244 with CWS odff. 

Created attachment 51547 [details]
patch for interpr2.cxx

Created attachment 51548 [details]
patch for interpr.hxx

Created attachment 51549 [details]
Additional test cases including the values of Excel, Gnumeric and OOo2.3.1. The examples of the first sheet might be useful as ODFF spec examples too

In cws odff03:

sc/source/core/inc/interpre.hxx  1.32.20.2
sc/source/core/tool/interpr2.cxx  1.35.20.2

Applied last patch as is, except that I added some spaces here and there for
readability ;-) between operators and variable names.

This includes returning values for roots < -1; plus accepting non-integer
PayType other than 0 or 1, with reservation to what the ODFF specification may
define in the end.

@regina: The test case document you attached in rows 9 and 10 has proofs with
comment "should be zero", but for various cases it isn't. I assume the "should"
is to be considered purely mathematical and the difference is within the error
margins of the algorithm used and acceptable?

One more question: does allowing PayType other than 0 or 1 affect the functions
FV, NPER, PMT and PV, would these have to be adapted? Currently they distinguish
between PayType > 0.0 and any other value to do different calculations, the
value itself is not included in the calculation.

Many thanks again for your work!

  Eike

@Eike:
 'I assume the "should" is to be considered purely mathematical'
Yes. I have added rows 16 and 17 to investigate it. The formula in row 17 is
similar to that in row 9 but uses the directly set values in row 16. Now when
you change the last digit in row 16, you can see, that it is not possible to get
zero in some cases. The other problem is the oscillating case (column J), where
the algorithm gives no better solution, because it would be a great effort to
detect and handle such oscillations.

'does allowing PayType other than 0 or 1 affect the functions FV, NPER, PMT and PV'
That functions set other PayType than 0 to value 1 and will work in the same way
as before. They are not affected directly. It is your decision whether to change
them. The ODF says "It is 0 if payments are due at the end of the period; 1 if
they are due at the beginning of the period." but it doesn't say what to do with
other values than 0 or 1, and on the other hand doesn't constrain it to 0 and 1.
Gnumeric uses different values than 0 and 1, Excel07 uses only 0 and 1. It is
the same problem as for RATE.  FV, NPER, PMT and PV use the same equation, so
the same solution as in RATE should be possible.
In addition that functions have the problem with non-integer Nper too.

A decision in the ODF spec is needed badly.

Reassigning to QA for verification.

verified in internal build cws_odff3

This issue is closed automatically and wasn't rechecked in a current version of
OOo. The fixed issue should be integrated in OOo since more than half a year. If
you think this issue isn't fixed in a current version (OOo 3.1), please reopen
it and change the field 'Target Milestone' accordingly.

If you want to download a current version of OOo =>
http://download.openoffice.org/index.html
If you want to know more about the handling of fixed/verified issues =>
http://wiki.services.openoffice.org/wiki/Handle_fixed_verified_issues