SQL Server RPIMDURATION Function

Updated 2024-02-29 14:30:29.347000

Description

Use the scalar function 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.

MDURATION=\frac{-\frac{\partial{P}}{\partial{y}}}{p_{dirty}}

Syntax

SELECT [westclintech].[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.

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

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.

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.

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

SELECT wct.RPIMDURATION(   '2014-05-01', --@Settlement
                           '2014-07-15', --@Maturity
                           0.0190,       --@Rate
                           0.0005,       --@Yield
                           100,          --@Redemption
                           2,            --@Frequency
                           0             --@Basis
                       ) as MDURATION;

This produces the following result.

This is an example of a bond paying interest every 26 weeks.

SELECT wct.RPIMDURATION(   '2014-10-01', --@Settlement
                           '2023-03-13', --@Maturity
                           0.1250,       --@Rate
                           0.1100,       --@Yield
                           100,          --@Redemption
                           182,          --@Frequency
                           9             --@Basis
                       ) as MDURATION;

This produces the following result.

See Also

PRICE - Price of a bond paying regular periodic interest

YIELD - Yield of a bond paying regular periodic coupon

BONDINT - Accrued interest on a bond paying regular, periodic interest

RPICONVEXITY - Convexity of a bond paying regular periodic interest

RPIDURATION - Duration of a bond paying regular periodic interest