Login     Register

        Contact Us     Search

XLeratorDB/financial Documentation

SQL Sever accrued interest factor for odd last coupons


AIFACTOR_OLC

Updated: 11 May 2012


Use the scalar-valued function AIFACTOR_OLC to calculate the Accrued Interest Factor for a bond during its odd last coupon period. AIFACTOR_OLC 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 odd last period.
Syntax
SELECT [wctFinancial].[wct].[AIFACTOR_OLC] (
  <@Basis, nvarchar(4000),>
 ,<@Rate, float,>
 ,<@LastCouponDate, datetime,>
 ,<@Settlement, datetime,>
 ,<@MaturityDate, datetime,>
 ,<@Frequency, int,>
 ,<@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.
@LastCouponDate
the start date for the odd last coupon period. @LastCouoponDate 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.
@MaturityDate
the maturity date for the bond. When the settlement date occurs on the maturity date, the factor returned will be for the entire last coupon period. @MaturityDate 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.
 
@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 NBD to create an appropriately formatted string.
Return Type
float
Remarks
·         @Settlement must be less than or equal to @Maturity
·         @Settlement must be greater than or equal to @LastInterestDate
·         If @Settlement = @LastInterestDate then the function returns zero
·         For bonds where the settlement date is before the last interest date, use AIFACTOR or AIFACTOR_RPI.
·         For bonds where the settlement date is in an odd first coupon period, use AIFACTOR or AIFACOR_OFC.
·         For more information on accrual calculations, see AIFACTOR.
Examples
In this example, we show the calculation of the accrued interest factor for the entire last 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 wctFinancial.wct.NBD(holiday)
      FROM HOLIDAYS
      WHERE COUNTRY = 'BR'
      AND holiday < '2041-01-01'
      )
 
SELECT n.Description
,wct.AIFACTOR_OLC(
 n.basis          --Basis
,.10              --Rate
,'2011-07-15'     --Last Coupon Date
,'2012-06-30'     --Settlement Date
,'2012-06-30'     --Maturity Date
,2                --Frequency
,@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                          958.333333333333
Actual / Actual               958.791208791209
Actual / 360                               975
Actual / 365                  961.643835616438
30E / 360                     958.333333333333
30 / 360 ISDA                 958.333333333333
NL / ACT                      958.563535911602
NL / 365                      958.904109589041
NL / 360                      972.222222222222
Actual / 364                  964.285714285714
BOND NON-EOM                  958.333333333333
Actual / Actual NON-EOM      958.791208791209
Actual / 360 NON-EOM                       975
Actual / 365 NON-EOM          961.643835616438
30E / 360 NON-EOM             958.333333333333
30 / 360 ISDA NON-EOM         958.333333333333
NL / ACT NON-EOM              958.563535911602
NL / 365 NON-EOM              958.904109589041
NL / 360 NON-EOM              972.222222222222
Actual / 364 NON-EOM          964.285714285714
BUS / 252                     936.814989072265
Actual / ISDA                 960.288943783217
Actual / ISMA                 958.791208791209
Actual / 365L                 959.016393442623
Actual / AFB                   960.39374204656
BUS / 252 NON-EOM             936.814989072265
In this example, we show the calculation of the accrued interest factor for each of the day-count conventions for the last interest date, the last day of the year within the odd coupon period, and for a date in the following year in the odd coupon period.
DECLARE @h as varchar(max)
SET @h = (
      SELECT wctFinancial.wct.NBD(holiday)
      FROM HOLIDAYS
      WHERE COUNTRY = 'BR'
      AND holiday < '2041-01-01'
      )
SELECT Description,
      [2011-07-15],
      [2011-12-31],
      [2012-04-01]
FROM (
      SELECT n.Description
      ,n.basis
      ,m.sd
      ,CAST(wct.AIFACTOR_OLC(
       n.basis          --Basis
      ,.10              --Rate
      ,'2011-07-15'     --Issue Date
      ,m.sd             --Settlement Date
      ,'2012-06-30'     --First Interest Date
      ,2                --Frequency
      ,@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 '2011-07-15' UNION ALL
            SELECT '2011-12-31' UNION ALL
            SELECT '2012-04-01'
            ) m(sd)
      ) d
PIVOT (min(INTEREST) FOR sd in (
      [2011-07-15],
      [2011-12-31],
      [2012-04-01])
      ) as P
ORDER by basis
This produces the following result.
Description                        2011-07-15            2011-12-31            2012-04-01
----------------------- --------------------- --------------------- ---------------------
BOND                                     0.00              461.1111              711.1111
Actual / Actual                          0.00              459.2391              711.5385
Actual / 360                             0.00              469.4444                725.00
Actual / 365                             0.00              463.0137              715.0685
30E / 360                                0.00              458.3333              711.1111
30 / 360 ISDA                            0.00              458.3333              711.1111
NL / ACT                                 0.00              459.2391              709.9448
NL / 365                                 0.00              463.0137              712.3288
NL / 360                                 0.00              469.4444              722.2222
Actual / 364                             0.00              464.2857               717.033
BOND NON-EOM                             0.00              461.1111              711.1111
Actual / Actual NON-EOM                  0.00              459.2391              711.5385
Actual / 360 NON-EOM                     0.00              469.4444                725.00
Actual / 365 NON-EOM                     0.00              463.0137              715.0685
30E / 360 NON-EOM                        0.00              458.3333              711.1111
30 / 360 ISDA NON-EOM                    0.00              458.3333              711.1111
NL / ACT NON-EOM                         0.00              459.2391              709.9448
NL / 365 NON-EOM                         0.00              463.0137              712.3288
NL / 360 NON-EOM                         0.00              469.4444              722.2222
Actual / 364 NON-EOM                     0.00              464.2857               717.033
BUS / 252                                0.00              449.3513              692.7707
Actual / ISDA                            0.00              463.0137              714.3873
Actual / ISMA                            0.00              459.2391              711.5385
Actual / 365L                            0.00              461.7486              713.1148
Actual / AFB                             0.00              463.0137              714.4921
BUS / 252 NON-EOM                        0.00              449.3513              692.7707
 

 

See Also

 



Copyright 2008-2017 Westclintech LLC         Privacy Policy        Terms of Service