LPMTSCHED

Updated 2023-10-11 12:06:48.083000

Syntax

SELECT * FROM [westclintech].[wct].[LPMTSCHED] (
   <@PV, float,>
 ,<@LoanDate, datetime,>
 ,<@Pmt, float,>
 ,<@Rate, float,>
 ,<@FirstPayDate, datetime,>
 ,<@NumPmts, int,>
 ,<@Pmtpyr, int,>
 ,<@DaysInYr, int,>
 ,<@FV, float,>
 ,<@IntRule, nvarchar(4000),>
 ,<@Decimals, int,>
 ,<@LastPmtNum, int,>)

Description

Use the table-valued function LPMTSCHED to generate a loan amortization schedule, given the period payment amount, the principal amount (or present value amount), and the balloon payment (or future value amount). The amortization schedule includes the payment number, the payment date, and the principal amount at the beginning of the period, the interest amount for the period, the principal payment for the period, any deferred interest for the period, and the ending principal amount.

LPMTSCHED supports loans with odd first periods, does US Rule or actuarial interest calculations, and allows you to specify a terminal (or future) value for the loan. LPMTSCHED also lets you calculate the amortization schedule using a different term for the amortization and the maturity, with the final payment amount adjusted for the outstanding principal balance.

LPMTSCHED lets you enter the periodic payment, the interest rate, or both. If the rate is not entered, the rate is calculated from the periodic payment. If the periodic payment is not entered, the periodic payment is calculated from the rate. If both the periodic payment and the rate are entered, then the schedule then the interest portion of each period is calculated using the rate, and the principal payment portion is calculated using periodic payment minus that interest payment amount. This may result in an odd final payment or even in a reduction in the number of payments for the loan.

Arguments

@Pmtpyr

the number of loan payments made in a year. @Pmtpyr 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.

@LoanDate

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

@Pmt

the payment made each period. @Pmt 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.

@IntRule

Identifies the loan as conforming to the US Rule ('U') or the actuarial rule ('A') regarding the compounding of interest in the odd first period.

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

@LastPmtNum

the last payment number if the life of the loan is shorter than the amortization period. @LastPmtNum 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.

@Decimals

the number of decimal places to round the calculated amounts. @Decimals is an expression of type int or of a type that can be implicitly converted to int.

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

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

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": "dee6e9ea-9924-407a-8bec-57b50c7217c5", "colName": "num_pmt", "colDatatype": "int", "colDesc": "The payment number."}, {"id": "b815d69c-b869-498a-a8a8-bd1a89a1e967", "colName": "date_pmt", "colDatatype": "datetime", "colDesc": "The date of the payment."}, {"id": "38d3dafc-ec20-4677-a8dd-165c1145b995", "colName": "amt_prin_init", "colDatatype": "float", "colDesc": "The principal amount at the beginning of the period. For the first period, the principal amount is the amount of the loan, otherwise the principal amount is the ending principal amount from the prior period."}, {"id": "1be042c2-009d-46e7-a02b-9de4641a800d", "colName": "amt_pmt", "colDatatype": "float", "colDesc": "The payment amount supplied to the function."}, {"id": "c6b28601-24d3-40d1-92d6-27f4ba17c55b", "colName": "amt_int_pay", "colDatatype": "float", "colDesc": "The interest payable amount for the period. The interest amount is the period interest rate (@Rate/@Pmtpyr) multiplied by the principal amount at the beginning of the period (amt_prin_init) rounded to the number of decimal places (@Decimals)."}, {"id": "6edfb12e-2390-4fde-a618-860ff86af2ec", "colName": "amt_prin_pay", "colDatatype": "float", "colDesc": "The principal payment amount for the period. For actuarial accrual loans, the principal payment amount is the payment amount (amt_pmt) minus the interest payment amount (amt_int_pay). If the interest payment amount is greater than the payment amount, then the principal payment amount is negative. For US rule loans, the principal payment amount will always be greater than or equal to zero."}, {"id": "1a2bfb0d-ee3c-4e46-9b1e-262662753a15", "colName": "amt_int_def", "colDatatype": "float", "colDesc": "The interest deferral (or escrow) amount. For US Rule loans only. If the interest payment amount (amt_int_pay) is greater than the payment amount (amt_pmt) then the difference is put into this column. When the interest payments (amt_int_pay) become less than the periodic payment (amt_pmt), the interest deferral amount (amt_int_def) from prior periods are reduced to zero, before applying any amounts to principal payments (amt_prin_pay)."}, {"id": "ff7a285b-0b5d-413d-a459-88d343814e34", "colName": "amt_prin_end", "colDatatype": "float", "colDesc": "The ending principal amount. Calculated as the beginning principal amount (amt_prin_init) less the principal payment amount for the period (amt_prin_pay)."}]}

Remarks

If @DaysInYr is NULL, then @DaysInYr = 360.

If @FV is NULL, then @FV = 0.

If @IntRule is NULL, then @IntRule = 'A'.

@FirstPayDate must be greater than @LoanDate.

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

@NumPmts must be greater than 1.

@Rate must be greater than zero.

@DaysInYr must be 360, 364, 365, 365360 or 365365.

@PV must be greater than zero.

If @Decimals is NULL, then @Decimals = 2.

If @LastPmtNum is NULL, the @LastPmtNum = @NumPmts.

Examples

Generate the amortization schedule for a 50,000, 5-year loan starting on 1 November with payments due on the first of every month. The periodic payment is 966.64.

SELECT *

FROM wct.LPMTSCHED(   50000,        --Present Value

                      '11/01/2010', --Loan Date

                      966.64,       --Pmt

                      NULL,         --Rate

                      '12/01/2010', --First Payment Date

                      60,           --Number of Payments

                      12,           --Payments per Year

                      NULL,         --Days In Year

                      0,            --Future Value

                      NULL,         --Intrule

                      2,            --Decimals

                      NULL          --Last Payment Number

                  );