AIFACTOR_OFC
Updated: 11 May 2012
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.
Syntax
SELECT [wctFinancial].[wct].[AIFACTOR_OFC] (
<@Basis, nvarchar(4000),>
,<@Rate, float,>
,<@IssueDate, datetime,>
,<@Settlement, datetime,>
,<@FirstInterestDate, 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.
@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.
@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.
@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 @FirstInterstDate
· @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 = (
SELECT wctFinancial.wct.NBD(holiday)
FROM HOLIDAYS
WHERE COUNTRY = 'BR'
AND holiday < '2041-01-01'
)
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.
Description Interest
----------------------- ----------------------
BOND 958.333333333333
Actual / Actual 959.239130434783
Actual / 360 975
Actual / 365 961.643835616438
30E / 360 958.333333333333
30 / 360 ISDA 958.333333333333
NL / ACT 959.239130434783
NL / 365 958.904109589041
NL / 360 972.222222222222
Actual / 364 964.285714285714
BOND NON-EOM 958.333333333333
Actual / Actual NON-EOM 959.016393442623
Actual / 360 NON-EOM 975
Actual / 365 NON-EOM 961.643835616439
30E / 360 NON-EOM 958.333333333333
30 / 360 ISDA NON-EOM 958.333333333333
NL / ACT NON-EOM 959.016393442623
NL / 365 NON-EOM 958.904109589041
NL / 360 NON-EOM 972.222222222222
Actual / 364 NON-EOM 964.285714285714
BUS / 252 933.901583098177
Actual / ISDA 960.288943783217
Actual / ISMA 959.239130434783
Actual / 365L 960.281458192979
Actual / AFB 960.27397260274
BUS / 252 NON-EOM 933.901583098177
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 = (
SELECT wctFinancial.wct.NBD(holiday)
FROM HOLIDAYS
WHERE COUNTRY = 'BR'
AND holiday < '2041-01-01'
)
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
This produces the following result.
Description 2011-07-15 2011-12-31 2012-04-01
----------------------- --------------------- --------------------- ---------------------
BOND 0.00 458.3333 711.1111
Actual / Actual 0.00 459.2391 711.9864
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 710.6203
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 458.3333 711.1111
Actual / Actual NON-EOM 0.00 461.7486 713.1148
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 461.7636 711.7636
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 445.8131 691.8097
Actual / ISDA 0.00 463.0137 714.3873
Actual / ISMA 0.00 459.2391 711.9864
Actual / 365L 0.00 463.0137 714.3798
Actual / AFB 0.00 463.0062 714.3723
BUS / 252 NON-EOM 0.00 445.8131 691.8097
See Also