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


Help: OASIS Mailing Lists Help | MarkMail Help

office-formula message

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

Subject: RE: [office-formula] "settlement The settlement date." - DISAPPEAR IT

Let's see if we can unpack this a little.

1. Parameter Naming

   1.1 First, settlement date is used in more than one way in the
descriptions of the various financial functions in OpenFormula.  You won't
find one meaning for all of the occurrences.  In addition, settlement date
in these functions is often narrower than what might be called the
settlement date in an actual securities transaction.  It would be better to
neutralize the description of the function down to what it calculates, not
some particular thing it might be used for. 

   1.2 The right hand side of a parameter description is not defining a
meaning for the label, it is defining (often only suggestively) the kind of
parameter value to be supplied for the parameter having that name in the
parameter list of the function syntax.  It's all about identifying the place
in the list.  It's about the parameter, not the parameter's name.  The fact
that the spelling of the parameter name is that of a familiar word is
intended to be helpful, although we should be noticing that such is not
always the case when the parameter name suggests more than is actually

   1.3 Finally, I agree that we are not consistent in specifying the
semantics of some parameters, although we need to be careful because this
difference may having nothing to do with the naming of the parameter -- the
naming may simply be ill-chosen along with the semantics being

   1.4 I disagree that rationalizing the definitions of parameters can be
done, at this time, without first making sure the definition is technically
sound.  We would be abusing your services to do otherwise.


   2.1 The descriptions associated with the named parameters seem rather
casual in the function descriptions I've inspected today.  Mostly what we
have in something like 

  Syntax: ACCRINT(DateParam issue; DateParam first; DateParam settlement;
Number coupon; ...)

  Returns: Currency 



     settlement   the security's settlement date

     coupon       the security's annual coupon rate

   2.2 All we know for sure is that the third parameter of ACCRINT is of
type DateParam and that it may marke some sort of settlement date when funds
change hands, or interest determination ends, or ...

   2.2 We also know that the fourth parameter is a positive, non-zero number
that is a "coupon rate" (and in this case it is definitely a fixed,
non-compounded annual interest rate).

But really, all we know is that settlement (in italics) is the third
parameter and it is a date representation.  To know what is going on, we
need to know how the date influences the ACCRINT result.  And we haven't
been told that.  We also need to know how the coupon rate and the frequency
are used together in determining a single regulary interest/coupon payment.
There are a number of things like this that we could be told and know
exactly how to understand what the ACCRINT result is.  (The hardest of all
is perhaps to understand how the day-count basis influences the result.)


   3.1 ACCRINTM is probably the simplest function there is in this category.
I am going to describe it in a different way to show how to (1) remove it
from a specific application and (2) give more precision to the parameters.  

   *** IMPORTANT: This description doesn't change how ACCRINTM is
implemented or used correctly in practice.  It doesn't change at all what
the ACCRINTM function is.  The changes are all descriptive in terms of how
the result is determined from the parameters, whatever they are called.
I've simply uses the simplest terms that fit what the function is and made
it easier to tie that to the semantics for the function.


Summary: the accrued simple interest that is earned at the end of a defined
calendar interval

Syntax: ACCRINTM(DateParam startDate; DateParm endDate; 
                 Number rate; Currency principal 
                 [; Basis basis=0] )

Returns: Currency value of the accrued interest at the end of the interval


  startDate  the calendar date on which the interest period begins

    endDate  the calendar date on which the interest period ends

       rate  the annual rate at which interest is earned

  principal  the amount on which interest is calculated

      basis  the numeric code for the basis on which time periods are
converted into 
                 fractional-year durations (section 3.10.6)

Constraints: startDate < endDate; rate > 0; principal > 0

[Note, the current OpenFormula definition does not constrain the startDate
and endDate relationship, and they can even be values for the same date.
I'm not sure that is universally accepted practice and we should be more
cautious than that, seems to me.]


  The ACCCRINTM result = principal*(rate*Y)

where Y is the number of fractional years (whole years plus any fractional
partial year) in the period from startDate to endDate in accordance with the
basis selection.  

   3.2 I have swept a number of matters under the rug, leaving them to be
resolved in how the basis is handled.  If we know which of startDate and
endDate is not in the interval, we know there are a number of cases where Y
is an exact integer or is an exact number of 12ths, and a couple of these
would be good to offer as examples.  These are also more-likely real-world
cases, even though the function determines results for intervals as short as
one day or as long as several years plus an odd number of days.

   3.3 I have done my best to say what the function *is* rather than what it
might be used for.  There's a fair amount of dependency on reality, because
the function requires real calendar intervals and the basis determinations
are seriously involved with how periods between dates are influenced by the
numbers of days in months, the spanning of leap-years (or at least a
February 29th), and so on.

   3.4 My suggestion for illustration beyond precisely what the function is
and demonstrate that there are many ways it might be used would be to offer
a couple of non-normative but indicative examples of use of the function in
a practical situation.  One might be in figuring the interest returned when
a bond having a fixed, non-compounded interest rate paid at maturity is
settled: the principal is returned with the interest.  There might need to
be a disclaimer that this is only an approximation of the results obtained
in actual arrangements with lenders, borrowers, and securities brokers
because of differences in terms, period determination, basis, and
calculation details.  I wouldn't go too far down this road in the

 - Dennis

-----Original Message-----
From: Patrick Durusau [mailto:patrick@durusau.net] 
Sent: Tuesday, December 22, 2009 16:40
To: dennis.hamilton@acm.org
Cc: office-formula@lists.oasis-open.org
Subject: Re: [office-formula] "settlement The settlement date."


Dennis E. Hamilton wrote:
> I think there is a continuing misunderstanding on what those "lists of
> terms" are.  I would like us to clear that up before we get too deeply
> massaging things.
[ ... ]
What do you think a "parameter value" for a "symbolic label" is if it 
isn't a "definition" of that label? Just curious.

Besides, your post misses the more important point that no matter what 
you call the left hand terms or even if they are symbols, we have not 
been consistent in specifying their semantics. Or at least that is what 
the spreadsheet I am constructing is designed to help members of the SC 
> 5. Being more precise about how "settlement date" is to be understood goes
> elsewhere, perhaps in the Semantics or in a preceding section of common
> terms used in the description of these financial functions.  There is a
> more that could be in the Semantics section about what these parameters
> represent and how they are calculated with.
Yes, there are all sorts of things we could define. What I am hoping we 
can focus on are things that are mentioned now in the draft and getting 
those defined. There may be others that need to be added but I would be 
happy just to enumerate all the known cases in the current draft.
[ ... ]

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