Login     Register

        Contact Us     Search

XLeratorDB/financial Documentation

SQL Server next payment date function


NPD
 
Updated: 06 December 2010

Use NPD to calculate the next payment date for loan with regularly scheduled periodic payments.
Syntax
SELECT [wctFinancial].[wct].[NPD] (
  <@SettDate, datetime,>
 ,<@FirstPayDate, datetime,>
 ,<@Pmtpyr, int,>
 ,<@NumPmts, int,>)
Arguments
@SettDate
the date from which you want to calculate the next payment date. The next payment date is the minimum payment date greater than @SettDate. @SettDate is an expression of type datetime or of a type that can be implicitly converted to datetime.
@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.
@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.
@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.
Return Type
datetime
Remarks
·         If @SettDate < @FirstPayDate, then @FirstPayDate 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 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 NPD will not return a date greater than the maturity date of the loan.
Example
Calculate the next payment date 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.NPD(
      '11/01/2010'      --@SettDate
      ,'09/15/2010'     --@FirstPayDate
      ,12               --@Pmtpyr
      ,60               --@NumPmts
      ) as NPD
This produces the following result.
NPD
-----------------------
2010-11-15 00:00:00.000
 
(1 row(s) affected)

Calculate the next payment date 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.NPD(
      '10/29/2010'      --@SettDate
      ,'7/29/2009'      --@FirstPayDate
      ,26               --@Pmtpyr
      ,NULL             --@NumPmts
      ) as NPD
This produces the following result.
NPD
-----------------------
2010-11-03 00:00:00.000
 
(1 row(s) affected)

Calculate the next payment date on 10/29/2010 for a loan that commenced on 2/28/2009, paying interest every semi-monthly starting on 3/15/2009.
SELECT wct.NPD(
      '10/29/2010'      --@SettDate
      ,'3/15/2009'      --@FirstPayDate
      ,24               --@Pmtpyr
      ,NULL             --@NumPmts
) as NPD
This produces the following result.
NPD
-----------------------
2010-10-31 00:00:00.000
 
(1 row(s) affected)

Calculate the maturity date on a loan by entering a date far in the future.
SELECT wct.NPD(
      '12/31/2099'      --@SettDate
      ,'3/15/2009'      --@FirstPayDate
      ,26               --@Pmtpyr
      ,78               --@NumPmts
) as NPD
This produces the following result.
NPD
-----------------------
2012-03-11 00:00:00.000
 
(1 row(s) affected)

Calculate the next payment date on 12/6/2010 for a loan that commenced on 12/06/2010, paying interest monthly on the 15th of the month, starting 12/15/2010.
SELECT wct.NPD(
       '12/06/2010'     --@SettDate
      ,'12/15/2010'     --@FirstPayDate
      ,12               --@Pmtpyr
      ,NULL             --@NumPmts
      ) as NPD
This produces the following result.
NPD
-----------------------
2010-12-15 00:00:00.000
 
(1 row(s) affected)


Copyright 2008-2013 WestClinTech LLC         Privacy Policy        Terms of Service