Login     Register

        Contact Us     Search

XLeratorDB/financial Documentation

SQL Server accrued interest function for stepped-rate bonds


STEPACCINT

Updated: 21 April 2014


Use STEPACCINT to calculate the accrued interest for a stepped-coupon bond with a par value of 100.
Syntax
SELECT [wctFinancial].[wct].[STEPACCINT](
  <@Settlement, datetime,>
 ,<@Maturity, datetime,>
 ,<@Frequency, float,>
 ,<@Basis, nvarchar(4000),>
 ,<@Coupons, nvarchar(max),>)
Arguments
@Settlement
the settlement date occurring within the coupon period of the bond. @Settlement is an expression that returns a datetime or smalldatetime value, or a character string in date format.
@Maturity
the maturity date of the bond. @Settlement is an expression that returns a datetime or smalldatetime value, or a character string in date format.
@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.
@Basis
is the type of day count to use. @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

 @Coupons
a SELECT statement, as a string, which identifies the coupon dates and rates to be used in the accrued interest calculations. The coupon rate is assumed to be in effect from the associated coupon date to the next greater coupon date returned by the SELECT statement. The last rate is assumed to be in effect from the last date until the maturity date of the bond.
Return Type
float
Remarks
·         If @Basis < 0 or @Basis > 4, STEPACCINT returns an error.
·         If @Maturity <= @Settlement 0 is returned.
·         If @Settlement is NULL, @Settlement = GETDATE()
·         If @Frequency is NULL, @Frequency = 2
·         If @Basis is NULL, @Basis = 0.
·         If @Coupons is empty or NULL then 0 is returned.
·         Accrued interest is calculated from the previous coupon date to the settlement date.
·         Previous coupon date is calculated backwards from the maturity date. If the maturity date is the last day of the month, all the previous coupon dates are assumed to occur on the last day of the month.
·         Previous coupon date <= @Settlement < next coupon date
Examples
In this example we calculate the accrued interest for a bond maturing on 2019-01-15 with the following step-up schedule.

2010-01-15
5.0%
2013-01-15
5.5%
2016-01-15
6.0%

The settlement date is April 21, 2014
SELECT wct.STEPACCINT(
      '2014-04-21',     --@Settlement
      '2019-01-15',     --@Maturity
      2,                --@Frequency
      0,                --@Basis
      'SELECT wct.CALCDATE(2010,1,15),0.05 UNION ALL
      SELECT wct.CALCDATE(2013,1,15),0.055 UNION ALL
      SELECT wct.CALCDATE(2016,1,15),0.06'      --@Coupons
      ) as ACCINT
This produces the following result
                ACCINT
----------------------
      1.46666666666667
The SELECT statement in @Coupons can make reference to another table, as in the following example.
SELECT
      *
INTO
      #coups
FROM (
      SELECT '2010-1-15',0.05 UNION ALL
      SELECT '2013-1-15',0.055 UNION ALL
      SELECT '2016-1,15',0.06
      )n(coupdate, couprate)
     
SELECT wct.STEPACCINT(
      '2014-04-21',           --@Settlement
      '2019-01-15',           --@Maturity
      2,                      --@Frequency
      0,                      --@Basis
      'SELECT * FROM #coups' --@Coupons
      ) as ACCINT
This produces the following result.
                ACCINT
----------------------
      1.46666666666667
In this example we have multiple securities with different step-up schedules. For purposes of this example, the coupon schedules are stored in a temporary table, #coups.
SELECT
   *
INTO
   #coups
FROM (
      SELECT 'ABC', '2010-01-15', 0.050 UNION ALL
      SELECT 'ABC', '2013-01-15', 0.055 UNION ALL
      SELECT 'ABC', '2016-01-15', 0.060 UNION ALL
      SELECT 'GHI', '2031-07-22', 0.070 UNION ALL
      SELECT 'GHI', '2026-07-22', 0.0675 UNION ALL
      SELECT 'GHI', '2021-07-22', 0.0650 UNION ALL
      SELECT 'GHI', '2016-07-22', 0.0625 UNION ALL
      SELECT 'GHI', '2011-07-22', 0.0600 UNION ALL
      SELECT 'XYZ', '2023-03-01', 0.0600 UNION ALL
      SELECT 'XYZ', '2019-03-01', 0.0575 UNION ALL
      SELECT 'XYZ', '2015-03-1', 0.0550 UNION ALL
      SELECT 'XYZ', '2011-03-1', 0.0
      )c(secid, coupdate, couprate)
 
SELECT
   secid
   ,wct.STEPACCINT(
      '2014-04-21',
      n.maturity,
      frequency,
      basis,
      'SELECT
         coupdate,
         couprate
      FROM
         #coups
      WHERE
         secid = ' + '''' + n.secid + ''''
    ) as ACCINT
   FROM (
       SELECT 'ABC', '2019-01-15', 103.670988, 100, 2, 0 UNION ALL
       SELECT 'GHI', '2036-07-22', 120.467994, 103, 2, 1 UNION ALL
       SELECT 'XYZ', '2027-03-01', 97.478325, 101, 1, 0
       )n(secid, maturity, price, redemption, frequency, basis)
This produces the following result.
secid                 ACCINT
----- ----------------------
ABC         1.46666666666667
GHI         1.47513812154696
XYZ                        0

 

See Also

 



Copyright 2008-2024 Westclintech LLC         Privacy Policy        Terms of Service