PAYMENTPERIODS
Updated: 18 Sep 2014
Use the table-valued function PAYMENTPERIODS to return the number of months from a reference date to: an initial grace period; the start of interim grace period; and the end of interim grace period. PAYMENTPERIODS also returns the months until the first payment, the length (in months) of the interim grace period, and the number of payments (provided a maturity date has been entered).
Syntax
SELECT * FROM [wct].[PAYMENTPERIODS](
<@ReferenceDate, datetime,>
,<@PaymentFrequency, int,>
,<@PrevPayDate, datetime,>
,<@StartDate, datetime,>
,<@FirstPayDate, datetime,>
,<@InterimGracePeriodStartDate, datetime,>
,<@InterimGracePeriodEndDate, datetime,>
,<@MaturityDate, datetime,>)
Arguments
@ReferenceDate
the starting date for the number of months with respect to all other dates. @ReferenceDate is an expression that returns a datetime or smalldatetime value, or a character string in date format.
@PaymentFrequency
the number of months between regular payments. @PaymentFrequency is an expression of type int or of a type that can be implicitly converted to int.
@PrevPayDate
the last interest payment date prior to the reference date. @PrevPayDate is an expression that returns a datetime or smalldatetime value, or a character string in date format.
@StartDate
the start date of the loan. @StartDate is an expression that returns a datetime or smalldatetime value, or a character string in date format.
@FirstPayDate
the first payment date of the loan if other than a regular periodic payment. @FirstPayDate is an expression that returns a datetime or smalldatetime value, or a character string in date format.
@InterimGracePeriodStartDate
the date on which the (interim) grace period commences. @InterimGracePeriodStartDate is an expression that returns a datetime or smalldatetime value, or a character string in date format.
@InterimGracePeriodEndDate
the date on which the (interim) grace period concludes. @InterimGracePeriodEndDate is an expression that returns a datetime or smalldatetime value, or a character string in date format.
@MaturityDate
the last payment date of the loan. @MaturityDate is an expression that returns a datetime or smalldatetime value, or a character string in date format.
Return Type
RETURNS TABLE (
[InitialGracePeriod] [int] NULL,
[InterimGracePeriodMonthStart] [int] NULL,
[InterimGracePeriodMonthEnd] [int] NULL,
[MonthsUntilFirstPayment] [int] NULL,
[InterimGracePeriod] [int] NULL,
[NumberOfPayments] [int] NULL
)
|
Column
|
Description
|
|
InitialGracePeriod
|
The number of months from @ReferenceDate to @FirstPayDate.
|
|
InterimGracePeriodMonthStart
|
The number of months from @ReferenceDate to @InterimGracePeriodStartDate.
|
|
InterimGracePeriodMonthEnd
|
The number of months from @ReferenceDate to @InterimGracePeriodEndDate.
|
|
MonthsUntilFirstPayment
|
The number of months from @ReferenceDate to the first payment date.
|
|
InterimGracePeriod
|
The length, in months, of the interim grace period
|
|
NumberOfPayments
|
The number of payments from the @ReferenceDate to the @MaturityDate.
|
Remarks
· If @ReferenceDate is NULL then @ReferenceDate = GETDATE().
· If @Frequency is NULL then @Frequency = 1.
· If @PaymentFrequency <= then an error is returned.
· For calculation purposes, all dates are moved to the end of the month.
· If @FirstPayDate is NULL then InitialGracePeriod = 0.
· If @FirstPayDate is not NULL then InitialGracePeriod is the greater of zero and the number of months between @ReferenceDate and @FirstPayDate.
· If @ReferenceDate is between @InterimGracePeriodStartDate and @InterimGracePeriodEndDate and @FirstPayDate is NULL or less than or equal to @ReferenceDate then InitialGracePeriod is the number of months from @ReferenceDate to @InterimGracePeriodEndDate.
· If InitialGracePeriod > 0 then MonthsUntilFirstPayment = InitialGracePeriod.
· If InitialGracePeriod = 0 and @StartDate is not NULL and the number of months from @StartDate to @ReferenceDate is less than @PaymentFrequency then MonthsUntilFirstPayment is calculated using @StartDate. If @StartDate is NULL then MonthsUntilFirstPayment is calculated from @PrevPayDate, otherwise MonthsUntilFirstPayment equals PaymentFrequency.
· Subsequent payments are calculated by adding the frequency to MonthsUntilFirstPayment.
· If a calculated payment date is greater than or equal to the @InterimGracePeriodStartDate and less than the @InterimGracePeriodEndDate then it is set to the @InterimGracePeriodEndDate and all subsequent payment dates are calculated by adding the @PaymentFrequency to the @InterimGracePeriodEndDate.
Examples
This SQL returns the result for a loan with a @ReferenceDate of 2014-09-15 and a @MaturityDate of 2019-09-15.
SELECT
*
FROM wct.PaymentPeriods(
'2014-09-15' --@ReferenceDate
,3 --@PaymentFrequency
,NULL --@PrevPayDate
,NULL --@StartDate
,NULL --@FirstPayDate
,NULL --@InterimGracePeriodStartDate
,NULL --@InterimGracePeriodEndDate
,'2019-09-15' --@MaturityDate
)
This produces the following result (which has been reformatted for ease of viewing).
|
Initial Grace Period
|
Interim Grace Period Month Start
|
Interim Grace Period Month End
|
Months Until First Payment
|
Interim Grace Period
|
Number Of Payments
|
|
0
|
0
|
0
|
3
|
0
|
20
|
SELECT
*
FROM wct.PaymentPeriods(
'2014-09-15' --@ReferenceDate
,3 --@PaymentFrequency
,NULL --@PrevPayDate
,NULL --@StartDate
,'2015-03-15' --@FirstPayDate
,NULL --@InterimGracePeriodStartDate
,NULL --@InterimGracePeriodEndDate
,'2019-09-15' --@MaturityDate
)
This produces the following result.
|
Initial Grace Period
|
Interim Grace Period Month Start
|
Interim Grace Period Month End
|
Months Until First Payment
|
Interim Grace Period
|
Number Of Payments
|
|
6
|
0
|
0
|
6
|
0
|
19
|
In this example we modify the SQL so that there are no payments in 2018.
SELECT
*
FROM wct.PaymentPeriods(
'2014-09-15' --@ReferenceDate
,3 --@PaymentFrequency
,NULL --@PrevPayDate
,NULL --@StartDate
,'2015-03-15' --@FirstPayDate
,'2018-01-01' --@InterimGracePeriodStartDate
,'2019-01-01' --@InterimGracePeriodEndDate
,'2019-09-15' --@MaturityDate
)
This produces the following result.
|
Initial Grace Period
|
Interim Grace Period Month Start
|
Interim Grace Period Month End
|
Months Until First Payment
|
Interim Grace Period
|
Number Of Payments
|
|
6
|
40
|
52
|
6
|
12
|
16
|
In this example we modify the SQL eliminating setting @FirstPayDate, @InterimGracePeriodStartDate, and @InterimGracePeriodEndDate to NULL and setting @PrevPayDate to be one month prior to @ReferenceDate.
SELECT
*
FROM wct.PaymentPeriods(
'2014-09-15' --@ReferenceDate
,3 --@PaymentFrequency
,'2014-08-15' --@PrevPayDate
,NULL --@StartDate
,NULL --@FirstPayDate
,NULL --@InterimGracePeriodStartDate
,NULL --@InterimGracePeriodEndDate
,'2019-09-15' --@MaturityDate
)
This produces the following result.
|
Initial Grace Period
|
Interim Grace Period Month Start
|
Interim Grace Period Month End
|
Months Until First Payment
|
Interim Grace Period
|
Number Of Payments
|
|
0
|
0
|
0
|
2
|
0
|
21
|
See Also