SQL Server UNEQUALLOANPAYMENTS Function

Updated 2023-10-11 14:48:55.630000

Description

Use the table-valued function UNEQUALLOANPAYMENTS to generate 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.

Syntax

SELECT * FROM [westclintech].[wct].[UNEQUALLOANPAYMENTS](
  <@PV, float,>
 ,<@Rate, float,>
 ,<@LoanDate, datetime,>
 ,<@InterestFrequency, int,>
 ,<@FirstPaymentDate, datetime,>
 ,<@DaysInYr, int,>
 ,<@PrinPaymentMultiple, int,>
 ,<@FirstPrinPayNo, int,>
 ,<@NumberOfPayments, int,>
 ,<@LastPaymentNumber, int,>
 ,<@FV, float,>
 ,<@IsRegPay, bit,>)

Arguments

@PV

the principal amount of the loan or lease. @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.

@InterestFrequency

the number if times that interest is paid in a year. @InterestFrequency 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 amount in the odd first period. @DaysInYr is an expression of type int or of a type that can be implicitly converted to int.

@PrinPaymentMultiple

the ratio of the frequency of the interest payments to the frequency of the interest payments. For example, a loan with monthly payments of interest and quarterly payments of principal would have a @PrinPaymentMultiple of 3. @PrinPaymentMultiple 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.

@NumberOfPayments

the total number of payments to be used in the calculation of the periodic payments. 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.

@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.

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 @InterestFrequency is NULL then @InterestFrequency = 12.

If @DaysInYr is NULL then @DaysInYr = 365.

If @NumberOfPayments is NULL then @NumberOfPayments = 1.

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

If @FV is NULL then @FV = 0.

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

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

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

@NumberOfPayments must be greater than 1.

@Rate must be greater than zero.

@DaysInYr must be 360 or 365.

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

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

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

If @FirstPrinPayNo < 2 then an error will be generated.

Examples

A 1,000,000 loan dated 2014-01-15 with monthly payments commencing on 2014-02-15 and quarterly principal payments commencing with the third interest payment. Notice that the quarterly payments are constant at 130,690.30 but that interest payments very from quarter to quarter though they are the same for each month between principal payments.

SELECT *
FROM wct.UnequalLoanPayments(   1000000,      --@PV
                                0.12,         --@Rate
                                '2014-01-15', --@LoanDate
                                12,           --@IntPmtPerYr
                                '2014-02-15', --@FirstPaymentDate
                                NULL,         --@DaysInYr
                                3,            --@PrinPayMultiple
                                3,            --@FirstPrinPayNo
                                24,           --@NumberOfPayments
                                NULL,         --@LastPaymentNumber
                                0,            --@FV
                                NULL          --@IsRegPay
                            ) k;

This produces the following result.

This loan, has monthly payments and is interest only for the first 6 months, with payments calculated assuming a 25-year maturity but payoff is at the end of 5 years.

SELECT *
FROM wct.UnequalLoanPayments(   1000000,      --@PV
                                0.05,         --@Rate
                                '2014-01-15', --@LoanDate
                                12,           --@IntPmtPerYr
                                '2014-02-15', --@FirstPaymentDate
                                NULL,         --@DaysInYr
                                1,            --@PrinPayMultiple
                                7,            --@FirstPrinPayNo
                                300,          --@NumberOfPayments
                                60,           --@LastPaymentNumber
                                0,            --@FV
                                NULL          --@IsRegPay
                            ) k;

This produces the following result.