SQL Server AIFACTOR_OLC Function
Updated 2023-10-06 11:56:42.617000
Description
Use the scalar-valued function AIFACTOR_OLC to calculate the Accrued Interest Factor for a bond during its odd last coupon period. AIFACTOR_OLC 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 last period.
Syntax
SELECT [westclintech].[wct].[AIFACTOR_OLC] (
<@Basis, nvarchar(4000),>
,<@Rate, float,>
,<@LastCouponDate, datetime,>
,<@Settlement, datetime,>
,<@MaturityDate, datetime,>
,<@Frequency, int,>
,<@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.
@LastCouponDate
the start date for the odd last coupon period. @LastCouoponDate 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.
@MaturityDate
the maturity date for the bond. When the settlement date occurs on the maturity date, the factor returned will be for the entire last coupon period. @MaturityDate 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.
@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 @Maturity.
@Settlement must be greater than or equal to @LastInterestDate.
If @Settlement = @LastInterestDate then the function returns zero.
For bonds where the settlement date is before the last interest date, use AIFACTOR or AIFACTOR_RPI.
For bonds where the settlement date is in an odd first coupon period, use AIFACTOR or AIFACOR_OFC.
For more information on accrual calculations, see AIFACTOR.
Examples
In this example, we show the calculation of the accrued interest factor for the entire last 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_OLC( n.basis, --Basis
.10, --Rate
'2011-07-15', --Last Coupon Date
'2012-06-30', --Settlement Date
'2012-06-30', --Maturity 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.
| Description | Interest |
|---|---|
| BOND | 958.333333333333 |
| Actual / Actual | 958.791208791209 |
| Actual / 360 | 975 |
| Actual / 365 | 961.643835616438 |
| 30E / 360 | 958.333333333333 |
| 30 / 360 ISDA | 958.333333333333 |
| NL / ACT | 958.563535911602 |
| NL / 365 | 958.904109589041 |
| NL / 360 | 972.222222222222 |
| Actual / 364 | 964.285714285714 |
| BOND NON-EOM | 958.333333333333 |
| Actual / Actual NON-EOM | 958.791208791209 |
| Actual / 360 NON-EOM | 975 |
| Actual / 365 NON-EOM | 961.643835616438 |
| 30E / 360 NON-EOM | 958.333333333333 |
| 30 / 360 ISDA NON-EOM | 958.333333333333 |
| NL / ACT NON-EOM | 958.563535911602 |
| NL / 365 NON-EOM | 958.904109589041 |
| NL / 360 NON-EOM | 972.222222222222 |
| Actual / 364 NON-EOM | 964.285714285714 |
| BUS / 252 | 949.351300614094 |
| Actual / ISDA | 960.288943783217 |
| Actual / ISMA | 958.791208791209 |
| Actual / 365L | 959.016393442623 |
| Actual / AFB | 960.39374204656 |
| BUS / 252 NON-EOM | 949.351300614094 |
In this example, we show the calculation of the accrued interest factor for each of the day-count conventions for the last interest 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_OLC( 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;
This produces the following result.
| Description | 2011-07-15 | 2011-12-31 | 2012-04-01 |
|---|---|---|---|
| BOND | 0.00 | 461.1111 | 711.1111 |
| Actual / Actual | 0.00 | 459.2391 | 711.5385 |
| Actual / 360 | 0.00 | 469.4444 | 725.00 |
| Actual / 365 | 0.00 | 463.0137 | 715.0685 |
| 30E / 360 | 0.00 | 458.3333 | 711.1111 |
| 30 / 360 ISDA | 0.00 | 458.3333 | 711.1111 |
| NL / ACT | 0.00 | 459.2391 | 709.9448 |
| NL / 365 | 0.00 | 463.0137 | 712.3288 |
| NL / 360 | 0.00 | 469.4444 | 722.2222 |
| Actual / 364 | 0.00 | 464.2857 | 717.033 |
| BOND NON-EOM | 0.00 | 461.1111 | 711.1111 |
| Actual / Actual NON-EOM | 0.00 | 459.2391 | 711.5385 |
| Actual / 360 NON-EOM | 0.00 | 469.4444 | 725.00 |
| Actual / 365 NON-EOM | 0.00 | 463.0137 | 715.0685 |
| 30E / 360 NON-EOM | 0.00 | 458.3333 | 711.1111 |
| 30 / 360 ISDA NON-EOM | 0.00 | 458.3333 | 711.1111 |
| NL / ACT NON-EOM | 0.00 | 459.2391 | 709.9448 |
| NL / 365 NON-EOM | 0.00 | 463.0137 | 712.3288 |
| NL / 360 NON-EOM | 0.00 | 469.4444 | 722.2222 |
| Actual / 364 NON-EOM | 0.00 | 464.2857 | 717.033 |
| BUS / 252 | 0.00 | 449.9566 | 705.3071 |
| Actual / ISDA | 0.00 | 463.0137 | 714.3873 |
| Actual / ISMA | 0.00 | 459.2391 | 711.5385 |
| Actual / 365L | 0.00 | 461.7486 | 713.1148 |
| Actual / AFB | 0.00 | 463.0137 | 714.4921 |
| BUS / 252 NON-EOM | 0.00 | 449.9566 | 705.3071 |
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_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_RPI - Calculate the Accrued Interest Factor for a Regular Periodic Interest period
BONDINT - Accrued interest on a bond paying regular, periodic interest
ODDFINT - Accrued interest for a bond with an odd first coupon
ODDLINT - Accrued interest for a bond with an odd last coupon