PPNO

Updated 2023-10-11 13:47:44.307000

Syntax

SELECT [westclintech].[wct].[PPNO] (
  <@SettDate, datetime,>
 ,<@FirstPayDate, datetime,>
 ,<@pmtpyr, int,>
 ,<@NumPmts, int,>)

Description

Use the scalar function PPNO to calculate the previous payment number for loan with regularly scheduled periodic payments.

Arguments

@NumPmts

the total number of payments to be recorded over the life of the loan. @NumPmts is an expression of type int or of a type that can be implicitly converted to int.

@SettDate

the date from which you want to calculate the previous payment number. The previous payment number is always associated with the maximum payment date less than or equal to @SettDate. @SettDate is an expression of type datetime or of a type that can be implicitly converted to datetime.

@Pmtpyr

the number of loan payments made in a year. @NumPmts is an expression of type int or of a type that can be implicitly converted to int.

@FirstPayDate

the date that the first payment is due. @FirstPayDate is an expression of type datetime or of a type that can be implicitly converted to datetime.

Return Type

float

Remarks

If@SettDate < @FirstPayDate, NULL is returned.

@Pmtpyr must be between 1 and 365.

If @Pmtpyr = 13, then payments are calculated every 28 days from @FirstPayDate.

If @Pmtpyr = 26, then payments are calculated every 14 days from @FirstPayDate.

If @Pmtpyr = 52, then payments are calculated every 7 days from @FirstPayDate.

If @Pmtpyr = 1, then payments are calculated every 1 year from @FirstPayDate.

If @Pmtpyr = 2, then payments are calculated every 6 months from @FirstPayDate.

If @Pmtpyr = 3, then payments are calculated every 4 months from @FirstPayDate.

If @Pmtpyr = 4, then payments are calculated every 3 months from @FirstPayDate.

If @Pmtpyr = 6, then payments are calculated every 2 months from @FirstPayDate.

If @Pmtpyr = 12, then payments are calculated every 1 month from @FirstPayDate.

If @Pmtpyr = 24, then payments are calculated every semi-monthly from @FirstPayDate. If the @FirstPayDate is the 15th of the month, payments are on the 15th and the last day of the month. If the @FirstPayDate is the last day of the month, then payment dates are on the last day of the month and the 15th day of the month.

If @NumPmts IS NOT NULL, then PPD will not return a payment number greater than the number of payments.

Examples

Calculate the previous payment number on 11/1/2010 for a loan that commenced on 8/31/2008, paying interest monthly on the 15th of the month, starting 9/15/2008.

SELECT wct.PPNO(   '11/01/2010', --@SettDate
                   '09/15/2010', --@FirstPayDate
                   12,           --@Pmtpyr
                   NULL          --@NumPmts
               ) as PPNO;

This produces the following result.

{"columns":[{"field":"PPNO","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"PPNO":"2"}]}

Calculate the previous payment number on 10/29/2010 for a loan that commenced on 7/15/2009, paying interest every two weeks starting on 7/29/2009.

SELECT wct.PPNO(   '10/29/2010', --@SettDate
                   '7/29/2009',  --@FirstPayDate
                   26,           --@Pmtpyr
                   NULL          --@NumPmts
               ) as PPNO;

This produces the following result.

{"columns":[{"field":"PPNO","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"PPNO":"33"}]}

Calculate the next payment number on 10/29/2010 for a loan that commenced on 2/28/2009, paying interest semi-monthly starting on 3/15/2009.

SELECT wct.PPNO(   '10/29/2010', --@SettDate
                   '3/15/2009',  --@FirstPayDate
                   24,           --@Pmtpyr
                   NULL          --@NumPmts
               ) as PPNO;

This produces the following result.

{"columns":[{"field":"PPNO","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"PPNO":"39"}]}

Calculate the previous payment number on 12/6/2010 for a loan that commenced on 12/6/2008, paying interest monthly on the 15th of the month, starting 12/15/2010.

SELECT wct.PPNO(   '12/6/2010',  --@SettDate

                   '12/15/2010', --@FirstPayDate

                   12,           --@Pmtpyr

                   NULL          --@NumPmts

               ) as PPNO;

This produces the following result.

{"columns":[{"field":"PPNO"}],"rows":[{"PPNO":"NULL"}]}