AIFACTOR
Updated: 11 May 2012
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.
|
@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.
@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 NDB 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.
AIFACTOR
----------------------
0.0125
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
wctMath.wct.SeriesDate('2012-02-15','2012-05-15',NULL,NULL,NULL) k
This produces the following result.
SeriesValue AIFACTOR
----------------------- ----------------------
2012-02-15 00:00:00.000 0.0125
2012-02-16 00:00:00.000 0.0126388888888889
2012-02-17 00:00:00.000 0.0127777777777778
2012-02-18 00:00:00.000 0.0129166666666667
2012-02-19 00:00:00.000 0.0130555555555556
2012-02-20 00:00:00.000 0.0131944444444444
2012-02-21 00:00:00.000 0.0133333333333333
2012-02-22 00:00:00.000 0.0134722222222222
2012-02-23 00:00:00.000 0.0136111111111111
2012-02-24 00:00:00.000 0.01375
2012-02-25 00:00:00.000 0.0138888888888889
2012-02-26 00:00:00.000 0.0140277777777778
2012-02-27 00:00:00.000 0.0141666666666667
2012-02-28 00:00:00.000 0.0143055555555556
2012-02-29 00:00:00.000 0.0144444444444444
2012-03-01 00:00:00.000 0.0147222222222222
2012-03-02 00:00:00.000 0.0148611111111111
2012-03-03 00:00:00.000 0.015
2012-03-04 00:00:00.000 0.0151388888888889
2012-03-05 00:00:00.000 0.0152777777777778
2012-03-06 00:00:00.000 0.0154166666666667
2012-03-07 00:00:00.000 0.0155555555555556
2012-03-08 00:00:00.000 0.0156944444444444
2012-03-09 00:00:00.000 0.0158333333333333
2012-03-10 00:00:00.000 0.0159722222222222
2012-03-11 00:00:00.000 0.0161111111111111
2012-03-12 00:00:00.000 0.01625
2012-03-13 00:00:00.000 0.0163888888888889
2012-03-14 00:00:00.000 0.0165277777777778
2012-03-15 00:00:00.000 0.0166666666666667
2012-03-16 00:00:00.000 0.0168055555555556
2012-03-17 00:00:00.000 0.0169444444444444
2012-03-18 00:00:00.000 0.0170833333333333
2012-03-19 00:00:00.000 0.0172222222222222
2012-03-20 00:00:00.000 0.0173611111111111
2012-03-21 00:00:00.000 0.0175
2012-03-22 00:00:00.000 0.0176388888888889
2012-03-23 00:00:00.000 0.0177777777777778
2012-03-24 00:00:00.000 0.0179166666666667
2012-03-25 00:00:00.000 0.0180555555555556
2012-03-26 00:00:00.000 0.0181944444444444
2012-03-27 00:00:00.000 0.0183333333333333
2012-03-28 00:00:00.000 0.0184722222222222
2012-03-29 00:00:00.000 0.0186111111111111
2012-03-30 00:00:00.000 0.01875
2012-03-31 00:00:00.000 0.0188888888888889
2012-04-01 00:00:00.000 0.0188888888888889
2012-04-02 00:00:00.000 0.0190277777777778
2012-04-03 00:00:00.000 0.0191666666666667
2012-04-04 00:00:00.000 0.0193055555555556
2012-04-05 00:00:00.000 0.0194444444444444
2012-04-06 00:00:00.000 0.0195833333333333
2012-04-07 00:00:00.000 0.0197222222222222
2012-04-08 00:00:00.000 0.0198611111111111
2012-04-09 00:00:00.000 0.02
2012-04-10 00:00:00.000 0.0201388888888889
2012-04-11 00:00:00.000 0.0202777777777778
2012-04-12 00:00:00.000 0.0204166666666667
2012-04-13 00:00:00.000 0.0205555555555556
2012-04-14 00:00:00.000 0.0206944444444444
2012-04-15 00:00:00.000 0.0208333333333333
2012-04-16 00:00:00.000 0.0209722222222222
2012-04-17 00:00:00.000 0.0211111111111111
2012-04-18 00:00:00.000 0.02125
2012-04-19 00:00:00.000 0.0213888888888889
2012-04-20 00:00:00.000 0.0215277777777778
2012-04-21 00:00:00.000 0.0216666666666667
2012-04-22 00:00:00.000 0.0218055555555556
2012-04-23 00:00:00.000 0.0219444444444444
2012-04-24 00:00:00.000 0.0220833333333333
2012-04-25 00:00:00.000 0.0222222222222222
2012-04-26 00:00:00.000 0.0223611111111111
2012-04-27 00:00:00.000 0.0225
2012-04-28 00:00:00.000 0.0226388888888889
2012-04-29 00:00:00.000 0.0227777777777778
2012-04-30 00:00:00.000 0.0229166666666667
2012-05-01 00:00:00.000 0.0230555555555556
2012-05-02 00:00:00.000 0.0231944444444444
2012-05-03 00:00:00.000 0.0233333333333333
2012-05-04 00:00:00.000 0.0234722222222222
2012-05-05 00:00:00.000 0.0236111111111111
2012-05-06 00:00:00.000 0.02375
2012-05-07 00:00:00.000 0.0238888888888889
2012-05-08 00:00:00.000 0.0240277777777778
2012-05-09 00:00:00.000 0.0241666666666667
2012-05-10 00:00:00.000 0.0243055555555556
2012-05-11 00:00:00.000 0.0244444444444444
2012-05-12 00:00:00.000 0.0245833333333333
2012-05-13 00:00:00.000 0.0247222222222222
2012-05-14 00:00:00.000 0.0248611111111111
2012-05-15 00:00:00.000 0
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. The results have been reformatted to make them easier to read.
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 wctMath.wct.SeriesDate('2012-02-15','2012-05-15',NULL,NULL,NULL) k
CROSS APPLY wctMATH.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, which have been reformatted for ease of reading.
|
SettDate
|
0
|
1
|
2
|
3
|
4
|
5
|
6
|
7
|
8
|
9
|
|
2012-02-15
|
0.012500
|
0.012637
|
0.012778
|
0.012603
|
0.012500
|
0.012500
|
0.012707
|
0.012603
|
0.012778
|
0.012637
|
|
2012-02-16
|
0.012639
|
0.012775
|
0.012917
|
0.012740
|
0.012639
|
0.012639
|
0.012845
|
0.012740
|
0.012917
|
0.012775
|
|
2012-02-17
|
0.012778
|
0.012912
|
0.013056
|
0.012877
|
0.012778
|
0.012778
|
0.012983
|
0.012877
|
0.013056
|
0.012912
|
|
2012-02-18
|
0.012917
|
0.013049
|
0.013194
|
0.013014
|
0.012917
|
0.012917
|
0.013122
|
0.013014
|
0.013194
|
0.013049
|
|
2012-02-19
|
0.013056
|
0.013187
|
0.013333
|
0.013151
|
0.013056
|
0.013056
|
0.013260
|
0.013151
|
0.013333
|
0.013187
|
|
2012-02-20
|
0.013194
|
0.013324
|
0.013472
|
0.013288
|
0.013194
|
0.013194
|
0.013398
|
0.013288
|
0.013472
|
0.013324
|
|
2012-02-21
|
0.013333
|
0.013462
|
0.013611
|
0.013425
|
0.013333
|
0.013333
|
0.013536
|
0.013425
|
0.013611
|
0.013462
|
|
2012-02-22
|
0.013472
|
0.013599
|
0.013750
|
0.013562
|
0.013472
|
0.013472
|
0.013674
|
0.013562
|
0.013750
|
0.013599
|
|
2012-02-23
|
0.013611
|
0.013736
|
0.013889
|
0.013699
|
0.013611
|
0.013611
|
0.013812
|
0.013699
|
0.013889
|
0.013736
|
|
2012-02-24
|
0.013750
|
0.013874
|
0.014028
|
0.013836
|
0.013750
|
0.013750
|
0.013950
|
0.013836
|
0.014028
|
0.013874
|
|
2012-02-25
|
0.013889
|
0.014011
|
0.014167
|
0.013973
|
0.013889
|
0.013889
|
0.014088
|
0.013973
|
0.014167
|
0.014011
|
|
2012-02-26
|
0.014028
|
0.014148
|
0.014306
|
0.014110
|
0.014028
|
0.014028
|
0.014227
|
0.014110
|
0.014306
|
0.014148
|
|
2012-02-27
|
0.014167
|
0.014286
|
0.014444
|
0.014247
|
0.014167
|
0.014167
|
0.014365
|
0.014247
|
0.014444
|
0.014286
|
|
2012-02-28
|
0.014306
|
0.014423
|
0.014583
|
0.014384
|
0.014306
|
0.014306
|
0.014503
|
0.014384
|
0.014583
|
0.014423
|
|
2012-02-29
|
0.014444
|
0.014560
|
0.014722
|
0.014521
|
0.014444
|
0.014583
|
0.014503
|
0.014384
|
0.014583
|
0.014560
|
|
2012-03-01
|
0.014722
|
0.014698
|
0.014861
|
0.014658
|
0.014722
|
0.014722
|
0.014641
|
0.014521
|
0.014722
|
0.014698
|
|
2012-03-02
|
0.014861
|
0.014835
|
0.015000
|
0.014795
|
0.014861
|
0.014861
|
0.014779
|
0.014658
|
0.014861
|
0.014835
|
|
2012-03-03
|
0.015000
|
0.014973
|
0.015139
|
0.014932
|
0.015000
|
0.015000
|
0.014917
|
0.014795
|
0.015000
|
0.014973
|
|
2012-03-04
|
0.015139
|
0.015110
|
0.015278
|
0.015068
|
0.015139
|
0.015139
|
0.015055
|
0.014932
|
0.015139
|
0.015110
|
|
2012-03-05
|
0.015278
|
0.015247
|
0.015417
|
0.015205
|
0.015278
|
0.015278
|
0.015193
|
0.015068
|
0.015278
|
0.015247
|
|
2012-03-06
|
0.015417
|
0.015385
|
0.015556
|
0.015342
|
0.015417
|
0.015417
|
0.015331
|
0.015205
|
0.015417
|
0.015385
|
|
2012-03-07
|
0.015556
|
0.015522
|
0.015694
|
0.015479
|
0.015556
|
0.015556
|
0.015470
|
0.015342
|
0.015556
|
0.015522
|
|
2012-03-08
|
0.015694
|
0.015659
|
0.015833
|
0.015616
|
0.015694
|
0.015694
|
0.015608
|
0.015479
|
0.015694
|
0.015659
|
|
2012-03-09
|
0.015833
|
0.015797
|
0.015972
|
0.015753
|
0.015833
|
0.015833
|
0.015746
|
0.015616
|
0.015833
|
0.015797
|
|
2012-03-10
|
0.015972
|
0.015934
|
0.016111
|
0.015890
|
0.015972
|
0.015972
|
0.015884
|
0.015753
|
0.015972
|
0.015934
|
|
2012-03-11
|
0.016111
|
0.016071
|
0.016250
|
0.016027
|
0.016111
|
0.016111
|
0.016022
|
0.015890
|
0.016111
|
0.016071
|
|
2012-03-12
|
0.016250
|
0.016209
|
0.016389
|
0.016164
|
0.016250
|
0.016250
|
0.016160
|
0.016027
|
0.016250
|
0.016209
|
|
2012-03-13
|
0.016389
|
0.016346
|
0.016528
|
0.016301
|
0.016389
|
0.016389
|
0.016298
|
0.016164
|
0.016389
|
0.016346
|
|
2012-03-14
|
0.016528
|
0.016484
|
0.016667
|
0.016438
|
0.016528
|
0.016528
|
0.016436
|
0.016301
|
0.016528
|
0.016484
|
|
2012-03-15
|
0.016667
|
0.016621
|
0.016806
|
0.016575
|
0.016667
|
0.016667
|
0.016575
|
0.016438
|
0.016667
|
0.016621
|
|
2012-03-16
|
0.016806
|
0.016758
|
0.016944
|
0.016712
|
0.016806
|
0.016806
|
0.016713
|
0.016575
|
0.016806
|
0.016758
|
|
2012-03-17
|
0.016944
|
0.016896
|
0.017083
|
0.016849
|
0.016944
|
0.016944
|
0.016851
|
0.016712
|
0.016944
|
0.016896
|
|
2012-03-18
|
0.017083
|
0.017033
|
0.017222
|
0.016986
|
0.017083
|
0.017083
|
0.016989
|
0.016849
|
0.017083
|
0.017033
|
|
2012-03-19
|
0.017222
|
0.017170
|
0.017361
|
0.017123
|
0.017222
|
0.017222
|
0.017127
|
0.016986
|
0.017222
|
0.017170
|
|
2012-03-20
|
0.017361
|
0.017308
|
0.017500
|
0.017260
|
0.017361
|
0.017361
|
0.017265
|
0.017123
|
0.017361
|
0.017308
|
|
2012-03-21
|
0.017500
|
0.017445
|
0.017639
|
0.017397
|
0.017500
|
0.017500
|
0.017403
|
0.017260
|
0.017500
|
0.017445
|
|
2012-03-22
|
0.017639
|
0.017582
|
0.017778
|
0.017534
|
0.017639
|
0.017639
|
0.017541
|
0.017397
|
0.017639
|
0.017582
|
|
2012-03-23
|
0.017778
|
0.017720
|
0.017917
|
0.017671
|
0.017778
|
0.017778
|
0.017680
|
0.017534
|
0.017778
|
0.017720
|
|
2012-03-24
|
0.017917
|
0.017857
|
0.018056
|
0.017808
|
0.017917
|
0.017917
|
0.017818
|
0.017671
|
0.017917
|
0.017857
|
|
2012-03-25
|
0.018056
|
0.017995
|
0.018194
|
0.017945
|
0.018056
|
0.018056
|
0.017956
|
0.017808
|
0.018056
|
0.017995
|
|
2012-03-26
|
0.018194
|
0.018132
|
0.018333
|
0.018082
|
0.018194
|
0.018194
|
0.018094
|
0.017945
|
0.018194
|
0.018132
|
|
2012-03-27
|
0.018333
|
0.018269
|
0.018472
|
0.018219
|
0.018333
|
0.018333
|
0.018232
|
0.018082
|
0.018333
|
0.018269
|
|
2012-03-28
|
0.018472
|
0.018407
|
0.018611
|
0.018356
|
0.018472
|
0.018472
|
0.018370
|
0.018219
|
0.018472
|
0.018407
|
|
2012-03-29
|
0.018611
|
0.018544
|
0.018750
|
0.018493
|
0.018611
|
0.018611
|
0.018508
|
0.018356
|
0.018611
|
0.018544
|
|
2012-03-30
|
0.018750
|
0.018681
|
0.018889
|
0.018630
|
0.018750
|
0.018750
|
0.018646
|
0.018493
|
0.018750
|
0.018681
|
|
2012-03-31
|
0.018889
|
0.018819
|
0.019028
|
0.018767
|
0.018750
|
0.018750
|
0.018785
|
0.018630
|
0.018889
|
0.018819
|
|
2012-04-01
|
0.018889
|
0.018956
|
0.019167
|
0.018904
|
0.018889
|
0.018889
|
0.018923
|
0.018767
|
0.019028
|
0.018956
|
|
2012-04-02
|
0.019028
|
0.019093
|
0.019306
|
0.019041
|
0.019028
|
0.019028
|
0.019061
|
0.018904
|
0.019167
|
0.019093
|
|
2012-04-03
|
0.019167
|
0.019231
|
0.019444
|
0.019178
|
0.019167
|
0.019167
|
0.019199
|
0.019041
|
0.019306
|
0.019231
|
|
2012-04-04
|
0.019306
|
0.019368
|
0.019583
|
0.019315
|
0.019306
|
0.019306
|
0.019337
|
0.019178
|
0.019444
|
0.019368
|
|
2012-04-05
|
0.019444
|
0.019505
|
0.019722
|
0.019452
|
0.019444
|
0.019444
|
0.019475
|
0.019315
|
0.019583
|
0.019505
|
|
2012-04-06
|
0.019583
|
0.019643
|
0.019861
|
0.019589
|
0.019583
|
0.019583
|
0.019613
|
0.019452
|
0.019722
|
0.019643
|
|
2012-04-07
|
0.019722
|
0.019780
|
0.020000
|
0.019726
|
0.019722
|
0.019722
|
0.019751
|
0.019589
|
0.019861
|
0.019780
|
|
2012-04-08
|
0.019861
|
0.019918
|
0.020139
|
0.019863
|
0.019861
|
0.019861
|
0.019890
|
0.019726
|
0.020000
|
0.019918
|
|
2012-04-09
|
0.020000
|
0.020055
|
0.020278
|
0.020000
|
0.020000
|
0.020000
|
0.020028
|
0.019863
|
0.020139
|
0.020055
|
|
2012-04-10
|
0.020139
|
0.020192
|
0.020417
|
0.020137
|
0.020139
|
0.020139
|
0.020166
|
0.020000
|
0.020278
|
0.020192
|
|
2012-04-11
|
0.020278
|
0.020330
|
0.020556
|
0.020274
|
0.020278
|
0.020278
|
0.020304
|
0.020137
|
0.020417
|
0.020330
|
|
2012-04-12
|
0.020417
|
0.020467
|
0.020694
|
0.020411
|
0.020417
|
0.020417
|
0.020442
|
0.020274
|
0.020556
|
0.020467
|
|
2012-04-13
|
0.020556
|
0.020604
|
0.020833
|
0.020548
|
0.020556
|
0.020556
|
0.020580
|
0.020411
|
0.020694
|
0.020604
|
|
2012-04-14
|
0.020694
|
0.020742
|
0.020972
|
0.020685
|
0.020694
|
0.020694
|
0.020718
|
|