Re: [office-formula] Difference on ACCRINT?

[Patrick Durusau <patrick@durusau.net>]

Dennis,

Don't hold back! Tell us what you really think. ;-)

Still, it should be instructive to know what has been written by the 
other major effort in this area.

Hope you are having a great day!

Patrick

Dennis E. Hamilton wrote:
> The OOXML (IS 29500-1:2008) section 18.17.7.2 ACCRINT has much more detail
> than the one in OpenFormula (OpenDocument-formula-20091221.odt) section
> 5.12.2 ACCRINT.  
>
> [I don't know if the OOXML ACCRINT is impacted by any DCOR or Amendment
> since IS29500-1:2008 was issued.)
>
> Although there are surface similarities between the two descriptions, there
> is no way to tell, at the technical level, whether the same results are
> obtainable and, if not, what the differences are.  One has more detail but
> those details are too non-specific and appeal to too many undefined terms to
> determine what the calculated result should be.  The other has no details
> and only test results, but no explanation of what rules would lead to their
> being matched.  
>
> They both deserve summary execution.
>
>  - Dennis
>
> FURTHER OBSERVATIONS
>
> I know what questions to ask in order to demystify this function and bring
> it down to a description that can be implemented and confirmed, but I have
> no idea what the answers are supposed to be in getting to that.  
>
> It is enlightening to perform an Internet search for "accrued interest" and
> learn how far those "familiar" terms wander from what needs to be known
> here.  The Wikipedia explanation is helpful although it raises even more
> uncomfortable questions about coupon dates:
> <http://en.wikipedia.org/wiki/Accrued_interest>.
>
> COMPARISON DETAILS
>
> The OpenFormula ACCRINT Summary is equivalent to the IS 29500 Summary.
>
> The OpenFormula ACCRINT Syntax is equivalent to the IS 29500 Syntax except
> that IS 29500 allows the fifth parameter (par value) to be omitted.
>
> There are differences in the parameter/argument (pseudo-)types and
> descriptions as follows:
>
>                       Type       Description
> Parameter     OpenFormula  OOXML  
>
> #1 issue      DateParam    Number  issue [or dated] date
> #2 first      DateParam    Number  first interest date (with no clue what
> this means - does interest start here or is this the date for which the
> first coupon payment is to occur, or something else entirely)
> #3 settlement DateParam    Number  settlement date
> #4 coupon     Number       Number  annual coupon rate
> #5 par        Number       [Number=1000] par value
> #6 frequency  Integer      Number(truncated) number of coupon payments per
> year (1, 2, 4 allowed)
> #7 basis      [Basis = 0]  [Number = 0] different rules for the numbers 0 to
> 4
>
> The basic differences are that OpenFormula allows frequency = 12, the basis
> rules are different for the same 0 to 4 values and that OOXML allows par to
> be omitted when 1000 is understood.
>
> Common Constraints
>   issue < settlement
>   coupon > 0
>   par > 0
>
> Different Constraints
>   OpenFormula also stipulates that issue < first < settlement and we have no
> idea whether omission of that constraint is a mistake in the OOXML
> description or whether first is indeed allowed to match or even be beyond
> issue or settlement
>   OOXML limits frequency to one of 1, 2, 4
>   OpenFormula limits frequency to one of 1, 2, 4, and 12
>   OOXML limits basis to 0, 1, 2, 3, 4
>
> Error cases
>   OOXML specifies #NUM! as the result when constraints are violated  [There
> may be additional errors based on syntax violations and errors in any of the
> OOXML arguments and that may be different from the OpenFormula error and
> error propagation cases.]  
>   OpenFormula gives no specific failure cases.
>
> Semantics
>   None are provided for OpenFormula ACCRINT other than the listing of the
> argument summaries
>
>   Separate from the argument summaries, OOXML provides a mathematical
> description of the formula and how the ACCRINT arguments figure into the
> computation.  (My copy of IS 29500 has errors in the explanation of formula
> terms apparently stemming from the generation of the PDF from some other
> source. For example, the first bullet under "where:" has "A[i]Ai = " where
> "A[i] = " should be read (with [i] as a subscript i).
>
> OOXML also specifies basis rules corresponding to the allowed values of the
> basis parameter, whereas OpenFormula has its own consolidated definitions.
> They are not easily compared and are to be presumed different based on the
> notes in the OpenFormula text.
>
> There are too many undefined terms in the OOXML semantics (e.g.,
> "quasi-coupon") and some of the definitions are suspect (e.g., "Odd long
> last coupon").  It is my considered opinion that the definitions for A[i],
> NC, and NL[i] are gibberish in this context and cannot serve as an
> interoperable definition of a spreadsheet function.
>
> For the OpenFormula cases all we have are test cases and we have to guess
> whether or not those cases demonstrate the same results that the
> (de-gibbered) OOXML semantics lead to.  Knowing that some version of
> Microsoft Excel might produce the same results for the test cases doesn't
> help either.
>
>
> -----Original Message-----
> From: Patrick Durusau [mailto:patrick@durusau.net] 
> http://lists.oasis-open.org/archives/office-formula/200912/msg00154.html
> Sent: Wednesday, December 23, 2009 17:25
> To: office-formula@lists.oasis-open.org
> Subject: [office-formula] Difference on ACCRINT?
>
> Hello,
>
> Compiling yet another comparison.
>
> Can anyone enlighten me on the difference between the OpenFormula 
> definition of ACCRINT and ACCRINT as defined by ISO 29500, 18.17.7.2?
>
> (I am aware that in ISO 29500 the syntax uses the term "rate" where 
> OpenFormula uses "coupon" but that isn't a significant difference.)
>
> Hope everyone is enjoying the holiday season!
>
> Patrick
>
>   

-- 
Patrick Durusau
patrick@durusau.net
Chair, V1 - US TAG to JTC 1/SC 34
Convener, JTC 1/SC 34/WG 3 (Topic Maps)
Editor, OpenDocument Format TC (OASIS), Project Editor ISO/IEC 26300
Co-Editor, ISO/IEC 13250-1, 13250-5 (Topic Maps)