Login     Register

        Contact Us     Search

XLeratorDB/financial Documentation

SQL Server disambiguation function for regular bonds


RPIFACTORS

Updated: 31 May 2014


Use the table-valued function RPIFACTORS to return the components used in the calculation of price and yield for a bond with regular periodic coupons.
Syntax
SELECT * FROM [wctFinancial].[wct].[RPIFACTORS](
  <@Settlement, datetime,>
 ,<@Maturity, datetime,>
 ,<@Rate, float,>
 ,<@Price, float,>
 ,<@Yield, float,>
 ,<@Redemption, 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.
@Price
the price of the bond. @Price is an expression of type float or of a type that can be implicitly converted to float.
@Yield
the security’s annual yield. @Yld is an expression of type float or of a type that can be implicitly converted to float.
@Redemption
the security’s redemption value per 100 face value. @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
RETURNS TABLE (
      [PrevCoup] [datetime] NULL,
      [NextCoup] [datetime] NULL,
      [A] [float] NULL,
      [DSC] [float] NULL,
      [E] [float] NULL,
      [N] [int] NULL,
      [C] [float] NULL,
      [P] [float] NULL,
      [AI] [float] NULL,
      [Y] [float] NULL
)

Column
Description
PrevCoup
Greatest coupon date less than or equal to the settlement date.
NextCoup
Least coupon date greater than the settlement date.
A
Number of accrued days from the previous coupon date to the settlement date.
DSC
Number of days from the settlement date to the next coupon date.
E
Number of days in the coupon period.
N
Number of coupons from the settlement date to the maturity date
C
Coupon amount
P
Price. If @Yield is NOT NULL then P is calculated from the inputs otherwise P is the value entered in @Price.
AI
Accrued interest as of the settlement date.
Y
Yield. If @Yield is NOT NULL then Y is the value entered in @Yield otherwise Y is calculated from the inputs.

 
Remarks
·         If @Settlement is NULL then @Settlement = GETDATE().
·         If @Rate is NULL then @Rate = 0.
·         If @Redemption is NULL then @Redemption = 100.
·         If @Frequency is NULL then @Frequency = 2.
·         If @Basis is NULL then @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 RPIFACTORS returns an error.
·         If @Basis invalid (see above list), RPIFACTORS returns an error.
·         If @Maturity is NULL then an error is returned.
·         DSC = E - A.
Examples
In this example we calculate the factors 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
   *
FROM
   wct.RPIFACTORS(
   '2014-05-01',  --@Settlement
   '2034-06-15',  --@Maturity
   0.025,         --@Rate
   NULL,          --@Price
   0.0276,        --@Yield
   100,           --@Redemption
   2,             --@Frequency
   1              --@Basis
   )
This produces the following result (which has been reformatted for ease of viewing).

PrevCoup
NextCoup
A
DSC
E
N
C
P
AI
Y
12/15/2013
6/15/2014
137
45
182
41
1.25
96.00437991
0.940934066
0.0276

In this example, we calculate the factors for a zero-coupon bond.
SELECT
   *
FROM
   wct.RPIFACTORS(
   '2014-05-01',  --@Settlement
   '2044-06-15',  --@Maturity
   0.00,          --@Rate
   NULL,          --@Price
   0.0301,        --@Yield
   100,           --@Redemption
   2,             --@Frequency
   1              --@Basis
   )
This produces the following result.

PrevCoup
NextCoup
A
DSC
E
N
C
P
AI
Y
12/15/2013
6/15/2014
137
45
182
61
0
40.65835761
0
0.0301

 In this example we calculate the factors for a bond settling in the final coupon period.
SELECT
   *
FROM
   wct.RPIFACTORS(
   '2014-05-01',  --@Settlement
   '2014-07-15',  --@Maturity
   0.0190,        --@Rate
   NULL,          --@Price
   0.0005,        --@Yield
   100,           --@Redemption
   2,             --@Frequency
   0              --@Basis
   )
This produces the following result.

PrevCoup
NextCoup
A
DSC
E
N
C
P
AI
Y
1/15/2014
7/15/2014
106
74
180
1
0.95
100.3801812
0.559444444
0.0005

 
Here we calculate the factors for a bond maturing on the 30th of September 2034, with semi-annual coupons payable on March 30th and September 30th.
SELECT
   *
FROM
   wct.RPIFACTORS(
   '2014-05-01',  --@Settlement
   '2034-09-30',  --@Maturity
   0.0257,        --@Rate
   98.123291,     --@Price
   NULL,          --@Yield
   100,           --@Redemption
   2,             --@Frequency
   11             --@Basis
   )
 
This produces the following result.

PrevCoup
NextCoup
A
DSC
E
N
C
P
AI
Y
3/30/2014
9/30/2014
32
152
184
41
1.29
98.123291
0.223478261
0.0269

Here's an example with a negative yield.
SELECT
   *
FROM
   wct.RPIFACTORS(
   '2014-05-01',  --@Settlement
   '2014-09-30',  --@Maturity
   0.0257,        --@Rate
   101,           --@Price
   NULL,          --@Yield
   98,            --@Redemption
   2,             --@Frequency
   0              --@Basis
   )
This produces the following result.

PrevCoup
NextCoup
A
DSC
E
N
C
P
AI
Y
3/31/2014
9/30/2014
31
149
180
1
1.29
101
0.221305556
-0.046219


This is an example of a bond paying interest every 26 weeks.
SELECT
   *
FROM
   wct.RPIFACTORS(
   '2014-10-01', --@Settlement
   '2023-03-13', --@Maturity
   0.1250,        --@Rate
   NULL,          --@Price
   0.1100,        --@Yield
   100,           --@Redemption
   182,           --@Frequency
   9              --@Basis
   )

PrevCoup
NextCoup
A
DSC
E
N
C
P
AI
Y
2014-09-22
2015-03-23
9
173
182
17
6.25
108.1261
0.309066
0.11

 

See Also

 



Copyright 2008-2017 Westclintech LLC         Privacy Policy        Terms of Service