CONSTPRINAMORT
Updated: 09 May 2014
Use the table-valued function CONSTPRINAMORT to generate an amortization schedule for a loan with a fixed principal repayment.
Syntax
SELECT * FROM [wctFinancial].[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,>)
Arguments
@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.
@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.
@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.
@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.
@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.
@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.
@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.
@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.
@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.
@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.
@PPMT
the principal payment amount. @PPMT 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.
Return Types
RETURNS TABLE (
[num_pmt] [int] NULL,
[date_pmt] [datetime] NULL,
[amt_prin_init] [float] NULL,
[amt_pmt] [float] NULL,
[amt_int_pay] [float] NULL,
[amt_prin_pay] [float] NULL,
[amt_prin_end] [float] NULL
)
|
Column
|
Column Description
|
|
num_pmt
|
The monotonically calculated payment number with the payment number on the first payment date = 1.
|
|
date_pmt
|
The date of the payment calculated chronologically from @FirstPaymentDate.
|
|
amt_prin_init
|
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.
|
|
amt_pmt
|
amt_int_pay + amt_prin_pay.
|
|
amt_int_pay
|
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
|
|
amt_prin_pay
|
The principal payment amount. See Remarks for more information.
|
|
amt_prin_end
|
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.
o If @DaysInYr = 360 and num_pmt > 1 then amt_int_pay = amt_prin_init * @Rate / @NumPmtsPerYear.
o 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).
o 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
All the examples have been reformatted to make them easier to read.
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.
|
num_pmt
|
date_pmt
|
amt_prin_init
|
amt_pmt
|
amt_int_pay
|
amt_prin_pay
|
amt_prin_end
|
|
0
|
2014-05-15
|
0
|
0
|
0
|
0
|
1000000
|
|
1
|
2014-06-15
|
1000000
|
25000
|
5000
|
20000
|
980000
|
|
2
|
2014-07-15
|
980000
|
24900
|
4900
|
20000
|
960000
|
|
3
|
2014-08-15
|
960000
|
24800
|
4800
|
20000
|
940000
|
|
4
|
2014-09-15
|
940000
|
24700
|
4700
|
20000
|
920000
|
|
5
|
2014-10-15
|
920000
|
24600
|
4600
|
20000
|
900000
|
|
6
|
2014-11-15
|
900000
|
24500
|
4500
|
20000
|
880000
|
|
7
|
2014-12-15
|
880000
|
24400
|
4400
|
20000
|
860000
|
|
8
|
2015-01-15
|
860000
|
24300
|
4300
|
20000
|
840000
|
|
9
|
2015-02-15
|
840000
|
24200
|
4200
|
20000
|
820000
|
|
10
|
2015-03-15
|
820000
|
24100
|
4100
|
20000
|
800000
|
|
11
|
2015-04-15
|
800000
|
24000
|
4000
|
20000
|
780000
|
|
12
|
2015-05-15
|
780000
|
23900
|
3900
|
20000
|
760000
|
|
13
|
2015-06-15
|
760000
|
23800
|
3800
|
20000
|
740000
|
|
14
|
2015-07-15
|
740000
|
23700
|
3700
|
20000
|
720000
|
|
15
|
2015-08-15
|
720000
|
23600
|
3600
|
20000
|
700000
|
|
16
|
2015-09-15
|
700000
|
23500
|
3500
|
20000
|
680000
|
|
17
|
2015-10-15
|
680000
|
23400
|
3400
|
20000
|
660000
|
|
18
|
2015-11-15
|
660000
|
23300
|
3300
|
20000
|
640000
|
|
19
|
2015-12-15
|
640000
|
23200
|
3200
|
20000
|
620000
|
|
20
|
2016-01-15
|
620000
|
23100
|
3100
|
20000
|
600000
|
|
21
|
2016-02-15
|
600000
|
23000
|
3000
|
20000
|
580000
|
|
22
|
2016-03-15
|
580000
|
22900
|
2900
|
20000
|
560000
|
|
23
|
2016-04-15
|
560000
|
22800
|
2800
|
20000
|
540000
|
|
24
|
2016-05-15
|
540000
|
22700
|
2700
|
20000
|
520000
|
|
25
|
2016-06-15
|
520000
|
22600
|
2600
|
20000
|
500000
|
|
26
|
2016-07-15
|
500000
|
22500
|
2500
|
20000
|
480000
|
|
27
|
2016-08-15
|
480000
|
22400
|
2400
|
20000
|
460000
|
|
28
|
2016-09-15
|
460000
|
22300
|
2300
|
20000
|
440000
|
|
29
|
2016-10-15
|
440000
|
22200
|
2200
|
20000
|
420000
|
|
30
|
2016-11-15
|
420000
|
22100
|
2100
|
20000
|
400000
|
|
31
|
2016-12-15
|
400000
|
22000
|
2000
|
20000
|
380000
|
|
32
|
2017-01-15
|
380000
|
21900
|
1900
|
20000
|
360000
|
|
33
|
2017-02-15
|
360000
|
21800
|
1800
|
20000
|
340000
|
|
34
|
2017-03-15
|
340000
|
21700
|
1700
|
20000
|
320000
|
|
35
|
2017-04-15
|
320000
|
21600
|
1600
|
20000
|
300000
|
|
36
|
2017-05-15
|
300000
|
21500
|
1500
|
20000
|
280000
|
|
37
|
2017-06-15
|
280000
|
21400
|
1400
|
20000
|
260000
|
|
38
|
2017-07-15
|
260000
|
21300
|
1300
|
20000
|
240000
|
|
39
|
2017-08-15
|
240000
|
21200
|
1200
|
20000
|
220000
|
|
40
|
2017-09-15
|
220000
|
21100
|
1100
|
20000
|
200000
|
|
41
|
2017-10-15
|
200000
|
21000
|
1000
|
20000
|
180000
|
|
42
|
2017-11-15
|
180000
|
20900
|
900
|
20000
|
160000
|
|
43
|
2017-12-15
|
160000
|
20800
|
800
|
20000
|
140000
|
|
44
|
2018-01-15
|
140000
|
20700
|
700
|
20000
|
120000
|
|
45
|
2018-02-15
|
120000
|
20600
|
600
|
20000
|
100000
|
|
46
|
2018-03-15
|
100000
|
20500
|
500
|
20000
|
80000
|
|
47
|
2018-04-15
|
80000
|
20400
|
400
|
20000
|
60000
|
|
48
|
2018-05-15
|
60000
|
20300
|
300
|
20000
|
40000
|
|
49
|
2018-06-15
|
40000
|
20200
|
200
|
20000
|
20000
|
|
50
|
2018-07-15
|
20000
|
20100
|
100
|
20000
|
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.
|
num_pmt
|
date_pmt
|
amt_prin_init
|
amt_pmt
|
amt_int_pay
|
amt_prin_pay
|
amt_prin_end
|
|
0
|
2014-05-15
|
0
|
0
|
0
|
0
|
1000000
|
|
1
|
2014-06-30
|
1000000
|
27500
|
7500
|
20000
|
980000
|
|
2
|
2014-07-31
|
980000
|
24900
|
4900
|
20000
|
960000
|
|
3
|
2014-08-31
|
960000
|
24800
|
4800
|
20000
|
940000
|
|
4
|
2014-09-30
|
940000
|
24700
|
4700
|
20000
|
920000
|
|
5
|
2014-10-31
|
920000
|
24600
|
4600
|
20000
|
900000
|
|
6
|
2014-11-30
|
900000
|
24500
|
4500
|
20000
|
880000
|
|
7
|
2014-12-31
|
880000
|
24400
|
4400
|
20000
|
860000
|
|
8
|
2015-01-31
|
860000
|
24300
|
4300
|
20000
|
840000
|
|
9
|
2015-02-28
|
840000
|
24200
|
4200
|
20000
|
820000
|
|
10
|
2015-03-31
|
820000
|
24100
|
4100
|
20000
|
800000
|
|
11
|
2015-04-30
|
800000
|
24000
|
4000
|
20000
|
780000
|
|
12
|
2015-05-31
|
780000
|
23900
|
3900
|
20000
|
760000
|
|
13
|
2015-06-30
|
760000
|
23800
|
3800
|
20000
|
740000
|
|
14
|
2015-07-31
|
740000
|
23700
|
3700
|
20000
|
720000
|
|
15
|
2015-08-31
|
720000
|
23600
|
3600
|
20000
|
700000
|
|
16
|
2015-09-30
|
700000
|
23500
|
3500
|
20000
|
680000
|
|
17
|
2015-10-31
|
680000
|
23400
|
3400
|
20000
|
660000
|
|
18
|
2015-11-30
|
660000
|
23300
|
3300
|
20000
|
640000
|
|
19
|
2015-12-31
|
640000
|
23200
|
3200
|
20000
|
620000
|
|
20
|
2016-01-31
|
620000
|
23100
|
3100
|
20000
|
600000
|
|
21
|
2016-02-29
|
600000
|
23000
|
3000
|
20000
|
580000
|
|
22
|
2016-03-31
|
580000
|
22900
|
2900
|
20000
|
560000
|
|
23
|
2016-04-30
|
560000
|
22800
|
2800
|
20000
|
540000
|
|
24
|
2016-05-31
|
540000
|
22700
|
2700
|
20000
|
520000
|
|
25
|
2016-06-30
|
520000
|
22600
|
2600
|
20000
|
500000
|
|
26
|
2016-07-31
|
500000
|
22500
|
2500
|
20000
|
480000
|
|
27
|
2016-08-31
|
480000
|
22400
|
2400
|
20000
|
460000
|
|
28
|
2016-09-30
|
460000
|
22300
|
2300
|
20000
|
440000
|
|
29
|
2016-10-31
|
440000
|
22200
|
2200
|
20000
|
420000
|
|
30
|
2016-11-30
|
420000
|
22100
|
2100
|
20000
|
400000
|
|
31
|
2016-12-31
|
400000
|
22000
|
2000
|
20000
|
380000
|
|
32
|
2017-01-31
|
380000
|
21900
|
1900
|
20000
|
360000
|
|
33
|
2017-02-28
|
360000
|
21800
|
1800
|
20000
|
340000
|
|
34
|
2017-03-31
|
340000
|
21700
|
1700
|
20000
|
320000
|
|
35
|
2017-04-30
|
320000
|
21600
|
1600
|
20000
|
300000
|
|
36
|
2017-05-31
|
300000
|
21500
|
1500
|
20000
|
280000
|
|
37
|
2017-06-30
|
280000
|
21400
|
1400
|
20000
|
260000
|
|
38
|
2017-07-31
|
260000
|
21300
|
1300
|
20000
|
240000
|
|
39
|
2017-08-31
|
240000
|
21200
|
1200
|
20000
|
220000
|
|
40
|
2017-09-30
|
220000
|
21100
|
1100
|
20000
|
200000
|
|
41
|
2017-10-31
|
200000
|
21000
|
1000
|
20000
|
180000
|
|
42
|
2017-11-30
|
180000
|
20900
|
900
|
20000
|
160000
|
|
43
|
2017-12-31
|
160000
|
20800
|
800
|
20000
|
140000
|
|
44
|
2018-01-31
|
140000
|
20700
|
700
|
20000
|
120000
|
|
45
|
2018-02-28
|
120000
|
20600
|
600
|
20000
|
100000
|
|
46
|
2018-03-31
|
100000
|
20500
|
500
|
20000
|
80000
|
|
47
|
2018-04-30
|
80000
|
20400
|
400
|
20000
|
60000
|
|
48
|
2018-05-31
|
60000
|
20300
|
300
|
20000
|
40000
|
|
49
|
2018-06-30
|
40000
|
20200
|
200
|
20000
|
20000
|
|
50
|
2018-07-31
|
20000
|
20100
|
100
|
20000
|
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 (some of the amounts have been truncated for presentation purposes).
|
num_pmt
|
date_pmt
|
amt_prin_init
|
amt_pmt
|
amt_int_pay
|
amt_prin_pay
|
amt_prin_end
|
|
0
|
2014-05-15
|
0
|
0
|
0
|
0
|
1000000
|
|
1
|
2014-06-30
|
1000000
|
15253.95
|
7561.644
|
7692.308
|
992307.7
|
|
2
|
2014-07-14
|
992307.7
|
9975.975
|
2283.667
|
7692.308
|
984615.4
|
|
3
|
2014-07-28
|
984615.4
|
9958.272
|
2265.964
|
7692.308
|
976923.1
|
|
4
|
2014-08-11
|
976923.1
|
9940.569
|
2248.261
|
7692.308
|
969230.8
|
|
5
|
2014-08-25
|
969230.8
|
9922.866
|
2230.558
|
7692.308
|
961538.5
|
|
6
|
2014-09-08
|
961538.5
|
9905.163
|
2212.856
|
7692.308
|
953846.2
|
|
7
|
2014-09-22
|
953846.2
|
9887.46
|
2195.153
|
7692.308
|
946153.8
|
|
8
|
2014-10-06
|
946153.8
|
9869.758
|