CONSTPRINAMORT
Updated 2023-10-10 21:43:31.117000
Syntax
SELECT * FROM [westclintech].[wct].[CONSTPRINAMORT](
<@PV, float,>
,<@Rate, float,>
,<@LoanDate, datetime,>
,<@NumPmtsPerYear, int,>
,<@FirstPaymentDate, datetime,>
,<@DaysInYr, int,>
,<@NumberOfPayments, int,>
,<@LastPaymentNumber, int,>
,<@FirstPrinPayNo, int,>
,<@FV, float,>
,<@PPMT, float,>
,<@eom, bit,>)
Description
Use the table-valued function CONSTPRINAMORT to generate an amortization schedule for a loan with a fixed principal repayment.
Arguments
@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.
@PPMT
the principal payment amount. @PPMT 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.
@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.
@NumberOfPayments
the total number of payments to be used in the calculation principal payment amount, if a principal payment amount (@PPMT) is not entered. 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.
@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.
@eom
a bit value specifying that if the @FirstPaymentDate is the last day of the month and the @NumPmtsPerYear is 1,2,4 or 12 that all subsequent payments occur on the last of the month.
@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.
@NumPmtsPerYear
the number of payments in a year. @NumPmtsPerYear 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.
@DaysInYr
the denominator number of days to be used in the calculation of the interest. @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. @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": "beb0675b-8723-4b12-886f-d30daa9bde47", "colName": "num_pmt", "colDatatype": "int", "colDesc": "The monotonically calculated payment number with the payment number on the first payment date = 1."}, {"id": "1dab8333-44dc-445b-b582-0c67b3accba1", "colName": "date_pmt", "colDatatype": "datetime", "colDesc": "The date of the payment calculated chronologically from @FirstPaymentDate."}, {"id": "8d5cdbb7-0281-4465-9495-d1f87adfd22d", "colName": "amt_prin_init", "colDatatype": "float", "colDesc": "The principal amount at the beginning of the period. When num_pmt is equal to 0, the principal amount is 0, otherwise the principal amount is the ending principal amount where num_pmt = num_pmt - 1."}, {"id": "b821cd26-487f-4656-a7a6-fa8b88a57369", "colName": "amt_pmt", "colDatatype": "float", "colDesc": "amt_int_pay + amt_prin_pay."}, {"id": "59d89f45-b4dc-4d98-b022-4da8c48e15b0", "colName": "amt_int_pay", "colDatatype": "float", "colDesc": "The interest amount for the period. The interest amount is calculated using @Rate, @DaysInYr, and amt_prin_init. See Remarks for more information on the calculation of amt_int_pay"}, {"id": "a8c7a08c-e1c1-494c-8667-8bcbbe8e72d9", "colName": "amt_prin_pay", "colDatatype": "float", "colDesc": "The principal payment amount. See Remarks for more information."}, {"id": "5bb936f7-d8e4-487d-ac76-29f47627dd5c", "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 @PV is NULL then @PV = 0.
If @Rate is NULL then @Rate = 0.
If @LoanDate is NULL then @LoanDate = GETDATE().
If @NumPmtsPerYear is NULL then @NumPmtsPerYear = 12.
If @DaysInYr is NULL then @DaysInYr = 365.
If @NumberOfPayments is NULL then @NumberOfPayments = 1.
If @FirstPrinPayNo is NULL then @FirstPrinPayNo = 1.
If @LastPaymentNumber is NULL then @LastPaymentNumber = @NumberOfPayments.
If @eom is NULL then @eom = 'TRUE'.
If @FirstPaymentDate is NULL then @FirstPaymentDate is calculated using @LoanDate and @NumPmtsPerYear.
@NumPmtsPerYear must be 1, 2, 3, 4, 6, 12, 13, 24, 26, 52 or 365.
@NumberOfPayments must be greater than 0.
@DaysInYr must be 360 or 365.
If @NumberOfPayments is less than 1 then an error will be generated.
If @LastPaymentNumber is less than 1 then an error will be generated.
If @FirstPrinPayNo is less than 1 then an error will be generated.
If @PPMT is NULL than @PPMT is calculated as (@PV – ISNULL(@FV, 0) / (@NumberOfPayments - @FirstPrinPayNo + 1).
If @NumPmtsPerYear = 365, 52, 26, or 13 the amt_int_pay is calculated assuming a 365 day year. If the first interest period is an odd period, then the amt_int_pay = @PV * @Rate * (@FirstPaymentDate - @LoanDate) / 365. For all other periods, the amt_int_pay = amt_prin_init * 364/@NumPmtsPerYear/365 * @Rate.
For all other values of @NumPmtsPerYear, amt_int_pay is calculated using @DaysInYr.If @DaysInYr = 360 and num_pmt > 1 then amt_int_pay = amt_prin_init * @Rate / @NumPmtsPerYear.If @DaysInYr = 360 and num_pmt = 1 and @FirstPaymentDate is a regular payment date then amt_int_pay is calculated as above, otherwise the calculation is amt_int_pay = @Rate * wct.YEARFRAC(start_date, d.date_pmt,0).If @DaysInYr = 365 and num_pmt > 1 then amt_int_pay = amt_prin_init * @Rate * wct.YEARFRAC(start_date, date_pmt,3) where start_date is date_pmt from the previous row.
Examples
A 1,000,000 loan dated 2014-05-14 with 50 monthly payments commencing on 2014-06-15. The interest rate is 6.0%.
SELECT *
FROM wct.CONSTPRINAMORT( 1000000, --@PV
.06, --@Rate
'2014-05-15', --@LoanDate
12, --@NumPmtsPerYear
'2014-06-15', --@FirstPaymentDate
360, --@DaysInYr
50, --@NumberOfPayments
NULL, --@LastPaymentNumber
NULL, --@FirstPrinPayNo
NULL, --@FV
NULL, --@PPMT
NULL --@eom
);
This produces the following result.
{"columns":[{"field":"num_pmt","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"date_pmt","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"amt_prin_init","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"amt_pmt","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"amt_int_pay","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"amt_prin_pay","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"amt_prin_end","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"num_pmt":"0","date_pmt":"2014-05-15 00:00:00.000","amt_prin_init":"0","amt_pmt":"0","amt_int_pay":"0","amt_prin_pay":"0","amt_prin_end":"1000000"},{"num_pmt":"1","date_pmt":"2014-06-15 00:00:00.000","amt_prin_init":"1000000","amt_pmt":"25000","amt_int_pay":"5000","amt_prin_pay":"20000","amt_prin_end":"980000"},{"num_pmt":"2","date_pmt":"2014-07-15 00:00:00.000","amt_prin_init":"980000","amt_pmt":"24900","amt_int_pay":"4900","amt_prin_pay":"20000","amt_prin_end":"960000"},{"num_pmt":"3","date_pmt":"2014-08-15 00:00:00.000","amt_prin_init":"960000","amt_pmt":"24800","amt_int_pay":"4800","amt_prin_pay":"20000","amt_prin_end":"940000"},{"num_pmt":"4","date_pmt":"2014-09-15 00:00:00.000","amt_prin_init":"940000","amt_pmt":"24700","amt_int_pay":"4700","amt_prin_pay":"20000","amt_prin_end":"920000"},{"num_pmt":"5","date_pmt":"2014-10-15 00:00:00.000","amt_prin_init":"920000","amt_pmt":"24600","amt_int_pay":"4600","amt_prin_pay":"20000","amt_prin_end":"900000"},{"num_pmt":"6","date_pmt":"2014-11-15 00:00:00.000","amt_prin_init":"900000","amt_pmt":"24500","amt_int_pay":"4500","amt_prin_pay":"20000","amt_prin_end":"880000"},{"num_pmt":"7","date_pmt":"2014-12-15 00:00:00.000","amt_prin_init":"880000","amt_pmt":"24400","amt_int_pay":"4400","amt_prin_pay":"20000","amt_prin_end":"860000"},{"num_pmt":"8","date_pmt":"2015-01-15 00:00:00.000","amt_prin_init":"860000","amt_pmt":"24300","amt_int_pay":"4300","amt_prin_pay":"20000","amt_prin_end":"840000"},{"num_pmt":"9","date_pmt":"2015-02-15 00:00:00.000","amt_prin_init":"840000","amt_pmt":"24200","amt_int_pay":"4200","amt_prin_pay":"20000","amt_prin_end":"820000"},{"num_pmt":"10","date_pmt":"2015-03-15 00:00:00.000","amt_prin_init":"820000","amt_pmt":"24100","amt_int_pay":"4100","amt_prin_pay":"20000","amt_prin_end":"800000"},{"num_pmt":"11","date_pmt":"2015-04-15 00:00:00.000","amt_prin_init":"800000","amt_pmt":"24000","amt_int_pay":"4000","amt_prin_pay":"20000","amt_prin_end":"780000"},{"num_pmt":"12","date_pmt":"2015-05-15 00:00:00.000","amt_prin_init":"780000","amt_pmt":"23900","amt_int_pay":"3900","amt_prin_pay":"20000","amt_prin_end":"760000"},{"num_pmt":"13","date_pmt":"2015-06-15 00:00:00.000","amt_prin_init":"760000","amt_pmt":"23800","amt_int_pay":"3800","amt_prin_pay":"20000","amt_prin_end":"740000"},{"num_pmt":"14","date_pmt":"2015-07-15 00:00:00.000","amt_prin_init":"740000","amt_pmt":"23700","amt_int_pay":"3700","amt_prin_pay":"20000","amt_prin_end":"720000"},{"num_pmt":"15","date_pmt":"2015-08-15 00:00:00.000","amt_prin_init":"720000","amt_pmt":"23600","amt_int_pay":"3600","amt_prin_pay":"20000","amt_prin_end":"700000"},{"num_pmt":"16","date_pmt":"2015-09-15 00:00:00.000","amt_prin_init":"700000","amt_pmt":"23500","amt_int_pay":"3500","amt_prin_pay":"20000","amt_prin_end":"680000"},{"num_pmt":"17","date_pmt":"2015-10-15 00:00:00.000","amt_prin_init":"680000","amt_pmt":"23400","amt_int_pay":"3400","amt_prin_pay":"20000","amt_prin_end":"660000"},{"num_pmt":"18","date_pmt":"2015-11-15 00:00:00.000","amt_prin_init":"660000","amt_pmt":"23300","amt_int_pay":"3300","amt_prin_pay":"20000","amt_prin_end":"640000"},{"num_pmt":"19","date_pmt":"2015-12-15 00:00:00.000","amt_prin_init":"640000","amt_pmt":"23200","amt_int_pay":"3200","amt_prin_pay":"20000","amt_prin_end":"620000"},{"num_pmt":"20","date_pmt":"2016-01-15 00:00:00.000","amt_prin_init":"620000","amt_pmt":"23100","amt_int_pay":"3100","amt_prin_pay":"20000","amt_prin_end":"600000"},{"num_pmt":"21","date_pmt":"2016-02-15 00:00:00.000","amt_prin_init":"600000","amt_pmt":"23000","amt_int_pay":"3000","amt_prin_pay":"20000","amt_prin_end":"580000"},{"num_pmt":"22","date_pmt":"2016-03-15 00:00:00.000","amt_prin_init":"580000","amt_pmt":"22900","amt_int_pay":"2900","amt_prin_pay":"20000","amt_prin_end":"560000"},{"num_pmt":"23","date_pmt":"2016-04-15 00:00:00.000","amt_prin_init":"560000","amt_pmt":"22800","amt_int_pay":"2800","amt_prin_pay":"20000","amt_prin_end":"540000"},{"num_pmt":"24","date_pmt":"2016-05-15 00:00:00.000","amt_prin_init":"540000","amt_pmt":"22700","amt_int_pay":"2700","amt_prin_pay":"20000","amt_prin_end":"520000"},{"num_pmt":"25","date_pmt":"2016-06-15 00:00:00.000","amt_prin_init":"520000","amt_pmt":"22600","amt_int_pay":"2600","amt_prin_pay":"20000","amt_prin_end":"500000"},{"num_pmt":"26","date_pmt":"2016-07-15 00:00:00.000","amt_prin_init":"500000","amt_pmt":"22500","amt_int_pay":"2500","amt_prin_pay":"20000","amt_prin_end":"480000"},{"num_pmt":"27","date_pmt":"2016-08-15 00:00:00.000","amt_prin_init":"480000","amt_pmt":"22400","amt_int_pay":"2400","amt_prin_pay":"20000","amt_prin_end":"460000"},{"num_pmt":"28","date_pmt":"2016-09-15 00:00:00.000","amt_prin_init":"460000","amt_pmt":"22300","amt_int_pay":"2300","amt_prin_pay":"20000","amt_prin_end":"440000"},{"num_pmt":"29","date_pmt":"2016-10-15 00:00:00.000","amt_prin_init":"440000","amt_pmt":"22200","amt_int_pay":"2200","amt_prin_pay":"20000","amt_prin_end":"420000"},{"num_pmt":"30","date_pmt":"2016-11-15 00:00:00.000","amt_prin_init":"420000","amt_pmt":"22100","amt_int_pay":"2100","amt_prin_pay":"20000","amt_prin_end":"400000"},{"num_pmt":"31","date_pmt":"2016-12-15 00:00:00.000","amt_prin_init":"400000","amt_pmt":"22000","amt_int_pay":"2000","amt_prin_pay":"20000","amt_prin_end":"380000"},{"num_pmt":"32","date_pmt":"2017-01-15 00:00:00.000","amt_prin_init":"380000","amt_pmt":"21900","amt_int_pay":"1900","amt_prin_pay":"20000","amt_prin_end":"360000"},{"num_pmt":"33","date_pmt":"2017-02-15 00:00:00.000","amt_prin_init":"360000","amt_pmt":"21800","amt_int_pay":"1800","amt_prin_pay":"20000","amt_prin_end":"340000"},{"num_pmt":"34","date_pmt":"2017-03-15 00:00:00.000","amt_prin_init":"340000","amt_pmt":"21700","amt_int_pay":"1700","amt_prin_pay":"20000","amt_prin_end":"320000"},{"num_pmt":"35","date_pmt":"2017-04-15 00:00:00.000","amt_prin_init":"320000","amt_pmt":"21600","amt_int_pay":"1600","amt_prin_pay":"20000","amt_prin_end":"300000"},{"num_pmt":"36","date_pmt":"2017-05-15 00:00:00.000","amt_prin_init":"300000","amt_pmt":"21500","amt_int_pay":"1500","amt_prin_pay":"20000","amt_prin_end":"280000"},{"num_pmt":"37","date_pmt":"2017-06-15 00:00:00.000","amt_prin_init":"280000","amt_pmt":"21400","amt_int_pay":"1400","amt_prin_pay":"20000","amt_prin_end":"260000"},{"num_pmt":"38","date_pmt":"2017-07-15 00:00:00.000","amt_prin_init":"260000","amt_pmt":"21300","amt_int_pay":"1300","amt_prin_pay":"20000","amt_prin_end":"240000"},{"num_pmt":"39","date_pmt":"2017-08-15 00:00:00.000","amt_prin_init":"240000","amt_pmt":"21200","amt_int_pay":"1200","amt_prin_pay":"20000","amt_prin_end":"220000"},{"num_pmt":"40","date_pmt":"2017-09-15 00:00:00.000","amt_prin_init":"220000","amt_pmt":"21100","amt_int_pay":"1100","amt_prin_pay":"20000","amt_prin_end":"200000"},{"num_pmt":"41","date_pmt":"2017-10-15 00:00:00.000","amt_prin_init":"200000","amt_pmt":"21000","amt_int_pay":"1000","amt_prin_pay":"20000","amt_prin_end":"180000"},{"num_pmt":"42","date_pmt":"2017-11-15 00:00:00.000","amt_prin_init":"180000","amt_pmt":"20900","amt_int_pay":"900","amt_prin_pay":"20000","amt_prin_end":"160000"},{"num_pmt":"43","date_pmt":"2017-12-15 00:00:00.000","amt_prin_init":"160000","amt_pmt":"20800","amt_int_pay":"800","amt_prin_pay":"20000","amt_prin_end":"140000"},{"num_pmt":"44","date_pmt":"2018-01-15 00:00:00.000","amt_prin_init":"140000","amt_pmt":"20700","amt_int_pay":"700","amt_prin_pay":"20000","amt_prin_end":"120000"},{"num_pmt":"45","date_pmt":"2018-02-15 00:00:00.000","amt_prin_init":"120000","amt_pmt":"20600","amt_int_pay":"600","amt_prin_pay":"20000","amt_prin_end":"100000"},{"num_pmt":"46","date_pmt":"2018-03-15 00:00:00.000","amt_prin_init":"100000","amt_pmt":"20500","amt_int_pay":"500","amt_prin_pay":"20000","amt_prin_end":"80000"},{"num_pmt":"47","date_pmt":"2018-04-15 00:00:00.000","amt_prin_init":"80000","amt_pmt":"20400","amt_int_pay":"400","amt_prin_pay":"20000","amt_prin_end":"60000"},{"num_pmt":"48","date_pmt":"2018-05-15 00:00:00.000","amt_prin_init":"60000","amt_pmt":"20300","amt_int_pay":"300","amt_prin_pay":"20000","amt_prin_end":"40000"},{"num_pmt":"49","date_pmt":"2018-06-15 00:00:00.000","amt_prin_init":"40000","amt_pmt":"20200","amt_int_pay":"200","amt_prin_pay":"20000","amt_prin_end":"20000"},{"num_pmt":"50","date_pmt":"2018-07-15 00:00:00.000","amt_prin_init":"20000","amt_pmt":"20100","amt_int_pay":"100","amt_prin_pay":"20000","amt_prin_end":"0"}]}
A 1,000,000 loan dated 2014-05-14 with 50 monthly payments commencing on 2014-06-30, with payments due at the end-of the month. The interest rate is 6.0%.
SELECT *
FROM wct.CONSTPRINAMORT( 1000000, --@PV
.06, --@Rate
'2014-05-15', --@LoanDate
12, --@NumPmtsPerYear
'2014-06-30', --@FirstPaymentDate
360, --@DaysInYr
50, --@NumberOfPayments
NULL, --@LastPaymentNumber
NULL, --@FirstPrinPayNo
NULL, --@FV
NULL, --@PPMT
'True' --@eom
);
This produces the following result.
{"columns":[{"field":"num_pmt","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"date_pmt","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"amt_prin_init","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"amt_pmt","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"amt_int_pay","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"amt_prin_pay","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"amt_prin_end","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"num_pmt":"0","date_pmt":"2014-05-15 00:00:00.000","amt_prin_init":"0","amt_pmt":"0","amt_int_pay":"0","amt_prin_pay":"0","amt_prin_end":"1000000"},{"num_pmt":"1","date_pmt":"2014-06-30 00:00:00.000","amt_prin_init":"1000000","amt_pmt":"27500","amt_int_pay":"7500","amt_prin_pay":"20000","amt_prin_end":"980000"},{"num_pmt":"2","date_pmt":"2014-07-31 00:00:00.000","amt_prin_init":"980000","amt_pmt":"24900","amt_int_pay":"4900","amt_prin_pay":"20000","amt_prin_end":"960000"},{"num_pmt":"3","date_pmt":"2014-08-31 00:00:00.000","amt_prin_init":"960000","amt_pmt":"24800","amt_int_pay":"4800","amt_prin_pay":"20000","amt_prin_end":"940000"},{"num_pmt":"4","date_pmt":"2014-09-30 00:00:00.000","amt_prin_init":"940000","amt_pmt":"24700","amt_int_pay":"4700","amt_prin_pay":"20000","amt_prin_end":"920000"},{"num_pmt":"5","date_pmt":"2014-10-31 00:00:00.000","amt_prin_init":"920000","amt_pmt":"24600","amt_int_pay":"4600","amt_prin_pay":"20000","amt_prin_end":"900000"},{"num_pmt":"6","date_pmt":"2014-11-30 00:00:00.000","amt_prin_init":"900000","amt_pmt":"24500","amt_int_pay":"4500","amt_prin_pay":"20000","amt_prin_end":"880000"},{"num_pmt":"7","date_pmt":"2014-12-31 00:00:00.000","amt_prin_init":"880000","amt_pmt":"24400","amt_int_pay":"4400","amt_prin_pay":"20000","amt_prin_end":"860000"},{"num_pmt":"8","date_pmt":"2015-01-31 00:00:00.000","amt_prin_init":"860000","amt_pmt":"24300","amt_int_pay":"4300","amt_prin_pay":"20000","amt_prin_end":"840000"},{"num_pmt":"9","date_pmt":"2015-02-28 00:00:00.000","amt_prin_init":"840000","amt_pmt":"24200","amt_int_pay":"4200","amt_prin_pay":"20000","amt_prin_end":"820000"},{"num_pmt":"10","date_pmt":"2015-03-31 00:00:00.000","amt_prin_init":"820000","amt_pmt":"24100","amt_int_pay":"4100","amt_prin_pay":"20000","amt_prin_end":"800000"},{"num_pmt":"11","date_pmt":"2015-04-30 00:00:00.000","amt_prin_init":"800000","amt_pmt":"24000","amt_int_pay":"4000","amt_prin_pay":"20000","amt_prin_end":"780000"},{"num_pmt":"12","date_pmt":"2015-05-31 00:00:00.000","amt_prin_init":"780000","amt_pmt":"23900","amt_int_pay":"3900","amt_prin_pay":"20000","amt_prin_end":"760000"},{"num_pmt":"13","date_pmt":"2015-06-30 00:00:00.000","amt_prin_init":"760000","amt_pmt":"23800","amt_int_pay":"3800","amt_prin_pay":"20000","amt_prin_end":"740000"},{"num_pmt":"14","date_pmt":"2015-07-31 00:00:00.000","amt_prin_init":"740000","amt_pmt":"23700","amt_int_pay":"3700","amt_prin_pay":"20000","amt_prin_end":"720000"},{"num_pmt":"15","date_pmt":"2015-08-31 00:00:00.000","amt_prin_init":"720000","amt_pmt":"23600","amt_int_pay":"3600","amt_prin_pay":"20000","amt_prin_end":"700000"},{"num_pmt":"16","date_pmt":"2015-09-30 00:00:00.000","amt_prin_init":"700000","amt_pmt":"23500","amt_int_pay":"3500","amt_prin_pay":"20000","amt_prin_end":"680000"},{"num_pmt":"17","date_pmt":"2015-10-31 00:00:00.000","amt_prin_init":"680000","amt_pmt":"23400","amt_int_pay":"3400","amt_prin_pay":"20000","amt_prin_end":"660000"},{"num_pmt":"18","date_pmt":"2015-11-30 00:00:00.000","amt_prin_init":"660000","amt_pmt":"23300","amt_int_pay":"3300","amt_prin_pay":"20000","amt_prin_end":"640000"},{"num_pmt":"19","date_pmt":"2015-12-31 00:00:00.000","amt_prin_init":"640000","amt_pmt":"23200","amt_int_pay":"3200","amt_prin_pay":"20000","amt_prin_end":"620000"},{"num_pmt":"20","date_pmt":"2016-01-31 00:00:00.000","amt_prin_init":"620000","amt_pmt":"23100","amt_int_pay":"3100","amt_prin_pay":"20000","amt_prin_end":"600000"},{"num_pmt":"21","date_pmt":"2016-02-29 00:00:00.000","amt_prin_init":"600000","amt_pmt":"23000","amt_int_pay":"3000","amt_prin_pay":"20000","amt_prin_end":"580000"},{"num_pmt":"22","date_pmt":"2016-03-31 00:00:00.000","amt_prin_init":"580000","amt_pmt":"22900","amt_int_pay":"2900","amt_prin_pay":"20000","amt_prin_end":"560000"},{"num_pmt":"23","date_pmt":"2016-04-30 00:00:00.000","amt_prin_init":"560000","amt_pmt":"22800","amt_int_pay":"2800","amt_prin_pay":"20000","amt_prin_end":"540000"},{"num_pmt":"24","date_pmt":"2016-05-31 00:00:00.000","amt_prin_init":"540000","amt_pmt":"22700","amt_int_pay":"2700","amt_prin_pay":"20000","amt_prin_end":"520000"},{"num_pmt":"25","date_pmt":"2016-06-30 00:00:00.000","amt_prin_init":"520000","amt_pmt":"22600","amt_int_pay":"2600","amt_prin_pay":"20000","amt_prin_end":"500000"},{"num_pmt":"26","date_pmt":"2016-07-31 00:00:00.000","amt_prin_init":"500000","amt_pmt":"22500","amt_int_pay":"2500","amt_prin_pay":"20000","amt_prin_end":"480000"},{"num_pmt":"27","date_pmt":"2016-08-31 00:00:00.000","amt_prin_init":"480000","amt_pmt":"22400","amt_int_pay":"2400","amt_prin_pay":"20000","amt_prin_end":"460000"},{"num_pmt":"28","date_pmt":"2016-09-30 00:00:00.000","amt_prin_init":"460000","amt_pmt":"22300","amt_int_pay":"2300","amt_prin_pay":"20000","amt_prin_end":"440000"},{"num_pmt":"29","date_pmt":"2016-10-31 00:00:00.000","amt_prin_init":"440000","amt_pmt":"22200","amt_int_pay":"2200","amt_prin_pay":"20000","amt_prin_end":"420000"},{"num_pmt":"30","date_pmt":"2016-11-30 00:00:00.000","amt_prin_init":"420000","amt_pmt":"22100","amt_int_pay":"2100","amt_prin_pay":"20000","amt_prin_end":"400000"},{"num_pmt":"31","date_pmt":"2016-12-31 00:00:00.000","amt_prin_init":"400000","amt_pmt":"22000","amt_int_pay":"2000","amt_prin_pay":"20000","amt_prin_end":"380000"},{"num_pmt":"32","date_pmt":"2017-01-31 00:00:00.000","amt_prin_init":"380000","amt_pmt":"21900","amt_int_pay":"1900","amt_prin_pay":"20000","amt_prin_end":"360000"},{"num_pmt":"33","date_pmt":"2017-02-28 00:00:00.000","amt_prin_init":"360000","amt_pmt":"21800","amt_int_pay":"1800","amt_prin_pay":"20000","amt_prin_end":"340000"},{"num_pmt":"34","date_pmt":"2017-03-31 00:00:00.000","amt_prin_init":"340000","amt_pmt":"21700","amt_int_pay":"1700","amt_prin_pay":"20000","amt_prin_end":"320000"},{"num_pmt":"35","date_pmt":"2017-04-30 00:00:00.000","amt_prin_init":"320000","amt_pmt":"21600","amt_int_pay":"1600","amt_prin_pay":"20000","amt_prin_end":"300000"},{"num_pmt":"36","date_pmt":"2017-05-31 00:00:00.000","amt_prin_init":"300000","amt_pmt":"21500","amt_int_pay":"1500","amt_prin_pay":"20000","amt_prin_end":"280000"},{"num_pmt":"37","date_pmt":"2017-06-30 00:00:00.000","amt_prin_init":"280000","amt_pmt":"21400","amt_int_pay":"1400","amt_prin_pay":"20000","amt_prin_end":"260000"},{"num_pmt":"38","date_pmt":"2017-07-31 00:00:00.000","amt_prin_init":"260000","amt_pmt":"21300","amt_int_pay":"1300","amt_prin_pay":"20000","amt_prin_end":"240000"},{"num_pmt":"39","date_pmt":"2017-08-31 00:00:00.000","amt_prin_init":"240000","amt_pmt":"21200","amt_int_pay":"1200","amt_prin_pay":"20000","amt_prin_end":"220000"},{"num_pmt":"40","date_pmt":"2017-09-30 00:00:00.000","amt_prin_init":"220000","amt_pmt":"21100","amt_int_pay":"1100","amt_prin_pay":"20000","amt_prin_end":"200000"},{"num_pmt":"41","date_pmt":"2017-10-31 00:00:00.000","amt_prin_init":"200000","amt_pmt":"21000","amt_int_pay":"1000","amt_prin_pay":"20000","amt_prin_end":"180000"},{"num_pmt":"42","date_pmt":"2017-11-30 00:00:00.000","amt_prin_init":"180000","amt_pmt":"20900","amt_int_pay":"900","amt_prin_pay":"20000","amt_prin_end":"160000"},{"num_pmt":"43","date_pmt":"2017-12-31 00:00:00.000","amt_prin_init":"160000","amt_pmt":"20800","amt_int_pay":"800","amt_prin_pay":"20000","amt_prin_end":"140000"},{"num_pmt":"44","date_pmt":"2018-01-31 00:00:00.000","amt_prin_init":"140000","amt_pmt":"20700","amt_int_pay":"700","amt_prin_pay":"20000","amt_prin_end":"120000"},{"num_pmt":"45","date_pmt":"2018-02-28 00:00:00.000","amt_prin_init":"120000","amt_pmt":"20600","amt_int_pay":"600","amt_prin_pay":"20000","amt_prin_end":"100000"},{"num_pmt":"46","date_pmt":"2018-03-31 00:00:00.000","amt_prin_init":"100000","amt_pmt":"20500","amt_int_pay":"500","amt_prin_pay":"20000","amt_prin_end":"80000"},{"num_pmt":"47","date_pmt":"2018-04-30 00:00:00.000","amt_prin_init":"80000","amt_pmt":"20400","amt_int_pay":"400","amt_prin_pay":"20000","amt_prin_end":"60000"},{"num_pmt":"48","date_pmt":"2018-05-31 00:00:00.000","amt_prin_init":"60000","amt_pmt":"20300","amt_int_pay":"300","amt_prin_pay":"20000","amt_prin_end":"40000"},{"num_pmt":"49","date_pmt":"2018-06-30 00:00:00.000","amt_prin_init":"40000","amt_pmt":"20200","amt_int_pay":"200","amt_prin_pay":"20000","amt_prin_end":"20000"},{"num_pmt":"50","date_pmt":"2018-07-31 00:00:00.000","amt_prin_init":"20000","amt_pmt":"20100","amt_int_pay":"100","amt_prin_pay":"20000","amt_prin_end":"0"}]}
In this example there are 26 payments per year and the principal is amortized as though the loan is maturing in 5 years, but it will be paid off, in full, in 2 years.
SELECT *
FROM wct.CONSTPRINAMORT( 1000000, --@PV
.06, --@Rate
'2014-05-15', --@LoanDate
26, --@NumPmtsPerYear
'2014-06-30', --@FirstPaymentDate
365, --@DaysInYr
130, --@NumberOfPayments
52, --@LastPaymentNumber
NULL, --@FirstPrinPayNo
NULL, --@FV
NULL, --@PPMT
NULL --@eom
);
This produces the following result.
{"columns":[{"field":"num_pmt","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"date_pmt","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"amt_prin_init","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"amt_pmt","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"amt_int_pay","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"amt_prin_pay","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"amt_prin_end","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"num_pmt":"0","date_pmt":"2014-05-15 00:00:00.000","amt_prin_init":"0","amt_pmt":"0","amt_int_pay":"0","amt_prin_pay":"0","amt_prin_end":"1000000"},{"num_pmt":"1","date_pmt":"2014-06-30 00:00:00.000","amt_prin_init":"1000000","amt_pmt":"15253.9515279241","amt_int_pay":"7561.64383561644","amt_prin_pay":"7692.30769230769","amt_prin_end":"992307.692307692"},{"num_pmt":"2","date_pmt":"2014-07-14 00:00:00.000","amt_prin_init":"992307.692307692","amt_pmt":"9975.97471022129","amt_int_pay":"2283.66701791359","amt_prin_pay":"7692.30769230769","amt_prin_end":"984615.384615385"},{"num_pmt":"3","date_pmt":"2014-07-28 00:00:00.000","amt_prin_init":"984615.384615385","amt_pmt":"9958.27186512118","amt_int_pay":"2265.96417281349","amt_prin_pay":"7692.30769230769","amt_prin_end":"976923.076923077"},{"num_pmt":"4","date_pmt":"2014-08-11 00:00:00.000","amt_prin_init":"976923.076923077","amt_pmt":"9940.56902002107","amt_int_pay":"2248.26132771338","amt_prin_pay":"7692.30769230769","amt_prin_end":"969230.769230769"},{"num_pmt":"5","date_pmt":"2014-08-25 00:00:00.000","amt_prin_init":"969230.769230769","amt_pmt":"9922.86617492097","amt_int_pay":"2230.55848261328","amt_prin_pay":"7692.30769230769","amt_prin_end":"961538.461538461"},{"num_pmt":"6","date_pmt":"2014-09-08 00:00:00.000","amt_prin_init":"961538.461538461","amt_pmt":"9905.16332982086","amt_int_pay":"2212.85563751317","amt_prin_pay":"7692.30769230769","amt_prin_end":"953846.153846154"},{"num_pmt":"7","date_pmt":"2014-09-22 00:00:00.000","amt_prin_init":"953846.153846154","amt_pmt":"9887.46048472076","amt_int_pay":"2195.15279241307","amt_prin_pay":"7692.30769230769","amt_prin_end":"946153.846153846"},{"num_pmt":"8","date_pmt":"2014-10-06 00:00:00.000","amt_prin_init":"946153.846153846","amt_pmt":"9869.75763962065","amt_int_pay":"2177.44994731296","amt_prin_pay":"7692.30769230769","amt_prin_end":"938461.538461538"},{"num_pmt":"9","date_pmt":"2014-10-20 00:00:00.000","amt_prin_init":"938461.538461538","amt_pmt":"9852.05479452055","amt_int_pay":"2159.74710221285","amt_prin_pay":"7692.30769230769","amt_prin_end":"930769.23076923"},{"num_pmt":"10","date_pmt":"2014-11-03 00:00:00.000","amt_prin_init":"930769.23076923","amt_pmt":"9834.35194942044","amt_int_pay":"2142.04425711275","amt_prin_pay":"7692.30769230769","amt_prin_end":"923076.923076923"},{"num_pmt":"11","date_pmt":"2014-11-17 00:00:00.000","amt_prin_init":"923076.923076923","amt_pmt":"9816.64910432034","amt_int_pay":"2124.34141201264","amt_prin_pay":"7692.30769230769","amt_prin_end":"915384.615384615"},{"num_pmt":"12","date_pmt":"2014-12-01 00:00:00.000","amt_prin_init":"915384.615384615","amt_pmt":"9798.94625922023","amt_int_pay":"2106.63856691254","amt_prin_pay":"7692.30769230769","amt_prin_end":"907692.307692307"},{"num_pmt":"13","date_pmt":"2014-12-15 00:00:00.000","amt_prin_init":"907692.307692307","amt_pmt":"9781.24341412012","amt_int_pay":"2088.93572181243","amt_prin_pay":"7692.30769230769","amt_prin_end":"899999.999999999"},{"num_pmt":"14","date_pmt":"2014-12-29 00:00:00.000","amt_prin_init":"899999.999999999","amt_pmt":"9763.54056902002","amt_int_pay":"2071.23287671233","amt_prin_pay":"7692.30769230769","amt_prin_end":"892307.692307692"},{"num_pmt":"15","date_pmt":"2015-01-12 00:00:00.000","amt_prin_init":"892307.692307692","amt_pmt":"9745.83772391991","amt_int_pay":"2053.53003161222","amt_prin_pay":"7692.30769230769","amt_prin_end":"884615.384615384"},{"num_pmt":"16","date_pmt":"2015-01-26 00:00:00.000","amt_prin_init":"884615.384615384","amt_pmt":"9728.13487881981","amt_int_pay":"2035.82718651212","amt_prin_pay":"7692.30769230769","amt_prin_end":"876923.076923076"},{"num_pmt":"17","date_pmt":"2015-02-09 00:00:00.000","amt_prin_init":"876923.076923076","amt_pmt":"9710.4320337197","amt_int_pay":"2018.12434141201","amt_prin_pay":"7692.30769230769","amt_prin_end":"869230.769230768"},{"num_pmt":"18","date_pmt":"2015-02-23 00:00:00.000","amt_prin_init":"869230.769230768","amt_pmt":"9692.7291886196","amt_int_pay":"2000.42149631191","amt_prin_pay":"7692.30769230769","amt_prin_end":"861538.461538461"},{"num_pmt":"19","date_pmt":"2015-03-09 00:00:00.000","amt_prin_init":"861538.461538461","amt_pmt":"9675.02634351949","amt_int_pay":"1982.7186512118","amt_prin_pay":"7692.30769230769","amt_prin_end":"853846.153846153"},{"num_pmt":"20","date_pmt":"2015-03-23 00:00:00.000","amt_prin_init":"853846.153846153","amt_pmt":"9657.32349841939","amt_int_pay":"1965.01580611169","amt_prin_pay":"7692.30769230769","amt_prin_end":"846153.846153845"},{"num_pmt":"21","date_pmt":"2015-04-06 00:00:00.000","amt_prin_init":"846153.846153845","amt_pmt":"9639.62065331928","amt_int_pay":"1947.31296101159","amt_prin_pay":"7692.30769230769","amt_prin_end":"838461.538461537"},{"num_pmt":"22","date_pmt":"2015-04-20 00:00:00.000","amt_prin_init":"838461.538461537","amt_pmt":"9621.91780821918","amt_int_pay":"1929.61011591148","amt_prin_pay":"7692.30769230769","amt_prin_end":"830769.23076923"},{"num_pmt":"23","date_pmt":"2015-05-04 00:00:00.000","amt_prin_init":"830769.23076923","amt_pmt":"9604.21496311907","amt_int_pay":"1911.90727081138","amt_prin_pay":"7692.30769230769","amt_prin_end":"823076.923076922"},{"num_pmt":"24","date_pmt":"2015-05-18 00:00:00.000","amt_prin_init":"823076.923076922","amt_pmt":"9586.51211801896","amt_int_pay":"1894.20442571127","amt_prin_pay":"7692.30769230769","amt_prin_end":"815384.615384614"},{"num_pmt":"25","date_pmt":"2015-06-01 00:00:00.000","amt_prin_init":"815384.615384614","amt_pmt":"9568.80927291886","amt_int_pay":"1876.50158061117","amt_prin_pay":"7692.30769230769","amt_prin_end":"807692.307692306"},{"num_pmt":"26","date_pmt":"2015-06-15 00:00:00.000","amt_prin_init":"807692.307692306","amt_pmt":"9551.10642781875","amt_int_pay":"1858.79873551106","amt_prin_pay":"7692.30769230769","amt_prin_end":"799999.999999999"},{"num_pmt":"27","date_pmt":"2015-06-29 00:00:00.000","amt_prin_init":"799999.999999999","amt_pmt":"9533.40358271865","amt_int_pay":"1841.09589041096","amt_prin_pay":"7692.30769230769","amt_prin_end":"792307.692307691"},{"num_pmt":"28","date_pmt":"2015-07-13 00:00:00.000","amt_prin_init":"792307.692307691","amt_pmt":"9515.70073761854","amt_int_pay":"1823.39304531085","amt_prin_pay":"7692.30769230769","amt_prin_end":"784615.384615383"},{"num_pmt":"29","date_pmt":"2015-07-27 00:00:00.000","amt_prin_init":"784615.384615383","amt_pmt":"9497.99789251844","amt_int_pay":"1805.69020021074","amt_prin_pay":"7692.30769230769","amt_prin_end":"776923.076923075"},{"num_pmt":"30","date_pmt":"2015-08-10 00:00:00.000","amt_prin_init":"776923.076923075","amt_pmt":"9480.29504741833","amt_int_pay":"1787.98735511064","amt_prin_pay":"7692.30769230769","amt_prin_end":"769230.769230768"},{"num_pmt":"31","date_pmt":"2015-08-24 00:00:00.000","amt_prin_init":"769230.769230768","amt_pmt":"9462.59220231823","amt_int_pay":"1770.28451001053","amt_prin_pay":"7692.30769230769","amt_prin_end":"761538.46153846"},{"num_pmt":"32","date_pmt":"2015-09-07 00:00:00.000","amt_prin_init":"761538.46153846","amt_pmt":"9444.88935721812","amt_int_pay":"1752.58166491043","amt_prin_pay":"7692.30769230769","amt_prin_end":"753846.153846152"},{"num_pmt":"33","date_pmt":"2015-09-21 00:00:00.000","amt_prin_init":"753846.153846152","amt_pmt":"9427.18651211801","amt_int_pay":"1734.87881981032","amt_prin_pay":"7692.30769230769","amt_prin_end":"746153.846153844"},{"num_pmt":"34","date_pmt":"2015-10-05 00:00:00.000","amt_prin_init":"746153.846153844","amt_pmt":"9409.48366701791","amt_int_pay":"1717.17597471022","amt_prin_pay":"7692.30769230769","amt_prin_end":"738461.538461537"},{"num_pmt":"35","date_pmt":"2015-10-19 00:00:00.000","amt_prin_init":"738461.538461537","amt_pmt":"9391.7808219178","amt_int_pay":"1699.47312961011","amt_prin_pay":"7692.30769230769","amt_prin_end":"730769.230769229"},{"num_pmt":"36","date_pmt":"2015-11-02 00:00:00.000","amt_prin_init":"730769.230769229","amt_pmt":"9374.0779768177","amt_int_pay":"1681.77028451001","amt_prin_pay":"7692.30769230769","amt_prin_end":"723076.923076921"},{"num_pmt":"37","date_pmt":"2015-11-16 00:00:00.000","amt_prin_init":"723076.923076921","amt_pmt":"9356.37513171759","amt_int_pay":"1664.0674394099","amt_prin_pay":"7692.30769230769","amt_prin_end":"715384.615384613"},{"num_pmt":"38","date_pmt":"2015-11-30 00:00:00.000","amt_prin_init":"715384.615384613","amt_pmt":"9338.67228661749","amt_int_pay":"1646.3645943098","amt_prin_pay":"7692.30769230769","amt_prin_end":"707692.307692306"},{"num_pmt":"39","date_pmt":"2015-12-14 00:00:00.000","amt_prin_init":"707692.307692306","amt_pmt":"9320.96944151738","amt_int_pay":"1628.66174920969","amt_prin_pay":"7692.30769230769","amt_prin_end":"699999.999999998"},{"num_pmt":"40","date_pmt":"2015-12-28 00:00:00.000","amt_prin_init":"699999.999999998","amt_pmt":"9303.26659641728","amt_int_pay":"1610.95890410958","amt_prin_pay":"7692.30769230769","amt_prin_end":"692307.69230769"},{"num_pmt":"41","date_pmt":"2016-01-11 00:00:00.000","amt_prin_init":"692307.69230769","amt_pmt":"9285.56375131717","amt_int_pay":"1593.25605900948","amt_prin_pay":"7692.30769230769","amt_prin_end":"684615.384615382"},{"num_pmt":"42","date_pmt":"2016-01-25 00:00:00.000","amt_prin_init":"684615.384615382","amt_pmt":"9267.86090621707","amt_int_pay":"1575.55321390937","amt_prin_pay":"7692.30769230769","amt_prin_end":"676923.076923075"},{"num_pmt":"43","date_pmt":"2016-02-08 00:00:00.000","amt_prin_init":"676923.076923075","amt_pmt":"9250.15806111696","amt_int_pay":"1557.85036880927","amt_prin_pay":"7692.30769230769","amt_prin_end":"669230.769230767"},{"num_pmt":"44","date_pmt":"2016-02-22 00:00:00.000","amt_prin_init":"669230.769230767","amt_pmt":"9232.45521601686","amt_int_pay":"1540.14752370916","amt_prin_pay":"7692.30769230769","amt_prin_end":"661538.461538459"},{"num_pmt":"45","date_pmt":"2016-03-07 00:00:00.000","amt_prin_init":"661538.461538459","amt_pmt":"9214.75237091675","amt_int_pay":"1522.44467860906","amt_prin_pay":"7692.30769230769","amt_prin_end":"653846.153846151"},{"num_pmt":"46","date_pmt":"2016-03-21 00:00:00.000","amt_prin_init":"653846.153846151","amt_pmt":"9197.04952581664","amt_int_pay":"1504.74183350895","amt_prin_pay":"7692.30769230769","amt_prin_end":"646153.846153844"},{"num_pmt":"47","date_pmt":"2016-04-04 00:00:00.000","amt_prin_init":"646153.846153844","amt_pmt":"9179.34668071654","amt_int_pay":"1487.03898840885","amt_prin_pay":"7692.30769230769","amt_prin_end":"638461.538461536"},{"num_pmt":"48","date_pmt":"2016-04-18 00:00:00.000","amt_prin_init":"638461.538461536","amt_pmt":"9161.64383561643","amt_int_pay":"1469.33614330874","amt_prin_pay":"7692.30769230769","amt_prin_end":"630769.230769228"},{"num_pmt":"49","date_pmt":"2016-05-02 00:00:00.000","amt_prin_init":"630769.230769228","amt_pmt":"9143.94099051633","amt_int_pay":"1451.63329820863","amt_prin_pay":"7692.30769230769","amt_prin_end":"623076.92307692"},{"num_pmt":"50","date_pmt":"2016-05-16 00:00:00.000","amt_prin_init":"623076.92307692","amt_pmt":"9126.23814541622","amt_int_pay":"1433.93045310853","amt_prin_pay":"7692.30769230769","amt_prin_end":"615384.615384613"},{"num_pmt":"51","date_pmt":"2016-05-30 00:00:00.000","amt_prin_init":"615384.615384613","amt_pmt":"9108.53530031612","amt_int_pay":"1416.22760800842","amt_prin_pay":"7692.30769230769","amt_prin_end":"607692.307692305"},{"num_pmt":"52","date_pmt":"2016-06-13 00:00:00.000","amt_prin_init":"607692.307692305","amt_pmt":"609090.832455213","amt_int_pay":"1398.52476290832","amt_prin_pay":"607692.307692305","amt_prin_end":"0"}]}
In this example, no interest is due until the 14th installment and then the principal will be paid down by 25,000 with each payment with balance paid off at maturity.
SELECT *
FROM wct.CONSTPRINAMORT( 1000000, --@PV
.06, --@Rate
'2014-05-15', --@LoanDate
26, --@NumPmtsPerYear
'2014-06-30', --@FirstPaymentDate
365, --@DaysInYr
52, --@NumberOfPayments
NULL, --@LastPaymentNumber
14, --@FirstPrinPayNo
NULL, --@FV
25000, --@PPMT
NULL --@eom
);
This produces the following result.
{"columns":[{"field":"num_pmt","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"date_pmt","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"amt_prin_init","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"amt_pmt","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"amt_int_pay","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"amt_prin_pay","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"amt_prin_end","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"num_pmt":"0","date_pmt":"2014-05-15 00:00:00.000","amt_prin_init":"0","amt_pmt":"0","amt_int_pay":"0","amt_prin_pay":"0","amt_prin_end":"1000000"},{"num_pmt":"1","date_pmt":"2014-06-30 00:00:00.000","amt_prin_init":"1000000","amt_pmt":"7561.64383561644","amt_int_pay":"7561.64383561644","amt_prin_pay":"0","amt_prin_end":"1000000"},{"num_pmt":"2","date_pmt":"2014-07-14 00:00:00.000","amt_prin_init":"1000000","amt_pmt":"2301.3698630137","amt_int_pay":"2301.3698630137","amt_prin_pay":"0","amt_prin_end":"1000000"},{"num_pmt":"3","date_pmt":"2014-07-28 00:00:00.000","amt_prin_init":"1000000","amt_pmt":"2301.3698630137","amt_int_pay":"2301.3698630137","amt_prin_pay":"0","amt_prin_end":"1000000"},{"num_pmt":"4","date_pmt":"2014-08-11 00:00:00.000","amt_prin_init":"1000000","amt_pmt":"2301.3698630137","amt_int_pay":"2301.3698630137","amt_prin_pay":"0","amt_prin_end":"1000000"},{"num_pmt":"5","date_pmt":"2014-08-25 00:00:00.000","amt_prin_init":"1000000","amt_pmt":"2301.3698630137","amt_int_pay":"2301.3698630137","amt_prin_pay":"0","amt_prin_end":"1000000"},{"num_pmt":"6","date_pmt":"2014-09-08 00:00:00.000","amt_prin_init":"1000000","amt_pmt":"2301.3698630137","amt_int_pay":"2301.3698630137","amt_prin_pay":"0","amt_prin_end":"1000000"},{"num_pmt":"7","date_pmt":"2014-09-22 00:00:00.000","amt_prin_init":"1000000","amt_pmt":"2301.3698630137","amt_int_pay":"2301.3698630137","amt_prin_pay":"0","amt_prin_end":"1000000"},{"num_pmt":"8","date_pmt":"2014-10-06 00:00:00.000","amt_prin_init":"1000000","amt_pmt":"2301.3698630137","amt_int_pay":"2301.3698630137","amt_prin_pay":"0","amt_prin_end":"1000000"},{"num_pmt":"9","date_pmt":"2014-10-20 00:00:00.000","amt_prin_init":"1000000","amt_pmt":"2301.3698630137","amt_int_pay":"2301.3698630137","amt_prin_pay":"0","amt_prin_end":"1000000"},{"num_pmt":"10","date_pmt":"2014-11-03 00:00:00.000","amt_prin_init":"1000000","amt_pmt":"2301.3698630137","amt_int_pay":"2301.3698630137","amt_prin_pay":"0","amt_prin_end":"1000000"},{"num_pmt":"11","date_pmt":"2014-11-17 00:00:00.000","amt_prin_init":"1000000","amt_pmt":"2301.3698630137","amt_int_pay":"2301.3698630137","amt_prin_pay":"0","amt_prin_end":"1000000"},{"num_pmt":"12","date_pmt":"2014-12-01 00:00:00.000","amt_prin_init":"1000000","amt_pmt":"2301.3698630137","amt_int_pay":"2301.3698630137","amt_prin_pay":"0","amt_prin_end":"1000000"},{"num_pmt":"13","date_pmt":"2014-12-15 00:00:00.000","amt_prin_init":"1000000","amt_pmt":"2301.3698630137","amt_int_pay":"2301.3698630137","amt_prin_pay":"0","amt_prin_end":"1000000"},{"num_pmt":"14","date_pmt":"2014-12-29 00:00:00.000","amt_prin_init":"1000000","amt_pmt":"27301.3698630137","amt_int_pay":"2301.3698630137","amt_prin_pay":"25000","amt_prin_end":"975000"},{"num_pmt":"15","date_pmt":"2015-01-12 00:00:00.000","amt_prin_init":"975000","amt_pmt":"27243.8356164384","amt_int_pay":"2243.83561643836","amt_prin_pay":"25000","amt_prin_end":"950000"},{"num_pmt":"16","date_pmt":"2015-01-26 00:00:00.000","amt_prin_init":"950000","amt_pmt":"27186.301369863","amt_int_pay":"2186.30136986301","amt_prin_pay":"25000","amt_prin_end":"925000"},{"num_pmt":"17","date_pmt":"2015-02-09 00:00:00.000","amt_prin_init":"925000","amt_pmt":"27128.7671232877","amt_int_pay":"2128.76712328767","amt_prin_pay":"25000","amt_prin_end":"900000"},{"num_pmt":"18","date_pmt":"2015-02-23 00:00:00.000","amt_prin_init":"900000","amt_pmt":"27071.2328767123","amt_int_pay":"2071.23287671233","amt_prin_pay":"25000","amt_prin_end":"875000"},{"num_pmt":"19","date_pmt":"2015-03-09 00:00:00.000","amt_prin_init":"875000","amt_pmt":"27013.698630137","amt_int_pay":"2013.69863013699","amt_prin_pay":"25000","amt_prin_end":"850000"},{"num_pmt":"20","date_pmt":"2015-03-23 00:00:00.000","amt_prin_init":"850000","amt_pmt":"26956.1643835616","amt_int_pay":"1956.16438356164","amt_prin_pay":"25000","amt_prin_end":"825000"},{"num_pmt":"21","date_pmt":"2015-04-06 00:00:00.000","amt_prin_init":"825000","amt_pmt":"26898.6301369863","amt_int_pay":"1898.6301369863","amt_prin_pay":"25000","amt_prin_end":"800000"},{"num_pmt":"22","date_pmt":"2015-04-20 00:00:00.000","amt_prin_init":"800000","amt_pmt":"26841.095890411","amt_int_pay":"1841.09589041096","amt_prin_pay":"25000","amt_prin_end":"775000"},{"num_pmt":"23","date_pmt":"2015-05-04 00:00:00.000","amt_prin_init":"775000","amt_pmt":"26783.5616438356","amt_int_pay":"1783.56164383562","amt_prin_pay":"25000","amt_prin_end":"750000"},{"num_pmt":"24","date_pmt":"2015-05-18 00:00:00.000","amt_prin_init":"750000","amt_pmt":"26726.0273972603","amt_int_pay":"1726.02739726027","amt_prin_pay":"25000","amt_prin_end":"725000"},{"num_pmt":"25","date_pmt":"2015-06-01 00:00:00.000","amt_prin_init":"725000","amt_pmt":"26668.4931506849","amt_int_pay":"1668.49315068493","amt_prin_pay":"25000","amt_prin_end":"700000"},{"num_pmt":"26","date_pmt":"2015-06-15 00:00:00.000","amt_prin_init":"700000","amt_pmt":"26610.9589041096","amt_int_pay":"1610.95890410959","amt_prin_pay":"25000","amt_prin_end":"675000"},{"num_pmt":"27","date_pmt":"2015-06-29 00:00:00.000","amt_prin_init":"675000","amt_pmt":"26553.4246575342","amt_int_pay":"1553.42465753425","amt_prin_pay":"25000","amt_prin_end":"650000"},{"num_pmt":"28","date_pmt":"2015-07-13 00:00:00.000","amt_prin_init":"650000","amt_pmt":"26495.8904109589","amt_int_pay":"1495.8904109589","amt_prin_pay":"25000","amt_prin_end":"625000"},{"num_pmt":"29","date_pmt":"2015-07-27 00:00:00.000","amt_prin_init":"625000","amt_pmt":"26438.3561643836","amt_int_pay":"1438.35616438356","amt_prin_pay":"25000","amt_prin_end":"600000"},{"num_pmt":"30","date_pmt":"2015-08-10 00:00:00.000","amt_prin_init":"600000","amt_pmt":"26380.8219178082","amt_int_pay":"1380.82191780822","amt_prin_pay":"25000","amt_prin_end":"575000"},{"num_pmt":"31","date_pmt":"2015-08-24 00:00:00.000","amt_prin_init":"575000","amt_pmt":"26323.2876712329","amt_int_pay":"1323.28767123288","amt_prin_pay":"25000","amt_prin_end":"550000"},{"num_pmt":"32","date_pmt":"2015-09-07 00:00:00.000","amt_prin_init":"550000","amt_pmt":"26265.7534246575","amt_int_pay":"1265.75342465753","amt_prin_pay":"25000","amt_prin_end":"525000"},{"num_pmt":"33","date_pmt":"2015-09-21 00:00:00.000","amt_prin_init":"525000","amt_pmt":"26208.2191780822","amt_int_pay":"1208.21917808219","amt_prin_pay":"25000","amt_prin_end":"500000"},{"num_pmt":"34","date_pmt":"2015-10-05 00:00:00.000","amt_prin_init":"500000","amt_pmt":"26150.6849315069","amt_int_pay":"1150.68493150685","amt_prin_pay":"25000","amt_prin_end":"475000"},{"num_pmt":"35","date_pmt":"2015-10-19 00:00:00.000","amt_prin_init":"475000","amt_pmt":"26093.1506849315","amt_int_pay":"1093.15068493151","amt_prin_pay":"25000","amt_prin_end":"450000"},{"num_pmt":"36","date_pmt":"2015-11-02 00:00:00.000","amt_prin_init":"450000","amt_pmt":"26035.6164383562","amt_int_pay":"1035.61643835616","amt_prin_pay":"25000","amt_prin_end":"425000"},{"num_pmt":"37","date_pmt":"2015-11-16 00:00:00.000","amt_prin_init":"425000","amt_pmt":"25978.0821917808","amt_int_pay":"978.082191780822","amt_prin_pay":"25000","amt_prin_end":"400000"},{"num_pmt":"38","date_pmt":"2015-11-30 00:00:00.000","amt_prin_init":"400000","amt_pmt":"25920.5479452055","amt_int_pay":"920.547945205479","amt_prin_pay":"25000","amt_prin_end":"375000"},{"num_pmt":"39","date_pmt":"2015-12-14 00:00:00.000","amt_prin_init":"375000","amt_pmt":"25863.0136986301","amt_int_pay":"863.013698630137","amt_prin_pay":"25000","amt_prin_end":"350000"},{"num_pmt":"40","date_pmt":"2015-12-28 00:00:00.000","amt_prin_init":"350000","amt_pmt":"25805.4794520548","amt_int_pay":"805.479452054794","amt_prin_pay":"25000","amt_prin_end":"325000"},{"num_pmt":"41","date_pmt":"2016-01-11 00:00:00.000","amt_prin_init":"325000","amt_pmt":"25747.9452054795","amt_int_pay":"747.945205479452","amt_prin_pay":"25000","amt_prin_end":"300000"},{"num_pmt":"42","date_pmt":"2016-01-25 00:00:00.000","amt_prin_init":"300000","amt_pmt":"25690.4109589041","amt_int_pay":"690.41095890411","amt_prin_pay":"25000","amt_prin_end":"275000"},{"num_pmt":"43","date_pmt":"2016-02-08 00:00:00.000","amt_prin_init":"275000","amt_pmt":"25632.8767123288","amt_int_pay":"632.876712328767","amt_prin_pay":"25000","amt_prin_end":"250000"},{"num_pmt":"44","date_pmt":"2016-02-22 00:00:00.000","amt_prin_init":"250000","amt_pmt":"25575.3424657534","amt_int_pay":"575.342465753425","amt_prin_pay":"25000","amt_prin_end":"225000"},{"num_pmt":"45","date_pmt":"2016-03-07 00:00:00.000","amt_prin_init":"225000","amt_pmt":"25517.8082191781","amt_int_pay":"517.808219178082","amt_prin_pay":"25000","amt_prin_end":"200000"},{"num_pmt":"46","date_pmt":"2016-03-21 00:00:00.000","amt_prin_init":"200000","amt_pmt":"25460.2739726027","amt_int_pay":"460.27397260274","amt_prin_pay":"25000","amt_prin_end":"175000"},{"num_pmt":"47","date_pmt":"2016-04-04 00:00:00.000","amt_prin_init":"175000","amt_pmt":"25402.7397260274","amt_int_pay":"402.739726027397","amt_prin_pay":"25000","amt_prin_end":"150000"},{"num_pmt":"48","date_pmt":"2016-04-18 00:00:00.000","amt_prin_init":"150000","amt_pmt":"25345.2054794521","amt_int_pay":"345.205479452055","amt_prin_pay":"25000","amt_prin_end":"125000"},{"num_pmt":"49","date_pmt":"2016-05-02 00:00:00.000","amt_prin_init":"125000","amt_pmt":"25287.6712328767","amt_int_pay":"287.671232876712","amt_prin_pay":"25000","amt_prin_end":"100000"},{"num_pmt":"50","date_pmt":"2016-05-16 00:00:00.000","amt_prin_init":"100000","amt_pmt":"25230.1369863014","amt_int_pay":"230.13698630137","amt_prin_pay":"25000","amt_prin_end":"75000"},{"num_pmt":"51","date_pmt":"2016-05-30 00:00:00.000","amt_prin_init":"75000","amt_pmt":"25172.602739726","amt_int_pay":"172.602739726027","amt_prin_pay":"25000","amt_prin_end":"50000"},{"num_pmt":"52","date_pmt":"2016-06-13 00:00:00.000","amt_prin_init":"50000","amt_pmt":"50115.0684931507","amt_int_pay":"115.068493150685","amt_prin_pay":"50000","amt_prin_end":"0"}]}
See Also
AMORTIZECASHFLOWS - Discounted cash flow schedule
AMORTSCHED - Amortization schedule for a loan with constant periodic payments