IAMFACTORS

Updated 2023-10-06 14:18:11.283000

Syntax

SELECT * FROM [westclintech].[wct].[IAMFACTORS](
  <@Settlement, datetime,>
 ,<@Maturity, datetime,>
 ,<@Issue, datetime,>
 ,<@Rate, float,>
 ,<@Price, float,>
 ,<@Yield, float,>
 ,<@Basis, nvarchar(4000),>)

Description

Use the table-valued function IAMFACTORS to return the components used in the calculation of price and yield for a security that pays interest at maturity.

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"}]}

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

@Yield

the security’s annual yield. @Yield is an expression of type float or of a type that can be implicitly converted to float.

@Issue

the issue date of the security. @Issue is an expression that returns a datetime or smalldatetime value, or a character string in date format.

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

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

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": [{"ordinal": 0, "colName": "A", "colDatatype": "float", "colDesc": "Number of accrued days from the previous coupon date to the settlement date."}, {"ordinal": 1, "colName": "B", "colDatatype": "float`", "colDesc": "Number of days in a year."}, {"ordinal": 2, "colName": "DIM", "colDatatype": "float", "colDesc": "Number of days from issue to maturity"}, {"ordinal": 3, "colName": "DSM", "colDatatype": "float", "colDesc": "Number of days from settlement to maturity"}, {"ordinal": 4, "colName": "P", "colDatatype": "float", "colDesc": "Price"}, {"ordinal": 5, "colName": "AI", "colDatatype": "float", "colDesc": "Accrued Interest"}, {"ordinal": 6, "colName": "Y", "colDatatype": "float", "colDesc": "Yield"}, {"ordinal": 7, "colName": "TI", "colDatatype": "float", "colDesc": "Total interest"}, {"ordinal": 8, "colName": "DP", "colDatatype": "float", "colDesc": "Dirty Price: P + TI"}]}

Remarks

If @Settlement is NULL then @Settlement = GETDATE().

If @Rate is NULL then @Rate = 0.

If @Basis is NULL then @Basis = 0.

If @Frequency is invalid then IAMFACTORS returns an error.

If @Basis invalid then IAMFACTORS returns an error.

If @Maturity is NULL then an error is returned.

If @Yield is NULL then Y is calculated from @Price and P = @Price otherwise P is calculated from @Yield and Y = @Yield.

Examples

In this example we calculate the factors for a security issued on 2014-07-31 maturing on 2014-12-15 with an interest rate of 0.5% and a yield of 0.2%. Interest is calculated using the Actual/365 day-count convention.

SELECT *

FROM wct.IAMFACTORS(   '2014-10-07', --@Settlement

                       '2014-12-15', --@Maturity

                       '2014-07-31', --@Issue   

                       0.005,        --@Rate

                       NULL,         --@Price

                       0.002,        --@Yield

                       3             --@Basis

                   );

This produces the following result.

{"columns":[{"field":"A","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"B","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"DIM","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"DSM","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"P","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"AI","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Y","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"TI","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"DP","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"A":"68","B":"365","DIM":"137","DSM":"69","P":"100.056655689645","AI":"0.0931506849315069","Y":"0.002","TI":"0.187671232876712","DP":"100.149806374576"}]}

In this example, we calculate the factors for a security issued on 2014-08-15 maturing on 2014-12-01 with an interest rate of -0.05% and a price of 99.977088. Interest is calculated using the Actual/360 day-count convention.

SELECT *

FROM wct.IAMFACTORS(   '2014-10-07', --@Settlement

                       '2014-12-01', --@Maturity

                       '2014-08-15', --@Issue   

                       -0.0005,      --@Rate

                       99.977088,    --@Price

                       NULL,         --@Yield

                       2             --@Basis

                   );

This produces the following result.

{"columns":[{"field":"A","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"B","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"DIM","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"DSM","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"P","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"AI","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Y","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"TI","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"DP","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"A":"53","B":"360","DIM":"108","DSM":"55","P":"99.977088","AI":"-0.00736111111111111","Y":"0.000999997275740647","TI":"-0.015","DP":"99.9697268888889"}]}

In this example we calculate the factors for a security issued on 2014-08-10 maturing on 2014-11-15 with a yield of -0.05% and an interest rate of 0.2%. Interest is calculated using the 30/E 360 (ISDA) day-count convention.

SELECT *

FROM wct.IAMFACTORS(   '2014-10-07', --@Settlement

                       '2014-11-15', --@Maturity

                       '2014-08-10', --@Issue   

                       0.002,        --@Rate

                       NULL,         --@Price

                       -0.0005,      --@Yield

                       4             --@Basis

                   );

This produces the following result.

{"columns":[{"field":"A","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"B","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"DIM","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"DSM","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"P","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"AI","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Y","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"TI","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"DP","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"A":"57","B":"360","DIM":"95","DSM":"38","P":"100.026391953094","AI":"0.0316666666666667","Y":"-0.0005","TI":"0.0527777777777778","DP":"100.05805861976"}]}

Here we calculate the factors for a security issued on 2014-07-01 matures on 2014-12-29 with an interest rate of 7.0% and a price of 99.628637. Interest is calculated using the Actual/364 day-count convention.

SELECT *

FROM wct.IAMFACTORS(   '2014-10-07', --@Settlement

                       '2014-12-29', --@Maturity

                       '2014-07-01', --@Issue   

                       0.07,         --@Rate

                       99.628637,    --@Price

                       NULL,         --@Yield

                       9             --@Basis

                   );

This produces the following result.

{"columns":[{"field":"A","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"B","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"DIM","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"DSM","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"P","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"AI","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Y","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"TI","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"DP","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"A":"98","B":"364","DIM":"181","DSM":"83","P":"99.628637","AI":"1.88461538461538","Y":"0.0850000161919074","TI":"3.48076923076923","DP":"101.513252384615"}]}

IAMFACTORS

Syntax

Description

Arguments

@Basis

@Rate

@Yield

@Issue

@Settlement

@Maturity

@Price

Return Type

Remarks

Examples

See Also