SQL Server bond modified duration function

RPIMDURATION

Updated: 05 May 2014

Use RPIMDURATION to calculate the effective duration of a bond that pays regular periodic interest. Effective 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.

Syntax

SELECT [wctFinancial].[wct].[RPIMDURATION](

<@Settlement,datetime,>

,<@Maturity,datetime,>

,<@Rate,float,>

,<@Yld,float,>

,<@Redemption,float,>

,<@Frequency,float,>

,<@Basis,nvarchar(4000),>)

Arguments

@Settlement

the settlement date occurring within a coupon period of the bond. @Settlement is an expression that returns a datetime or smalldatetime value, or a character string in date format.

@Maturity

the maturity date of the bond. @Settlement is an expression that returns a datetime or smalldatetime value, or a character string in date format.

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

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

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

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

@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 @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 RPIMDURATION returns an error.

In this example we calculate the modified duration for a bond maturing on 2034-06-15. The settlement date is 2014-05-01, the yield is 2.76%, the coupon rate is 2.50%, the redemption value is 100, the coupon is paid twice-yearly, and the bais code is 1.

SELECT wct.RPIMDURATION(

'2014-05-01',--@Settlement

'2034-06-15',--@Maturity

0.025,--@Rate

0.0276,--@Yield

100,--@Redemption

2,--@Frequency

1 --@Basis

)as MDURATION

This produces the following result

MDURATION

----------------------

15.4645972227576

In this example, we calculate the modified duration of a zero-coupon bond.

SELECT wct.RPIMDURATION(

'2014-05-01',--@Settlement

'2044-06-15',--@Maturity

0.00,--@Rate

0.0301,--@Yield

100,--@Redemption

2,--@Frequency

1 --@Basis

)as MDURATION

This produces the following result.

MDURATION

----------------------

29.6770334638356

In this example we know the price of the bond (99.9875), but not the yield.

SELECT wct.RPIMDURATION(

'2014-05-01',--@Settlement

'2024-09-15',--@Maturity

0.0190,--@Rate

wct.YIELD(

'2014-05-01',

'2024-09-15',

0.0190,

99.9875,

100,

2,

1

),--@Yield

100,--@Redemption

2,--@Frequency

1 --@Basis

)as MDURATION

This produces the following result.

MDURATION

----------------------

9.3516794184541

In this example we calculate the modified duration of a bond settling in the final coupon period.