SQL Server AIFACTOR Function

Updated 2023-10-06 11:52:14.687000

Description

Use the scalar-valued function AIFACTOR to calculate the Accrued Interest Factor. AIFACTOR 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 [wct].[AIFACTOR] (
  <@Basis, nvarchar(4000),>
 ,<@Rate, float,>
 ,<@Maturity, datetime,>
 ,<@Settlement, datetime,>
 ,<@Frequency, int,>
 ,<@FirstInterestDate, datetime,>
 ,<@LastInterestDate, datetime,>
 ,<@IssueDate, 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.

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

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

@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 coupon date of the security. The period from the issue date until the first coupon date defines the odd first interest period. All subsequent coupon dates are assumed to occur at regular periodic intervals as defined by @Frequency in relation to the @LastInterestDate (if entered) or @Maturity. @FirstInterestDate is an expression of type datetime or of a type that can be implicitly converted to datetime.

@LastInterestDate

the last coupon date of the security prior to maturity date, if the last coupon period is an odd period. The period from the last interest date date until the maturity date defines the odd last interest period. All previous coupon dates are assumed to occur at regular periodic intervals as defined by @Frequency. @LastInterestDate is an expression of type datetime or of a type that can be implicitly converted to datetime.

@IssueDate

the issue date of the security; the date from which the security starts accruing interest. @IssueDate 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

If @Basis IS NULL, then @Basis = '0'.

For bonds with regular period coupons, coupon dates are calculated backwards from @Maturity.

For bonds with an odd first coupon period, settling in the odd period, the coupon dates are calculated backward from @FirstInterestDate.

Odd first coupon bonds with @Settlement >= @FirstInterestDate and no @LastInterestDate are treated as regular periodic bonds.

If the last interest date is not NULL and the settlement date is less than the @LastInterestDate and greater than or equal to the @FirstInterestDate or @FirstInterestDate IS NULL, than the coupon dates are calculated backwards from @LastInterestDate.

If the @LastInterestDate IS NOT NULL and @Settlement >= @LastInterestDate then coupon dates are calculated from the quasi-maturity date, which is calculated forward from @LastInterestDate.

Examples

Calculate the accrued interest factor for a bond with a coupon rate of 5% paying coupons semi-annually, with a Maturity Date of 2012-05-15 and a settlement date of 2012-02-15.

SELECT wct.AIFACTOR(   '0',          --Basis
                       .05,          --Rate
                       '2012-05-15', --Maturity
                       '2012-02-15', --Settlement
                       2,            --Frequency
                       NULL,         --FirstInterestDate
                       NULL,         --LastInterestDate
                       NULL,         --IssueDate
                       NULL          --Holidays
                   ) as AIFACTOR;

This produces the following result.

We can use the SeriesDate Function to calculate the AIFACTOR for every day from the settlement date to the maturity date.

SELECT k.SeriesValue,
       wct.AIFACTOR(   '0',           --Basis
                       .05,           --Rate
                       '2012-05-15',  --Maturity
                       k.SeriesValue, --Settlement
                       2,             --Frequency
                       NULL,          --FirstInterestDate
                       NULL,          --LastInterestDate
                       NULL,          --IssueDate
                       NULL           --Holidays
                   ) as AIFACTOR
FROM wct.SeriesDate('2012-02-15', '2012-05-15', NULL, NULL, NULL) k;

This produces the following result.

In this SELECT statement we will calculate the AIFACTOR for each date from the 2012-02-15 though 2012-05-15 and for each day-count convention 0 through 9.

SELECT SeriesValue as SettDate,
       ROUND([0], 6) as [0],
       ROUND([1], 6) as [1],
       ROUND([2], 6) as [2],
       ROUND([3], 6) as [3],
       ROUND([4], 6) as [4],
       ROUND([5], 6) as [5],
       ROUND([6], 6) as [6],
       ROUND([7], 6) as [7],
       ROUND([8], 6) as [8],
       ROUND([9], 6) as [9]
FROM
(
    SELECT k.SeriesValue,
           l.seriesvalue as basis,
           wct.AIFACTOR(   l.seriesValue, --Basis
                           .05,           --rate
                           '2012-05-15',  --Maturity Date
                           k.SeriesValue, --Settlement Date
                           2,             --Frequency
                           NULL,          --First Interest Date
                           NULL,          --Last Interest Date
                           NULL,          --Issue Date
                           NULL           --Holidays
                       ) as AIF
    FROM wct.SeriesDate('2012-02-15', '2012-05-15', NULL, NULL, NULL) k
        CROSS APPLY wct.SERIESINT(0, 9, NULL, NULL, NULL) l
) d
PIVOT
(
    min(AIF)
    for basis in ([0], [1], [2], [3], [4], [5], [6], [7], [8], [9])
) as P;

This produces the following result.

If the maturity date, last interest date, or first interest date is the last day of the month, then the calculation of the previous coupon date (which is used in the calculation of the accrued interest), can be affected by the choice of @Basis. In this example, we will look at the difference between the Actual/Actual (1) and the Actual/Actual NON_EOM (11) values for @Basis.

SELECT LEFT(convert(varchar, l.seriesvalue, 106), 11) as Settlement,
       wct.AIFACTOR(   1,             --Basis
                       .05,           --rate
                       '2012-06-30',  --Maturity Date
                       l.SeriesValue, --Settlement Date
                       2,             --Frequency
                       NULL,          --First Interest Date
                       NULL,          --Last Interest Date
                       NULL,          --Issue Date
                       NULL           --Holidays
                   ) as [Actual/Actual],
       wct.AIFACTOR(   11,            --Basis
                       .05,           --rate
                       '2012-06-30',  --Maturity Date
                       l.SeriesValue, --Settlement Date
                       2,             --Frequency
                       NULL,          --First Interest Date
                       NULL,          --Last Interest Date
                       NULL,          --Issue Date
                       NULL           --Holidays
                   ) as [Actual/Actual NON EOM]
FROM wct.SeriesDate('2011-11-30', '2012-01-31', NULL, NULL, NULL) l;

This produces the following result.

The BUS/252 day-count convention only accrues interest on business days, so there is no change in the AIFACTOR on weekends or holidays. In addition, the calculation of the coupon interest amount for the coupon period is different than for other day-count conventions and is consistent with the ANDIMA specification.

SELECT LEFT(convert(varchar, l.seriesvalue, 106), 11) as Settlement,
       wct.AIFACTOR(   20,              --Basis
                       .05,             --rate
                       '2012-06-30',    --Maturity Date
                       l.SeriesValue,   --Settlement Date
                       2,               --Frequency
                       NULL,            --First Interest Date
                       NULL,            --Last Interest Date
                       NULL,            --Issue Date
                       '20111225,20120101,20120220,20120221,20120406,20120421,20120501,20120607'				--Holidays
                   ) as AIFACTOR
FROM wct.SeriesDate('2011-11-30', '2012-01-31', NULL, NULL, NULL) l
GROUP BY l.seriesvalue
ORDER BY SeriesValue

This produces the following result.

Settlement                AIFACTOR
----------- ----------------------
30 Nov 2011     0.0203921465830275
01 Dec 2011     0.0205792304966333
02 Dec 2011      0.020766314410239
03 Dec 2011     0.0209533983238448
04 Dec 2011     0.0209533983238448
05 Dec 2011     0.0209533983238448
06 Dec 2011     0.0211404822374505
07 Dec 2011     0.0213275661510563
08 Dec 2011     0.0215146500646621
09 Dec 2011     0.0217017339782678
10 Dec 2011     0.0218888178918736
11 Dec 2011     0.0218888178918736
12 Dec 2011     0.0218888178918736
13 Dec 2011     0.0220759018054793
14 Dec 2011     0.0222629857190851
15 Dec 2011     0.0224500696326908
16 Dec 2011     0.0226371535462966
17 Dec 2011     0.0228242374599024
18 Dec 2011     0.0228242374599024
19 Dec 2011     0.0228242374599024
20 Dec 2011     0.0230113213735081
21 Dec 2011     0.0231984052871139
22 Dec 2011     0.0233854892007196
23 Dec 2011     0.0235725731143254
24 Dec 2011     0.0237596570279311
25 Dec 2011     0.0237596570279311
26 Dec 2011     0.0237596570279311
27 Dec 2011     0.0239467409415369
28 Dec 2011     0.0241338248551427
29 Dec 2011     0.0243209087687484
30 Dec 2011     0.0245079926823542
31 Dec 2011                      0
01 Jan 2012                      0
02 Jan 2012                      0
03 Jan 2012   0.000197560612767679
04 Jan 2012   0.000395121225535359
05 Jan 2012   0.000592681838303038
06 Jan 2012   0.000790242451070718
07 Jan 2012   0.000987803063838397
08 Jan 2012   0.000987803063838397
09 Jan 2012   0.000987803063838397
10 Jan 2012    0.00118536367660608
11 Jan 2012    0.00138292428937376
12 Jan 2012    0.00158048490214144
13 Jan 2012    0.00177804551490911
14 Jan 2012    0.00197560612767679
15 Jan 2012    0.00197560612767679
16 Jan 2012    0.00197560612767679
17 Jan 2012    0.00217316674044447
18 Jan 2012    0.00237072735321215
19 Jan 2012    0.00256828796597983
20 Jan 2012    0.00276584857874751
21 Jan 2012    0.00296340919151519
22 Jan 2012    0.00296340919151519
23 Jan 2012    0.00296340919151519
24 Jan 2012    0.00316096980428287
25 Jan 2012    0.00335853041705055
26 Jan 2012    0.00355609102981823
27 Jan 2012    0.00375365164258591
28 Jan 2012    0.00395121225535359
29 Jan 2012    0.00395121225535359
30 Jan 2012    0.00395121225535359
31 Jan 2012    0.00414877286812127

Here's an example of the accrued interest factor calculation using the Actual/Actual ISDA day-count convention.

SELECT LEFT(convert(varchar, l.seriesvalue, 106), 11) as Settlement,
       wct.AIFACTOR(   21,            --Basis
                       .05,           --rate
                       '2012-05-31',  --Maturity Date
                       l.SeriesValue, --Settlement Date
                       2,             --Frequency
                       NULL,          --First Interest Date
                       NULL,          --Last Interest Date
                       NULL,          --Issue Date
                       NULL           --Holidays
                   ) as AIFACTOR
FROM wct.SeriesDate('2011-11-30', '2012-01-31', NULL, NULL, NULL) l;

This produces the following result.

Notice how the AIFACTOR increases smoothly by 0.00013698630 for each day in 2011 and then AIFACTOR changes by 0.00013661202 for each day in 2012.

Here's an example of the accrued interest factor calculation using the Actual/365L day-count convention.

SELECT LEFT(convert(varchar, l.seriesvalue, 106), 11) as Settlement,
       wct.AIFACTOR(   23,            --Basis
                       .05,           --rate
                       '2012-05-31',  --Maturity Date
                       l.SeriesValue, --Settlement Date
                       2,             --Frequency
                       NULL,          --First Interest Date
                       NULL,          --Last Interest Date
                       NULL,          --Issue Date
                       NULL           --Holidays
                   ) as AIFACTOR
FROM wct.SeriesDate('2011-11-30', '2012-01-31', NULL, NULL, NULL) l;

This produces the following result.

Notice how the AIFACTOR increases smoothly by 0.00013661202 for each day in the coupon period even as the year changes from 2011 to 2012.

Here's an example of the accrued interest factor calculation using the Actual/AFB day-count convention.

SELECT LEFT(convert(varchar, l.seriesvalue, 106), 11) as Settlement,
       wct.AIFACTOR(   24,            --Basis
                       .05,           --rate
                       '2012-05-31',  --Maturity Date
                       l.SeriesValue, --Settlement Date
                       2,             --Frequency
                       NULL,          --First Interest Date
                       NULL,          --Last Interest Date
                       NULL,          --Issue Date
                       NULL           --Holidays
                   ) as AIFACTOR
FROM wct.SeriesDate('2011-11-30', '2012-01-31', NULL, NULL, NULL) l;

This produces the following results.

This produces the same result as Actual/Actual Long.

Finally, let's look at a comparison of all the day-count conventions for a single settlement date.

DECLARE @h as varchar(max);
SET @h = '20120101,20120220,20120221,20120406,20120421,20120501,20120607,20120907,
          20121012,20121102,20121115,20121225';
SELECT n.description,
       wct.AIFACTOR(   n.basis,      --Basis
                       .10,          --Rate
                       '2012-05-31', --Maturity Date
                       '2012-03-31', --Settlement Date
                       2,            --Frequency
                       NULL,         --First Interest Date
                       NULL,         --Last Interest Date
                       NULL,         --Issue Date
                       @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.

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_IAM - Calculate the Accrued Interest Factor for an Interest-at-Maturity security

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