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' --@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