AIFACTOR_RPI
Updated: 11 May 2012
Use the scalarvalued 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 daycount 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 nonendofmonth

11

Actual/Actual nonendofmonth

12

Actual/360 nonendofmonth

13

Actual/365 nonendofmonth

14

European 30/360 nonendofmonth

15

30/360 ISDA nonendofmonth

16

NL/ACT nonendofmonth

17

NL/365 nonendofmonth

18

NL/360 nonendofmonth

19

A/364 nonendofmonth

20

BUS/252

21

Actual/ISDA

22

Actual/ISMA

23

Actual/365L

24

Actual/AFB

30

BUS/252 nonendofmonth

@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 semiannual, @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 (nonbusiness) 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 daycount 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 NONEOM daycount conventions.
DECLARE @h as varchar(max)
SET @h = (
SELECT wct.NBD(holiday)
FROM HOLIDAYS
WHERE COUNTRY = 'BR'
AND holiday < '20410101'
)
SELECT n.Description
,wct.AIFACTOR_RPI(
n.basis Basis
,.10 Rate
,'20031101' Previous Coupon Date
,'20040501' Settlement Date
,'20040501' Next Coupon Date
,2 Frequency
,'20091101' 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 NONEOM' UNION ALL
SELECT 11, 'Actual / Actual NONEOM' UNION ALL
SELECT 12, 'Actual / 360 NONEOM' UNION ALL
SELECT 13, 'Actual / 365 NONEOM' UNION ALL
SELECT 14, '30E / 360 NONEOM' UNION ALL
SELECT 15, '30 / 360 ISDA NONEOM' UNION ALL
SELECT 16, 'NL / ACT NONEOM' UNION ALL
SELECT 17, 'NL / 365 NONEOM' UNION ALL
SELECT 18, 'NL / 360 NONEOM' UNION ALL
SELECT 19, 'Actual / 364 NONEOM' 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 NONEOM'
) 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 NONEOM 500
Actual / Actual NONEOM 500
Actual / 360 NONEOM 505.555555555556
Actual / 365 NONEOM 498.630136986301
30E / 360 NONEOM 500
30 / 360 ISDA NONEOM 500
NL / ACT NONEOM 500
NL / 365 NONEOM 495.890410958904
NL / 360 NONEOM 502.777777777778
Actual / 364 NONEOM 500
BUS / 252 488.088481701516
Actual / ISDA 497.724380567408
Actual / ISMA 500
Actual / 365L 497.267759562841
Actual / AFB 497.267759562841
BUS / 252 NONEOM 488.088481701516
In this example, we show the calculation of the accrued interest factor for each of the daycount 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 < '20410101'
)
SELECT Description,
[20031101],
[20031231],
[20040401]
FROM (
SELECT n.Description
,n.basis
,m.sd
,CAST(wct.AIFACTOR_RPI(
n.basis Basis
,.10 Rate
,'20031101' Previous Coupon Date
,m.sd Settlement Date
,'20040501' Next Coupon Date
,2 Frequency
,'20091101' 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 NONEOM' UNION ALL
SELECT 11, 'Actual / Actual NONEOM' UNION ALL
SELECT 12, 'Actual / 360 NONEOM' UNION ALL
SELECT 13, 'Actual / 365 NONEOM' UNION ALL
SELECT 14, '30E / 360 NONEOM' UNION ALL
SELECT 15, '30 / 360 ISDA NONEOM' UNION ALL
SELECT 16, 'NL / ACT NONEOM' UNION ALL
SELECT 17, 'NL / 365 NONEOM' UNION ALL
SELECT 18, 'NL / 360 NONEOM' UNION ALL
SELECT 19, 'Actual / 364 NONEOM' 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 NONEOM'
) n(basis, Description)
CROSS APPLY (
SELECT '20031101' UNION ALL
SELECT '20031231' UNION ALL
SELECT '20040401'
) m(sd)
) d
PIVOT (min(INTEREST) FOR sd in (
[20031101],
[20031231],
[20040401])
) as P
ORDER by basis
This produces the following result.
Description 20031101 20031231 20040401
   
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 NONEOM 0.00 166.6667 416.6667
Actual / Actual NONEOM 0.00 164.8352 417.5824
Actual / 360 NONEOM 0.00 166.6667 422.2222
Actual / 365 NONEOM 0.00 164.3836 416.4384
30E / 360 NONEOM 0.00 163.8889 416.6667
30 / 360 ISDA NONEOM 0.00 163.8889 416.6667
NL / ACT NONEOM 0.00 165.7459 417.1271
NL / 365 NONEOM 0.00 164.3836 413.6986
NL / 360 NONEOM 0.00 166.6667 419.4444
Actual / 364 NONEOM 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 NONEOM 0.00 162.6962 412.6927
See Also