SQL Server AIFACTOR_RPI Function

Updated 2023-10-06 11:57:53.710000

Description

Use the scalar function AIFACTOR_RPI to calculate the Accrued Interest Factor for a Regular Periodic Interest period. AIFACTOR_RPI 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 regular period.

Syntax

SELECT [wctFinancial].[wct].[AIFACTOR_RPI] (
  <@Basis, nvarchar(4000),>
 ,<@Rate, float,>
 ,<@PrevCoupDate, datetime,>
 ,<@Settlement, datetime,>
 ,<@NextCoupDate, datetime,>
 ,<@Frequency, int,>
 ,<@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
1	Actual/Actual
2	Actual/360
3	Actual/365
4	European 30/360
5	30/360 ISDA
6	NL/ACT
7	NL/365
8	NL/360
9	A/364
10	US (NASD) 30/360 non-end-of-month
11	Actual/Actual 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
16	NL/ACT 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
20	BUS/252
21	Actual/ISDA
22	Actual/ISMA
23	Actual/365L
24	Actual/AFB
30	BUS/252 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.

@PrevCoupDate

the previous coupon date, in relation to the settlement date of the transaction. When the settlement date occurs on a coupon date, the previous coupon date can be either the settlement date or the coupon date before the settlement date. @Settlement 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.

@NextCoupDate

the next coupon date, in relation to the settlement date of the transaction. When the settlement date occurs on a coupon date, the next coupon date can be either the settlement date or the coupon date after the settlement date. @Settlement is an expression of type datetime or of a type that can be implicitly converted to datetime.

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

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

Return Type

float

Remarks

@PrevCoupDate must be less than @NextCoupDate.

If @Settlement = @NextCoupDate then the function returns the coupon interest for the full coupon period.

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

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

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 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_RPI(   n.basis,      --Basis
                           .10,          --Rate
                           '2003-11-01', --Previous Coupon Date
                           '2004-05-01', --Settlement Date
                           '2004-05-01', --Next Coupon Date
                           2,            --Frequency
                           '2009-11-01', --Maturity
                           @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.

Description	Interest
BOND	500
Actual / Actual	500
Actual / 360	505.555555555556
Actual / 365	498.630136986301
30E / 360	500
30 / 360 ISDA	500
NL / ACT	500
NL / 365	495.890410958904
NL / 360	502.777777777778
Actual / 364	500
BOND NON-EOM	500
Actual / Actual NON-EOM	500
Actual / 360 NON-EOM	505.555555555556
Actual / 365 NON-EOM	498.630136986301
30E / 360 NON-EOM	500
30 / 360 ISDA NON-EOM	500
NL / ACT NON-EOM	500
NL / 365 NON-EOM	495.890410958904
NL / 360 NON-EOM	502.777777777778
Actual / 364 NON-EOM	500
BUS / 252	488.088481701516
Actual / ISDA	497.724380567408
Actual / ISMA	500
Actual / 365L	497.267759562841
Actual / AFB	497.267759562841
BUS / 252 NON-EOM	488.088481701516

In this example, we show the calculation of the accrued interest factor for each of the day-count conventions for the first day of the coupon period, the last day of the year within the coupon period, and for a date in the following year in the 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,
       [2003-11-01],
       [2003-12-31],
       [2004-04-01]
FROM
(
    SELECT n.Description,
           n.basis,
           m.sd,
           CAST(wct.AIFACTOR_RPI(   n.basis,      --Basis
                                    .10,          --Rate
                                    '2003-11-01', --Previous Coupon Date
                                    m.sd,         --Settlement Date
                                    '2004-05-01', --Next Coupon Date
                                    2,            --Frequency
                                    '2009-11-01', --Maturity
                                    @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 '2003-11-01'
        UNION ALL
        SELECT '2003-12-31'
        UNION ALL
        SELECT '2004-04-01'
    ) m(sd)
) d
PIVOT
(
    min(INTEREST)
    FOR sd in ([2003-11-01], [2003-12-31], [2004-04-01])
) as P
ORDER by basis;