SQL Server CONSTPRINAMORT Function

Updated 2023-10-10 21:43:31.117000

Description

Use the table-valued function CONSTPRINAMORT to generate an amortization schedule for a loan with a fixed principal repayment.

Syntax

SELECT * FROM [westclintech].[wct].[CONSTPRINAMORT](
  <@PV, float,>
 ,<@Rate, float,>
 ,<@LoanDate, datetime,>
 ,<@NumPmtsPerYear, int,>
 ,<@FirstPaymentDate, datetime,>
 ,<@DaysInYr, int,>
 ,<@NumberOfPayments, int,>
 ,<@LastPaymentNumber, int,>
 ,<@FirstPrinPayNo, int,>
 ,<@FV, float,>
 ,<@PPMT, float,>
 ,<@eom, bit,>)

Arguments

@PV

the principal amount of the loan. @PV is an expression of type float or of a type that can be implicitly converted to float.

@Rate

the annual interest rate for the loan. @Rate is an expression of type float or of a type that can be implicitly converted to float.

@LoanDate

the date that the loan starts accruing interest. @LoanDate is an expression of type datetime or of a type that can be implicitly converted to datetime.

@NumPmtsPerYear

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

@FirstPaymentDate

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

@DaysInYr

the denominator number of days to be used in the calculation of the interest. @DaysInYr is an expression of type int or of a type that can be implicitly converted to int.

@NumberOfPayments

the total number of payments to be used in the calculation principal payment amount, if a principal payment amount (@PPMT) is not entered. This may not be the actual number of payments on the loan, which can be specified by using @LastPaymentNumber. @NumberOfPayments is an expression of type int or of a type that can be implicitly converted to int.

@LastPaymentNumber

the number of the last loan payment if different than the @NumberOfPayments. @LastPaymentNumber is an expression of type int or of a type that can be implicitly converted to int.

@FirstPrinPayNo

the payment number of the first principal payment. @FirstPrinPayNo is an expression of type int or of a type that can be implicitly converted to int.

@FV

the future value at the end of the loan. @FV is an expression of type float or of a type that can be implicitly converted to float.

@PPMT

the principal payment amount. @PPMT is an expression of type float or of a type that can be implicitly converted to float.

@eom

a bit value specifying that if the @FirstPaymentDate is the last day of the month and the @NumPmtsPerYear is 1,2,4 or 12 that all subsequent payments occur on the last of the month.

Return Type

table

Remarks

If @PV is NULL then @PV = 0.

If @Rate is NULL then @Rate = 0.

If @LoanDate is NULL then @LoanDate = GETDATE().

If @NumPmtsPerYear is NULL then @NumPmtsPerYear = 12.

If @DaysInYr is NULL then @DaysInYr = 365.

If @NumberOfPayments is NULL then @NumberOfPayments = 1.

If @FirstPrinPayNo is NULL then @FirstPrinPayNo = 1.

If @LastPaymentNumber is NULL then @LastPaymentNumber = @NumberOfPayments.

If @eom is NULL then @eom = 'TRUE'.

If @FirstPaymentDate is NULL then @FirstPaymentDate is calculated using @LoanDate and @NumPmtsPerYear.

@NumPmtsPerYear must be 1, 2, 3, 4, 6, 12, 13, 24, 26, 52 or 365.

@NumberOfPayments must be greater than 0.

@DaysInYr must be 360 or 365.

If @NumberOfPayments is less than 1 then an error will be generated.

If @LastPaymentNumber is less than 1 then an error will be generated.

If @FirstPrinPayNo is less than 1 then an error will be generated.

If @PPMT is NULL than @PPMT is calculated as (@PV – ISNULL(@FV, 0) / (@NumberOfPayments - @FirstPrinPayNo + 1).

If @NumPmtsPerYear = 365, 52, 26, or 13 the amt_int_pay is calculated assuming a 365 day year. If the first interest period is an odd period, then the amt_int_pay = @PV * @Rate * (@FirstPaymentDate - @LoanDate) / 365. For all other periods, the amt_int_pay = amt_prin_init * 364/@NumPmtsPerYear/365 * @Rate.

For all other values of @NumPmtsPerYear, amt_int_pay is calculated using @DaysInYr.

    If @DaysInYr = 360 and num_pmt > 1 then amt_int_pay = amt_prin_init * @Rate / @NumPmtsPerYear.

    If @DaysInYr = 360 and num_pmt = 1 and @FirstPaymentDate is a regular payment date then amt_int_pay is calculated as above, otherwise the calculation is amt_int_pay = @Rate * wct.YEARFRAC(start_date, d.date_pmt,0).

    If @DaysInYr = 365 and num_pmt > 1 then amt_int_pay = amt_prin_init * @Rate * wct.YEARFRAC(start_date, date_pmt,3) where start_date is date_pmt from the previous row.

Examples

A 1,000,000 loan dated 2014-05-14 with 50 monthly payments commencing on 2014-06-15. The interest rate is 6.0%.

SELECT *
FROM wct.CONSTPRINAMORT(   1000000,      --@PV
                           .06,          --@Rate
                           '2014-05-15', --@LoanDate
                           12,           --@NumPmtsPerYear
                           '2014-06-15', --@FirstPaymentDate
                           360,          --@DaysInYr
                           50,           --@NumberOfPayments
                           NULL,         --@LastPaymentNumber
                           NULL,         --@FirstPrinPayNo
                           NULL,         --@FV
                           NULL,         --@PPMT
                           NULL          --@eom
                       );

This produces the following result.

A 1,000,000 loan dated 2014-05-14 with 50 monthly payments commencing on 2014-06-30, with payments due at the end-of the month. The interest rate is 6.0%.

SELECT *
FROM wct.CONSTPRINAMORT(   1000000,      --@PV
                           .06,          --@Rate
                           '2014-05-15', --@LoanDate
                           12,           --@NumPmtsPerYear
                           '2014-06-30', --@FirstPaymentDate
                           360,          --@DaysInYr
                           50,           --@NumberOfPayments
                           NULL,         --@LastPaymentNumber
                           NULL,         --@FirstPrinPayNo
                           NULL,         --@FV
                           NULL,         --@PPMT
                           'True'        --@eom
                       );

This produces the following result.

In this example there are 26 payments per year and the principal is amortized as though the loan is maturing in 5 years, but it will be paid off, in full, in 2 years.

SELECT *
FROM wct.CONSTPRINAMORT(   1000000,      --@PV
                           .06,          --@Rate
                           '2014-05-15', --@LoanDate
                           26,           --@NumPmtsPerYear
                           '2014-06-30', --@FirstPaymentDate
                           365,          --@DaysInYr
                           130,          --@NumberOfPayments
                           52,           --@LastPaymentNumber
                           NULL,         --@FirstPrinPayNo
                           NULL,         --@FV
                           NULL,         --@PPMT
                           NULL          --@eom
                       );

This produces the following result.

In this example, no interest is due until the 14^th installment and then the principal will be paid down by 25,000 with each payment with balance paid off at maturity.

SELECT *
FROM wct.CONSTPRINAMORT(   1000000,      --@PV
                           .06,          --@Rate
                           '2014-05-15', --@LoanDate
                           26,           --@NumPmtsPerYear
                           '2014-06-30', --@FirstPaymentDate
                           365,          --@DaysInYr
                           52,           --@NumberOfPayments
                           NULL,         --@LastPaymentNumber
                           14,           --@FirstPrinPayNo
                           NULL,         --@FV
                           25000,        --@PPMT
                           NULL          --@eom
                       );

This produces the following result.

See Also

AMORTIZECASHFLOWS - Discounted cash flow schedule

AMORTSCHED - Amortization schedule for a loan with constant periodic payments

LPMTSCHED - Amortization schedule for a loan with constant periodic payemnts and an odd first period where interest is accrued using the US rule

UNEQUALLOANPAYMENTS - Calculate a payment schedule for a loan where the interest payment frequency and the principal payment frequency are different, or the loan starts with an interest only schedule with principal repayments commencing after the first interest payment date.