SQL Server SimpleAccrual Function

Updated 2023-10-11 14:28:36.013000

Description

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 [westclintech].[wct].[SimpleAccrual] (
       <@date_start, datetime,>
      ,<@date_end, datetime,>
      ,<@bal_start, float,>
      ,<@Rate, sql_variant,>
      ,<@Spread, sql_variant,>
      ,<@CashMvMnt, nvarchar(max),>
      ,<@Basis, nvarchar(4000),>)

Arguments

@date_start

Accrual start date. Must be of type datetime or of a type that can implicitly convert to datetime.

@date_end

Accrual end date. Must be of type datetime or of a type that can implicitly convert to datetime.

@bal_start

Starting balance. Must be of type float or of a type that can implicitly convert to float.

@Rate

The underlying interest rate for accrual purposes.

@Spread

An amount added to @Rate.

@CashMvMnt

A TSQL statement which produces the dates and amounts of any cash movements to be used in the accrual calculation.

@Basis

Day-count convention used in the accrual calculation.

Return Type

table

Remarks

If @bal_start is NULL then @bal_start = 0.

If @date_start is NULL the @date_start =GETDATE().

If @date_end is NULL then @date_end = GETDATE().

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:

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

ACCRINT - Calculate the accrued interest for a security that pays periodic interest.

ACCRINTM - Calculate the accrued interest for a security that pays interest at maturity.

BONDINT - Accrued interest on a bond paying regular, periodic interest