SQL Server AIFACTOR_IAM Function

Updated 2023-10-06 11:53:53.850000

Description

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.

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

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.

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.

See Also

ACCRINT - Calculate the accrued interest for a security that pays periodic interest.

ACCRINTM - Calculate the accrued interest for a security that pays interest at maturity.

AIFACTOR - Calculate the Accrued Interest Factor

AIFACTOR_OFC - Calculate the Accrued Interest Factor for a bond during its odd first coupon period

AIFACTOR_OLC - Calculate the Accrued Interest Factor for a bond during its odd last coupon period

AIFACTOR_RPI - Calculate the Accrued Interest Factor for a Regular Periodic Interest period

BONDINT - Accrued interest on a bond paying regular, periodic interest

COMPINT - Calculate the accrued interest for a security where interest is compounded periodically and paid at maturity

ODDCOMPINT - Calculate the accrued interest in first coupon period for a bond with an odd first coupon and a par value of 100

ODDFINT - Accrued interest for a bond with an odd first coupon

ODDLINT - Accrued interest for a bond with an odd last coupon