Login     Register

        Contact Us     Search

XLeratorDB/financial Documentation

SQL Server accrued interest factor for regular coupons


AIFACTOR_RPI

Updated: 11 May 2012


Use the scalar-valued 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 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.
@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 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.
 
@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 (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
·         @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 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 wct.NBD(holiday)
      FROM HOLIDAYS
      WHERE COUNTRY = 'BR'
      AND holiday < '2041-01-01'
      )
 
SELECT n.Description
,wct.AIFACTOR_RPI(
 n.basis          --Basis
,.10              --Rate
,'2003-11-01'     --Previous Coupon Date
,'2004-05-01'     --Settlement Date
,'2004-05-01'     --Next Coupon Date
,2                --Frequency
,'2009-11-01'     --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 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                                       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 NON-EOM                               500
Actual / Actual NON-EOM                    500
Actual / 360 NON-EOM          505.555555555556
Actual / 365 NON-EOM          498.630136986301
30E / 360 NON-EOM                          500
30 / 360 ISDA NON-EOM                      500
NL / ACT NON-EOM                           500
NL / 365 NON-EOM              495.890410958904
NL / 360 NON-EOM              502.777777777778
Actual / 364 NON-EOM                       500
BUS / 252                     488.088481701516
Actual / ISDA                 497.724380567408
Actual / ISMA                              500
Actual / 365L                 497.267759562841
Actual / AFB                  497.267759562841
BUS / 252 NON-EOM             488.088481701516
In this example, we show the calculation of the accrued interest factor for each of the day-count 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 < '2041-01-01'
      )
SELECT Description,
      [2003-11-01],
      [2003-12-31],
      [2004-04-01]
FROM (
      SELECT n.Description
      ,n.basis
      ,m.sd
      ,CAST(wct.AIFACTOR_RPI(
       n.basis          --Basis
      ,.10              --Rate
      ,'2003-11-01'     --Previous Coupon Date
      ,m.sd             --Settlement Date
      ,'2004-05-01'     --Next Coupon Date
      ,2                --Frequency
      ,'2009-11-01'     --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 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 '2003-11-01' UNION ALL
            SELECT '2003-12-31' UNION ALL
            SELECT '2004-04-01'
            ) m(sd)
      ) d
PIVOT (min(INTEREST) FOR sd in (
      [2003-11-01],
      [2003-12-31],
      [2004-04-01])
      ) as P
ORDER by basis
This produces the following result.

 

Description                        2003-11-01            2003-12-31            2004-04-01
----------------------- --------------------- --------------------- ---------------------
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 NON-EOM                             0.00              166.6667              416.6667
Actual / Actual NON-EOM                  0.00              164.8352              417.5824
Actual / 360 NON-EOM                     0.00              166.6667              422.2222
Actual / 365 NON-EOM                     0.00              164.3836              416.4384
30E / 360 NON-EOM                        0.00              163.8889              416.6667
30 / 360 ISDA NON-EOM                    0.00              163.8889              416.6667
NL / ACT NON-EOM                         0.00              165.7459              417.1271
NL / 365 NON-EOM                         0.00              164.3836              413.6986
NL / 360 NON-EOM                         0.00              166.6667              419.4444
Actual / 364 NON-EOM                     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 NON-EOM                        0.00              162.6962              412.6927
 

 

See Also

 



Copyright 2008-2024 Westclintech LLC         Privacy Policy        Terms of Service