SQL Server ISREGULARPAY Function

Updated 2024-02-13 20:49:55.283000

Description

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 [westclintech].[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.

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.