Now that’s ODD
Oct
31
Written by:
Charles Flock
10/31/2008 7:00 PM
We found something interesting while testing our ODDLYIELD function
In our testing of the ODDLYIELD function, we had a lot of problems getting our results to agree with the EXCEL calculations. For example, we performed the following calculation:
SELECT wct.ODDLYIELD('11/1/2007'
,'3/31/2008'
,'9/17/2007'
,.070995034
,100.3774830392
,100
,2
,0)
Which returned the following results:
----------------------
0.0638662498348664
(1 row(s) affected)
However, plugging the same values into EXCEL, gave us 0.064457604. As we usually do in these situations, we went back to double check our calculations.
The calculation of ODDLYIELD, when the settlement date is greater than or equal to the last interest date, is straightforward in that it does not require iteration. Basically, the formula is:
(mat_value - dirty_price) / dirty_price * frequency / DSC_fraction
Where:
mat_value is the redemption amount plus the interest due at maturity
dirty_price is the price plus the accrued interested as at the settlement date
frequency is the number of time coupons are paid per year (1, 2, or 4)
DSC_fraction is the time until maturity as a fraction of the coupon period.
So, for this particular calculation we had the following values:
mat_value is 105.32462755 which was calculated as the redemption value (specified in the input) plus the interest at maturity, which we calculated as 0.75 * .070995034 * 100.
How did we come up that? For this calculation the par value is assumed to be 100. The interest rate, .070995034, was supplied. We had to calculate the 0.75. To do this, we needed to know the frequency (2), the basis (0), the last interest date (1/6/2007) and the maturity date (10/6/2007). A normal coupon period for this bond would have been from 1/6/2007 to 7/6/2007 (because it pays interest twice a year, or every six months). The actual maturity date of 10/6/2007 falls halfway between a coupon period starting 7/6/2007 and ending 1/6/2008. Since the basis for this bond is 0 (which assumes 30 day months and a 360 day year), we calculated one full 180-day period (1/6/2007 to 7/6/2007) and half of the next 180-day period (7/6/2007 to 1/6/2008). 1.5 periods divided by the frequency, gives us 0.75.
The dirty_price we calculated as the price (supplied) plus the accrued interest. We calculated the number of days from the last interest date to the settlement date using the basis (0) and we came up with 52. The accrued interest then is 52/180 * (100*.070995034)/2 or 1.02548382444. Add this to the price (100.377430392) and the sum is 101.402914216588.
This leaves the DSC_fraction. We calculated that there were 128 days remaining in the coupon period where settlement occurred (180-52) and 90 days in the coupon period of the maturity date. So, our DSC_fraction is 128/180 + 90/180 or 1.211111111111. Plug all these values into the equation and we verify our result.
While this gives us some comfort that our result is, at the very least, the result that we intended to get, it still didn’t explain the difference with EXCEL. What we did discover, was that if the DSC_fraction were 1.20 instead of 1.21111111, we got the same result as EXCEL. After scratching our head a little bit, we saw that 126/180 + 90/180 equaled 1.20.
How to come up with 126? It turns out DAYS360(DATE(2007,2,28),DATE(2007,7,6)) = 126. We infer from this that EXCEL calculated the number of days from the settlement date to the ‘quasi’ coupon date using something like the DAYS360 calculation, whereas we calculated it as the number of days in the ‘quasi’ coupon period minus the number of accrued days.
After checking with several third party bond calculators, we decided to leave our methodology as it is and simply document our findings. Thus, if you are comparing the results using the XLeratorDB function to results from EXCEL, differences will arise because of the differences in methodology in calculating the time to maturity.