Hi Eike,
a remark on the ODFF-spec:
The parameter 'PaymentType' has got type 'number'. In the description
only the values 0 and 1 are mentioned. But there is no constraint to
{0;1}. The description doesn't say what to do with other values. The
parameter 'PaymentType' in the equation can be interpreted as constant
offset to the regular date of payment. So other values are meaningful
too. Therefore the description should explicitly allow or forbid values
others than {0;1}.
The current implementation in OOo uses silently the value 1 for all
values but 0, but makes no use from it in the Newton-algorithm. I'll not
change that.
Other comments inside:
Eike Rathke schrieb:
Btw, in CWS odff I changed (and will change) a lot of things and diffs
created against earlier code respectively current milestones probably
won't apply anymore without adapting them. If possible I would
appreciate patches based on that CWS. This would require though that you
build a CWS version for your changes, not a master milestone. Would that
be feasible for you?
I have not done it yet. Now I have installed WinCvs and have been able
to check out the sc modul with the tag cws_src680_odff. Therefore I
think, that it will be feasible for me in principle. If I get problems,
I will surely get help on the German mailing list.
EIS says the current milestone of that cws is m243, but I find no
download for it. Do I need to get the whole source via check out? Is it
right, that I had to use that milestone for the cws odff?
(2)
The parameter Nper has the type 'number'. May it be non integer? For non
integer Nper there must be RATE>=-1 because of (1+Rate)^Nper. Should
RATE<-1 be excluded in all cases or only if Nper isn't integer?
The ODFF draft working document has a comment about this:
| TBD: In Gnumeric, Nper seems to be truncated if it is not an integer. In
| OOo2, Nper seems not to be truncated if it is not an integer and the
| fractional part is included in the calculation. What does excel do? We
| should include a test for this, but what is the right thing to do? For
| example, in Gnumeric, RATE(12.9,-100,1000) is 2.92% but in OOo2
| RATE(12.9,-100,1000) is 3.88%.
It wasn't decided yet whether it should be required to truncate Nper to
integer. Usually, if there is no mathematical requirement to do so, so
far we did not put constraints on argument values. From your view as
a mathematician, does it make sense to allow non-integer Nper values?
I would say, that non-integer Nper values are questionable. You can use
such non-integer Nper values in the given equation and the
Newton-algorithm will work and the results will be valid roots. But the
term '((1+x)^Nper-1)/x' is a shortened result of the underlaying sum
'sum from i=0 to (Nper-1) (1+x)^i' and therein a non-integer Nper value
makes no sense.
If
the implementation works correctly for both, integer and non-integer,
I'd say support also non-integers. In case we'll define integers only
should be supported it should be fairly easy to restrict implementation
accordingly.
The right side of the equation has a domain of [-1;infinity[ for
non-integer Nper values. If Nper is an integer value, one can use a
domain including values <-1, for a pure mathematical view. For "real
live" use cases, I think, that mathematical solutions <-1 are senseless.
That claims the reporter of the issue too. Therefore I like to set such
"solutions" to invalid, if you agree. Doing that, it would make no
difference in the central algorithm whether you use only integer values
or not.
(3)
„RATE solves the equitation...“
In many cases there is not only one solution but there are two, sometimes
three. Currently OOo returns solutions, which are far away from 0, Excel
doesn't, what the issue is about. But what to do, if the user starts with a
GUESS that will lead to such a solution and the user sets GUESS to that
value with the intention to get such solution?
Would the result be correct then? Even if far away from 0? If not, we
should generate an error, I think.
Yes, the results are correct in a pure mathematical view.
Leaves the question to define a limit
for "far away" ...
That's the problem. I like to set results <-1 to invalid, whether they
are mathematically correct or not, see above.
(4)
What role should GUESS have? "start the iterative computation" makes not
sure, that different implementations will return the same value.
Unfortunately algorithm and implementation are not defined, also
ECMA/MOOXML doesn't tell anything, as usual. Do you know algorithms that
would yield better results than others? Maybe we can define them in
ODFF.
The now used Newton-algorithm works fine in most cases and is very fast
(quadratic). Another one is "Regula falsi". The latter requires a second
initial value, so that the function has a change of sign in between.
Depending on the way how this value is determined, the solution from
Regula falsi might differ from that of the Newton-algorithm.
(5)
What should RATE return, if solutions exist, but values are far away from
0? Example: Nper=4; Pv=-1; Payment=0.82; Fv=2. It has one solution round
RATE=100% and another one round RATE=-215%. Excel gives an error in this
special case.
If you can detect that, I think generating an error is appropriate.
On the negative side, rejecting values lower than -100% might be
meaningful (see above).
I have examined an example, which has no financial context: You inject
drug with constant amount and period and at the end you measure the
concentration in the body. You want to know the concentration decreasing
rate. In this case, you might get rates which are considerable greater
than in financial context.
I looked at the example Nper=4; Pv=-1; Payment=0.82; Fv=2 again. The
error in Excel arises because the algorithm converges to the "wrong"
root. If you start with an adapted GUESS, for example 100%, you get the
solution value '90%' in Excel.
Therefore I think now, that on the positive side no general limit is
possible.
I'll come back with a draft version in a while.
kind regards
Regina
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
