OASIS Mailing List ArchivesView the OASIS mailing list archive below
or browse/search using MarkMail.

# office-formula message

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

Subject: RE: [office-formula] frequency

• From: "Dennis E. Hamilton" <dennis.hamilton@acm.org>
• To: "'Andreas J Guelzow'" <aguelzow@math.concordia.ab.ca>,<office-formula@lists.oasis-open.org>
• Date: Wed, 23 Dec 2009 10:41:06 -0800

```Hi Andreas,

1. RATIONALIZATION OF SPECIFICATION OF FREQUENCY PARAMETER VALUES.

I agree there are only a couple of cases for what the frequency values are,
but having the tabular definition is useful, and it would satisfy Patrick's
sensibilities around redundancy if the specific table could be linked from
the description of a parameter value.  It looks like there are only 1,2,4
and 1,2,4,12 defined anywhere.

(By plausible, I meant that they are all exact divisors of 12 so the
intervals are exact numbers of months that roll up to 12 in the specified
number of annual payments.  I did mean to suggest that we allowed all such
frequency values on all of the functions that have such a parameter.)

Here is where I see contradiction in COUPDAYBS:

2.1 "Summary: ... the number of days between the beginning of the coupon
period that contains a settlement date and the settlement date."  (I
corrected the use of articles here.)

2.2 "Semantics: [returns] the number of days from the beginning of the
coupon period to the settlement date."

2.3 The Summary is more precise than the semantics (?!).

2.4 Also, there are an immense number of unstated assumptions, such as how
the maturity and settlement parameters are different and how one determines
the start date of the coupon period containing the settlement date
(presumably the date given as the value of the settlement parameter).

2.5 This is terribly ill-defined.  I could make up a precise definition
but the key is how the coupon periods are determined from the information
given.  That is, does maturity date have anything to do with it or does the
calendar and the basis determine coupon periods with no consideration of the
settlement and maturity dates?  (It may well be that in practice maturity
dates are chosen to have things end well, but who knows?)  These things have
to be nailed down so the functions can be audited and so user level
documentation can provide the proper guidance about what to expect when
using a spreadsheet where this function is the OpenFormula one.
Determination of how this function is usable in regard to a particular
financial practice is not our business, but there must be enough information
where a subject-matter expert would know whether, how, and when to every use
it.  (Also, this should be well-defined enough where an implementation could
be compared with long-hand versions of the computation using the same

3. ABSTRACTING AWAY CASUAL USE OF FAMILIAR BUT UNDEFINED/ILL-DEFINED TERMS
(ODDLYIELD)

3.1 Yes, I agree we should abstract away anything except what the
parameters are as parameter values and how they are used in the calculation,
not how they are arrived at based on external knowledge of a particular
financial arrangement or practice.  I tried this on a very easy example with
ACCRINTM
(<http://lists.oasis-open.org/archives/office-formula/200912/msg00132.html>)
and I think we should endeavor to do this for all of these.  Of course,
there is more missing information that has to be unearthed to do this with
the more complicated functions.

3.2 I am not quarreling about how one understands %, rather about how one
expects rate values to be supplied and delivered.  I am concerted about
confusion between how a field is formatted (and what happens on data entry)
and what the calculation is and what the numbers are in the calculation.  I
don't think % symbols should be used in the test cases (but I also expect
the test cases to disappear).

- Dennis

-----Original Message-----
From: Andreas J Guelzow [mailto:aguelzow@math.concordia.ab.ca]
http://lists.oasis-open.org/archives/office-formula/200912/msg00131.html
Sent: Tuesday, December 22, 2009 19:53
To: office-formula@lists.oasis-open.org
Subject: RE: [office-formula] frequency

On Tue, 2009-12-22 at 18:11 -0800, Dennis E. Hamilton wrote:
http://lists.oasis-open.org/archives/office-formula/200912/msg00130.html
> 2. COUPDAYBS
[ ... ]
>
>    2.2 I would not say anything about interest because interest is not
> involved in this calculation and we have no idea how coupon value is
> determined and what the amount associated with a coupon is.  We are
> concerned only with a calendar question.

correct

>
>    2.3 I do note, however, that the summary and the semantics are
> contradictory, and I am not sure that settlement date is the correct term
> here.

I don't see the contradiction (unless you believe that the two
statements say different things about whether the settlement and
maturity dates are included or not.)

[ ... ]

> 3. ODDLYIELD
>
>    3.1 We could definitely use some consistency here.
>
>    3.2 Change the parameter names to be in lower case.
>
>    3.3 Move the identification of parameters as I have already proposed.
>
>    3.4 Use the same layout for the list of parameter names with their
> summary descriptions as elsewhere, including italic names.
>
>    3.5 I think we will find all sorts of difficulties in the weeds for
this
> function.  For example, the test-case results are given as %-ages, but the
> result type is Number.  So is the actual result of the first test case
> (independent of how it might be formatted for presentation) .049977.. or
> 4.9977.. ?  I can ask similar questions about the rate parameter.

I never understand the confusion about "percentages". They are just
numbers:  4.9977% == 0.049977 == 4.9977 x 10^-2 ==...
>
>    3.6 I have a feeling that settlement and maturity here correspond to
> issue and settlement of ACCRINT, and ACCRINT uses coupon where rate is
used
> in ODDLYIELD.  There is other slop to figure out in order to achieve
> consistency.

Do you want consistency within OpenFormula or consistency with the terms
used in accounting that uses these functions? In the latter case some
functions are intended to calculate the accrued interest prior to the
settlement date others relate to interest between settlement and
maturity. If it is the former, perhaps we should really just use
fromdate, todate, etc.

Andreas
>

--
Andreas J. Guelzow
Concordia University College of Alberta

---------------------------------------------------------------------
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

```

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