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