Login     Register

        Contact Us     Search

XLeratorDB/financial Documentation

SQL Server DURATION function


DURATION

Updated: 10 July 2014


Use 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:

Where

D
=
Duration
CFi
=
ith future cash flow
ti
=
time, in coupon periods, to the ith cash flow
DF
=
(1 + @Yld/@Frequency)
N
=
Number of coupon payments from settlement to maturity

Syntax
SELECT [westclintech].[wct].[DURATION] (
  <@Settlement, datetime,>
 ,<@Maturity, datetime,>
 ,<@Rate, float,>
 ,<@Yld, 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. 
@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.
@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.
@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 @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.
Example
This bond has a coupon rate of 3.0%, pays interest semi-annually on the 30th 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.
              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
This produces the following result.
              DURATION
----------------------
      20.4272373512074

 

See Also

 



Copyright 2008-2024 Westclintech LLC         Privacy Policy        Terms of Service