Hi Andreas, On Saturday, 2007-11-17 21:51:20 +0100, Andreas wrote:
> I hope this is the right forum for my mail. If not, please point me to the > proper one. For spreadsheet specific devloper discussion we have the [EMAIL PROTECTED] mailing list. We might move over there if it turns out that further in-depth discussion is needed. > The following topic is non-trivial, so please bear with me. Ah, yes, the dark chapter of day count algorithms.. in the OASIS OpenDocument Format Formula specification subcommittee we struggle with that as well, namely for the YEARFRAC() function and all related financial functions that are based on it. > CALC supports the function DAYS360(...;...;type) where type is 0 or 1. > > The following is all about this function. > > The online help says, for type = 0 the US method (NASD, National Association > of Securities Dealers, Inc.) is used, whereas for type = 1 (or <> 0) the > European method is used. > > (1) To begin with: The results calculated with this function are the same > (for > the examples I checked), as those evaluated with Microsoft Excel 2000. > > Question: What is the importance of this fact? The DAYS360() function was implemented to follow Excel calculations and deliver the same results. So yes, this is important. > (2) The documentation is way to short. There are a couple of interest > calculation methods used in practise , that are of the type "30/360". These > interest calculation methods all need a corresponding type of this function. As the function strives to achieve compatibility with Excel, treating all available day count algorithms wasn't of interest. We may add another day count function in future with a different name and behavior though. > A detailed definition of the interest calculation methods can be found in > > [2] http://www.swx.com/download/trading/products/bonds/accrued_interest_en.pdf Thanks for the pointer. Some additional documentation is available at http://www.isda.org/c_and_a/pdf/mktc1198.pdf > (3) For all examples I tried, DAYS360 for type = 1 yields the same results, > as "Special German", also denoted as 30S/360 or 30E/360. Another name is > Eurobond basis. > > So, I >guess<, DAYS360 for type = 1 is meant to be this method, although it > is > called "European method" in the documentation. > > Question: Is that so? If not, what method is it? Comparing with the implementation: yes, it is the German 30S/360 method, no special treatment of February 28/29 dates. Note that when calling it "Eurobond basis" it is important to mention ISDA 2006 as well, as Eurobond basis ISDA 2000 (30E/360 ISDA) calculates differently. > (4) I guess DAYS360 for type = 0 is wrong! Actually it isn't, compared to what Excel does that is.. just that it is hard to find a definition of the algorithm used. No, also the Excel online help that was copied to the ECMA/MOOXML specification doesn't have it. I once had a reference explaining it, unfortunately that vanished from the web: http://www.bondmarkets.com/eCommerce/SMD_Fields_030802.pdf It also said that Excel was the only application implementing this method.. Basically the method is called PSA 30 or NASD 30, date adjustment is: If D1 is 31 or the last day of February (29 in leap years, 28 in non leap years) then change D1 to 30. If D2 is 31 and D1 is 30 then change D2 to 30. > I >guess<, that the US-method according to NASD is the same as 30U/360, as > described in references [1,2]. For all examples, that are given in reference > [3], one gets the same results. No, it's different in how it treats D2 if both D1 and D2 are the last day of February. What you obviously noticed in your tests. > From a practical point of view, all three examples given above, should yield > a > day count that is a multiple of 30. This is the reason I think, that the CALC > function (and Microsoft Excel, for that matter) is wrong. As said, the OOoCalc function followed Excel for interoperability and implemented the PSA 30 method. > Question: Is this right, or is there a more precise definition of the NASD US > method given in some normative source, that proves me wrong? I don't have a normative source at hand. However, MS Brian Jones for this matter at some point mentioned Standard Securities Calculation Methods: Fixed Income Securities Formulas for Analytic Measures, Vol. 2, Spring 1995. I don't know though if that would be a reference for what MS-Excel implemented or what they documented instead.. > Question: Is there a way to improve CALC (code and documentation), so that > all "30/360" interest calculation methods are supported? (One could do it in > a way that does not break compatibility to the "bug" in Microsoft Excel). Only if you changed the type's parameter type from boolean to number (incompatible in MS-Excel) and introduced other type values, or added a 4th parameter that again would break export to MS-Excel. A completely new function would be the way to go. Eike -- OOo/SO Calc core developer. Number formatter stricken i18n transpositionizer. SunSign 0x87F8D412 : 2F58 5236 DB02 F335 8304 7D6C 65C9 F9B5 87F8 D412 OpenOffice.org Engineering at Sun: http://blogs.sun.com/GullFOSS Please don't send personal mail to the [EMAIL PROTECTED] account, which I use for mailing lists only and don't read from outside Sun. Use [EMAIL PROTECTED] Thanks.
pgpLi47U3vO0t.pgp
Description: PGP signature
