Greetings!
Apologies for the slow foward on additional comments from Arne on
OpenFormula.
Hope everyone is having a great week!
Patrick
-------- Original Message --------
Subject: ODF 1.4 recalculated formula spec suggestions, batch 5
Date: 2026-04-26 05:55
From: "arne@arne-thomassen.de" <
arne@arne-thomassen.de>
To: "patrick@durusau.net" <
patrick@durusau.net>
Hi,
the OpenFormula specification OpenDocument-v1.4-os-part4-formula.pdf
appears to have some flaws. I'd like to suggest the following changes to
individual functions in addition to the batches from my e-mails on 19
January, 15 February, 9 March and 31 March 2026:
- section 6.12 Financial Functions: the declared type of the parameter
Nper is sometimes Integer (for PMT and PPMT) and often Number. Please
make these declarations consistent. It seems to be technically and
semantically proper to use the type Integer for the parameter Nper for
all of these functions. Maybe the return type of the function NPER
should also be Integer. - The occasion for these suggestions is a
potential problem in 6.12.42 RATE: the paragraph "Constraints" only
requires Nper>0. (The wording should be cleaned up BTW.) If Nper may be
a fraction less than 1, the given mathematical formula has undesirable
behavior; the graph of a basic exponential function "flips", the
function decays.
- 6.12.11 CUMIPMT, 6.12.12 CUMPRINC: the type of the formal parameter
Periods should be Integer, matching the types of Start and End and the
prose "The number of periods".
- 6.12.12 CUMPRINC: in the paragraph "Constraints", the table for the
parameter "Type" is given twice (at least in the PDF version), but the
constraints for other parameters are missing. The constraints should
probably be the same as in 6.12.11 CUMIPMT.
- 6.12.16 DOLLARDE, 6.12.17 DOLLARFR: the return type should be Currency
instead of Number.
- 6.12.24 IRR, 6.12.30 NPV: shouldn't the paragraph "Constraints" state
that the "Values" parameters must contain at least one number? When no
numbers are given, NPV returns 0. That's not a big problem for NPV, but
the function IRR refers to NPV and cannot calculate a specific rate when
no number is given in "Values". Arbitrary rates would be a correct
return value of IRR. - Additionally, maybe the parameter type for IRR
should be NumberSequenceList instead of NumberSequence in order to match
the NPV parameter type.
- 6.12.24 IRR, 6.12.42 RATE, 6.12.51 XIRR: please specify or at least
recommend that implementations use the Newton-Raphson method (or a
specific other method) for approximating a solution. (Other methods
exist and might lead to different return values, impairing
interoperability.) Functions like 6.12.32 ODDFYIELD and 6.12.53 YIELD
also seem to need such a method, at least for some cases; please add the
respective hints if true. (These proposals detail some suggestions from
my 31 March e-mail.)
- 6.12.37 PPMT: the given return type Number should be Currency because
it represents a payment (according to the paragraph "Summary"). -
Similarly for 6.12.43 RECEIVED and 6.12.50 VDB and some other functions.
Please check for all functions in section 6.12 which have the return
type Number whether the more specific type Currency or Percentage is
appropriate.
- 6.12.45 SLN: the paragraph "Syntax" says "DDB" instead of "SLN".
- 6.12.45 SLN, 6.12.46 SYD: the paragraph "Constraints" of these two
functions says "None", but the prose for the parameter LifeTime says: "A
positive integer." Please remove the prose and add "LifeTime > 0" to the
paragraph "Constraints". Additionally, the parameter LifeTime has the
declared type Number, but the prose says "integer"; please clear up this
ambiguity.
- 6.12.51 XIRR: the return type Number should be Percentage because the
returned value represents a rate.
- 6.12.53 YIELD, 6.12.54 YIELDDISC, 6.12.55 YIELDMAT and other functions
seem to lack a constraint like "Settlement < Maturity", e.g. to prevent
division by 0. Please add the respective constraints for all functions
in section 6.12 which have DateParam parameters.
- 6.13.6 COUNT: "Only numbers in references are counted; all other types
are ignored." Please specify whether the notion "other types" refers to
the word "numbers", to the word "references" or both. For example,
should the contents of an array be counted, even though the types Array
and Reference are different? Probably, the quoted words "numbers in
references" should simply be shortened to "numbers" because it is
irrelevant in which types or kinds of data structures the numbers exist.
The word "numbers" also seems superfluous because the formal parameter
type is already NumberSequenceList. Maybe the entire quoted sentence
should be removed.
- 6.15.2 AND, 6.15.8 OR: in addition to or as an alternative to my prior
proposal for these functions, please specify the functions "AND.SC" and
"OR.SC", where "SC" stands for short-circuiting. These functions may
always immediately return when the Logical return value cannot change
anymore, ignoring any possible errors that might exist later in the
sequences of input values. If the abbreviation "SC" is too obscure,
please consider identifiers like "AND.LAX".
- 6.16.6 ACOTH: the notion "hyperbolic arc" in the paragraphs "Summary"
and "Semantics" is mathematically wrong. (Hyperbolic functions are
related to areas, not arcs.) Please use the notion "inverse hyperbolic
cotangent" like the ones already used for related functions, e.g. in
6.16.8 ASINH and 6.16.11 ATANH.
- 6.18.2 AVEDEV, 6.18.3 AVERAGE, 6.18.4 AVERAGEA: please add the hint
that these functions calculate the arithmetic mean. (This is useful for
readers who search for the usual notion "arithmetic mean".)
- 6.18.40 LARGE, 6.18.70 SMALL: e.g. says "If N is an array of numbers,
an array of smallest values is returned." Please specify the width and
height of the returned array. For example, should it have the same
dimensions as the input array N; or should it be a column vector or a
row vector? Or should the calculated numbers be stored directly in the
input array N? If N resulted from a cell rectangle, should the
calculated numbers spill into these cells? (The last question is related
to the topic "array formulas" in my 31 March e-mail.)
- 6.20.3 CHAR: the type of the parameter N should be Integer and not
Number. All ASCII characters and all Unicode code points use only
integers.
- 6.20.5 CODE: the paragraph "Constraints" says: "Evaluators may
evaluate expressions where Length(T) > 0.". The operator ">" should
probably be "="; or the word "only" is missing in the sentence, but in
this case, the constraint should simply be "Length(T) > 0" without the
prose.
Please consider these additional suggestions.
Thank you,
Arne Thomaßen</
patrick@durusau.net></
arne@arne-thomassen.de>