Login     Register

        Contact Us     Search

XLeratorDB/financial Documentation

SQL Server fixed principal amount amortization


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

 



Copyright 2008-2024 Westclintech LLC         Privacy Policy        Terms of Service