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

CF_{i}

=

i^{th} future cash flow

t_{i}

=

time, in coupon periods, to the i^{th} 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 semiannual, @Frequency = 2; for quarterly, @Frequency = 4; for bimonthly @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 NONEOM'

US (NASD) 30/360 nonendofmonth

11, 'ACTUAL NONEOM'

Actual/Actual nonendofmonth

12, 'A360 NONEOM'

Actual/360 nonendofmonth

13, 'A365 NONEOM'

Actual/365 nonendofmonth

14, '30E/360 NONEOM', '30E/360 ICMA NONEOM', 'EBOND NONEOM'

European 30/360 nonendofmonth

15, '30/360 NONEOM', '30/360 ISDA NONEOM', 'GERMAN NONEOM'

30/360 ISDA nonendofmonth

16, 'NL/ACT NONEOM'

No Leap Year/ACT nonendofmonth

17, 'NL/365 NONEOM'

No Leap Year/365 nonendofmonth

18, 'NL/360 NONEOM'

No Leap Year/360 nonendofmonth

19, 'A/364 NONEOM'

Actual/364 nonendofmonth

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 semiannually on the 30^{th} of November and the 31^{st} of May and matures on 20331130. The settlement date for the transaction is 20141010 and the yield is 3.15%. The bond is quoted using the Actual/Actual daycount convention.
SELECT
wct.DURATION(
'20141010' @Settlement
,'20331130' @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 semiannually on the 30^{th} of September and the 30^{th} of March and matures on 20440930. The price of the bond is 99.375 and the date of the transaction is 20141010. The bond is quoted using the US 30/360 daycount convention.
SELECT
wct.DURATION(
'20141010' @Settlement
,'20440930' @Maturity
,0.028 @Coupon
,wct.YIELD(
'20141010' @Settlement
,'20440930' @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