CONSTPRINAMORT
Updated: 09 May 2014
Use the tablevalued 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 20140514 with 50 monthly payments commencing on 20140615. The interest rate is 6.0%.
SELECT
*
FROM
wct.CONSTPRINAMORT(
1000000, @PV
.06, @Rate
'20140515', @LoanDate
12, @NumPmtsPerYear
'20140615', @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

20140515

0

0

0

0

1000000

1

20140615

1000000

25000

5000

20000

980000

2

20140715

980000

24900

4900

20000

960000

3

20140815

960000

24800

4800

20000

940000

4

20140915

940000

24700

4700

20000

920000

5

20141015

920000

24600

4600

20000

900000

6

20141115

900000

24500

4500

20000

880000

7

20141215

880000

24400

4400

20000

860000

8

20150115

860000

24300

4300

20000

840000

9

20150215

840000

24200

4200

20000

820000

10

20150315

820000

24100

4100

20000

800000

11

20150415

800000

24000

4000

20000

780000

12

20150515

780000

23900

3900

20000

760000

13

20150615

760000

23800

3800

20000

740000

14

20150715

740000

23700

3700

20000

720000

15

20150815

720000

23600

3600

20000

700000

16

20150915

700000

23500

3500

20000

680000

17

20151015

680000

23400

3400

20000

660000

18

20151115

660000

23300

3300

20000

640000

19

20151215

640000

23200

3200

20000

620000

20

20160115

620000

23100

3100

20000

600000

21

20160215

600000

23000

3000

20000

580000

22

20160315

580000

22900

2900

20000

560000

23

20160415

560000

22800

2800

20000

540000

24

20160515

540000

22700

2700

20000

520000

25

20160615

520000

22600

2600

20000

500000

26

20160715

500000

22500

2500

20000

480000

27

20160815

480000

22400

2400

20000

460000

28

20160915

460000

22300

2300

20000

440000

29

20161015

440000

22200

2200

20000

420000

30

20161115

420000

22100

2100

20000

400000

31

20161215

400000

22000

2000

20000

380000

32

20170115

380000

21900

1900

20000

360000

33

20170215

360000

21800

1800

20000

340000

34

20170315

340000

21700

1700

20000

320000

35

20170415

320000

21600

1600

20000

300000

36

20170515

300000

21500

1500

20000

280000

37

20170615

280000

21400

1400

20000

260000

38

20170715

260000

21300

1300

20000

240000

39

20170815

240000

21200

1200

20000

220000

40

20170915

220000

21100

1100

20000

200000

41

20171015

200000

21000

1000

20000

180000

42

20171115

180000

20900

900

20000

160000

43

20171215

160000

20800

800

20000

140000

44

20180115

140000

20700

700

20000

120000

45

20180215

120000

20600

600

20000

100000

46

20180315

100000

20500

500

20000

80000

47

20180415

80000

20400

400

20000

60000

48

20180515

60000

20300

300

20000

40000

49

20180615

40000

20200

200

20000

20000

50

20180715

20000

20100

100

20000

0

A 1,000,000 loan dated 20140514 with 50 monthly payments commencing on 20140630, with payments due at the endof the month. The interest rate is 6.0%.
SELECT
*
FROM
wct.CONSTPRINAMORT(
1000000, @PV
.06, @Rate
'20140515', @LoanDate
12, @NumPmtsPerYear
'20140630', @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

20140515

0

0

0

0

1000000

1

20140630

1000000

27500

7500

20000

980000

2

20140731

980000

24900

4900

20000

960000

3

20140831

960000

24800

4800

20000

940000

4

20140930

940000

24700

4700

20000

920000

5

20141031

920000

24600

4600

20000

900000

6

20141130

900000

24500

4500

20000

880000

7

20141231

880000

24400

4400

20000

860000

8

20150131

860000

24300

4300

20000

840000

9

20150228

840000

24200

4200

20000

820000

10

20150331

820000

24100

4100

20000

800000

11

20150430

800000

24000

4000

20000

780000

12

20150531

780000

23900

3900

20000

760000

13

20150630

760000

23800

3800

20000

740000

14

20150731

740000

23700

3700

20000

720000

15

20150831

720000

23600

3600

20000

700000

16

20150930

700000

23500

3500

20000

680000

17

20151031

680000

23400

3400

20000

660000

18

20151130

660000

23300

3300

20000

640000

19

20151231

640000

23200

3200

20000

620000

20

20160131

620000

23100

3100

20000

600000

21

20160229

600000

23000

3000

20000

580000

22

20160331

580000

22900

2900

20000

560000

23

20160430

560000

22800

2800

20000

540000

24

20160531

540000

22700

2700

20000

520000

25

20160630

520000

22600

2600

20000

500000

26

20160731

500000

22500

2500

20000

480000

27

20160831

480000

22400

2400

20000

460000

28

20160930

460000

22300

2300

20000

440000

29

20161031

440000

22200

2200

20000

420000

30

20161130

420000

22100

2100

20000

400000

31

20161231

400000

22000

2000

20000

380000

32

20170131

380000

21900

1900

20000

360000

33

20170228

360000

21800

1800

20000

340000

34

20170331

340000

21700

1700

20000

320000

35

20170430

320000

21600

1600

20000

300000

36

20170531

300000

21500

1500

20000

280000

37

20170630

280000

21400

1400

20000

260000

38

20170731

260000

21300

1300

20000

240000

39

20170831

240000

21200

1200

20000

220000

40

20170930

220000

21100

1100

20000

200000

41

20171031

200000

21000

1000

20000

180000

42

20171130

180000

20900

900

20000

160000

43

20171231

160000

20800

800

20000

140000

44

20180131

140000

20700

700

20000

120000

45

20180228

120000

20600

600

20000

100000

46

20180331

100000

20500

500

20000

80000

47

20180430

80000

20400

400

20000

60000

48

20180531

60000

20300

300

20000

40000

49

20180630

40000

20200

200

20000

20000

50

20180731

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
'20140515', @LoanDate
26, @NumPmtsPerYear
'20140630', @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

20140515

0

0

0

0

1000000

1

20140630

1000000

15253.95

7561.644

7692.308

992307.7

2

20140714

992307.7

9975.975

2283.667

7692.308

984615.4

3

20140728

984615.4

9958.272

2265.964

7692.308

976923.1

4

20140811

976923.1

9940.569

2248.261

7692.308

969230.8

5

20140825

969230.8

9922.866

2230.558

7692.308

961538.5

6

20140908

961538.5

9905.163

2212.856

7692.308

953846.2

7

20140922

953846.2

9887.46

2195.153

7692.308

946153.8

8

20141006

946153.8

9869.758

