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