Login     Register

        Contact Us     Search

Menu

XLeratorDB/financial Documentation

SQL Server Simple Accrual function


SimpleAccrual
Updated: 17 Apr 2017

Use the table-valued function SimpleAccrual to return the daily interest accruals over a range of dates for a single cash flow or a series of cash flows, using a single rate or a series of rates.

Syntax
    SELECT * FROM [wct].[SimpleAccrual] (
       <@date_start, datetime,>
      ,<@date_end, datetime,>
      ,<@bal_start, float,>
      ,<@Rate, sql_variant,>
      ,<@Spread, sql_variant,>
      ,<@CashMvMnt, nvarchar(max),>
      ,<@Basis, nvarchar(4000),>)
Arguments
Input NameDescription
@date_startAccrual start date. Must be of type datetime or of a type that can implicitly convert to datetime.
@date_endAccrual end date. Must be of type datetime or of a type that can implicitly convert to datetime.
@bal_startStarting balance. Must be of type float or of a type that can implicitly convert to float.
@RateThe underlying interest rate for accrual purposes.
@SpreadAn amount added to @Rate.
@CashMvMntA TSQL statement which produces the dates and amounts of any cash movements to be used in the accrual calculation.
@BasisDay-count convention used in the accrual calculation.
Return Type
RETURNS  TABLE (
    [aDate] [datetime] NULL,
    [Rate] [float] NULL,
    [Spread] [float] NULL,
    [BalBegin] [float] NULL,
    [Movement] [float] NULL,
    [BalEnd] [float] NULL,
    [Interest] [float] NULL
)
ColumnDescription
aDateaccrual date
Rateinterest rate in effect for the accrual date
Spreadspread rate in effect for the accrual date
BalBeginbeginning balance for the accrual date
Movementcash movement occurring on the accrual date
BalEndBalBegin + Movement
InterestThe daily accrual amount
Remarks
  • If @date_start is NULL the @date_start =GETDATE().
  • If @date_end is NULL then @date_end = GETDATE().
  • If @bal_start is NULL then @bal_start = 0.
  • If @Rate is NULL then @Rate = 0.
  • If @Spread is NULL then @Spread = 0.
  • If @Basis is NULL then @Basis = '3'
  • Valid @Basis codes are:
    • 0, 'BOND'
    • 1, 'ACTUAL'
    • 2, 'A360'
    • 3, 'A365'
    • 4, '30E/360 (ISDA)', '30E/360', 'ISDA', '30E/360 ISDA', 'EBOND'
    • 21, 'Actual/ISDA'
  • Available in XLeratorDB / financial 2008 only
Examples
Example #1

In this example we accrue interest at a fixed rate with a fixed spread from 1 March 2017 to 31 March 2017. There are no cash movements during this period. Interest is accrued using the Actual / 365 day-count convention.

SELECT
     aDate
    ,Rate
    ,Spread
    ,BalBegin
    ,Movement
    ,BalEnd
    ,Interest
    ,SUM(Interest) OVER (ORDER BY aDate) as [Cumulative Interest]
FROM
    wct.SimpleAccrual(
         '20170301'        --@date_start
        ,'20170331'        --@date_end
        ,100000            --@bal_start
        ,.0075                --@rate
        ,.0250                --@spread
        ,NULL                --@CashMvMnt
        ,3                    --@Basis
        )

This produces the following result.

Example #2

In this example the balance changes during the accrual period. Note that there can be multiple cash movements for the same day.

SELECT
    *
INTO
    #cm
FROM (VALUES
     ('20170301',15000)
    ,('20170308',-7500)
    ,('20170315',2200)
    ,('20170315',3750)
    ,('20170322',-50000)
    ,('20170329',22500)
    )n(dt,amt)
 
SELECT
     aDate
    ,Rate
    ,Spread
    ,BalBegin
    ,Movement
    ,BalEnd
    ,Interest
    ,SUM(Interest) OVER (ORDER BY aDate) as [Cumulative Interest]
FROM
    wct.SimpleAccrual(
         '20170301'        --@date_start
        ,'20170331'        --@date_end
        ,100000            --@bal_start
        ,.0075                --@rate
        ,.0250                --@spread
        ,'SELECT * FROM #cm'    --@CashMvMnt
        ,3                    --@Basis
        )

This produces the following result.

It's not necessary to use a temp table to pass the cash flows into the function. The following SQL produces the same result.

SELECT
     aDate
    ,Rate
    ,Spread
    ,BalBegin
    ,Movement
    ,BalEnd
    ,Interest
    ,SUM(Interest) OVER (ORDER BY aDate) as [Cumulative Interest]
FROM
    wct.SimpleAccrual(
         '20170301'        --@date_start
        ,'20170331'        --@date_end
        ,100000                --@bal_start
        ,.0075                --@rate
        ,.0250                --@spread
        ,'SELECT
            *
        FROM (VALUES
             (''20170301'',15000)
            ,(''20170308'',-7500)
            ,(''20170315'',2200)
            ,''20170315'',3750)
            ,(''20170322'',-50000)
            ,(''20170329'',22500)
            )n(dt,amt)'    --@CashMvMnt
        ,3                    --@Basis
        )
Example #3

In this example, not only are there cash movements, but the rates change as well.

SELECT
    *
INTO
    #cm
FROM (VALUES
     ('20170301',15000)
    ,('20170308',-7500)
    ,('20170315',2200)
    ,('20170315',3750)
    ,('20170322',-50000)
    ,('20170329',22500)
    )n(dt,amt)
 
SELECT
    *
INTO
    #rm
FROM (
    VALUES
     ('20170301', .0075)
    ,('20170315',.00875)
    ,('20170329',0.0093625)
    )n(dt,rate)
 
SELECT
     aDate
    ,Rate
    ,Spread
    ,BalBegin
    ,Movement
    ,BalEnd
    ,Interest
    ,SUM(Interest) OVER (ORDER BY aDate) as [Cumulative Interest]
FROM
    wct.SimpleAccrual(
         '20170301'        --@date_start
        ,'20170331'        --@date_end
        ,100000            --@bal_start
        ,'SELECT * FROM #rm'    --@rate
        ,.0250                --@spread
        ,'SELECT * FROM #cm'    --@CashMvMnt
        ,3                    --@Basis
        )

This produces the following result.

Example #4

In this example, there are cash movements, the rates change, and the spread changes.

SELECT
    *
INTO
    #cm
FROM (VALUES
     ('20170301',15000)
    ,('20170308',-7500)
    ,('20170315',2200)
    ,('20170315',3750)
    ,('20170322',-50000)
    ,('20170329',22500)
    )n(dt,amt)
 
SELECT
    *
INTO
    #rm
FROM (
    VALUES
     ('20170301', .0075)
    ,('20170315',.00875)
    ,('20170329',0.0093625)
    )n(dt,rate)
 
SELECT
    *
INTO
    #s
FROM (
    VALUES
     ('20170301',.0250)
    ,('20170315',.0275)
    )n(dt,rate)
 
SELECT
     aDate
    ,Rate
    ,Spread
    ,BalBegin
    ,Movement
    ,BalEnd
    ,Interest
    ,SUM(Interest) OVER (ORDER BY aDate) as [Cumulative Interest]
FROM
    wct.SimpleAccrual(
         '20170301'        --@date_start
        ,'20170331'        --@date_end
        ,100000            --@bal_start
        ,'SELECT * FROM #rm'    --@rate
        ,'SELECT * FROM #s'    --@spread
        ,'SELECT * FROM #cm'    --@CashMvMnt
        ,3                    --@Basis
        )

This produces the following result.

See Also


View Topic History
Copyright 2008-2024 Westclintech LLC         Privacy Policy        Terms of Service