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 Name | Description |
| @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
RETURNS TABLE (
[aDate] [datetime] NULL,
[Rate] [float] NULL,
[Spread] [float] NULL,
[BalBegin] [float] NULL,
[Movement] [float] NULL,
[BalEnd] [float] NULL,
[Interest] [float] NULL
)
| Column | Description |
| aDate | accrual date |
| Rate | interest rate in effect for the accrual date |
| Spread | spread rate in effect for the accrual date |
| BalBegin | beginning balance for the accrual date |
| Movement | cash movement occurring on the accrual date |
| BalEnd | BalBegin + Movement |
| Interest | The 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'
,'20170331'
,100000
,.0075
,.0250
,NULL
,3
)
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'
,'20170331'
,100000
,.0075
,.0250
,'SELECT * FROM #cm'
,3
)
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'
,'20170331'
,100000
,.0075
,.0250
,'SELECT
*
FROM (VALUES
(''20170301'',15000)
,(''20170308'',-7500)
,(''20170315'',2200)
,''20170315'',3750)
,(''20170322'',-50000)
,(''20170329'',22500)
)n(dt,amt)'
,3
)
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'
,'20170331'
,100000
,'SELECT * FROM #rm'
,.0250
,'SELECT * FROM #cm'
,3
)
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'
,'20170331'
,100000
,'SELECT * FROM #rm'
,'SELECT * FROM #s'
,'SELECT * FROM #cm'
,3
)
This produces the following result.
See Also