ConstantPaymentAmount

Updated 2024-02-16 15:00:52.257000

Syntax

SELECT * FROM [westclintech].[wct].[ConstantPaymentAmount](
  <@OutstandingAmount, float,>
 ,<@InterestBasis, nvarchar(4000),>
 ,<@InterestRate, float,>
 ,<@PaymentFrequency, int,>
 ,<@PaymentAmount, float,>
 ,<@ReferenceDate, datetime,>
 ,<@PrevPayDate, datetime,>
 ,<@StartDate, datetime,>
 ,<@FirstPayDate, datetime,>
 ,<@GracePeriodStartDate, datetime,>
 ,<@GracePeriodEndDate, datetime,>)

Description

Use the table-valued function ConstantPaymentAmount to return the cash flow schedule for a loan with a fixed payment amount but no fixed maturity date. ConstantPaymentAmount computes the periodic interest and principal amounts until the loan balance has been brought to zero.

The payment period is entered in ConstantPaymentAmount as the number of months between payments. For example, a loan with monthly payments would have a frequency of 1. A loan with quarterly payments would have frequency of 3. A loan with annual payments would have a frequency of 12.

ConstantPaymentAmount supports both an initial grace period and an additional grace period during the life of the loan. All payments and their associated dates are calculated with respect to the reference date supplied to the function (which should not be confused with the start date). If an initial grace period is entered in ConstantPaymentAmount and it is greater than the reference rate, then it becomes the first interest payment date and subsequent interest payments are calculated from that date forward.

If any payments would otherwise occur in the specified grace period, then that payment is moved to the end of the grace period and all remaining payments are calculated from the end of the grace period.

If no initial grace period is specified then the first payment date is calculated using the interest payment frequency. If the start date has been entered and the number of months between the start date and the reference date is less than the frequency, then the first payment date is calculated by adding the frequency (as a number of months) to the start date.

If no start date has been entered but a previous payment date has been entered and the number of months between the previous payment date and the reference date is less than the frequency, then the first payment date is calculated by adding the frequency (as a number of months) to the previous payment date.

If there is no start date and previous payment dates or the number of months between those dates and the reference date is greater than the frequency, then the first payment date is calculated by adding the frequency (as a number of months) to the reference date.

All payments in the resultant table are moved to the end of the month and interest is calculated using these end-of-month dates.

The interest payment is calculated as:

I=P\times\left[\left(\left(1+\frac{R\times{F}}{12}\right)^{12\slash{F}}\right)^T\right]-1

Where:

{"columns":[{"field":"column 1"},{"field":"column 2"},{"field":"column 3"}],"rows":[{"column 1":"I","column 2":"=","column 3":"InterestPayment"},{"column 1":"P","column 2":"=","column 3":"@OutstandingAmount"},{"column 1":"R","column 2":"=","column 3":"@InterestRate"},{"column 1":"F","column 2":"=","column 3":"@Frequency"},{"column 1":"T","column 2":"=","column 3":"Time, in years, from PaymentDate ( Period -1) to PaymentDate"}]}

If the irregular period is longer than the regular period then the interest amount is broken out into the regular interest amount and a 'grace' interest amount.

Arguments

@InterestRate

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

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

@OutstandingAmount

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

@PaymentAmount

the amount of the periodic payment. @PaymentAmount is an expression that returns a float or float value, or a character string in date format.

@GracePeriodStartDate

the date on which the (interim) grace period commences. @GracePeriodStartDate is an expression that returns a datetime or smalldatetime value, or a character string in date format.

@PaymentFrequency

the number of months in a regular interest payment. @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.

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

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

@GracePeriodEndDate

the date on which the (interim) grace period concludes. @GracePeriodEndDate is an expression that returns a datetime or smalldatetime value, or a character string in date format.

@InterestBasis

the day count convention used to calculate the interest amount. @InterestBasis can be 30/360, Actual/360, Actual/365, or Actual/Actual. @InterestBasis is an expression of the character string data type category.

Return Type

table

{"columns": [{"field": "colName", "headerName": "Name", "header": "name"}, {"field": "colDatatype", "headerName": "Type", "header": "type"}, {"field": "colDesc", "headerName": "Description", "header": "description", "minWidth": 1000}], "rows": [{"id": "a5c8ee6c-c340-4380-8a62-f535e67f58d5", "colName": "Period", "colDatatype": "int", "colDesc": "A reference number uniquely identifying a row in the resultant table."}, {"id": "226c8ef4-13f5-40b9-a684-1e445a4e069b", "colName": "PrincipalPayment", "colDatatype": "float", "colDesc": "w in the resultant table. PrincipalPayment"}, {"id": "a679cc12-f098-4242-8ea1-ab2b7b2b84f1", "colName": "InterestPayment", "colDatatype": "float", "colDesc": "The amount of the regular interest payment."}, {"id": "b26eb276-1371-4500-8c89-f8a5b106bbb4", "colName": "CashFlow", "colDatatype": "float", "colDesc": "PrincipalPayment + InterestPayment + GraceInterest."}, {"id": "9277cedc-1289-4e94-9038-90713330c3f8", "colName": "OutstandingExposure", "colDatatype": "float", "colDesc": "When Period = 0 then @OutstandingAmount. For Period > 0 then OutstandingExposure(Period-1) + InterestPayment."}, {"id": "f38bd7a6-6b8f-4824-ba0b-e8f6677b9fae", "colName": "CapitalAmountInDebt", "colDatatype": "float", "colDesc": "When Period = 0, @OutstandingAmount. For Period > 0 then CapitalAmountInDebt(Period-1) \u2013 PrincipalPayment"}, {"id": "2459e38e-3bb3-413a-96e2-91d70ce5e5a9", "colName": "TotalExposure", "colDatatype": "float", "colDesc": "When Period = 0, @OutstandingAmount. For Period > 0 then CapitalAmountInDebt(Period-1) + InterestPayment"}, {"id": "93d0b87d-5789-4447-a294-b605425be136", "colName": "NumberOfMonth", "colDatatype": "int", "colDesc": "The number of months between the @ReferenceDate and the PaymentDate."}, {"id": "0d2df6ae-6dd9-4591-9467-16b4d330e57e", "colName": "PaymentDate", "colDatatype": "datetime", "colDesc": "The end-of-month date of the payment."}, {"id": "ec501853-6b5b-4228-95f1-068b505a935a", "colName": "GraceInterest", "colDatatype": "float", "colDesc": "The amount of the grace interest"}, {"id": "4a0efb97-ebf7-425e-9578-60ed65d13333", "colName": "InterestRate", "colDatatype": "float", "colDesc": "The interest amount from PaymentDate(Period-1) to PaymentDate assuming a principal amount of 1"}]}

Remarks

The PaymentDate for all rows is generated as the last day of the month.

For Period = 0, PrincipalPayment, InterestPayment, CashFlow, NumberOfMonth, GraceInterest, and InterestRate are set to 0.

If @Frequency is NULL then @Frequency = 1.

If @InterestRate is NULL then @InterestRate = 0.

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

GraceInterest is only calculated on @FirstPayDate and @GracePeriodEndDate.

GraceInterest is only calculated if NumberOfMonth – NumberOfMonth(Period-1) > @PaymentFrequency.

GraceInterest is the difference between the interest for the period from the previous row to the current row minus interest that would have been calculated for a period with length equal to @PaymentFrequency.

CashFlow may not equal PaymentAmount on @FirstPayDate or @GracePeriodEndDate due to GraceInterest.

The final payment is adjusted for CapitalAmountInDebt(Period-1) and the length of the period if it is less than @PaymentFrequency.

The last row returned will always be for the maturity date and may be shorter than a regular period depending on the combination of dates and @PaymentFrequency.