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
|
2177.45
|
7692.308
|
938461.5
|
|
9
|
2014-10-20
|
938461.5
|
9852.055
|
2159.747
|
7692.308
|
930769.2
|
|
10
|
2014-11-03
|
930769.2
|
9834.352
|
2142.044
|
7692.308
|
923076.9
|
|
11
|
2014-11-17
|
923076.9
|
9816.649
|
2124.341
|
7692.308
|
915384.6
|
|
12
|
2014-12-01
|
915384.6
|
9798.946
|
2106.639
|
7692.308
|
907692.3
|
|
13
|
2014-12-15
|
907692.3
|
9781.243
|
2088.936
|
7692.308
|
900000
|
|
14
|
2014-12-29
|
900000
|
9763.541
|
2071.233
|
7692.308
|
892307.7
|
|
15
|
2015-01-12
|
892307.7
|
9745.838
|
2053.53
|
7692.308
|
884615.4
|
|
16
|
2015-01-26
|
884615.4
|
9728.135
|
2035.827
|
7692.308
|
876923.1
|
|
17
|
2015-02-09
|
876923.1
|
9710.432
|
2018.124
|
7692.308
|
869230.8
|
|
18
|
2015-02-23
|
869230.8
|
9692.729
|
2000.421
|
7692.308
|
861538.5
|
|
19
|
2015-03-09
|
861538.5
|
9675.026
|
1982.719
|
7692.308
|
853846.2
|
|
20
|
2015-03-23
|
853846.2
|
9657.323
|
1965.016
|
7692.308
|
846153.8
|
|
21
|
2015-04-06
|
846153.8
|
9639.621
|
1947.313
|
7692.308
|
838461.5
|
|
22
|
2015-04-20
|
838461.5
|
9621.918
|
1929.61
|
7692.308
|
830769.2
|
|
23
|
2015-05-04
|
830769.2
|
9604.215
|
1911.907
|
7692.308
|
823076.9
|
|
24
|
2015-05-18
|
823076.9
|
9586.512
|
1894.204
|
7692.308
|
815384.6
|
|
25
|
2015-06-01
|
815384.6
|
9568.809
|
1876.502
|
7692.308
|
807692.3
|
|
26
|
2015-06-15
|
807692.3
|
9551.106
|
1858.799
|
7692.308
|
800000
|
|
27
|
2015-06-29
|
800000
|
9533.404
|
1841.096
|
7692.308
|
792307.7
|
|
28
|
2015-07-13
|
792307.7
|
9515.701
|
1823.393
|
7692.308
|
784615.4
|
|
29
|
2015-07-27
|
784615.4
|
9497.998
|
1805.69
|
7692.308
|
776923.1
|
|
30
|
2015-08-10
|
776923.1
|
9480.295
|
1787.987
|
7692.308
|
769230.8
|
|
31
|
2015-08-24
|
769230.8
|
9462.592
|
1770.285
|
7692.308
|
761538.5
|
|
32
|
2015-09-07
|
761538.5
|
9444.889
|
1752.582
|
7692.308
|
753846.2
|
|
33
|
2015-09-21
|
753846.2
|
9427.187
|
1734.879
|
7692.308
|
746153.8
|
|
34
|
2015-10-05
|
746153.8
|
9409.484
|
1717.176
|
7692.308
|
738461.5
|
|
35
|
2015-10-19
|
738461.5
|
9391.781
|
1699.473
|
7692.308
|
730769.2
|
|
36
|
2015-11-02
|
730769.2
|
9374.078
|
1681.77
|
7692.308
|
723076.9
|
|
37
|
2015-11-16
|
723076.9
|
9356.375
|
1664.067
|
7692.308
|
715384.6
|
|
38
|
2015-11-30
|
715384.6
|
9338.672
|
1646.365
|
7692.308
|
707692.3
|
|
39
|
2015-12-14
|
707692.3
|
9320.969
|
1628.662
|
7692.308
|
700000
|
|
40
|
2015-12-28
|
700000
|
9303.267
|
1610.959
|
7692.308
|
692307.7
|
|
41
|
2016-01-11
|
692307.7
|
9285.564
|
1593.256
|
7692.308
|
684615.4
|
|
42
|
2016-01-25
|
684615.4
|
9267.861
|
1575.553
|
7692.308
|
676923.1
|
|
43
|
2016-02-08
|
676923.1
|
9250.158
|
1557.85
|
7692.308
|
669230.8
|
|
44
|
2016-02-22
|
669230.8
|
9232.455
|
1540.148
|
7692.308
|
661538.5
|
|
45
|
2016-03-07
|
661538.5
|
9214.752
|
1522.445
|
7692.308
|
653846.2
|
|
46
|
2016-03-21
|
653846.2
|
9197.05
|
1504.742
|
7692.308
|
646153.8
|
|
47
|
2016-04-04
|
646153.8
|
9179.347
|
1487.039
|
7692.308
|
638461.5
|
|
48
|
2016-04-18
|
638461.5
|
9161.644
|
1469.336
|
7692.308
|
630769.2
|
|
49
|
2016-05-02
|
630769.2
|
9143.941
|
1451.633
|
7692.308
|
623076.9
|
|
50
|
2016-05-16
|
623076.9
|
9126.238
|
1433.93
|
7692.308
|
615384.6
|
|
51
|
2016-05-30
|
615384.6
|
9108.535
|
1416.228
|
7692.308
|
607692.3
|
|
52
|
2016-06-13
|
607692.3
|
609090.8
|
1398.525
|
607692.3
|
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 (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
|
7561.644
|
7561.644
|
0
|
1000000
|
|
2
|
2014-07-14
|
1000000
|
2301.37
|
2301.37
|
0
|
1000000
|
|
3
|
2014-07-28
|
1000000
|
2301.37
|
2301.37
|
0
|
1000000
|
|
4
|
2014-08-11
|
1000000
|
2301.37
|
2301.37
|
0
|
1000000
|
|
5
|
2014-08-25
|
1000000
|
2301.37
|
2301.37
|
0
|
1000000
|
|
6
|
2014-09-08
|
1000000
|
2301.37
|
2301.37
|
0
|
1000000
|
|
7
|
2014-09-22
|
1000000
|
2301.37
|
2301.37
|
0
|
1000000
|
|
8
|
2014-10-06
|
1000000
|
2301.37
|
2301.37
|
0
|
1000000
|
|
9
|
2014-10-20
|
1000000
|
2301.37
|
2301.37
|
0
|
1000000
|
|
10
|
2014-11-03
|
1000000
|
2301.37
|
2301.37
|
0
|
1000000
|
|
11
|
2014-11-17
|
1000000
|
2301.37
|
2301.37
|
0
|
1000000
|
|
12
|
2014-12-01
|
1000000
|
2301.37
|
2301.37
|
0
|
1000000
|
|
13
|
2014-12-15
|
1000000
|
2301.37
|
2301.37
|
0
|
1000000
|
|
14
|
2014-12-29
|
1000000
|
27301.37
|
2301.37
|
25000
|
975000
|
|
15
|
2015-01-12
|
975000
|
27243.84
|
2243.836
|
25000
|
950000
|
|
16
|
2015-01-26
|
950000
|
27186.3
|
2186.301
|
25000
|
925000
|
|
17
|
2015-02-09
|
925000
|
27128.77
|
2128.767
|
25000
|
900000
|
|
18
|
2015-02-23
|
900000
|
27071.23
|
2071.233
|
25000
|
875000
|
|
19
|
2015-03-09
|
875000
|
27013.7
|
2013.699
|
25000
|
850000
|
|
20
|
2015-03-23
|
850000
|
26956.16
|
1956.164
|
25000
|
825000
|
|
21
|
2015-04-06
|
825000
|
26898.63
|
1898.63
|
25000
|
800000
|
|
22
|
2015-04-20
|
800000
|
26841.1
|
1841.096
|
25000
|
775000
|
|
23
|
2015-05-04
|
775000
|
26783.56
|
1783.562
|
25000
|
750000
|
|
24
|
2015-05-18
|
750000
|
26726.03
|
1726.027
|
25000
|
725000
|
|
25
|
2015-06-01
|
725000
|
26668.49
|
1668.493
|
25000
|
700000
|
|
26
|
2015-06-15
|
700000
|
26610.96
|
1610.959
|
25000
|
675000
|
|
27
|
2015-06-29
|
675000
|
26553.42
|
1553.425
|
25000
|
650000
|
|
28
|
2015-07-13
|
650000
|
26495.89
|
1495.89
|
25000
|
625000
|
|
29
|
2015-07-27
|
625000
|
26438.36
|
1438.356
|
25000
|
600000
|
|
30
|
2015-08-10
|
600000
|
26380.82
|
1380.822
|
25000
|
575000
|
|
31
|
2015-08-24
|
575000
|
26323.29
|
1323.288
|
25000
|
550000
|
|
32
|
2015-09-07
|
550000
|
26265.75
|
1265.753
|
25000
|
525000
|
|
33
|
2015-09-21
|
525000
|
26208.22
|
1208.219
|
25000
|
500000
|
|
34
|
2015-10-05
|
500000
|
26150.68
|
1150.685
|
25000
|
475000
|
|
35
|
2015-10-19
|
475000
|
26093.15
|
1093.151
|
25000
|
450000
|
|
36
|
2015-11-02
|
450000
|
26035.62
|
1035.616
|
25000
|
425000
|
|
37
|
2015-11-16
|
425000
|
25978.08
|
978.0822
|
25000
|
400000
|
|
38
|
2015-11-30
|
400000
|
25920.55
|
920.5479
|
25000
|
375000
|
|
39
|
2015-12-14
|
375000
|
25863.01
|
863.0137
|
25000
|
350000
|
|
40
|
2015-12-28
|
350000
|
25805.48
|
805.4795
|
25000
|
325000
|
|
41
|
2016-01-11
|
325000
|
25747.95
|
747.9452
|
25000
|
300000
|
|
42
|
2016-01-25
|
300000
|
25690.41
|
690.411
|
25000
|
275000
|
|
43
|
2016-02-08
|
275000
|
25632.88
|
632.8767
|
25000
|
250000
|
|
44
|
2016-02-22
|
250000
|
25575.34
|
575.3425
|
25000
|
225000
|
|
45
|
2016-03-07
|
225000
|
25517.81
|
517.8082
|
25000
|
200000
|
|
46
|
2016-03-21
|
200000
|
25460.27
|
460.274
|
25000
|
175000
|
|
47
|
2016-04-04
|
175000
|
25402.74
|
402.7397
|
25000
|
150000
|
|
48
|
2016-04-18
|
150000
|
25345.21
|
345.2055
|
25000
|
125000
|
|
49
|
2016-05-02
|
125000
|
25287.67
|
287.6712
|
25000
|
100000
|
|
50
|
2016-05-16
|
100000
|
25230.14
|
230.137
|
25000
|
75000
|
|
51
|
2016-05-30
|
75000
|
25172.6
|
172.6027
|
25000
|
50000
|
|
52
|
2016-06-13
|
50000
|
50115.07
|
115.0685
|
50000
|
0
|
See Also