DURATION

Updated 2024-02-23 14:11:37.860000

Syntax

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

Description

Use the scalar function DURATION to calculate the Macaulay duration (in years) of a security with regular, periodic interest payments. DURATION assumes a redemption value of 100. The formula for DURATION is:

\mathrm{D=\frac{\Sigma_{i=1}^NCF_i\times{t_i}\times{DF^{t_i}}}{\Sigma_{i=1}^NCF_i\times{DF^{t_i}}}}

WhereD = DurationCFi = ith future cash flowti = time, in coupon periods, to the ith cash flowDF = (1 + @Yld/@Frequency)N = Number of coupon payments from settlement to maturity

Arguments

@Yld

the security’s annual yield. @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 bi-monthly @Frequency = 6; for monthly @Frequency =12. @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.

{"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 security’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.

Return Type

float

Remarks

If @Frequency is any number other than 1, 2, 4, 6 or 12 DURATION returns an error.

If @Basis is invalid (see above list), DURATION returns an error.

Examples

This bond has a coupon rate of 3.0%, pays interest semi-annually on the 30 th of November and the 31st of May and matures on 2033-11-30. The settlement date for the transaction is 2014-10-10 and the yield is 3.15%. The bond is quoted using the Actual/Actual day-count convention.

SELECT wct.DURATION(   '2014-10-10', --@Settlement

                       '2033-11-30', --@Maturity

                       0.03,         --@Rate

                       0.0315,       --@Yld

                       2,            --@Frequency

                       1             --@Basis

                   ) AS DURATION;

This produces the following result.

{"columns":[{"field":"DURATION","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"DURATION":"14.4816608104738"}]}

This bond has a 2.80% coupon rate, pays interest semi-annually on the 30th of September and the 30th of March and matures on 2044-09-30. The price of the bond is 99.375 and the date of the transaction is 2014-10-10. The bond is quoted using the US 30/360 day-count convention.

SELECT wct.DURATION(   '2014-10-10', --@Settlement

                       '2044-09-30', --@Maturity

                       0.028,        --@Coupon

                       wct.YIELD(   '2014-10-10', --@Settlement

                                    '2044-09-30', --@Maturity

                                    0.028,        --@Coupon

                                    99.375,       --@Price

                                    100,          --@Redemption

                                    2,            --@Frequency

                                    1             --@Basis

                                ),   --@Yield

                       2,            --@Frequency

                       10            --@Basis

                   ) AS DURATION;