AIFACTOR_RPI
Updated: 11 May 2012
Use the scalar-valued 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 NDB 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 = (
SELECT wct.NBD(holiday)
FROM HOLIDAYS
WHERE COUNTRY = 'BR'
AND holiday < '2041-01-01'
)
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 = (
SELECT wct.NBD(holiday)
FROM HOLIDAYS
WHERE COUNTRY = 'BR'
AND holiday < '2041-01-01'
)
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
This produces the following result.
Description 2003-11-01 2003-12-31 2004-04-01
----------------------- --------------------- --------------------- ---------------------
BOND 0.00 166.6667 416.6667
Actual / Actual 0.00 164.8352 417.5824
Actual / 360 0.00 166.6667 422.2222
Actual / 365 0.00 164.3836 416.4384
30E / 360 0.00 163.8889 416.6667
30 / 360 ISDA 0.00 163.8889 416.6667
NL / ACT 0.00 165.7459 417.1271
NL / 365 0.00 164.3836 413.6986
NL / 360 0.00 166.6667 419.4444
Actual / 364 0.00 164.8352 417.5824
BOND NON-EOM 0.00 166.6667 416.6667
Actual / Actual NON-EOM 0.00 164.8352 417.5824
Actual / 360 NON-EOM 0.00 166.6667 422.2222
Actual / 365 NON-EOM 0.00 164.3836 416.4384
30E / 360 NON-EOM 0.00 163.8889 416.6667
30 / 360 ISDA NON-EOM 0.00 163.8889 416.6667
NL / ACT NON-EOM 0.00 165.7459 417.1271
NL / 365 NON-EOM 0.00 164.3836 413.6986
NL / 360 NON-EOM 0.00 166.6667 419.4444
Actual / 364 NON-EOM 0.00 164.8352 417.5824
BUS / 252 0.00 162.6962 412.6927
Actual / ISDA 0.00 164.3836 415.7572
Actual / ISMA 0.00 164.8352 417.5824
Actual / 365L 0.00 163.9344 415.3005
Actual / AFB 0.00 163.9344 415.3005
BUS / 252 NON-EOM 0.00 162.6962 412.6927
See Also