CUMLPPMT
Updated 2023-10-10 22:17:43.443000
Syntax
SELECT [westclintech].[wct].[CUMLIPMT] (
<@PV, float,>
,<@LoanDate, datetime,>
,<@Rate, float,>
,<@FirstPayDate, datetime,>
,<@NumPmts, int,>
,<@Pmtpyr, int,>
,<@StartPer, int,>
,<@EndPer, int,>
,<@DaysInYr, int,>
,<@FV, float,>
,<@IntRule, nvarchar(4000),>)
Description
Use the scalar function CUMLPPMT to calculate the cumulative principal payments for a specified range of periods for a loan or lease. CUMLPPMT calculates the principal payment amount as the payment amount minus the interest payment amount. In the case of actuarial rule loans, this means that the principal payment may be negative, increasing the principal balance for the subsequent period. For US Rule loans, there will never be a negative principal payment arising from the interest payment.
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.
@StartPer
the period number for which you want to start accumulating the payment information. @StartPer is an expression of type int or of a type that can be implicitly converted to int.
@EndPer
the period number for which you want to stop accumulating the payment information. @EndPer 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
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.
@StartPer must be between 1 and @NumPmts.
@EndPer must be between @StartPer and @NumPmts.
If @StartPer = @EndPer then CUMLPPMT = LPPMT.
Examples
Calculate the first year’s cumulative principal 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.CUMLPPMT( 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
1, --Start Period Number
12, --End Period Number
NULL, --Days in year (defaults to 360)
NULL, --FV (defaults to 0)
NULL --IntRule (Defaults to 'A' for acturial)
) as CUMLPPMT;
This produces the following result.
{"columns":[{"field":"CUMLPPMT","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"CUMLPPMT":"8840.1583622327"}]}
Calculate the last year’s principal payments 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.CUMLPPMT( 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
49, --Start Period Number
60, --End Period Number
NULL, --Days in year (defaults to 360)
NULL, --FV (defaults to 0)
NULL --IntRule (Defaults to 'A' for acturial)
) as CUMLPPMT;
This produces the following result.
{"columns":[{"field":"CUMLPPMT","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"CUMLPPMT":"11231.3253815834"}]}
Calculate the cumulative principal payments for the first two periods on 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.CUMLPPMT( 150000, --Loan Amount
'10/13/2010', --Loan Start Date
.12, --Annual Interest Rate
'01/04/2011', --First Payment Date
52, --Number of payments (5*12)
13, --Number of payments per year
1, --Period Number
2, --Period Number
365, --Days in year
0, --FV
'U' --IntRule
) as CUMLPPMT;
This produces the following result.
{"columns":[{"field":"CUMLPPMT","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"CUMLPPMT":"1942.21293090094"}]}
Let’s look at the same cumulative principal payment, but this time we will specify the actuarial method as the interest rule.
SELECT wct.CUMLPPMT( 150000, --Loan Amount
'10/13/2010', --Loan Start Date
.12, --Annual Interest Rate
'01/04/2011', --First Payment Date
52, --Number of payments (5*12)
13, --Number of payments per year
1, --Period Number
2, --Period Number
365, --Days in year
0, --FV
'A' --IntRule
) as CUMLPPMT;
This produces the following result.
{"columns":[{"field":"CUMLPPMT","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"CUMLPPMT":"1938.83811031556"}]}
The difference between the interest payment amount and the payment amount under the actuarial method is added to the principal, resulting in a negative principal payment in the first period, increasing the outstanding principal balance.
If you don’t know the period number that you want, you can use the NPNO (Next Payment Number) or PPNO PPNO (Previous Payment Number) functions to calculate the period number.
In this example, we calculate the cumulative principal paid in 2010 for a 50,000, 5-year loan which originated on 9/15/2008 at a rate of 5.5% with monthly payments commencing on 10/15/2008.
SELECT wct.CUMLPPMT( 50000, --Loan Amount
'09/15/2008', --Loan Start Date
.055, --Annual Interest Rate
'10/15/2008', --First Payment Date
60, --Number of payments (5*12)
12, --Number of payments per year
wct.NPNO( '12/31/2009', --Settlement Date
'10/15/2008', --First Payment Date
12, --Payments per Yer
60 --Number of Payments
), --Start Period Number
wct.PPNO( '12/31/2010', --Settlement Date
'10/15/2008', --First Payment Date
12, --Payments per Yer
60 --Number of Payments
), --End Period Number
365, --Days in year
0, --FV
'A' --IntRule
) as [2010 Principal Payments];
This produces the following result.
{"columns":[{"field":"2010 Principal Payments","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"2010 Principal Payments":"9567.36286031945"}]}
In this example we calculate the cumulative principal payments on a 75,000 5-year loan made on Sep 15th with the first payment due on Jan 1st using the 365/360 basis.
SELECT wct.CUMLPPMT( 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
1, --Start Period Number
12, --End Period Number
365360, --Days in year (defaults to 360)
NULL, --FV (defaults to 0)
NULL --IntRule (Defaults to 'A' for acturial)
) as CUMLPPMT;
This produces the following result.
{"columns":[{"field":"CUMLPPMT","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"CUMLPPMT":"7655.41066235976"}]}