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