SimpleAccrual
Updated 2023-10-11 14:28:36.013000
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),>)
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.
Arguments
@Basis
Day-count convention used in the accrual calculation.
@Rate
The underlying interest rate for accrual purposes.
@bal_start
Starting balance. Must be of type float or of a type that can implicitly convert to float.
@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.
@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.
Return Type
table
{"columns": [{"field": "colName", "headerName": "Name", "header": "name"}, {"field": "colDatatype", "headerName": "Type", "header": "type"}, {"field": "colDesc", "headerName": "Description", "header": "description", "minWidth": 1000}], "rows": [{"id": "0a1d4aca-bba6-4b63-99d3-187be600dbc4", "colName": "aDate", "colDatatype": "datetime", "colDesc": "accrual date"}, {"id": "194d58a5-70d2-48c6-951b-a36378495986", "colName": "Rate", "colDatatype": "float", "colDesc": "interest rate in effect for the accrual date"}, {"id": "2d4f1466-18c4-4ad3-8ef8-be2a5858a92c", "colName": "Spread", "colDatatype": "float", "colDesc": "spread rate in effect for the accrual date"}, {"id": "9a11b152-bd58-4e31-a7fd-d3ca60ded8c7", "colName": "BalBegin", "colDatatype": "float", "colDesc": "beginning balance for the accrual date"}, {"id": "1b2868f8-ea07-41a9-bc3d-e16785f6d0ae", "colName": "Movement", "colDatatype": "float", "colDesc": "cash movement occurring on the accrual date"}, {"id": "3917f381-7d4e-44c0-9957-b9ba0d0f64c7", "colName": "BalEnd", "colDatatype": "float", "colDesc": "BalBegin + Movement"}, {"id": "b9731af9-67f3-4610-b383-34b044f471f5", "colName": "Interest", "colDatatype": "float", "colDesc": "The daily accrual amount"}]}
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:
{"columns":[{"field":"column 1","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"column 2"}],"rows":[{"column 1":"0","column 2":"'BOND'"},{"column 1":"1","column 2":"'ACTUAL'"},{"column 1":"2","column 2":"'A360'"},{"column 1":"3","column 2":"'A365'"},{"column 1":"4","column 2":"'30E/360 (ISDA)','30E/360','ISDA','30E/360 ISDA','EBOND'"},{"column 1":"21","column 2":"'Actual/ISDA'"}]}
Examples
Example #1In 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.
{"columns":[{"field":"aDate","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Rate","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Spread","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"BalBegin","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Movement","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"BalEnd","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Interest","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Cumulative Interest","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"aDate":"2017-03-01 00:00:00.000","Rate":"0.0075","Spread":"0.025","BalBegin":"100000","Movement":"0","BalEnd":"100000","Interest":"8.9041095890411","Cumulative Interest":"8.9041095890411"},{"aDate":"2017-03-02 00:00:00.000","Rate":"0.0075","Spread":"0.025","BalBegin":"100000","Movement":"0","BalEnd":"100000","Interest":"8.9041095890411","Cumulative Interest":"17.8082191780822"},{"aDate":"2017-03-03 00:00:00.000","Rate":"0.0075","Spread":"0.025","BalBegin":"100000","Movement":"0","BalEnd":"100000","Interest":"8.9041095890411","Cumulative Interest":"26.7123287671233"},{"aDate":"2017-03-04 00:00:00.000","Rate":"0.0075","Spread":"0.025","BalBegin":"100000","Movement":"0","BalEnd":"100000","Interest":"8.9041095890411","Cumulative Interest":"35.6164383561644"},{"aDate":"2017-03-05 00:00:00.000","Rate":"0.0075","Spread":"0.025","BalBegin":"100000","Movement":"0","BalEnd":"100000","Interest":"8.9041095890411","Cumulative Interest":"44.5205479452055"},{"aDate":"2017-03-06 00:00:00.000","Rate":"0.0075","Spread":"0.025","BalBegin":"100000","Movement":"0","BalEnd":"100000","Interest":"8.9041095890411","Cumulative Interest":"53.4246575342466"},{"aDate":"2017-03-07 00:00:00.000","Rate":"0.0075","Spread":"0.025","BalBegin":"100000","Movement":"0","BalEnd":"100000","Interest":"8.9041095890411","Cumulative Interest":"62.3287671232877"},{"aDate":"2017-03-08 00:00:00.000","Rate":"0.0075","Spread":"0.025","BalBegin":"100000","Movement":"0","BalEnd":"100000","Interest":"8.9041095890411","Cumulative Interest":"71.2328767123288"},{"aDate":"2017-03-09 00:00:00.000","Rate":"0.0075","Spread":"0.025","BalBegin":"100000","Movement":"0","BalEnd":"100000","Interest":"8.9041095890411","Cumulative Interest":"80.1369863013699"},{"aDate":"2017-03-10 00:00:00.000","Rate":"0.0075","Spread":"0.025","BalBegin":"100000","Movement":"0","BalEnd":"100000","Interest":"8.9041095890411","Cumulative Interest":"89.041095890411"},{"aDate":"2017-03-11 00:00:00.000","Rate":"0.0075","Spread":"0.025","BalBegin":"100000","Movement":"0","BalEnd":"100000","Interest":"8.9041095890411","Cumulative Interest":"97.9452054794521"},{"aDate":"2017-03-12 00:00:00.000","Rate":"0.0075","Spread":"0.025","BalBegin":"100000","Movement":"0","BalEnd":"100000","Interest":"8.9041095890411","Cumulative Interest":"106.849315068493"},{"aDate":"2017-03-13 00:00:00.000","Rate":"0.0075","Spread":"0.025","BalBegin":"100000","Movement":"0","BalEnd":"100000","Interest":"8.9041095890411","Cumulative Interest":"115.753424657534"},{"aDate":"2017-03-14 00:00:00.000","Rate":"0.0075","Spread":"0.025","BalBegin":"100000","Movement":"0","BalEnd":"100000","Interest":"8.9041095890411","Cumulative Interest":"124.657534246575"},{"aDate":"2017-03-15 00:00:00.000","Rate":"0.0075","Spread":"0.025","BalBegin":"100000","Movement":"0","BalEnd":"100000","Interest":"8.9041095890411","Cumulative Interest":"133.561643835616"},{"aDate":"2017-03-16 00:00:00.000","Rate":"0.0075","Spread":"0.025","BalBegin":"100000","Movement":"0","BalEnd":"100000","Interest":"8.9041095890411","Cumulative Interest":"142.465753424658"},{"aDate":"2017-03-17 00:00:00.000","Rate":"0.0075","Spread":"0.025","BalBegin":"100000","Movement":"0","BalEnd":"100000","Interest":"8.9041095890411","Cumulative Interest":"151.369863013699"},{"aDate":"2017-03-18 00:00:00.000","Rate":"0.0075","Spread":"0.025","BalBegin":"100000","Movement":"0","BalEnd":"100000","Interest":"8.9041095890411","Cumulative Interest":"160.27397260274"},{"aDate":"2017-03-19 00:00:00.000","Rate":"0.0075","Spread":"0.025","BalBegin":"100000","Movement":"0","BalEnd":"100000","Interest":"8.9041095890411","Cumulative Interest":"169.178082191781"},{"aDate":"2017-03-20 00:00:00.000","Rate":"0.0075","Spread":"0.025","BalBegin":"100000","Movement":"0","BalEnd":"100000","Interest":"8.9041095890411","Cumulative Interest":"178.082191780822"},{"aDate":"2017-03-21 00:00:00.000","Rate":"0.0075","Spread":"0.025","BalBegin":"100000","Movement":"0","BalEnd":"100000","Interest":"8.9041095890411","Cumulative Interest":"186.986301369863"},{"aDate":"2017-03-22 00:00:00.000","Rate":"0.0075","Spread":"0.025","BalBegin":"100000","Movement":"0","BalEnd":"100000","Interest":"8.9041095890411","Cumulative Interest":"195.890410958904"},{"aDate":"2017-03-23 00:00:00.000","Rate":"0.0075","Spread":"0.025","BalBegin":"100000","Movement":"0","BalEnd":"100000","Interest":"8.9041095890411","Cumulative Interest":"204.794520547945"},{"aDate":"2017-03-24 00:00:00.000","Rate":"0.0075","Spread":"0.025","BalBegin":"100000","Movement":"0","BalEnd":"100000","Interest":"8.9041095890411","Cumulative Interest":"213.698630136986"},{"aDate":"2017-03-25 00:00:00.000","Rate":"0.0075","Spread":"0.025","BalBegin":"100000","Movement":"0","BalEnd":"100000","Interest":"8.9041095890411","Cumulative Interest":"222.602739726027"},{"aDate":"2017-03-26 00:00:00.000","Rate":"0.0075","Spread":"0.025","BalBegin":"100000","Movement":"0","BalEnd":"100000","Interest":"8.9041095890411","Cumulative Interest":"231.506849315069"},{"aDate":"2017-03-27 00:00:00.000","Rate":"0.0075","Spread":"0.025","BalBegin":"100000","Movement":"0","BalEnd":"100000","Interest":"8.9041095890411","Cumulative Interest":"240.41095890411"},{"aDate":"2017-03-28 00:00:00.000","Rate":"0.0075","Spread":"0.025","BalBegin":"100000","Movement":"0","BalEnd":"100000","Interest":"8.9041095890411","Cumulative Interest":"249.315068493151"},{"aDate":"2017-03-29 00:00:00.000","Rate":"0.0075","Spread":"0.025","BalBegin":"100000","Movement":"0","BalEnd":"100000","Interest":"8.9041095890411","Cumulative Interest":"258.219178082192"},{"aDate":"2017-03-30 00:00:00.000","Rate":"0.0075","Spread":"0.025","BalBegin":"100000","Movement":"0","BalEnd":"100000","Interest":"8.9041095890411","Cumulative Interest":"267.123287671233"},{"aDate":"2017-03-31 00:00:00.000","Rate":"0.0075","Spread":"0.025","BalBegin":"100000","Movement":"0","BalEnd":"100000","Interest":"8.9041095890411","Cumulative Interest":"276.027397260274"}]}
Example #2In 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.
{"columns":[{"field":"aDate","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Rate","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Spread","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"BalBegin","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Movement","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"BalEnd","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Interest","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Cumulative Interest","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"aDate":"2017-03-01 00:00:00.000","Rate":"0.0075","Spread":"0.025","BalBegin":"100000","Movement":"15000","BalEnd":"115000","Interest":"10.2397260273973","Cumulative Interest":"10.2397260273973"},{"aDate":"2017-03-02 00:00:00.000","Rate":"0.0075","Spread":"0.025","BalBegin":"115000","Movement":"0","BalEnd":"115000","Interest":"10.2397260273973","Cumulative Interest":"20.4794520547945"},{"aDate":"2017-03-03 00:00:00.000","Rate":"0.0075","Spread":"0.025","BalBegin":"115000","Movement":"0","BalEnd":"115000","Interest":"10.2397260273973","Cumulative Interest":"30.7191780821918"},{"aDate":"2017-03-04 00:00:00.000","Rate":"0.0075","Spread":"0.025","BalBegin":"115000","Movement":"0","BalEnd":"115000","Interest":"10.2397260273973","Cumulative Interest":"40.958904109589"},{"aDate":"2017-03-05 00:00:00.000","Rate":"0.0075","Spread":"0.025","BalBegin":"115000","Movement":"0","BalEnd":"115000","Interest":"10.2397260273973","Cumulative Interest":"51.1986301369863"},{"aDate":"2017-03-06 00:00:00.000","Rate":"0.0075","Spread":"0.025","BalBegin":"115000","Movement":"0","BalEnd":"115000","Interest":"10.2397260273973","Cumulative Interest":"61.4383561643836"},{"aDate":"2017-03-07 00:00:00.000","Rate":"0.0075","Spread":"0.025","BalBegin":"115000","Movement":"0","BalEnd":"115000","Interest":"10.2397260273973","Cumulative Interest":"71.6780821917808"},{"aDate":"2017-03-08 00:00:00.000","Rate":"0.0075","Spread":"0.025","BalBegin":"115000","Movement":"-7500","BalEnd":"107500","Interest":"9.57191780821918","Cumulative Interest":"81.25"},{"aDate":"2017-03-09 00:00:00.000","Rate":"0.0075","Spread":"0.025","BalBegin":"107500","Movement":"0","BalEnd":"107500","Interest":"9.57191780821918","Cumulative Interest":"90.8219178082192"},{"aDate":"2017-03-10 00:00:00.000","Rate":"0.0075","Spread":"0.025","BalBegin":"107500","Movement":"0","BalEnd":"107500","Interest":"9.57191780821918","Cumulative Interest":"100.393835616438"},{"aDate":"2017-03-11 00:00:00.000","Rate":"0.0075","Spread":"0.025","BalBegin":"107500","Movement":"0","BalEnd":"107500","Interest":"9.57191780821918","Cumulative Interest":"109.965753424658"},{"aDate":"2017-03-12 00:00:00.000","Rate":"0.0075","Spread":"0.025","BalBegin":"107500","Movement":"0","BalEnd":"107500","Interest":"9.57191780821918","Cumulative Interest":"119.537671232877"},{"aDate":"2017-03-13 00:00:00.000","Rate":"0.0075","Spread":"0.025","BalBegin":"107500","Movement":"0","BalEnd":"107500","Interest":"9.57191780821918","Cumulative Interest":"129.109589041096"},{"aDate":"2017-03-14 00:00:00.000","Rate":"0.0075","Spread":"0.025","BalBegin":"107500","Movement":"0","BalEnd":"107500","Interest":"9.57191780821918","Cumulative Interest":"138.681506849315"},{"aDate":"2017-03-15 00:00:00.000","Rate":"0.0075","Spread":"0.025","BalBegin":"107500","Movement":"5950","BalEnd":"113450","Interest":"10.1017123287671","Cumulative Interest":"148.783219178082"},{"aDate":"2017-03-16 00:00:00.000","Rate":"0.0075","Spread":"0.025","BalBegin":"113450","Movement":"0","BalEnd":"113450","Interest":"10.1017123287671","Cumulative Interest":"158.884931506849"},{"aDate":"2017-03-17 00:00:00.000","Rate":"0.0075","Spread":"0.025","BalBegin":"113450","Movement":"0","BalEnd":"113450","Interest":"10.1017123287671","Cumulative Interest":"168.986643835616"},{"aDate":"2017-03-18 00:00:00.000","Rate":"0.0075","Spread":"0.025","BalBegin":"113450","Movement":"0","BalEnd":"113450","Interest":"10.1017123287671","Cumulative Interest":"179.088356164384"},{"aDate":"2017-03-19 00:00:00.000","Rate":"0.0075","Spread":"0.025","BalBegin":"113450","Movement":"0","BalEnd":"113450","Interest":"10.1017123287671","Cumulative Interest":"189.190068493151"},{"aDate":"2017-03-20 00:00:00.000","Rate":"0.0075","Spread":"0.025","BalBegin":"113450","Movement":"0","BalEnd":"113450","Interest":"10.1017123287671","Cumulative Interest":"199.291780821918"},{"aDate":"2017-03-21 00:00:00.000","Rate":"0.0075","Spread":"0.025","BalBegin":"113450","Movement":"0","BalEnd":"113450","Interest":"10.1017123287671","Cumulative Interest":"209.393493150685"},{"aDate":"2017-03-22 00:00:00.000","Rate":"0.0075","Spread":"0.025","BalBegin":"113450","Movement":"-50000","BalEnd":"63450","Interest":"5.64965753424658","Cumulative Interest":"215.043150684932"},{"aDate":"2017-03-23 00:00:00.000","Rate":"0.0075","Spread":"0.025","BalBegin":"63450","Movement":"0","BalEnd":"63450","Interest":"5.64965753424658","Cumulative Interest":"220.692808219178"},{"aDate":"2017-03-24 00:00:00.000","Rate":"0.0075","Spread":"0.025","BalBegin":"63450","Movement":"0","BalEnd":"63450","Interest":"5.64965753424658","Cumulative Interest":"226.342465753425"},{"aDate":"2017-03-25 00:00:00.000","Rate":"0.0075","Spread":"0.025","BalBegin":"63450","Movement":"0","BalEnd":"63450","Interest":"5.64965753424658","Cumulative Interest":"231.992123287671"},{"aDate":"2017-03-26 00:00:00.000","Rate":"0.0075","Spread":"0.025","BalBegin":"63450","Movement":"0","BalEnd":"63450","Interest":"5.64965753424658","Cumulative Interest":"237.641780821918"},{"aDate":"2017-03-27 00:00:00.000","Rate":"0.0075","Spread":"0.025","BalBegin":"63450","Movement":"0","BalEnd":"63450","Interest":"5.64965753424658","Cumulative Interest":"243.291438356164"},{"aDate":"2017-03-28 00:00:00.000","Rate":"0.0075","Spread":"0.025","BalBegin":"63450","Movement":"0","BalEnd":"63450","Interest":"5.64965753424658","Cumulative Interest":"248.941095890411"},{"aDate":"2017-03-29 00:00:00.000","Rate":"0.0075","Spread":"0.025","BalBegin":"63450","Movement":"22500","BalEnd":"85950","Interest":"7.65308219178082","Cumulative Interest":"256.594178082192"},{"aDate":"2017-03-30 00:00:00.000","Rate":"0.0075","Spread":"0.025","BalBegin":"85950","Movement":"0","BalEnd":"85950","Interest":"7.65308219178082","Cumulative Interest":"264.247260273973"},{"aDate":"2017-03-31 00:00:00.000","Rate":"0.0075","Spread":"0.025","BalBegin":"85950","Movement":"0","BalEnd":"85950","Interest":"7.65308219178082","Cumulative Interest":"271.900342465754"}]}
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 #3In 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.
{"columns":[{"field":"aDate","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Rate","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Spread","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"BalBegin","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Movement","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"BalEnd","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Interest","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Cumulative Interest","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"aDate":"2017-03-01 00:00:00.000","Rate":"0.0075","Spread":"0.025","BalBegin":"100000","Movement":"15000","BalEnd":"115000","Interest":"10.2397260273973","Cumulative Interest":"10.2397260273973"},{"aDate":"2017-03-02 00:00:00.000","Rate":"0.0075","Spread":"0.025","BalBegin":"115000","Movement":"0","BalEnd":"115000","Interest":"10.2397260273973","Cumulative Interest":"20.4794520547945"},{"aDate":"2017-03-03 00:00:00.000","Rate":"0.0075","Spread":"0.025","BalBegin":"115000","Movement":"0","BalEnd":"115000","Interest":"10.2397260273973","Cumulative Interest":"30.7191780821918"},{"aDate":"2017-03-04 00:00:00.000","Rate":"0.0075","Spread":"0.025","BalBegin":"115000","Movement":"0","BalEnd":"115000","Interest":"10.2397260273973","Cumulative Interest":"40.958904109589"},{"aDate":"2017-03-05 00:00:00.000","Rate":"0.0075","Spread":"0.025","BalBegin":"115000","Movement":"0","BalEnd":"115000","Interest":"10.2397260273973","Cumulative Interest":"51.1986301369863"},{"aDate":"2017-03-06 00:00:00.000","Rate":"0.0075","Spread":"0.025","BalBegin":"115000","Movement":"0","BalEnd":"115000","Interest":"10.2397260273973","Cumulative Interest":"61.4383561643836"},{"aDate":"2017-03-07 00:00:00.000","Rate":"0.0075","Spread":"0.025","BalBegin":"115000","Movement":"0","BalEnd":"115000","Interest":"10.2397260273973","Cumulative Interest":"71.6780821917808"},{"aDate":"2017-03-08 00:00:00.000","Rate":"0.0075","Spread":"0.025","BalBegin":"115000","Movement":"-7500","BalEnd":"107500","Interest":"9.57191780821918","Cumulative Interest":"81.25"},{"aDate":"2017-03-09 00:00:00.000","Rate":"0.0075","Spread":"0.025","BalBegin":"107500","Movement":"0","BalEnd":"107500","Interest":"9.57191780821918","Cumulative Interest":"90.8219178082192"},{"aDate":"2017-03-10 00:00:00.000","Rate":"0.0075","Spread":"0.025","BalBegin":"107500","Movement":"0","BalEnd":"107500","Interest":"9.57191780821918","Cumulative Interest":"100.393835616438"},{"aDate":"2017-03-11 00:00:00.000","Rate":"0.0075","Spread":"0.025","BalBegin":"107500","Movement":"0","BalEnd":"107500","Interest":"9.57191780821918","Cumulative Interest":"109.965753424658"},{"aDate":"2017-03-12 00:00:00.000","Rate":"0.0075","Spread":"0.025","BalBegin":"107500","Movement":"0","BalEnd":"107500","Interest":"9.57191780821918","Cumulative Interest":"119.537671232877"},{"aDate":"2017-03-13 00:00:00.000","Rate":"0.0075","Spread":"0.025","BalBegin":"107500","Movement":"0","BalEnd":"107500","Interest":"9.57191780821918","Cumulative Interest":"129.109589041096"},{"aDate":"2017-03-14 00:00:00.000","Rate":"0.0075","Spread":"0.025","BalBegin":"107500","Movement":"0","BalEnd":"107500","Interest":"9.57191780821918","Cumulative Interest":"138.681506849315"},{"aDate":"2017-03-15 00:00:00.000","Rate":"0.00875","Spread":"0.025","BalBegin":"107500","Movement":"5950","BalEnd":"113450","Interest":"10.4902397260274","Cumulative Interest":"149.171746575342"},{"aDate":"2017-03-16 00:00:00.000","Rate":"0.00875","Spread":"0.025","BalBegin":"113450","Movement":"0","BalEnd":"113450","Interest":"10.4902397260274","Cumulative Interest":"159.66198630137"},{"aDate":"2017-03-17 00:00:00.000","Rate":"0.00875","Spread":"0.025","BalBegin":"113450","Movement":"0","BalEnd":"113450","Interest":"10.4902397260274","Cumulative Interest":"170.152226027397"},{"aDate":"2017-03-18 00:00:00.000","Rate":"0.00875","Spread":"0.025","BalBegin":"113450","Movement":"0","BalEnd":"113450","Interest":"10.4902397260274","Cumulative Interest":"180.642465753425"},{"aDate":"2017-03-19 00:00:00.000","Rate":"0.00875","Spread":"0.025","BalBegin":"113450","Movement":"0","BalEnd":"113450","Interest":"10.4902397260274","Cumulative Interest":"191.132705479452"},{"aDate":"2017-03-20 00:00:00.000","Rate":"0.00875","Spread":"0.025","BalBegin":"113450","Movement":"0","BalEnd":"113450","Interest":"10.4902397260274","Cumulative Interest":"201.622945205479"},{"aDate":"2017-03-21 00:00:00.000","Rate":"0.00875","Spread":"0.025","BalBegin":"113450","Movement":"0","BalEnd":"113450","Interest":"10.4902397260274","Cumulative Interest":"212.113184931507"},{"aDate":"2017-03-22 00:00:00.000","Rate":"0.00875","Spread":"0.025","BalBegin":"113450","Movement":"-50000","BalEnd":"63450","Interest":"5.86695205479452","Cumulative Interest":"217.980136986301"},{"aDate":"2017-03-23 00:00:00.000","Rate":"0.00875","Spread":"0.025","BalBegin":"63450","Movement":"0","BalEnd":"63450","Interest":"5.86695205479452","Cumulative Interest":"223.847089041096"},{"aDate":"2017-03-24 00:00:00.000","Rate":"0.00875","Spread":"0.025","BalBegin":"63450","Movement":"0","BalEnd":"63450","Interest":"5.86695205479452","Cumulative Interest":"229.71404109589"},{"aDate":"2017-03-25 00:00:00.000","Rate":"0.00875","Spread":"0.025","BalBegin":"63450","Movement":"0","BalEnd":"63450","Interest":"5.86695205479452","Cumulative Interest":"235.580993150685"},{"aDate":"2017-03-26 00:00:00.000","Rate":"0.00875","Spread":"0.025","BalBegin":"63450","Movement":"0","BalEnd":"63450","Interest":"5.86695205479452","Cumulative Interest":"241.447945205479"},{"aDate":"2017-03-27 00:00:00.000","Rate":"0.00875","Spread":"0.025","BalBegin":"63450","Movement":"0","BalEnd":"63450","Interest":"5.86695205479452","Cumulative Interest":"247.314897260274"},{"aDate":"2017-03-28 00:00:00.000","Rate":"0.00875","Spread":"0.025","BalBegin":"63450","Movement":"0","BalEnd":"63450","Interest":"5.86695205479452","Cumulative Interest":"253.181849315069"},{"aDate":"2017-03-29 00:00:00.000","Rate":"0.0093625","Spread":"0.025","BalBegin":"63450","Movement":"22500","BalEnd":"85950","Interest":"8.09166267123288","Cumulative Interest":"261.273511986301"},{"aDate":"2017-03-30 00:00:00.000","Rate":"0.0093625","Spread":"0.025","BalBegin":"85950","Movement":"0","BalEnd":"85950","Interest":"8.09166267123288","Cumulative Interest":"269.365174657534"},{"aDate":"2017-03-31 00:00:00.000","Rate":"0.0093625","Spread":"0.025","BalBegin":"85950","Movement":"0","BalEnd":"85950","Interest":"8.09166267123288","Cumulative Interest":"277.456837328767"}]}
Example #4In 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.
{"columns":[{"field":"aDate","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Rate","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Spread","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"BalBegin","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Movement","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"BalEnd","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Interest","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Cumulative Interest","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"aDate":"2017-03-01 00:00:00.000","Rate":"0.0075","Spread":"0.025","BalBegin":"100000","Movement":"15000","BalEnd":"115000","Interest":"10.2397260273973","Cumulative Interest":"10.2397260273973"},{"aDate":"2017-03-02 00:00:00.000","Rate":"0.0075","Spread":"0.025","BalBegin":"115000","Movement":"0","BalEnd":"115000","Interest":"10.2397260273973","Cumulative Interest":"20.4794520547945"},{"aDate":"2017-03-03 00:00:00.000","Rate":"0.0075","Spread":"0.025","BalBegin":"115000","Movement":"0","BalEnd":"115000","Interest":"10.2397260273973","Cumulative Interest":"30.7191780821918"},{"aDate":"2017-03-04 00:00:00.000","Rate":"0.0075","Spread":"0.025","BalBegin":"115000","Movement":"0","BalEnd":"115000","Interest":"10.2397260273973","Cumulative Interest":"40.958904109589"},{"aDate":"2017-03-05 00:00:00.000","Rate":"0.0075","Spread":"0.025","BalBegin":"115000","Movement":"0","BalEnd":"115000","Interest":"10.2397260273973","Cumulative Interest":"51.1986301369863"},{"aDate":"2017-03-06 00:00:00.000","Rate":"0.0075","Spread":"0.025","BalBegin":"115000","Movement":"0","BalEnd":"115000","Interest":"10.2397260273973","Cumulative Interest":"61.4383561643836"},{"aDate":"2017-03-07 00:00:00.000","Rate":"0.0075","Spread":"0.025","BalBegin":"115000","Movement":"0","BalEnd":"115000","Interest":"10.2397260273973","Cumulative Interest":"71.6780821917808"},{"aDate":"2017-03-08 00:00:00.000","Rate":"0.0075","Spread":"0.025","BalBegin":"115000","Movement":"-7500","BalEnd":"107500","Interest":"9.57191780821918","Cumulative Interest":"81.25"},{"aDate":"2017-03-09 00:00:00.000","Rate":"0.0075","Spread":"0.025","BalBegin":"107500","Movement":"0","BalEnd":"107500","Interest":"9.57191780821918","Cumulative Interest":"90.8219178082192"},{"aDate":"2017-03-10 00:00:00.000","Rate":"0.0075","Spread":"0.025","BalBegin":"107500","Movement":"0","BalEnd":"107500","Interest":"9.57191780821918","Cumulative Interest":"100.393835616438"},{"aDate":"2017-03-11 00:00:00.000","Rate":"0.0075","Spread":"0.025","BalBegin":"107500","Movement":"0","BalEnd":"107500","Interest":"9.57191780821918","Cumulative Interest":"109.965753424658"},{"aDate":"2017-03-12 00:00:00.000","Rate":"0.0075","Spread":"0.025","BalBegin":"107500","Movement":"0","BalEnd":"107500","Interest":"9.57191780821918","Cumulative Interest":"119.537671232877"},{"aDate":"2017-03-13 00:00:00.000","Rate":"0.0075","Spread":"0.025","BalBegin":"107500","Movement":"0","BalEnd":"107500","Interest":"9.57191780821918","Cumulative Interest":"129.109589041096"},{"aDate":"2017-03-14 00:00:00.000","Rate":"0.0075","Spread":"0.025","BalBegin":"107500","Movement":"0","BalEnd":"107500","Interest":"9.57191780821918","Cumulative Interest":"138.681506849315"},{"aDate":"2017-03-15 00:00:00.000","Rate":"0.00875","Spread":"0.0275","BalBegin":"107500","Movement":"5950","BalEnd":"113450","Interest":"11.2672945205479","Cumulative Interest":"149.948801369863"},{"aDate":"2017-03-16 00:00:00.000","Rate":"0.00875","Spread":"0.0275","BalBegin":"113450","Movement":"0","BalEnd":"113450","Interest":"11.2672945205479","Cumulative Interest":"161.216095890411"},{"aDate":"2017-03-17 00:00:00.000","Rate":"0.00875","Spread":"0.0275","BalBegin":"113450","Movement":"0","BalEnd":"113450","Interest":"11.2672945205479","Cumulative Interest":"172.483390410959"},{"aDate":"2017-03-18 00:00:00.000","Rate":"0.00875","Spread":"0.0275","BalBegin":"113450","Movement":"0","BalEnd":"113450","Interest":"11.2672945205479","Cumulative Interest":"183.750684931507"},{"aDate":"2017-03-19 00:00:00.000","Rate":"0.00875","Spread":"0.0275","BalBegin":"113450","Movement":"0","BalEnd":"113450","Interest":"11.2672945205479","Cumulative Interest":"195.017979452055"},{"aDate":"2017-03-20 00:00:00.000","Rate":"0.00875","Spread":"0.0275","BalBegin":"113450","Movement":"0","BalEnd":"113450","Interest":"11.2672945205479","Cumulative Interest":"206.285273972603"},{"aDate":"2017-03-21 00:00:00.000","Rate":"0.00875","Spread":"0.0275","BalBegin":"113450","Movement":"0","BalEnd":"113450","Interest":"11.2672945205479","Cumulative Interest":"217.552568493151"},{"aDate":"2017-03-22 00:00:00.000","Rate":"0.00875","Spread":"0.0275","BalBegin":"113450","Movement":"-50000","BalEnd":"63450","Interest":"6.30154109589041","Cumulative Interest":"223.854109589041"},{"aDate":"2017-03-23 00:00:00.000","Rate":"0.00875","Spread":"0.0275","BalBegin":"63450","Movement":"0","BalEnd":"63450","Interest":"6.30154109589041","Cumulative Interest":"230.155650684932"},{"aDate":"2017-03-24 00:00:00.000","Rate":"0.00875","Spread":"0.0275","BalBegin":"63450","Movement":"0","BalEnd":"63450","Interest":"6.30154109589041","Cumulative Interest":"236.457191780822"},{"aDate":"2017-03-25 00:00:00.000","Rate":"0.00875","Spread":"0.0275","BalBegin":"63450","Movement":"0","BalEnd":"63450","Interest":"6.30154109589041","Cumulative Interest":"242.758732876712"},{"aDate":"2017-03-26 00:00:00.000","Rate":"0.00875","Spread":"0.0275","BalBegin":"63450","Movement":"0","BalEnd":"63450","Interest":"6.30154109589041","Cumulative Interest":"249.060273972603"},{"aDate":"2017-03-27 00:00:00.000","Rate":"0.00875","Spread":"0.0275","BalBegin":"63450","Movement":"0","BalEnd":"63450","Interest":"6.30154109589041","Cumulative Interest":"255.361815068493"},{"aDate":"2017-03-28 00:00:00.000","Rate":"0.00875","Spread":"0.0275","BalBegin":"63450","Movement":"0","BalEnd":"63450","Interest":"6.30154109589041","Cumulative Interest":"261.663356164384"},{"aDate":"2017-03-29 00:00:00.000","Rate":"0.0093625","Spread":"0.0275","BalBegin":"63450","Movement":"22500","BalEnd":"85950","Interest":"8.68036130136986","Cumulative Interest":"270.343717465753"},{"aDate":"2017-03-30 00:00:00.000","Rate":"0.0093625","Spread":"0.0275","BalBegin":"85950","Movement":"0","BalEnd":"85950","Interest":"8.68036130136986","Cumulative Interest":"279.024078767123"},{"aDate":"2017-03-31 00:00:00.000","Rate":"0.0093625","Spread":"0.0275","BalBegin":"85950","Movement":"0","BalEnd":"85950","Interest":"8.68036130136986","Cumulative Interest":"287.704440068493"}]}
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