[Date Prev] | [Thread Prev] | [Thread Next] | [Date Next] -- [Date Index] | [Thread Index] | [List Home]

*Subject*: **RE: [office-formula] Difference on ACCRINT?**

*From*:**"Dennis E. Hamilton" <dennis.hamilton@acm.org>***To*: "'Patrick Durusau'" <patrick@durusau.net>,<office-formula@lists.oasis-open.org>*Date*: Thu, 24 Dec 2009 09:11:27 -0800

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) --------------------------------------------------------------------- To unsubscribe from this mail list, you must leave the OASIS TC that generates this mail. Follow this link to all your TCs in OASIS at: https://www.oasis-open.org/apps/org/workgroup/portal/my_workgroups.php

**Follow-Ups**:**Re: [office-formula] Difference on ACCRINT?***From:*Patrick Durusau <patrick@durusau.net>

**References**:**Difference on ACCRINT?***From:*Patrick Durusau <patrick@durusau.net>

[Date Prev] | [Thread Prev] | [Thread Next] | [Date Next] -- [Date Index] | [Thread Index] | [List Home]