Login     Register

        Contact Us     Search

XLeratorDB/financial Documentation

SQL Server accrued interest factor for interest-at-maturity securities


AIFACTOR_IAM

Updated: 11 May 2012


Use the scalar-valued function AIFACTOR_IAM to calculate the Accrued Interest Factor for an Interest-at-Maturity security. AIFACTOR_IAM returns a decimal value which can then be multiplied by the face amount of the bond to return the monetary value of the accrued interest.
Syntax
SELECT [wctFinancial].[wct].[AIFACTOR_IAM] (
  <@Basis, nvarchar(4000),>
 ,<@Rate, float,>
 ,<@IssueDate, datetime,>
 ,<@Settlement, datetime,>
 ,<@Maturity, datetime,>
 ,<@Holidays, nvarchar(max),>)
Arguments
@Basis
the day-count convention used in the calculation of the accrued coupon interest. @Basis is an expression of the character string data type category.
 

@Basis
Day count basis
 
0 or omitted
US (NASD) 30/360
2
Actual/360
3
Actual/365
4
European 30/360
5
30/360 ISDA
7
NL/365
8
NL/360
9
A/364
10
US (NASD) 30/360 non-end-of-month
12
Actual/360 non-end-of-month
13
Actual/365 non-end-of-month
14
European 30/360 non-end-of-month
15
30/360 ISDA non-end-of-month
17
NL/365 non-end-of-month
18
NL/360 non-end-of-month
19
A/364 non-end-of-month

@Rate
the coupon rate, as a decimal, for the financial instrument. @Rate is an expression of type float or of a type that can be implicitly converted to float.
@IssueDate
the first interest accrual date for the security. @IssueDate is an expression of type datetime or of a type that can be implicitly converted to datetime.
@Settlement
the settlement date of the transaction. @Settlement is an expression of type datetime or of a type that can be implicitly converted to datetime.
@Maturity
the maturity date for the financial instrument. @Maturity is an expression of type datetime or of a type that can be implicitly converted to datetime.
@Holidays
a comma separated string containing the holiday (non-business) dates to be used in the calculation of the number of business days. You can use the aggregate function NDB to create an appropriately formatted string.
Return Type
float
Remarks
·         For more information on accrual calculations, go to AIFACTOR.
·         @Settlement must be greater than or equal to @IssueDate and less than or equal to @Maturity.
Examples
This is a bond issued on 01-Mar-2012, settling on 21-May-2012 with a maturity date of 01-Jul-2012. The bond has an interest rate of 0.50% and interest is calculated using the Actual / 360 day-count convention.
SELECT wct.AIFACTOR_IAM(
 2                --Basis
,.005             --Rate
,'2012-03-01'     --Issue Date
,'2012-05-21'     --Settlement
,'2012-07-01'     --Maturity
,NULL             --Holidays
) as [Accrued Interest]
This produces the following result.
      Accrued Interest
----------------------
              0.001125
If we had purchased 100,000,000 face amount of the previous bond, the monetary value of the accrued interest would be:
SELECT wct.AIFACTOR_IAM(
 2                      --Basis
,.005             --Rate
,'2012-03-01'     --Issue Date
,'2012-05-21'     --Settlement
,'2012-07-01'     --Maturity
,NULL             --Holidays
) * 100000000 as [Accrued Interest]
This produces the following result.
      Accrued Interest
----------------------
                112500
 
Here we look at the same bound across a variety of day count conventions.
SELECT dcm
,wct.AIFACTOR_IAM(
 dcm              --Basis
,.005             --Rate
,'2012-03-01'     --Issue Date
,'2012-05-21'     --Settlement
,'2012-07-01'     --Maturity
,NULL             --Holidays
) * 100000000 as [Accrued Interest]
FROM (
      SELECT 0 UNION ALL                  --US (NASD) 30/360
      SELECT 2 UNION ALL                  --Actual/360
      SELECT 3 UNION ALL                  --Actual/365
      SELECT 4 UNION ALL                  --European 30/360
      SELECT 5 UNION ALL                  --30/360 ISDA
      SELECT 7 UNION ALL                  --NL/365
      SELECT 8 UNION ALL                  --NL/360
      SELECT 9 UNION ALL                  --A/364
      SELECT 10 UNION ALL                 --US (NASD) 30/360 non-end-of-month
      SELECT 12 UNION ALL                 --Actual/360 non-end-of-month
      SELECT 13 UNION ALL                 --Actual/365 non-end-of-month
      SELECT 14 UNION ALL                 --European 30/360 non-end-of-month
      SELECT 15 UNION ALL                 --30/360 ISDA non-end-of-month
      SELECT 17 UNION ALL                 --NL/365 non-end-of-month
      SELECT 18 UNION ALL                 --NL/360 non-end-of-month
      SELECT 19                           --A/364 non-end-of-month
      ) n(dcm)
This produces the following result.
        dcm       Accrued Interest
----------- ----------------------
          0      111111.111111111
          2                 112500
          3       110958.904109589
          4       111111.111111111
          5       111111.111111111
          7       110958.904109589
          8                 112500
          9       111263.736263736
         10       111111.111111111
         12                 112500
         13       110958.904109589
         14       111111.111111111
         15       111111.111111111
         17       110958.904109589
         18                 112500
         19       111263.736263736
 

 

See Also

 



Copyright 2008-2017 Westclintech LLC         Privacy Policy        Terms of Service