SQL Server DISFACTORS Function
Updated 2023-10-06 14:05:47.283000
Description
Use the table-valued function DISFACTORS to calculate the components used in the calculation of price, discount rate, and yield for a discount security.
Syntax
SELECT * FROM [westclintech].[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
table
| colName | colDatatype | colDesc |
|---|---|---|
| DSM | float | Number of days from settlement to maturity |
| B | float | Number of days in a year |
| P | float | Price |
| D | float | Discount Rate |
| Y | float | Yield |
| T | float | 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 f actors 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.
| DSM | B | P | D | Y | T |
|---|---|---|---|---|---|
| 69 | 365 | 99.72 | 0.0148115942028987 | 0.0148531831156226 | 0.189041095890411 |
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.86111111111 | 0.019 | 0.0191322784473767 | 0.363888888888889 |
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.205876001 | 0.0550000055849243 | 0.05662566 | 0.521978021978022 |
See Also
DIS - Price, discount rate, and/or yield of a discount security
DISC - Discount rate of a discount security
IAMFACTORS - Factors for the price calculation of a security paying interest at maturity
OFCFACTORS - Returns the components of the ODDFPRICE equation
OLCFACTORS - Returns the components of the ODDLPRICE equation
OFLFACTORS - Returns the components of the OFLPRICE equation
PRICEDISC - Price of a discount security
RPIFACTORS - Factors for the calculation of the price of a bond that pays regular periodic interest