SQL Server IAMFACTORS Function

Updated 2023-10-06 14:18:11.283000

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.

Syntax

SELECT * FROM [westclintech].[wct].[IAMFACTORS](
  <@Settlement, datetime,>
 ,<@Maturity, datetime,>
 ,<@Issue, datetime,>
 ,<@Rate, 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.

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

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

Return Type

table

colName	colDatatype	colDesc
A	float	Number of accrued days from the previous coupon date to the settlement date.
B	float`	Number of days in a year.
DIM	float	Number of days from issue to maturity
DSM	float	Number of days from settlement to maturity
P	float	Price
AI	float	Accrued Interest
Y	float	Yield
TI	float	Total interest
DP	float	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.

A	B	DIM	DSM	P	AI	Y	TI	DP
68	365	137	69	100.056655689645	0.0931506849315069	0.002	0.187671232876712	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.

A	B	DIM	DSM	P	AI	Y	TI	DP
53	360	108	55	99.977088	-0.00736111111111111	0.000999997275740647	-0.015	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.

A	B	DIM	DSM	P	AI	Y	TI	DP
57	360	95	38	100.026391953094	0.0316666666666667	-0.0005	0.0527777777777778	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.

A	B	DIM	DSM	P	AI	Y	TI	DP
98	364	181	83	99.628637	1.88461538461538	0.0850000161919074	3.48076923076923	101.513252384615

Resources

SQL Server IAMFACTORS Function

Description

Syntax

Arguments

@Settlement

@Maturity

@Issue

@Rate

@Price

@Yield

@Basis

Return Type

Remarks

Examples

See Also