AIFACTOR_OFC

Updated 2023-10-06 11:55:35.177000

Syntax

SELECT [westclintech].[wct].[AIFACTOR_OFC] (
  <@Basis, nvarchar(4000),>
 ,<@Rate, float,>
 ,<@IssueDate, datetime,>
 ,<@Settlement, datetime,>
 ,<@FirstInterestDate, datetime,>
 ,<@Frequency, int,>
 ,<@Holidays, nvarchar(max),>);

Description

Use the scalar-valued function AIFACTOR_OFC to calculate the Accrued Interest Factor for a bond during its odd first coupon period. AIFACTOR_OCF 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 for the odd first period.

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.

{"columns":[{"field":"@Basis","width":139},{"field":"Day count basis","width":396}],"rows":[{"@Basis":"0 or omitted","Day count basis":"US (NASD) 30/360"},{"@Basis":"1","Day count basis":"Actual/Actual"},{"@Basis":"2","Day count basis":"Actual/360"},{"@Basis":"3","Day count basis":"Actual/365"},{"@Basis":"4","Day count basis":"European 30/360"},{"@Basis":"5","Day count basis":"30/360 ISDA"},{"@Basis":"6","Day count basis":"NL/ACT"},{"@Basis":"7","Day count basis":"NL/365"},{"@Basis":"8","Day count basis":"NL/360"},{"@Basis":"9","Day count basis":"A/364"},{"@Basis":"10","Day count basis":"US (NASD) 30/360 non-end-of-month"},{"@Basis":"11","Day count basis":"Actual/Actual non-end-of-month"},{"@Basis":"12","Day count basis":"Actual/360 non-end-of-month"},{"@Basis":"13","Day count basis":"Actual/365 non-end-of-month"},{"@Basis":"14","Day count basis":"European 30/360 non-end-of-month"},{"@Basis":"15","Day count basis":"30/360 ISDA non-end-of-month"},{"@Basis":"16","Day count basis":"NL/ACT non-end-of-month"},{"@Basis":"17","Day count basis":"NL/365 non-end-of-month"},{"@Basis":"18","Day count basis":"NL/360 non-end-of-month"},{"@Basis":"19","Day count basis":"A/364 non-end-of-month"},{"@Basis":"20","Day count basis":"BUS/252"},{"@Basis":"21","Day count basis":"Actual/ISDA"},{"@Basis":"22","Day count basis":"Actual/ISMA"},{"@Basis":"23","Day count basis":"Actual/365L"},{"@Basis":"24","Day count basis":"Actual/AFB"},{"@Basis":"30","Day count basis":"BUS/252 non-end-of-month"}]}

@Frequency

the number of coupon payments per year. For annual payments, @Frequency = 1; for semi-annual, @Frequency = 2; for quarterly, @Frequency = 4; for monthly, @Frequency = 12. @Frequency is an expression of type float or of a type that can be implicitly converted to float.

@FirstInterestDate

the first interest payment date for the bond. When the settlement date occurs on the first interest date, the factor returned will be for the entire first coupon period. @FirstInterestDate is an expression of type datetime or of a type that can be implicitly converted to datetime.

@IssueDate

the first accrual date for the bond. @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.

@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 NBD to create an appropriately formatted string.

Return Type

float

Remarks

@Settlement must be less than or equal to @FirstInterestDate.

@Settlement must be greater than or equal to @IssueDate.

If @Settlement = @IssueDate then the function returns zero.

For bonds where the settlement date is after the first interest date, use AIFACTOR or AIFACTOR_RPI.

For bonds where the settlement date is in an odd last coupon period, use AIFACTOR or AIFACOR_OLC.

For more information on accrual calculations, go to AIFACTOR.

Examples

In this example, we show the calculation of the accrued interest factor for the entire coupon period for all the supported day-count conventions. To keep the SELECT statement simple, we have put the holidays into a scalar variable up front. The holidays are only required for BUS/252 and BUS/252 NON-EOM day-count conventions.

DECLARE @h as varchar(max);

SET @h

    = '20110101,20110307,20110308,20110421,20110422,20110501,20110623,20110907,

              20111012,20111102,20111115,20111225,20120101,20120220,20120221,

              20120406,20120421,20120501,20120607,20120907,20121012,20121102,

              20121115,20121225';

SELECT n.Description,

       wct.AIFACTOR_OFC(   n.basis,      --Basis

                           .10,          --Rate

                           '2011-07-15', --Issue Date

                           '2012-06-30', --Settlement Date

                           '2012-06-30', --First Interest Date

                           2,            --Frequency

                           @h            --Holidays

                       ) * 10000 as [Interest]

FROM

(

    SELECT 0,

           'BOND'

    UNION ALL

    SELECT 1,

           'Actual / Actual'

    UNION ALL

    SELECT 2,

           'Actual / 360'

    UNION ALL

    SELECT 3,

           'Actual / 365'

    UNION ALL

    SELECT 4,

           '30E / 360'

    UNION ALL

    SELECT 5,

           '30 / 360 ISDA'

    UNION ALL

    SELECT 6,

           'NL / ACT'

    UNION ALL

    SELECT 7,

           'NL / 365'

    UNION ALL

    SELECT 8,

           'NL / 360'

    UNION ALL

    SELECT 9,

           'Actual / 364'

    UNION ALL

    SELECT 10,

           'BOND NON-EOM'

    UNION ALL

    SELECT 11,

           'Actual / Actual NON-EOM'

    UNION ALL

    SELECT 12,

           'Actual / 360 NON-EOM'

    UNION ALL

    SELECT 13,

           'Actual / 365 NON-EOM'

    UNION ALL

    SELECT 14,

           '30E / 360 NON-EOM'

    UNION ALL

    SELECT 15,

           '30 / 360 ISDA NON-EOM'

    UNION ALL

    SELECT 16,

           'NL / ACT NON-EOM'

    UNION ALL

    SELECT 17,

           'NL / 365 NON-EOM'

    UNION ALL

    SELECT 18,

           'NL / 360 NON-EOM'

    UNION ALL

    SELECT 19,

           'Actual / 364 NON-EOM'

    UNION ALL

    SELECT 20,

           'BUS / 252'

    UNION ALL

    SELECT 21,

           'Actual / ISDA'

    UNION ALL

    SELECT 22,

           'Actual / ISMA'

    UNION ALL

    SELECT 23,

           'Actual / 365L'

    UNION ALL

    SELECT 24,

           'Actual / AFB'

    UNION ALL

    SELECT 30,

           'BUS / 252 NON-EOM'

) n(basis, Description);

This produces the following result.

{"columns":[{"field":"Description"},{"field":"Interest","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"Description":"BOND","Interest":"958.333333333333"},{"Description":"Actual / Actual","Interest":"959.239130434783"},{"Description":"Actual / 360","Interest":"975"},{"Description":"Actual / 365","Interest":"961.643835616438"},{"Description":"30E / 360","Interest":"958.333333333333"},{"Description":"30 / 360 ISDA","Interest":"958.333333333333"},{"Description":"NL / ACT","Interest":"959.239130434783"},{"Description":"NL / 365","Interest":"958.904109589041"},{"Description":"NL / 360","Interest":"972.222222222222"},{"Description":"Actual / 364","Interest":"964.285714285714"},{"Description":"BOND NON-EOM","Interest":"958.333333333333"},{"Description":"Actual / Actual NON-EOM","Interest":"959.016393442623"},{"Description":"Actual / 360 NON-EOM","Interest":"975"},{"Description":"Actual / 365 NON-EOM","Interest":"961.643835616439"},{"Description":"30E / 360 NON-EOM","Interest":"958.333333333333"},{"Description":"30 / 360 ISDA NON-EOM","Interest":"958.333333333333"},{"Description":"NL / ACT NON-EOM","Interest":"959.016393442623"},{"Description":"NL / 365 NON-EOM","Interest":"958.904109589041"},{"Description":"NL / 360 NON-EOM","Interest":"972.222222222222"},{"Description":"Actual / 364 NON-EOM","Interest":"964.285714285714"},{"Description":"BUS / 252","Interest":"934.557015350965"},{"Description":"Actual / ISDA","Interest":"960.288943783217"},{"Description":"Actual / ISMA","Interest":"959.239130434783"},{"Description":"Actual / 365L","Interest":"960.281458192979"},{"Description":"Actual / AFB","Interest":"960.27397260274"},{"Description":"BUS / 252 NON-EOM","Interest":"934.557015350965"}]}

In this example, we show the calculation of the accrued interest factor for each of the day-count conventions for the issue date, the last day of the year within the odd coupon period, and for a date in the following year in the odd coupon period.

DECLARE @h as varchar(max);

SET @h

    = '20110101,20110307,20110308,20110421,20110422,20110501,20110623,20110907,

              20111012,20111102,20111115,20111225,20120101,20120220,20120221,

              20120406,20120421,20120501,20120607,20120907,20121012,20121102,

              20121115,20121225';

SELECT Description,

       [2011-07-15],

       [2011-12-31],

       [2012-04-01]

FROM

(

    SELECT n.Description,

           n.basis,

           m.sd,

           CAST(wct.AIFACTOR_OFC(   n.basis,      --Basis

                                    .10,          --Rate

                                    '2011-07-15', --Issue Date

                                    m.sd,         --Settlement Date

                                    '2012-06-30', --First Interest Date

                                    2,            --Frequency

                                    @h            --Holidays

                                ) * 10000 as money) as [Interest]

    FROM

    (

        SELECT 0,

               'BOND'

        UNION ALL

        SELECT 1,

               'Actual / Actual'

        UNION ALL

        SELECT 2,

               'Actual / 360'

        UNION ALL

        SELECT 3,

               'Actual / 365'

        UNION ALL

        SELECT 4,

               '30E / 360'

        UNION ALL

        SELECT 5,

               '30 / 360 ISDA'

        UNION ALL

        SELECT 6,

               'NL / ACT'

        UNION ALL

        SELECT 7,

               'NL / 365'

        UNION ALL

        SELECT 8,

               'NL / 360'

        UNION ALL

        SELECT 9,

               'Actual / 364'

        UNION ALL

        SELECT 10,

               'BOND NON-EOM'

        UNION ALL

        SELECT 11,

               'Actual / Actual NON-EOM'

        UNION ALL

        SELECT 12,

               'Actual / 360 NON-EOM'

        UNION ALL

        SELECT 13,

               'Actual / 365 NON-EOM'

        UNION ALL

        SELECT 14,

               '30E / 360 NON-EOM'

        UNION ALL

        SELECT 15,

               '30 / 360 ISDA NON-EOM'

        UNION ALL

        SELECT 16,

               'NL / ACT NON-EOM'

        UNION ALL

        SELECT 17,

               'NL / 365 NON-EOM'

        UNION ALL

        SELECT 18,

               'NL / 360 NON-EOM'

        UNION ALL

        SELECT 19,

               'Actual / 364 NON-EOM'

        UNION ALL

        SELECT 20,

               'BUS / 252'

        UNION ALL

        SELECT 21,

               'Actual / ISDA'

        UNION ALL

        SELECT 22,

               'Actual / ISMA'

        UNION ALL

        SELECT 23,

               'Actual / 365L'

        UNION ALL

        SELECT 24,

               'Actual / AFB'

        UNION ALL

        SELECT 30,

               'BUS / 252 NON-EOM'

    ) n(basis, Description)

        CROSS APPLY

    (

        SELECT '2011-07-15'

        UNION ALL

        SELECT '2011-12-31'

        UNION ALL

        SELECT '2012-04-01'

    ) m(sd)

) d

PIVOT

(

    min(INTEREST)

    FOR sd in ([2011-07-15], [2011-12-31], [2012-04-01])

) as P

ORDER by basis;