ODD{F,L}, Yield and Price spreadsheet functions.

[robert_weir@us.ibm.com]

I had an action item to see if OOXML
has good definitions for ODD{F,L}, Yield and Price spreadsheet functions.

The final OOXML text is not available
publicly, or even to JTC1 or SC34 participants, but I happened upon a copy,
and what I'll report is based on this text of April 30th.

The two functions ODDFPRICE and ODDLYIELD
have full definitions, including a formula.  I have no idea whether
this formula is correct (from a financial perspective) or whether it in
fact reflects Microsoft Excel's calculations.  

For example, ODDLYIELD is defined as:

ODDFYIELD and ODDLPRICE  had useless
definitions.  They just said something like  "Computes the
price per $100 face value of a security having an odd (short or long) last
coupon period", with no formula or reference given.

To move forward on this we need to consider
three possibly different definitions of any spreadsheet function:

1) The correct formula, as determined
by an external authority, perhaps another standard, or a standard textbook
or handbook in the field.
2) What Excel calculates.  This
may differ in some cases with the external authority, for example with
leap year calculations, or the definition of the CEILING function.
3) The formula that OOXML provides.
 We should not assume that this formula accurately reflects what Excel
actually does.  In fact, using these definitions as a short cut to
our own definitions is likely to lead us astray.

I'll drill down more into these financial
functions.   I've ordered some books on financial mathematics that
should explain these and other mysteries of life.



-Rob
___________________________

Rob Weir
Software Architect
Workplace, Portal and Collaboration Software
IBM Software Group

email: robert_weir@us.ibm.com
phone: 1-978-399-7122
blog: http://www.robweir.com/blog/