IAMFACTORS
Updated: 10 Oct 2014
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 [wctFinancial].[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
RETURNS TABLE (
[A] [float] NULL,
[B] [float] NULL,
[DIM] [float] NULL,
[DSM] [float] NULL,
[P] [float] NULL,
[AI] [float] NULL,
[Y] [float] NULL,
[TI] [float] NULL,
[DP] [float] NULL
)
|
Column
|
Description
|
|
A
|
Number of accrued days from the previous coupon date to the settlement date.
|
|
B
|
Number of days from in a year.
|
|
DIM
|
Number of days from issue to maturity
|
|
DSM
|
Number of days from settlement to maturity
|
|
P
|
Price
|
|
AI
|
Accrued Interest
|
|
Y
|
Yield
|
|
TI
|
Total interest
|
|
DP
|
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 (which has been reformatted for ease of reading).
|
A
|
B
|
DIM
|
DSM
|
P
|
AI
|
Y
|
TI
|
DP
|
|
68
|
365
|
137
|
69
|
100.0566557
|
0.093150685
|
0.002
|
0.187671233
|
100.1498064
|
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.007361111
|
0.000999997
|
-0.015
|
99.96972689
|
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.026392
|
0.031666667
|
-0.0005
|
0.052777778
|
100.0580586
|
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.884615385
|
0.085000016
|
3.480769231
|
101.5132524
|
See Also