Login     Register

        Contact Us     Search

XLeratorDB/financial Documentation

SQL Server accrued interest factor function


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
0.020548
0.020833
0.020742
2012-04-15
0.020833
0.020879
0.021111
0.020822
0.020833
0.020833
0.020856
0.020685
0.020972
0.020879
2012-04-16
0.020972
0.021016
0.021250
0.020959
0.020972
0.020972
0.020994
0.020822
0.021111
0.021016
2012-04-17
0.021111
0.021154
0.021389
0.021096
0.021111
0.021111
0.021133
0.020959
0.021250
0.021154
2012-04-18
0.021250
0.021291
0.021528
0.021233
0.021250
0.021250
0.021271
0.021096
0.021389
0.021291
2012-04-19
0.021389
0.021429
0.021667
0.021370
0.021389
0.021389
0.021409
0.021233
0.021528
0.021429
2012-04-20
0.021528
0.021566
0.021806
0.021507
0.021528
0.021528
0.021547
0.021370
0.021667
0.021566
2012-04-21
0.021667
0.021703
0.021944
0.021644
0.021667
0.021667
0.021685
0.021507
0.021806
0.021703
2012-04-22
0.021806
0.021841
0.022083
0.021781
0.021806
0.021806
0.021823
0.021644
0.021944
0.021841
2012-04-23
0.021944
0.021978
0.022222
0.021918
0.021944
0.021944
0.021961
0.021781
0.022083
0.021978
2012-04-24
0.022083
0.022115
0.022361
0.022055
0.022083
0.022083
0.022099
0.021918
0.022222
0.022115
2012-04-25
0.022222
0.022253
0.022500
0.022192
0.022222
0.022222
0.022238
0.022055
0.022361
0.022253
2012-04-26
0.022361
0.022390
0.022639
0.022329
0.022361
0.022361
0.022376
0.022192
0.022500
0.022390
2012-04-27
0.022500
0.022527
0.022778
0.022466
0.022500
0.022500
0.022514
0.022329
0.022639
0.022527
2012-04-28
0.022639
0.022665
0.022917
0.022603
0.022639
0.022639
0.022652
0.022466
0.022778
0.022665
2012-04-29
0.022778
0.022802
0.023056
0.022740
0.022778
0.022778
0.022790
0.022603
0.022917
0.022802
2012-04-30
0.022917
0.022940
0.023194
0.022877
0.022917
0.022917
0.022928
0.022740
0.023056
0.022940
2012-05-01
0.023056
0.023077
0.023333
0.023014
0.023056
0.023056
0.023066
0.022877
0.023194
0.023077
2012-05-02
0.023194
0.023214
0.023472
0.023151
0.023194
0.023194
0.023204
0.023014
0.023333
0.023214
2012-05-03
0.023333
0.023352
0.023611
0.023288
0.023333
0.023333
0.023343
0.023151
0.023472
0.023352
2012-05-04
0.023472
0.023489
0.023750
0.023425
0.023472
0.023472
0.023481
0.023288
0.023611
0.023489
2012-05-05
0.023611
0.023626
0.023889
0.023562
0.023611
0.023611
0.023619
0.023425
0.023750
0.023626
2012-05-06
0.023750
0.023764
0.024028
0.023699
0.023750
0.023750
0.023757
0.023562
0.023889
0.023764
2012-05-07
0.023889
0.023901
0.024167
0.023836
0.023889
0.023889
0.023895
0.023699
0.024028
0.023901
2012-05-08
0.024028
0.024038
0.024306
0.023973
0.024028
0.024028
0.024033
0.023836
0.024167
0.024038
2012-05-09
0.024167
0.024176
0.024444
0.024110
0.024167
0.024167
0.024171
0.023973
0.024306
0.024176
2012-05-10
0.024306
0.024313
0.024583
0.024247
0.024306
0.024306
0.024309
0.024110
0.024444
0.024313
2012-05-11
0.024444
0.024451
0.024722
0.024384
0.024444
0.024444
0.024448
0.024247
0.024583
0.024451
2012-05-12
0.024583
0.024588
0.024861
0.024521
0.024583
0.024583
0.024586
0.024384
0.024722
0.024588
2012-05-13
0.024722
0.024725
0.025000
0.024658
0.024722
0.024722
0.024724
0.024521
0.024861
0.024725
2012-05-14
0.024861
0.024863
0.025139
0.024795
0.024861
0.024861
0.024862
0.024658
0.025000
0.024863
2012-05-15
0.000000
0.000000
0.000000
0.000000
0.000000
0.000000
0.000000
0.000000
0.000000
0.000000

If the maturity date, last interest date, or first interest date is the last day of the month, then the calculation of the previous coupon date (which is used in the calculation of the accrued interest), can be affected by the choice of @Basis. In this example, we will look at the difference between the Actual/Actual (1) and the Actual/Actual NON_EOM (11) values for @Basis.
SELECT LEFT(convert(varchar, l.seriesvalue, 106), 11) as Settlement
,wct.AIFACTOR(
 1                --Basis
,.05              --rate
,'2012-06-30'     --Maturity Date
,l.SeriesValue    --Settlement Date
,2                --Frequency
,NULL             --First Interest Date
,NULL             --Last Interest Date
,NULL             --Issue Date
,NULL             --Holidays
) as [Actual/Actual]
,wct.AIFACTOR(
 11               --Basis
,.05              --rate
,'2012-06-30'     --Maturity Date
,l.SeriesValue    --Settlement Date
,2                --Frequency
,NULL             --First Interest Date
,NULL             --Last Interest Date
,NULL             --Issue Date
,NULL             --Holidays
) as [Actual/Actual NON EOM]
FROM wctMath.wct.SeriesDate('2011-11-30','2012-01-31',NULL,NULL,NULL) l
This produces the following result.
Settlement           Actual/Actual Actual/Actual NON EOM
----------- ---------------------- ----------------------
30 Nov 2011     0.0207880434782609     0.0209016393442623
01 Dec 2011     0.0209239130434783     0.0210382513661202
02 Dec 2011     0.0210597826086957     0.0211748633879781
03 Dec 2011      0.021195652173913     0.0213114754098361
04 Dec 2011     0.0213315217391304      0.021448087431694
05 Dec 2011     0.0214673913043478     0.0215846994535519
06 Dec 2011     0.0216032608695652     0.0217213114754098
07 Dec 2011     0.0217391304347826     0.0218579234972678
08 Dec 2011               0.021875     0.0219945355191257
09 Dec 2011     0.0220108695652174     0.0221311475409836
10 Dec 2011     0.0221467391304348     0.0222677595628415
11 Dec 2011     0.0222826086956522     0.0224043715846995
12 Dec 2011     0.0224184782608696     0.0225409836065574
13 Dec 2011      0.022554347826087     0.0226775956284153
14 Dec 2011     0.0226902173913043     0.0228142076502732
15 Dec 2011     0.0228260869565217     0.0229508196721312
16 Dec 2011     0.0229619565217391     0.0230874316939891
17 Dec 2011     0.0230978260869565      0.023224043715847
18 Dec 2011     0.0232336956521739     0.0233606557377049
19 Dec 2011     0.0233695652173913     0.0234972677595628
20 Dec 2011     0.0235054347826087     0.0236338797814208
21 Dec 2011     0.0236413043478261     0.0237704918032787
22 Dec 2011     0.0237771739130435     0.0239071038251366
23 Dec 2011     0.0239130434782609     0.0240437158469945
24 Dec 2011     0.0240489130434783     0.0241803278688525
25 Dec 2011     0.0241847826086957     0.0243169398907104
26 Dec 2011      0.024320652173913     0.0244535519125683
27 Dec 2011     0.0244565217391304     0.0245901639344262
28 Dec 2011     0.0245923913043478     0.0247267759562842
29 Dec 2011     0.0247282608695652     0.0248633879781421
30 Dec 2011     0.0248641304347826                      0
31 Dec 2011                      0   0.000136612021857923
01 Jan 2012   0.000137362637362637   0.000273224043715847
02 Jan 2012   0.000274725274725275   0.000409836065573771
03 Jan 2012   0.000412087912087912   0.000546448087431694
04 Jan 2012   0.000549450549450549   0.000683060109289617
05 Jan 2012   0.000686813186813187   0.000819672131147541
06 Jan 2012   0.000824175824175824   0.000956284153005465
07 Jan 2012   0.000961538461538462    0.00109289617486339
08 Jan 2012     0.0010989010989011    0.00122950819672131
09 Jan 2012    0.00123626373626374    0.00136612021857923
10 Jan 2012    0.00137362637362637    0.00150273224043716
11 Jan 2012    0.00151098901098901    0.00163934426229508
12 Jan 2012    0.00164835164835165    0.00177595628415301
13 Jan 2012    0.00178571428571429    0.00191256830601093
14 Jan 2012    0.00192307692307692    0.00204918032786885
15 Jan 2012    0.00206043956043956    0.00218579234972678
16 Jan 2012     0.0021978021978022     0.0023224043715847
17 Jan 2012    0.00233516483516484    0.00245901639344262
18 Jan 2012    0.00247252747252747    0.00259562841530055
19 Jan 2012    0.00260989010989011    0.00273224043715847
20 Jan 2012    0.00274725274725275    0.00286885245901639
21 Jan 2012    0.00288461538461538    0.00300546448087432
22 Jan 2012    0.00302197802197802    0.00314207650273224
23 Jan 2012    0.00315934065934066    0.00327868852459016
24 Jan 2012     0.0032967032967033    0.00341530054644809
25 Jan 2012    0.00343406593406593    0.00355191256830601
26 Jan 2012    0.00357142857142857    0.00368852459016393
27 Jan 2012    0.00370879120879121    0.00382513661202186
28 Jan 2012    0.00384615384615385    0.00396174863387978
29 Jan 2012    0.00398351648351648    0.00409836065573771
30 Jan 2012    0.00412087912087912    0.00423497267759563
31 Jan 2012    0.00425824175824176    0.00437158469945355
The BUS/252 day-count convention only accrues interest on business days, so there is no change in the AIFACTOR on weekends or holidays. In addition, the calculation of the coupon interest amount for the coupon period is different than for other day-count conventions and is consistent with the ANDIMA specification.
SELECT LEFT(convert(varchar,l.seriesvalue,106),11) as Settlement
,wct.AIFACTOR(
 20               --Basis
,.05              --rate
,'2012-06-30'     --Maturity Date
,l.SeriesValue    --Settlement Date
,2                --Frequency
,NULL             --First Interest Date
,NULL             --Last Interest Date
,NULL             --Issue Date
,wct.NBD(holiday) --Holidays
) as AIFACTOR
FROM wctMath.wct.SeriesDate('2011-11-30','2012-01-31',NULL,NULL,NULL) l,
HOLIDAYS
WHERE COUNTRY = 'BR'
AND HOLIDAY < '2040-12-31'
GROUP BY l.seriesvalue
This produces the following result.
Settlement                AIFACTOR
----------- ----------------------
30 Nov 2011     0.0204171893116204
01 Dec 2011     0.0206116387336358
02 Dec 2011     0.0208060881556513
03 Dec 2011     0.0208060881556513
04 Dec 2011     0.0208060881556513
05 Dec 2011     0.0210005375776667
06 Dec 2011     0.0211949869996821
07 Dec 2011     0.0213894364216976
08 Dec 2011      0.021583885843713
09 Dec 2011     0.0217783352657284
10 Dec 2011     0.0217783352657284
11 Dec 2011     0.0217783352657284
12 Dec 2011     0.0219727846877439
13 Dec 2011     0.0221672341097593
14 Dec 2011     0.0223616835317747
15 Dec 2011     0.0225561329537902
16 Dec 2011     0.0227505823758056
17 Dec 2011     0.0227505823758056
18 Dec 2011     0.0227505823758056
19 Dec 2011      0.022945031797821
20 Dec 2011     0.0231394812198365
21 Dec 2011     0.0233339306418519
22 Dec 2011     0.0235283800638673
23 Dec 2011     0.0237228294858828
24 Dec 2011     0.0237228294858828
25 Dec 2011     0.0237228294858828
26 Dec 2011    0.0239172789078982
27 Dec 2011     0.0241117283299136
28 Dec 2011     0.0243061777519291
29 Dec 2011     0.0245006271739445
30 Dec 2011     0.0124455346336782
31 Dec 2011                      0
01 Jan 2012                      0
02 Jan 2012                      0
03 Jan 2012   0.000199153843515806
04 Jan 2012   0.000398307687031612
05 Jan 2012   0.000597461530547418
06 Jan 2012   0.000796615374063224
07 Jan 2012   0.000796615374063224
08 Jan 2012   0.000796615374063224
09 Jan 2012    0.00099576921757903
10 Jan 2012    0.00119492306109484
11 Jan 2012    0.00139407690461064
12 Jan 2012    0.00159323074812645
13 Jan 2012    0.00179238459164225
14 Jan 2012    0.00179238459164225
15 Jan 2012    0.00179238459164225
16 Jan 2012    0.00199153843515806
17 Jan 2012    0.00219069227867387
18 Jan 2012    0.00238984612218967
19 Jan 2012    0.00258899996570548
20 Jan 2012    0.00278815380922128
21 Jan 2012    0.00278815380922128
22 Jan 2012    0.00278815380922128
23 Jan 2012    0.00298730765273709
24 Jan 2012    0.00318646149625289
25 Jan 2012     0.0033856153397687
26 Jan 2012    0.00358476918328451
27 Jan 2012    0.00378392302680031
28 Jan 2012    0.00378392302680031
29 Jan 2012    0.00378392302680031
30 Jan 2012    0.00398307687031612
31 Jan 2012    0.00418223071383192
Here’s an example of the accrued interest factor calculation using the Actual/Actual ISDA day-count convention.
SELECT LEFT(convert(varchar,l.seriesvalue,106),11) as Settlement
,wct.AIFACTOR(
 21               --Basis
,.05              --rate
,'2012-05-31'     --Maturity Date
,l.SeriesValue    --Settlement Date
,2                --Frequency
,NULL             --First Interest Date
,NULL             --Last Interest Date
,NULL             --Issue Date
,NULL             --Holidays
) as AIFACTOR
FROM wctMath.wct.SeriesDate('2011-11-30','2012-01-31',NULL,NULL,NULL) l
This produces the following result.
Settlement                AIFACTOR
----------- ----------------------
30 Nov 2011                      0
01 Dec 2011   0.000136986301369863
02 Dec 2011   0.000273972602739726
03 Dec 2011   0.000410958904109589
04 Dec 2011   0.000547945205479452
05 Dec 2011   0.000684931506849315
06 Dec 2011   0.000821917808219178
07 Dec 2011   0.000958904109589041
08 Dec 2011     0.0010958904109589
09 Dec 2011    0.00123287671232877
10 Dec 2011    0.00136986301369863
11 Dec 2011    0.00150684931506849
12 Dec 2011    0.00164383561643836
13 Dec 2011    0.00178082191780822
14 Dec 2011    0.00191780821917808
15 Dec 2011    0.00205479452054795
16 Dec 2011    0.00219178082191781
17 Dec 2011    0.00232876712328767
18 Dec 2011    0.00246575342465753
19 Dec 2011     0.0026027397260274
20 Dec 2011    0.00273972602739726
21 Dec 2011    0.00287671232876712
22 Dec 2011    0.00301369863013699
23 Dec 2011    0.00315068493150685
24 Dec 2011    0.00328767123287671
25 Dec 2011    0.00342465753424658
26 Dec 2011    0.00356164383561644
27 Dec 2011     0.0036986301369863
28 Dec 2011    0.00383561643835616
29 Dec 2011    0.00397260273972603
30 Dec 2011    0.00410958904109589
31 Dec 2011    0.00424657534246575
01 Jan 2012    0.00438356164383562
02 Jan 2012    0.00452017366569354
03 Jan 2012    0.00465678568755146
04 Jan 2012    0.00479339770940939
05 Jan 2012    0.00493000973126731
06 Jan 2012    0.00506662175312523
07 Jan 2012    0.00520323377498316
08 Jan 2012    0.00533984579684108
09 Jan 2012    0.00547645781869901
10 Jan 2012    0.00561306984055693
11 Jan 2012    0.00574968186241485
12 Jan 2012    0.00588629388427278
13 Jan 2012     0.0060229059061307
14 Jan 2012    0.00615951792798862
15 Jan 2012    0.00629612994984655
16 Jan 2012    0.00643274197170447
17 Jan 2012    0.00656935399356239
18 Jan 2012    0.00670596601542032
19 Jan 2012    0.00684257803727824
20 Jan 2012    0.00697919005913616
21 Jan 2012    0.00711580208099409
22 Jan 2012    0.00725241410285201
23 Jan 2012    0.00738902612470993
24 Jan 2012    0.00752563814656786
25 Jan 2012    0.00766225016842578
26 Jan 2012     0.0077988621902837
27 Jan 2012    0.00793547421214163
28 Jan 2012    0.00807208623399955
29 Jan 2012    0.00820869825585747
30 Jan 2012     0.0083453102777154
31 Jan 2012    0.00848192229957332
Notice how the AIFACTOR increases smoothly by 0.00013698630 for each day in 2011 and then AIFACTOR changes by 0.00013661202 for each day in 2012.
Here’s an example of the accrued interest factor calculation using the Actual/365L day-count convention.
SELECT LEFT(convert(varchar,l.seriesvalue,106),11) as Settlement
,wct.AIFACTOR(
 23               --Basis
,.05              --rate
,'2012-05-31'     --Maturity Date
,l.SeriesValue    --Settlement Date
,2                --Frequency
,NULL             --First Interest Date
,NULL             --Last Interest Date
,NULL             --Issue Date
,NULL             --Holidays
) as AIFACTOR
FROM wctMath.wct.SeriesDate('2011-11-30','2012-01-31',NULL,NULL,NULL) l
This produces the following result.
Notice how the AIFACTOR increases smoothly by 0.00013661202 for each day in the coupon period even as the year changes from 2011 to 2012.
Here’s an example of the accrued interest factor calculation using the Actual/AFB day-count convention.
SELECT LEFT(convert(varchar,l.seriesvalue,106),11) as Settlement
,wct.AIFACTOR(
 24               --Basis
,.05              --rate
,'2012-05-31'     --Maturity Date
,l.SeriesValue    --Settlement Date
,2                --Frequency
,NULL             --First Interest Date
,NULL             --Last Interest Date
,NULL             --Issue Date
,NULL             --Holidays
) as AIFACTOR
FROM wctMath.wct.SeriesDate('2011-11-30','2012-01-31',NULL,NULL,NULL) l
This produces the following results.
Settlement                AIFACTOR
----------- ----------------------
30 Nov 2011                      0
01 Dec 2011   0.000136612021857923
02 Dec 2011   0.000273224043715847
03 Dec 2011   0.000409836065573771
04 Dec 2011   0.000546448087431694
05 Dec 2011   0.000683060109289617
06 Dec 2011   0.000819672131147541
07 Dec 2011   0.000956284153005465
08 Dec 2011    0.00109289617486339
09 Dec 2011    0.00122950819672131
10 Dec 2011    0.00136612021857923
11 Dec 2011    0.00150273224043716
12 Dec 2011    0.00163934426229508
13 Dec 2011    0.00177595628415301
14 Dec 2011    0.00191256830601093
15 Dec 2011    0.00204918032786885
16 Dec 2011    0.00218579234972678
17 Dec 2011     0.0023224043715847
18 Dec 2011    0.00245901639344262
19 Dec 2011    0.00259562841530055
20 Dec 2011    0.00273224043715847
21 Dec 2011    0.00286885245901639
22 Dec 2011    0.00300546448087432
23 Dec 2011    0.00314207650273224
24 Dec 2011    0.00327868852459016
25 Dec 2011    0.00341530054644809
26 Dec 2011    0.00355191256830601
27 Dec 2011    0.00368852459016393
28 Dec 2011    0.00382513661202186
29 Dec 2011    0.00396174863387978
30 Dec 2011    0.00409836065573771
31 Dec 2011    0.00423497267759563
01 Jan 2012    0.00437158469945355
02 Jan 2012    0.00450819672131148
03 Jan 2012     0.0046448087431694
04 Jan 2012    0.00478142076502732
05 Jan 2012    0.00491803278688525
06 Jan 2012    0.00505464480874317
07 Jan 2012    0.00519125683060109
08 Jan 2012    0.00532786885245902
09 Jan 2012    0.00546448087431694
10 Jan 2012    0.00560109289617486
11 Jan 2012    0.00573770491803279
12 Jan 2012    0.00587431693989071
13 Jan 2012    0.00601092896174863
14 Jan 2012    0.00614754098360656
15 Jan 2012    0.00628415300546448
16 Jan 2012     0.0064207650273224
17 Jan 2012    0.00655737704918033
18 Jan 2012    0.00669398907103825
19 Jan 2012    0.00683060109289617
20 Jan 2012     0.0069672131147541
21 Jan 2012    0.00710382513661202
22 Jan 2012    0.00724043715846995
23 Jan 2012    0.00737704918032787
24 Jan 2012    0.00751366120218579
25 Jan 2012    0.00765027322404372
26 Jan 2012    0.00778688524590164
27 Jan 2012    0.00792349726775956
28 Jan 2012    0.00806010928961749
29 Jan 2012    0.00819672131147541
30 Jan 2012    0.00833333333333333
31 Jan 2012    0.00846994535519126
This produces the same result as Actual/Actual Long.
Finally, let’s look at a comparison of all the day-count conventions for a single settlement date.
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
,dbo.AIFACTOR(
 n.basis          --Basis
,.10              --Rate
,'2012-05-31'     --Maturity Date
,'2012-03-31'     --Settlement Date
,2                      --Frequency
,NULL             --First Interest Date
,NULL             --Last Interest Date
,NULL             --Issue Date
,@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                          333.333333333333
Actual / Actual               333.333333333333
Actual / 360                  338.888888888889
Actual / 365                  334.246575342466
30E / 360                     333.333333333333
30 / 360 ISDA                 333.333333333333
NL / ACT                      332.417582417582
NL / 365                      331.506849315069
NL / 360                      336.111111111111
Actual / 364                  335.164835164835
BOND NON-EOM                  333.333333333333
Actual / Actual NON-EOM       333.333333333333
Actual / 360 NON-EOM          338.888888888889
Actual / 365 NON-EOM          334.246575342466
30E / 360 NON-EOM             333.333333333333
30 / 360 ISDA NON-EOM         333.333333333333
NL / ACT NON-EOM              332.417582417582
NL / 365 NON-EOM              331.506849315069
NL / 360 NON-EOM              336.111111111111
Actual / 364 NON-EOM          335.164835164835
BUS / 252                     326.673393264794
Actual / ISDA                 333.572872220975
Actual / ISMA                 333.333333333333
Actual / 365L                 333.333333333333
Actual / AFB                  333.333333333333
BUS / 252 NON-EOM             326.673393264794
See Also

 



Copyright 2008-2024 Westclintech LLC         Privacy Policy        Terms of Service