COUPDAYS and DAYS360
Oct
22
Written by:
Charles Flock
10/22/2008 7:00 PM
Differences between the COUPDAYS calculation when the basis is 0 and DAYS360 function and why February is really the cruelest month.
In EXCEL it turns out that there are 2 ways to calculate the number of days in a coupon period when the basis is 0, US (NASD) 30/360, or 4, European 30/360. The first method is to use the DAYS360 function, which returns the number of days between a start date and an end date and uses whichever method is specified by the method switch (you can find the EXCEL documentation here http://office.microsoft.com/en-us/excel/HP052090471033.aspx?pid=CH062528231033).
The second way to do this is to use the COUPDAYS function. COUPDAYS returns the number of days in the coupon period in which a settlement occurs. For calculation purposes, it is assumed the coupon period has a previous coupon date that is less than or equal to the settlement date and a next coupon date greater than the settlement date. And there are EXCEL functions that calculate these values; COUPPCD and COUPNCD.
Basically, then, in our testing, we expected the following condition to be true:
COUPDAYS(settlement,maturity,frequency,0) = DAYS360(start_date,end_date)
where
Start_date = COUPPCD(settlement, maturity, frequency, 0)
end_date = COUPNCD(settlement, maturity, frequency, 0)
In almost all cases, this turns out to be a TRUE condition. The problem arises when you have a maturity date of August 28th or 29th and the frequency is 2 or 4.
When the basis is 0 and the frequency is 2, COUPDAYS always returns 180 (as least as far as we can tell). Assuming that you had a settlement date of 3/1/2009, maturity date of 8/28/2013, a frequency of 2, and a basis of 0, you would get the following results in EXCEL:
|
COUPDAYS
|
180
|
|
COUPPCD
|
2/28/2009
|
|
COUPNCD
|
8/28/2009
|
However, DAYS360(2/28/2009, 8/28/2009) returns a value of 178. So we tried this out on a few bond calculators and they were all pretty unhappy with these dates. It seems as though the rules for US 30/360 aren't particularly happy with these conditions either, as a bond paying regular periodic interest on a semi-annual basis would have 180 coupon days in each coupon period, other than an odd period. But, strict application of the published rules provides the result returned by the DAYS360 calculation.
The moral of the story? Don't issue or buy bonds with a maturity date of August 28th that pay interest semi-annually or quarterly and accrue interest using the US 30/360 day count convention.