RPIDURATION
Updated: 05 May 2014
Use RPIDURATION to calculate the duration for a bond that pays regular periodic interest. The duration is calculated as the first derivative of the price of the bond with respect to yield multiplied by -1, divided by the dirty price of the bond multiplied by 1 plus the yield divided by the frequency.
Syntax
SELECT [wctFinancial].[wct].[RPICONVEXITY](
<@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 RPIDURATION returns an error.
· If @Basis is invalid (see above list), RPIDURATION 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 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.RPIDURATION(
'2014-05-01', --@Settlement
'2034-06-15', --@Maturity
0.025, --@Rate
0.0276, --@Yield
100, --@Redemption
2, --@Frequency
1 --@Basis
) as DURATION
This produces the following result
DURATION
----------------------
15.6780086644317
In this example, we calculate the duration of a zero-coupon bond.
SELECT wct.RPIDURATION(
'2014-05-01', --@Settlement
'2044-06-15', --@Maturity
0.00, --@Rate
0.0301, --@Yield
100, --@Redemption
2, --@Frequency
1 --@Basis
) as DURATION
This produces the following result.
DURATION
----------------------
30.1236728174663
In this example we know the price of the bond (99.9875), but not the yield.
SELECT wct.RPIDURATION(
'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 DURATION
This produces the following result.
DURATION
----------------------
9.44057846789297
In this example we calculate the duration of a bond settling in the final coupon period.
SELECT wct.RPIDURATION(
'2014-05-01', --@Settlement
'2014-07-15', --@Maturity
0.0190, --@Rate
0.0005, --@Yield
100, --@Redemption
2, --@Frequency
0 --@Basis
) as DURATION
This produces the following result.
DURATION
----------------------
0.205585814877239
This is an example of a bond paying interest every 26 weeks.
SELECT wct.RPIDURATION(
'2014-10-01', --@Settlement
'2023-03-13', --@Maturity
0.1250, --@Rate
0.1100, --@Yield
100, --@Redemption
182, --@Frequency
9 --@Basis
) as DURATION
This produces the following result.
DURATION
----------------------
5.56584970656666
See Also