DISFACTORS
Updated: 23 May 2016
Use the table-valued function DISFACTORS to return the components used in the calculation of price, discount rate, and yield for a discount security.
Syntax
SELECT * FROM [wct].[DISFACTORS](
<@Settlement, datetime,>
,<@Maturity, datetime,>
,<@Redemption, float,>
,<@DRate, float,>
,<@Price, float,>
,<@Yield, 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.
@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.
@DRate
the discount rate. @DRate is an expression of type float or of a type that can be implicitly converted to float.
@Price
the security’s price per 100 face value. @Price is an expression of type float or of a type that can be implicitly converted to float.
@Yield
the security’s annual yield. @Yield 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
|
|
7, 'NL/365'
|
No Leap Year /365
|
|
8, 'NL/360'
|
No Leap Year /360
|
|
9, 'A/364'
|
Actual/364
|
|
21, 'Actual/ISDA'
|
Actual/ISDA
|
Return Type
RETURNS TABLE (
[DSM] [float] NULL,
[B] [float] NULL,
[P] [float] NULL,
[D] [float] NULL,
[Y] [float] NULL,
[T] [float] NULL
)
|
Column
|
Description
|
|
DSM
|
Number of days from settlement to maturity
|
|
B
|
Number of days from in a year.
|
|
P
|
Price
|
|
D
|
Discount Rate
|
|
Y
|
Yield
|
|
T
|
Time, in years, from @Settlement to @Maturity; DSM/B
|
Remarks
· If @Settlement is NULL then @Settlement = GETDATE().
· If @Basis is NULL then @Basis = 2.
· If @Redemption is NULL then @Redemption = 100.
· If @Basis invalid then DISFACTORS returns an error.
· If @Maturity is NULL then an error is returned.
· If @DRate is NULL and @Price is NULL and @Yield is NULL nothing is returned.
· If @DRate is NOT NULL then D = @DRate and P and Y are calculated using @DRate else if @Price is NOT NULL then P = @Price and D and Y are calculated from @Price else if @Yield is NOT NULL then Y = @Yield and P and D are calculated from @Yield.
Examples
In this example we calculate the factors for a security maturing on 2014-12-15 with a 100 redemption value and a price of 99.72. The discount rate is quoted using the Actual/365 day-count convention.
SELECT
*
FROM
wct.DISFACTORS(
'2014-10-07' --@Settlement
,'2014-12-15' --@Maturity
,100 --@Redemption
,NULL --@DRate
,99.72 --@Price
,NULL --@Yield
,3 --@Basis
)
This produces the following result (which has been reformatted for ease of reading).
|
DSM
|
B
|
P
|
D
|
Y
|
T
|
|
69
|
365
|
99.72
|
0.014811594
|
0.014853183
|
0.189041096
|
In this example, we calculate the factors for a security maturing on 2015-02-15 with a 10000 redemption value and a discount rate of 1.9%. The discount rate is quoted using the Actual/360 day-count convention.
SELECT
*
FROM
wct.DISFACTORS(
'2014-10-07' --@Settlement
,'2015-02-15' --@Maturity
,10000 --@Redemption
,0.019 --@DRate
,NULL --@Price
,NULL --@Yield
,2 --@Basis
)
This produces the following result.
|
DSM
|
B
|
P
|
D
|
Y
|
T
|
|
131
|
360
|
9930.861111
|
0.019
|
0.019132278
|
0.363888889
|
In this example we calculate the factors for a security maturing on 2015-04-15 with a 1,000,000 redemption value and a yield of 0.05662566. The yield is quoted using the Actual/364 day-count convention.
SELECT
*
FROM
wct.DISFACTORS(
'2014-10-07' --@Settlement
,'2015-04-15' --@Maturity
,1000000 --@Redemption
,NULL --@DRate
,NULL --@Price
,0.05662566 --@Yield
,9 --@Basis
)
This produces the following result.
|
DSM
|
B
|
P
|
D
|
Y
|
T
|
|
190
|
364
|
971291.2059
|
0.055000006
|
0.05662566
|
0.521978022
|
See Also