LPMT
Updated 2023-10-11 11:58:46.470000
Syntax
SELECT [westclintech].[wct].[LPMT] (
<@PV, float,>
,<@LoanDate, datetime,>
,<@Rate, float,>
,<@FirstPayDate, datetime,>
,<@NumPmts, int,>
,<@Pmtpyr, int,>
,<@DaysInYr, float,>
,<@FV, float,>
,<@IntRule, nvarchar(4000),>)
Description
Use the scalar function LPMT to calculate the periodic payment for a loan or lease. LPMT calculates periodic payment amounts for loans (leases) with odd first payment periods, with balloon payments at the end, and supports actuarial and US Rule interest accruals.
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.
@Rate
the annual interest rate. @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.
@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.
@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
float
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.
Examples
Calculate the payment amount for a 50,000, 5-year loan starting on 1 November with payments due on the first of every month. The rate on the loan is 6 per cent.
SELECT wct.LPMT( 50000, --Loan Amount
'11/01/2010', --Loan Start Date
.06, --Annual Interest Rate
'12/01/2010', --First Payment Date
60, --Number of payments (5*12)
12, --Number of payments per year
NULL, --Days in year (defaults to 360)
NULL, --FV (defaults to 0)
NULL --IntRule (defaults to 'A' meaning actuarial)
) as PMT;
This produces the following result.
{"columns":[{"field":"PMT","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"PMT":"966.640076471413"}]}
Calculate the payment amount for a 50,000, 5-year loan starting on 1 November with payments due every 4 weeks, starting on 1 December. The rate on the loan is 6 per cent.
SELECT wct.LPMT( 50000, --Loan Amount
'11/01/2010', --Loan Start Date
.06, --Annual Interest Rate
'12/01/2010', --First Payment Date
65, --Number of payments (5*13)
13, --Number of payments per year
365, --Days in year
NULL, --FV (defaults to 0)
NULL --IntRule (defaults to 'A' meaning actuarial)
) as PMT;
This produces the following result.
{"columns":[{"field":"PMT","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"PMT":"892.076053138519"}]}
Calculate the payment amount for a 50,000, 5-year loan starting on 1 November with payments due semi-monthly, starting on 1 December. The rate on the loan is 6 per cent and the loan has a balloon payment of 10,000 at the end.
SELECT wct.LPMT( 50000, --Loan Amount
'11/01/2010', --Loan Start Date
.06, --Annual Interest Rate
'12/01/2010', --First Payment Date
120, --Number of payments (5*24)
24, --Number of payments per year
360, --Days in year
10000, --FV
NULL --IntRule (defaults to 'A' meaning actuarial)
) as PMT;
This produces the following result.
{"columns":[{"field":"PMT","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"PMT":"412.446978104019"}]}
Calculate the payment amount for a 150,000 4-year loan at 12 percent interest using the US Rule made on 13 October with no payments due until 4 January and with payments due every 4 weeks after that.
SELECT wct.LPMT( 150000, --Loan Amount
'10/13/2010', --Loan Start Date
.12, --Annual Interest Rate
'01/04/2011', --First Payment Date
52, --Number of payments (4*13)
13, --Number of payments per year
365, --Days in year
0, --FV
'U' --IntRule (US Rule)
) as PMT;
This produces the following result.
{"columns":[{"field":"PMT","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"PMT":"3708.09276682132"}]}
Calculate the periodic payment for a 5-year 75,000 loan which starts on Sep 15th, has a first payment date of Jan 1st and a 365/360 interest basis.
SELECT wct.LPMT( 75000, --Loan Amount
'09/15/2022', --Loan Start Date
.18, --Annual Interest Rate
'01/01/2023', --First Payment Date
60, --Number of payments (5*12)
12, --Number of payments per year
365360, --Days in year (defaults to 360)
NULL, --FV (defaults to 0)
NULL --IntRule (Defaults to 'A' for acturial)
) as LPMT;
This produces the following result.
{"columns":[{"field":"LPMT","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"LPMT":"1987.77068214426"}]}