Login     Register

        Contact Us     Search

XLeratorDB/financial Documentation

SQL Server modified duration function for odd last coupon bonds


OLCMDURATION

Updated: 30 June 2014


Use OLCMDURATION to calculate the modified duration for a bond that has an odd last coupon. Modified duration is calculated as the first derivative of the price with respect to yield multiplied by -1 divided by the dirty price of the bond.

Syntax
SELECT [wctFinancial].[wct].[OLCMDURATION](
  <@Settlement, datetime,>
 ,<@Maturity, datetime,>
 ,<@LastCouponDate, datetime,>
 ,<@Rate, float,>
 ,<@Yld, float,>
 ,<@Redemption, float,>
 ,<@Frequency, float,>
 ,<@Basis, nvarchar(4000),>)
Arguments
@Settlement
the settlement date of the security. @Settlement is an expression that returns a datetime or smalldatetime value, or a character string in date format. 
@Maturity
the maturity date of the security. @Maturity is an expression that returns a datetime or smalldatetime value, or a character string in date format. 
@LastCouponDate
the last coupon date of the security. The period from the last coupon date until the maturity date defines the odd interest period. The quasi-maturity date is assumed to occur at a regular periodic interval as defined by @Frequency and @Basis. @LastCouponDate is an expression that returns a datetime or smalldatetime value, or a character string in date format. 
@Rate
the bond’s annual coupon rate. @Rate is an expression of type float or of a type that can be implicitly converted to float.
@Yld
the yield for the maturity date passed into the function. @Yld is an expression of type float or of a type that can be implicitly converted to float.
@Redemption
the redemption value of the bond assuming a par value of 100. @Redemption is an expression of type float or of a type that can be implicitly converted to float.
@Frequency
the number of coupon payments per year. For annual payments, @Frequency = 1; for semi-annual, @Frequency = 2; for quarterly, @Frequency = 4; for bimonthly @Frequency = 6; for monthly @Frequency = 12. For bonds with @Basis = 'A/364' or 9, you can enter 364 for payments made every 52 weeks, 182 for payments made every 26 weeks, 91 for payments made every 13 weeks, 28 for payments made every 4 weeks, 14 for payments made every 2 weeks, and 7 for weekly payments. @Frequency is an expression of type float or of a type that can be implicitly converted to float.
@Basis
is the type of day count to use. @Basis is an expression of the character string data type category.
@Basis
Day count basis
0, 'BOND'
US (NASD) 30/360
1, 'ACTUAL'
Actual/Actual
2, 'A360'
Actual/360
3, 'A365'
Actual/365
4, '30E/360 (ISDA)', '30E/360', 'ISDA', '30E/360 ISDA', 'EBOND'
European 30/360
5, '30/360', '30/360 ISDA', 'GERMAN'
30/360 ISDA
6, 'NL/ACT'
No Leap Year/ACT
7, 'NL/365'
No Leap Year /365
8, 'NL/360'
No Leap Year /360
9, 'A/364'
Actual/364
10, 'BOND NON-EOM'
US (NASD) 30/360 non-end-of-month
11, 'ACTUAL NON-EOM'
Actual/Actual non-end-of-month
12, 'A360 NON-EOM'
Actual/360 non-end-of-month
13, 'A365 NON-EOM'
Actual/365 non-end-of-month
14, '30E/360 NON-EOM', '30E/360 ICMA NON-EOM', 'EBOND NON-EOM'
European 30/360 non-end-of-month
15, '30/360 NON-EOM', '30/360 ISDA NON-EOM', 'GERMAN NON-EOM'
30/360 ISDA non-end-of-month
16, 'NL/ACT NON-EOM'
No Leap Year/ACT non-end-of-month
17, 'NL/365 NON-EOM'
No Leap Year/365 non-end-of-month
18, 'NL/360 NON-EOM'
No Leap Year/360 non-end-of-month
19, 'A/364 NON-EOM'
Actual/364 non-end-of-month
Return Type
float
Remarks
·         If @Maturity <= @Settlement 0 is returned.
·         If @Settlement is NULL, @Settlement = GETDATE()
·         If @Rate is NULL, @Rate = 0
·         If @Yld is NULL, @Yld = 0
·         If @Frequency is NULL, @Frequency = 2
·         If @Basis is NULL, @Basis = 0.
·         If @Frequency is any number other than 1, 2, 4, 6 or 12, or for @Basis = 'A/364' any number other than 1, 2, 4, 6, or 12 as well as 7, 14, 28, 91, 182, or 364 OLCMDURATION returns an error.
·         If @Basis is invalid (see above list), OLCMDURATION returns an error.
·         @Rate is entered as a decimal value; 1.0% = 0.01
·         @Yld is entered as a decimal value; 1.0% = 0.01
·         If @Maturity is NULL an error will be returned.
·         If @IssueDate is NULL an error will be returned.
·         If @LastCouponDate is NULL an error will be returned.
Examples
This is a bond with an odd short last coupon period where the settlement date in the last coupon period.
SELECT
   wct.OLCMDURATION(
      '2014-10-01',     --@Settlement
      '2014-12-15',     --@Maturity
      '2014-09-15',     --@Last_interest
      0.0225,           --@Rate
      0.0010,           --@Yield
      100,              --@Redemption
      2,                --@Frequency
      1                 --@Basis
      ) as MDuration
This produces the following result.
             MDuration
----------------------
     0.207139404908496


This is a bond with an odd long last coupon period with a settlement date in the last coupon period.
SELECT
   wct.OLCMDURATION(
      '2014-10-01',     --@Settlement
      '2014-12-15',     --@Maturity
      '2014-03-15',     --@Last_interest
      0.0225,           --@Rate
      0.0010,           --@Yield
      100,              --@Redemption
      2,                --@Frequency
      1                 --@Basis
      ) as MDuration
This produces the following result.
             MDuration
----------------------
     0.207139404908115


This is a bond with an odd short last coupon with a settlement date prior to the last coupon date.
SELECT
   wct.OLCMDURATION(
      '2014-10-01',     --@Settlement
      '2034-12-15',     --@Maturity
      '2034-09-15',     --@Last_interest
      0.0425,           --@Rate
      0.0400,           --@Yield
      100,              --@Redemption
      2,                --@Frequency
      1                 --@Basis
      ) as MDuration
This produces the following result.
             MDuration
----------------------
      13.5818659657806


This is a bond with an odd long last coupon period with a settlement date prior to the last coupon date.
SELECT
   wct.OLCMDURATION(
      '2014-10-01',     --@Settlement
      '2034-12-15',     --@Maturity
      '2034-03-15',     --@Last_interest
      0.0425,           --@Rate
      0.0400,           --@Yield
      100,              --@Redemption
      2,                --@Frequency
      1                 --@Basis
      ) as MDuration
This produces the following result.
             MDuration
----------------------
       13.583584787529


This is an example of a bond paying interest every 26 weeks.
SELECT
   wct.OLCMDURATION(
      '2014-10-04',     --@Settlement
      '2014-12-15',     --@Maturity
      '2014-06-01',     --@Last_interest
      0.1250,           --@Rate
      0.1100,           --@Yield
      100,              --@Redemption
      182,              --@Frequency
      9                 --@Basis
   ) as MDURATION
This produces the following result.
             MDURATION
----------------------
     0.193590019431094

 

See Also

 



Copyright 2008-2013 WestClinTech LLC         Privacy Policy        Terms of Service