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 semiannual, @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 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 @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.
Â· If @Basis is invalid (see above list), RPIMDURATION returns an error.
Â· @Rate is entered as a decimal value; 1.0% = 0.01
Â· @Yld is entered as a decimal value; 1.0% = 0.01
Examples
In this example we calculate the modified duration for a bond maturing on 20340615. The settlement date is 20140501, the yield is 2.76%, the coupon rate is 2.50%, the redemption value is 100, the coupon is paid twiceyearly, and the bais code is 1.
SELECT wct.RPIMDURATION(
'20140501', @Settlement
'20340615', @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 zerocoupon bond.
SELECT wct.RPIMDURATION(
'20140501', @Settlement
'20440615', @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(
'20140501', @Settlement
'20240915', @Maturity
0.0190, @Rate
wct.YIELD(
'20140501',
'20240915',
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.
SELECT wct.RPIMDURATION(
'20140501', @Settlement
'20140715', @Maturity
0.0190, @Rate
0.0005, @Yield
100, @Redemption
2, @Frequency
0 @Basis
) as MDURATION
This produces the following result.
MDURATION

0.205534431269422
This is an example of a bond paying interest every 26 weeks.
SELECT wct.RPIMDURATION(
'20141001', @Settlement
'20230313', @Maturity
0.1250, @Rate
0.1100, @Yield
100, @Redemption
182, @Frequency
9 @Basis
) as MDURATION
This produces the following result.
MDURATION

5.2756869256556
See Also