RE: [office-formula] Difference on ACCRINT?

["Dennis E. Hamilton" <dennis.hamilton@acm.org>]

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