ISREGULARPAY
Updated: 13 January 2014
Use the scalar function ISREGULARPAY to determine if a date is a regular payment date for a loan given the first payment date, the issue date, and the number of payments per year.
Syntax
SELECT [wctFinancial].[wct].[ISREGULARPAY](
<@IssueDate, datetime,>
,<@FirstPaymentDate, datetime,>
,<@PmtPerYear, int,>)
Arguments
@IssueDate
the start or first interest accrual date of the loan. @IssueDate is an expression of type datetime or of a type that can be implicitly converted to datetime.
@FirstPaymentDate
the first interest payment date of the loan. @FirstPaymentDate is an expression of type datetime or of a type that can be implicitly converted to datetime.
@PmtPerYear
the number of payments per year. @PmtPerYear is an expression of type int or of a type that can be implicitly converted to int.
Return Type
bit
Remarks
· @PmtPerYear must be 1, 2, 3, 4, 6, 12, 13, 24, 26, 52 or 365.
Examples
A loan with weekly repayments with the first payment 7 days after the issue date.
SELECT
wct.ISREGULARPAY(
'2014-01-13', --@IssueDate
'2014-01-20', --@FirstPaymentDate
52 --@PmtPerYear
) as IsRegularPay
This produces the following result.
IsRegularPay
------------
1
The following example uses 2013-12-31 as @IssueDate and then compares all the days in January 2014 (using the SeriesDate function from the XLeratorDB math library) as @FirstPaymentDate and pivots the results for each possible value of @PmtPerYear.
SELECT
FirstPaymentDate,
[1],
[2],
[3],
[4],
[6],
[12],
[13],
[24],
[26],
[365]
FROM(
SELECT
x.PmtPerYear,
CAST(k.seriesvalue as date) as FirstPaymentDate,
CAST(wct.ISREGULARPAY('2013-12-31',k.seriesValue,x.PmtPerYear) as float) as RegPay
FROM
(VALUES
(1),(2),(3),(4),(6),(12),(13),(24),(26),(52),(365)
)x(PmtPerYear)
CROSS APPLY
wctMath.wct.SeriesDate('2013-12-31','2014-01-31',1,NULL,NULL)k
) d
PIVOT(
SUM(RegPay)
FOR
PmtPeryear
IN(
[1],
[2],
[3],
[4],
[6],
[12],
[13],
[24],
[26],
[365]
)
) as P
This produces the following result, which has been reformatted for ease of viewing.
|
FirstPaymentDate
|
1
|
2
|
3
|
4
|
6
|
12
|
13
|
24
|
26
|
365
|
|
2013-12-31
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
|
2014-01-01
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
1
|
|
2014-01-02
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
|
2014-01-03
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
|
2014-01-04
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
|
2014-01-05
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
|
2014-01-06
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
|
2014-01-07
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
|
2014-01-08
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
|
2014-01-09
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
|
2014-01-10
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
|
2014-01-11
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
|
2014-01-12
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
|
2014-01-13
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
|
2014-01-14
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
1
|
0
|
|
2014-01-15
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
1
|
0
|
0
|
|
2014-01-16
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
|
2014-01-17
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
|
2014-01-18
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
|
2014-01-19
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
|
2014-01-20
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
|
2014-01-21
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
|
2014-01-22
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
|
2014-01-23
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
|
2014-01-24
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
|
2014-01-25
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
|
2014-01-26
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
|
2014-01-27
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
|
2014-01-28
|
0
|
0
|
0
|
0
|
0
|
0
|
1
|
0
|
0
|
0
|
|
2014-01-29
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
|
2014-01-30
|
0
|
0
|
0
|
0
|
0
|
1
|
0
|
0
|
0
|
0
|
|
2014-01-31
|
0
|
0
|
0
|
0
|
0
|
1
|
0
|
0
|
0
|
0
|