OLCMDURATION

Updated 2024-02-28 20:26:14.483000

Syntax

SELECT [westclintech].[wct].[OLCMDURATION](
  <@Settlement, datetime,>
 ,<@Maturity, datetime,>
 ,<@LastCouponDate, datetime,>
 ,<@Rate, float,>
 ,<@Yld, float,>
 ,<@Redemption, float,>
 ,<@Frequency, float,>
 ,<@Basis, nvarchar(4000),>)

Description

Use the scalar function 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.

MDURATION=\frac{-\frac{\partial{P}}{\partial{y}}}{p_{dirty}}

Arguments

@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.

@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.

@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.

@Basis

is the type of day count to use. @Basis is an expression of the character string data type category.

{"columns":[{"field":"@Basis","width":313},{"field":"Day count basis","width":277}],"rows":[{"@Basis":"0 , 'BOND'","Day count basis":"US (NASD) 30/360"},{"@Basis":"1 , 'ACTUAL'","Day count basis":"Actual/Actual"},{"@Basis":"2 , 'A360'","Day count basis":"Actual/360"},{"@Basis":"3 , 'A365'","Day count basis":"Actual/365"},{"@Basis":"4 , '30E/360 (ISDA)' , '30E/360' , 'ISDA' , '30E/360 ISDA' , 'EBOND'","Day count basis":"European 30/360"},{"@Basis":"5 , '30/360' , '30/360 ISDA' , 'GERMAN'","Day count basis":"30/360 ISDA"},{"@Basis":"6 , 'NL/ACT'","Day count basis":"No Leap Year/ACT"},{"@Basis":"7 , 'NL/365'","Day count basis":"No Leap Year /365"},{"@Basis":"8 , 'NL/360'","Day count basis":"No Leap Year /360"},{"@Basis":"9 , 'A/364'","Day count basis":"Actual/364"},{"@Basis":"10 , 'BOND NON-EOM'","Day count basis":"US (NASD) 30/360 non-end-of-month"},{"@Basis":"11 , 'ACTUAL NON-EOM'","Day count basis":"Actual/Actual non-end-of-month"},{"@Basis":"12 , 'A360 NON-EOM'","Day count basis":"Actual/360 non-end-of-month"},{"@Basis":"13 , 'A365 NON-EOM'","Day count basis":"Actual/365 non-end-of-month"},{"@Basis":"14 , '30E/360 NON-EOM' , '30E/360 ICMA NON-EOM' , 'EBOND NON-EOM'","Day count basis":"European 30/360 non-end-of-month"},{"@Basis":"15 , '30/360 NON-EOM' , '30/360 ISDA NON-EOM' , 'GERMAN NON-EOM'","Day count basis":"30/360 ISDA non-end-of-month"},{"@Basis":"16 , 'NL/ACT NON-EOM'","Day count basis":"No Leap Year/ACT non-end-of-month"},{"@Basis":"17 , 'NL/365 NON-EOM'","Day count basis":"No Leap Year/365 non-end-of-month"},{"@Basis":"18 , 'NL/360 NON-EOM'","Day count basis":"No Leap Year/360 non-end-of-month"},{"@Basis":"19 , 'A/364 NON-EOM'","Day count basis":"Actual/364 non-end-of-month"}]}

@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.

@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.

@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.

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;