Login     Register

        Contact Us     Search

XLeratorDB/financial Documentation

SQL Server function to check scheduled payment date


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

 


Copyright 2008-2017 Westclintech LLC         Privacy Policy        Terms of Service