When this whole business started, it was supposed to be about the
Excel DAYS360 function, wasn't it?
The whole purpose of DAYS360 is to realize a calendar of 360 days, consisting of 12 months of 30 days each. The idea is, if the month happens to consist of 30 days, leave it alone. If not, adjust it so that it does. Let's restate exactly what this means.
Define the starting date (Dt1) and the ending date (Dt2) as follows:
Dt1 = MM1/DD1/YYYY1
and
Dt2 = MM2/DD2/YYYY2
For example, If the starting date were March 5, 2007, then MM1 = 03, DD1 = 05, and YYY1 = 2007.
Then, every 30/360 calendar ever devised attempts to define the days between Dt1 and Dt2 (Days) as
Days = (DD2 - DD1) + 30*(MM2 - MM1) + 360*(YYYY2 - YYYY1)
All of the differences and tricks and complications come from adjusting the days and months to fit the requirements of 30 days per month, regardless of what the Gregorian Calendar might suggest. Different systems use different conventions, but they all have one thing in common: after all the conventions are applied, neither DD1 nor DD2
ever equal 31.
Here is an example of a series of dates, each falling on the first of the month (all values as calculated with the Office 2003 version of Excel, set for the US calculation). We expect that each month should have 30 days:
Date 1 Date 2 Days
--------- -------- -----
1/1/2009 2/1/2009 30
2/1/2009 3/1/2009 30
3/1/2009 4/1/2009 30
4/1/2009 5/1/2009 30
5/1/2009 6/1/2009 30
6/1/2009 7/1/2009 30
7/1/2009 8/1/2009 30
8/1/2009 9/1/2009 30
9/1/2009 10/1/2009 30
10/1/2009 11/1/2009 30
11/1/2009 12/1/2009 30
12/1/2009 1/1/2010 30
======
360
And we see that we do. And you get the same result if you try the 2nd, 3rd, ... up to the 27th of the month. And, we would expect the same result for the 28th. But, when we plug these dates into the formula we get:
Date 1 Date 2 Days
========= ========= ====
1/28/2009 2/28/2009 30
2/28/2009 3/28/2009 28
3/28/2009 4/28/2009 30
4/28/2009 5/28/2009 30
5/28/2009 6/28/2009 30
6/28/2009 7/28/2009 30
7/28/2009 8/28/2009 30
8/28/2009 9/28/2009 30
9/28/2009 10/28/2009 30
10/28/2009 11/28/2009 30
11/28/2009 12/28/2009 30
12/28/2009 1/28/2010 30
===
360
No, I have not forgotten how to add. DAYS360(1/28/2009, 1/28/2010) happens to equal 360 and not 358. Here, the sum of the individual months does not equal the year.
Which brings up two questions:
- Is this a bug?
- What is the correct value for Days360('2/28/2009','3/28/2009') ?
Let's put this aside for a moment, and ask the question: what happens if we stipulate that the dates are to be the last of the month rather than the first. Security traders do not like to stipulate that last of the month (for reasons that will become obvious) and lenders greatly prefer to specify a date, rather than just "the last of the month". However, this is not an impossible question, and we should
expect a definite answer.
One would expect a 30/360 calendar to give a result similar to that from the 1st of the month. But, we find instead:
Date 1 Date 2 Days
========= ========= ====
1/31/2009 2/28/2009 28
2/28/2009 3/31/2009 30
3/31/2009 4/30/2009 30
4/30/2009 5/31/2009 30
5/31/2009 6/30/2009 30
6/30/2009 7/31/2009 30
7/31/2009 8/31/2009 30
8/31/2009 9/30/2009 30
9/30/2009 10/31/2009 30
10/31/2009 11/30/2009 30
11/30/2009 12/31/2009 30
12/31/2009 1/31/2010 30
====
360
And you know by now where the 360 came from. Examining the 1/31/2009 to 2/28/2009 case more closely, the formula works out as
Days = (28 - 30) + 30*(2 - 1) + 360*(2009 - 2009) = 28
where DD1 was changed from 31 to 30 (we are supposed to have 30 day months, remember?)
But, 2/28 is the last day of the month. And we are supposed to have 30 day months (remember?). Which suggests that DD2 should also be changed to 30. In which case, Days becomes
Days = (30 - 30) + 30*(2 - 1) + 360*(2009 - 2009) = 30
Except that then 1/28/2009 to 2/28/2009 becomes
Days = (30 - 28) + 30*(2 - 1) + 360*(2009 - 2009) = 32
There are other anomalies around Feb. 28 as well:
Date 1 Date 2 Days
========= ========= ====
2/1/2009 2/26/2009 25
2/1/2009 2/27/2009 26
2/1/2009 2/28/2009 27
2/1/2009 3/1/2009 30
1/30/2009 2/26/2009 26
1/30/2009 2/27/2009 27
1/30/2009 2/28/2009 28
1/30/2009 3/1/2009 31
The transition from Feb. 28 to Mar. 1 doesn't "seem right", no matter what approach one takes.
But we are supposed to be talking about the Excel DAYS360 function. We may as well start with the Microsoft documentation for Office 2003 (which can be found at
http://office.microsoft.com/en-us/excel/HP052090471033.aspx:
U.S. (NASD) method. If the starting date is the last day of a month, it becomes equal to the 30th of the same month. If the ending date is the last day of a month and the starting date is earlier than the 30th of a month, the ending date becomes equal to the 1st of the next month; otherwise the ending date becomes equal to the 30th of the same month.
If this is correct, then DD2 should be changed to 30 from 28 in the month of February.
While Microsoft must be given that last word for what their DAYS360 function is supposed to do, they also mention that it is supposed to emulate the NASD day counting method. Exactly what that method is supposed to do should not be open for question -- except that I have not been able to find any conclusive documentation about it. I have found a couple of references which attempt to get into this level of detail:
http://www.six-swiss-exchange.com/download/trading/products/bonds/accrued_interest_en.pdf from the Swiss Exchange, and
http://www.eclipsesoftware.biz/DayCountConventions.html?
from the Eclipse foundation. But neither seems conclusive.
In addition, each refers to securities. My interest sprang from the small loan industry, which, in the U.S., is State and not Federal regulated. It is quite common (or, at least it used to be) for an identical loan pattern to pay a different number of days interest in different states. So, depending on the use one is making of these formulae, none of them may be correct.
So, I'm not going to offer any corrections. My assumption is that anyone who has read this far must have gained the ability to think for themselves. You, dear reader, know what the constraints of your loan regulations far better than I, and you now have everything you need (or at least everything I can give you) to adapt them to your unique situation.
No comments:
Post a Comment