Login     Register

        Contact Us     Search

XLeratorDB/financial Documentation

SQL Server disambiguation function for interest-at-maturity securities


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

 



Copyright 2008-2013 WestClinTech LLC         Privacy Policy        Terms of Service