DISFACTORS
Updated 2023-10-06 14:05:47.283000
Syntax
SELECT * FROM [westclintech].[wct].[DISFACTORS](
<@Settlement, datetime,>
,<@Maturity, datetime,>
,<@Redemption, float,>
,<@DRate, float,>
,<@Price, float,>
,<@Yield, float,>
,<@Basis, nvarchar(4000),>)
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.
Arguments
@Basis
is the type of day count to use. @Basis is an expression of the character string data type category.
{"columns":[{"field":"@Basis","width":313},{"field":"Day count basis","width":277}],"rows":[{"@Basis":"0 , 'BOND'","Day count basis":"US (NASD) 30/360"},{"@Basis":"1 , 'ACTUAL'","Day count basis":"Actual/Actual"},{"@Basis":"2 , 'A360'","Day count basis":"Actual/360"},{"@Basis":"3 , 'A365'","Day count basis":"Actual/365"},{"@Basis":"4 , '30E/360 (ISDA)' , '30E/360' , 'ISDA' , '30E/360 ISDA' , 'EBOND'","Day count basis":"European 30/360"},{"@Basis":"5 , '30/360' , '30/360 ISDA' , 'GERMAN'","Day count basis":"30/360 ISDA"},{"@Basis":"7 , 'NL/365'","Day count basis":"No Leap Year /365"},{"@Basis":"8 , 'NL/360'","Day count basis":"No Leap Year /360"},{"@Basis":"9 , 'A/364'","Day count basis":"Actual/364"},{"@Basis":"21 , 'Actual/ISDA'","Day count basis":"Actual/ISDA"}]}
@Yield
the security’s annual yield. @Yield 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.
@DRate
the discount rate. @DRate is an expression of type float or of a type that can be implicitly converted to float.
@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.
Return Type
table
{"columns": [{"field": "colName", "headerName": "Name", "header": "name"}, {"field": "colDatatype", "headerName": "Type", "header": "type"}, {"field": "colDesc", "headerName": "Description", "header": "description", "minWidth": 1000}], "rows": [{"id": "9b59512b-c7d6-4794-8c96-4ab9de30e5f1", "colName": "DSM", "colDatatype": "float", "colNull": true, "colDesc": "Number of days from settlement to maturity"}, {"id": "1f491c00-ffff-4e1a-9d8d-b3d5d23bce54", "colName": "B", "colDatatype": "float", "colNull": true, "colDesc": "Number of days in a year"}, {"id": "8b292976-1de3-4b0c-a37b-d2fc2460b281", "colName": "P", "colDatatype": "float", "colNull": true, "colDesc": "Price"}, {"id": "05d79caf-15ce-4c46-9d67-56a71a96271f", "colName": "D", "colDatatype": "float", "colNull": true, "colDesc": "Discount Rate"}, {"id": "1cedaafa-6340-44cb-ad94-14954192885e", "colName": "Y", "colDatatype": "float", "colNull": true, "colDesc": "Yield"}, {"id": "001582ee-09e2-4465-8b81-fd3daaf713e2", "colName": "T", "colDatatype": "float", "colNull": true, "colDesc": "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.
{"columns":[{"field":"DSM","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"B","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"P","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"D","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Y","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"T","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"DSM":"69","B":"365","P":"99.72","D":"0.0148115942028987","Y":"0.0148531831156226","T":"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.
{"columns":[{"field":"DSM","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"B","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"P","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"D","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Y","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"T","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"DSM":"131","B":"360","P":"9930.86111111111","D":"0.019","Y":"0.0191322784473767","T":"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.
{"columns":[{"field":"DSM","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"B","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"P","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"D","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Y","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"T","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"DSM":"190","B":"364","P":"971291.205876001","D":"0.0550000055849243","Y":"0.05662566","T":"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