LPMTSCHED
Updated: 13 May 2012
Use LPMTSCHED to generate a loan amortization schedule, given the period payment amount, the principal amount (or present value amount), and the balloon payment (or future value amount). The amortization schedule includes the payment number, the payment date, and the principal amount at the beginning of the period, the interest amount for the period, the principal payment for the period, any deferred interest for the period, and the ending principal amount.
LPMTSCHED supports loans with odd first periods, does US Rule or actuarial interest calculations, and allows you to specify a terminal (or future) value for the loan. LPMTSCHED also lets you calculate the amortization schedule using a different term for the amortization and the maturity, with the final payment amount adjusted for the outstanding principal balance.
LPMTSCHED lets you enter the periodic payment, the interest rate, or both. If the rate is not entered, the rate is calculated from the periodic payment. If the periodic payment is not entered, the periodic payment is calculated from the rate. If both the periodic payment and the rate are entered, then the schedule then the interest portion of each period is calculated using the rate, and the principal payment portion is calculated using periodic payment minus that interest payment amount. This may result in an odd final payment or even in a reduction in the number of payments for the loan.
Syntax
SELECT * FROM [wctFinancial].[wct].[LPMTSCHED] (
<@PV, float,>
,<@LoanDate, datetime,>
,<@Pmt, float,>
,<@Rate, float,>
,<@FirstPayDate, datetime,>
,<@NumPmts, int,>
,<@Pmtpyr, int,>
,<@DaysInYr, int,>
,<@FV, float,>
,<@IntRule, nvarchar(4000),>
,<@Decimals, int,>
,<@LastPmtNum, int,>)
Arguments
@PV
the principal amount of the loan or lease. @PV 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. @LoanStartDate is an expression of type datetime or of a type that can be implicitly converted to datetime.
@Pmt
the payment made each period. @Pmt 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.
@FirstPayDate
the date that the first payment is due. @FirstPayDate is an expression of type datetime or of a type that can be implicitly converted to datetime.
@NumPmts
the total number of payments to be recorded over the life of the loan. @NumPmts is an expression of type int or of a type that can be implicitly converted to int.
@Pmtpyr
the number of loan payments made in a year. @Pmtpyr is an expression of type int or of a type that can be implicitly converted to int.
@DaysInYr
the denominator number of days to be used in the calculation of the interest amount in the odd first period. @DaysInYr 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.
@IntRule
Identifies the loan as conforming to the US Rule (“U”) or the actuarial rule (“A”) regarding the compounding of interest in the odd first period.
@Decimals
the number of decimal places to round the calculated amounts. @Decimals is an expression of type int or of a type that can be implicitly converted to int.
@LastPmtNum
the last payment number if the life of the loan is shorter than the amortization period. @LastPmtNum is an expression of type int or of a type that can be implicitly converted to int.
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_int_def] [float] NULL,
[amt_prin_end] [float] NULL
)
|
Column
|
Column Description
|
|
num_pmt
|
The payment number.
|
|
date_pmt
|
The date of the payment.
|
|
amt_prin_init
|
The principal amount at the beginning of the period. For the first period, the principal amount is the amount of the loan, otherwise the principal amount is the ending principal amount from the prior period.
|
|
amt_pmt
|
The payment amount supplied to the function.
|
|
amt_int_pay
|
The interest payable amount for the period. The interest amount is the period interest rate (@Rate/@Pmtpyr) multiplied by the principal amount at the beginning of the period (amt_prin_init) rounded to the number of decimal places (@Decimals).
|
|
amt_prin_pay
|
The principal payment amount for the period. For actuarial accrual loans, the principal payment amount is the payment amount (amt_pmt) minus the interest payment amount (amt_int_pay). If the interest payment amount is greater than the payment amount, then the principal payment amount is negative. For US rule loans, the principal payment amount will always be greater than or equal to zero.
|
|
amt_int_def
|
The interest deferral (or escrow) amount. For US Rule loans only. If the interest payment amount (amt_int_pay) is greater than the payment amount (amt_pmt) then the difference is put into this column. When the interest payments (amt_int_pay) become less than the periodic payment (amt_pmt), the interest deferral amount (amt_int_def) from prior periods are reduced to zero, before applying any amounts to principal payments (amt_prin_pay).
|
|
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 @DaysInYr is NULL, then @DaysInYr = 360
· If @FV is NULL, then @FV = 0
· If @IntRule is NULL, then @IntRule = “A”
· @FirstPayDate must be greater than @LoanDate
· @Pmtpyr must be 1, 2, 3, 4, 6, 12, 13, 24, 26, 52, or 365
· @NumPmts must be greater than 1
· @Rate must be greater than zero
· @DaysInYr must be 360, 364, or 365
· @PV must be greater than zero
· If @Decimals is NULL, then @Decimals = 2
· If @LastPmtNum is NULL, the @LastPmtNum = @NumPmts
Examples
All the examples have been reformatted to make them easier to read.
Generate the amortization schedule for a 50,000, 5-year loan starting on 1 November with payments due on the first of every month. The periodic payment is 966.64.
SELECT *
FROM wct.LPMTSCHED(
50000 --Present Value
,'11/01/2010' --Loan Date
,966.64 --Pmt
,NULL --Rate
,'12/01/2010' --First Payment Date
,60 --Number of Payments
,12 --Payments per Year
,NULL --Days In Year
,0 --Future Value
,NULL --Intrule
,2 --Decimals
,NULL --Last Payment Number
)
This produces the following result.
|
num_pmt
|
date_pmt
|
amt_prin_init
|
amt_pmt
|
amt_int_pay
|
amt_prin_pay
|
amt_int_def
|
amt_prin_end
|
|
1
|
1-Dec-10
|
50000
|
966.64
|
250
|
716.64
|
0
|
49283.36
|
|
2
|
1-Jan-11
|
49283.36
|
966.64
|
246.42
|
720.22
|
0
|
48563.14
|
|
3
|
1-Feb-11
|
48563.14
|
966.64
|
242.82
|
723.82
|
0
|
47839.32
|
|
4
|
1-Mar-11
|
47839.32
|
966.64
|
239.2
|
727.44
|
0
|
47111.88
|
|
5
|
1-Apr-11
|
47111.88
|
966.64
|
235.56
|
731.08
|
0
|
46380.8
|
|
6
|
1-May-11
|
46380.8
|
966.64
|
231.9
|
734.74
|
0
|
45646.06
|
|
7
|
1-Jun-11
|
45646.06
|
966.64
|
228.23
|
738.41
|
0
|
44907.65
|
|
8
|
1-Jul-11
|
44907.65
|
966.64
|
224.54
|
742.1
|
0
|
44165.55
|
|
9
|
1-Aug-11
|
44165.55
|
966.64
|
220.83
|
745.81
|
0
|
43419.74
|
|
10
|
1-Sep-11
|
43419.74
|
966.64
|
217.1
|
749.54
|
0
|
42670.2
|
|
11
|
1-Oct-11
|
42670.2
|
966.64
|
213.35
|
753.29
|
0
|
41916.91
|
|
12
|
1-Nov-11
|
41916.91
|
966.64
|
209.58
|
757.06
|
0
|
41159.85
|
|
13
|
1-Dec-11
|
41159.85
|
966.64
|
205.8
|
760.84
|
0
|
40399.01
|
|
14
|
1-Jan-12
|
40399.01
|
966.64
|
202
|
764.64
|
0
|
39634.37
|
|
15
|
1-Feb-12
|
39634.37
|
966.64
|
198.17
|
768.47
|
0
|
38865.9
|
|
16
|
1-Mar-12
|
38865.9
|
966.64
|
194.33
|
772.31
|
0
|
38093.59
|
|
17
|
1-Apr-12
|
38093.59
|
966.64
|
190.47
|
776.17
|
0
|
37317.42
|
|
18
|
1-May-12
|
37317.42
|
966.64
|
186.59
|
780.05
|
0
|
36537.37
|
|
19
|
1-Jun-12
|
36537.37
|
966.64
|
182.69
|
783.95
|
0
|
35753.42
|
|
20
|
1-Jul-12
|
35753.42
|
966.64
|
178.77
|
787.87
|
0
|
34965.55
|
|
21
|
1-Aug-12
|
34965.55
|
966.64
|
174.83
|
791.81
|
0
|
34173.74
|
|
22
|
1-Sep-12
|
34173.74
|
966.64
|
170.87
|
795.77
|
0
|
33377.97
|
|
23
|
1-Oct-12
|
33377.97
|
966.64
|
166.89
|
799.75
|
0
|
32578.22
|
|
24
|
1-Nov-12
|
32578.22
|
966.64
|
162.89
|
803.75
|
0
|
31774.47
|
|
25
|
1-Dec-12
|
31774.47
|
966.64
|
158.87
|
807.77
|
0
|
30966.7
|
|
26
|
1-Jan-13
|
30966.7
|
966.64
|
154.83
|
811.81
|
0
|
30154.89
|
|
27
|
1-Feb-13
|
30154.89
|
966.64
|
150.77
|
815.87
|
0
|
29339.02
|
|
28
|
1-Mar-13
|
29339.02
|
966.64
|
146.7
|
819.94
|
0
|
28519.08
|
|
29
|
1-Apr-13
|
28519.08
|
966.64
|
142.6
|
824.04
|
0
|
27695.04
|
|
30
|
1-May-13
|
27695.04
|
966.64
|
138.48
|
828.16
|
0
|
26866.88
|
|
31
|
1-Jun-13
|
26866.88
|
966.64
|
134.33
|
832.31
|
0
|
26034.57
|
|
32
|
1-Jul-13
|
26034.57
|
966.64
|
130.17
|
836.47
|
0
|
25198.1
|
|
33
|
1-Aug-13
|
25198.1
|
966.64
|
125.99
|
840.65
|
0
|
24357.45
|
|
34
|
1-Sep-13
|
24357.45
|
966.64
|
121.79
|
844.85
|
0
|
23512.6
|
|
35
|
1-Oct-13
|
23512.6
|
966.64
|
117.56
|
849.08
|
0
|
22663.52
|
|
36
|
1-Nov-13
|
22663.52
|
966.64
|
113.32
|
853.32
|
0
|
21810.2
|
|
37
|
1-Dec-13
|
21810.2
|
966.64
|
109.05
|
857.59
|
0
|
20952.61
|
|
38
|
1-Jan-14
|
20952.61
|
966.64
|
104.76
|
861.88
|
0
|
20090.73
|
|
39
|
1-Feb-14
|
20090.73
|
966.64
|
100.45
|
866.19
|
0
|
19224.54
|
|
40
|
1-Mar-14
|
19224.54
|
966.64
|
96.12
|
870.52
|
0
|
18354.02
|
|
41
|
1-Apr-14
|
18354.02
|
966.64
|
91.77
|
874.87
|
0
|
17479.15
|
|
42
|
1-May-14
|
17479.15
|
966.64
|
87.4
|
879.24
|
0
|
16599.91
|
|
43
|
1-Jun-14
|
16599.91
|
966.64
|
83
|
883.64
|
0
|
15716.27
|
|
44
|
1-Jul-14
|
15716.27
|
966.64
|
78.58
|
888.06
|
0
|
14828.21
|
|
45
|
1-Aug-14
|
14828.21
|
966.64
|
74.14
|
892.5
|
0
|
13935.71
|
|
46
|
1-Sep-14
|
13935.71
|
966.64
|
69.68
|
896.96
|
0
|
13038.75
|
|
47
|
1-Oct-14
|
13038.75
|
966.64
|
65.19
|
901.45
|
0
|
12137.3
|
|
48
|
1-Nov-14
|
12137.3
|
966.64
|
60.69
|
905.95
|
0
|
11231.35
|
|
49
|
1-Dec-14
|
11231.35
|
966.64
|
56.16
|
910.48
|
0
|
10320.87
|
|
50
|
1-Jan-15
|
10320.87
|
966.64
|
51.6
|
915.04
|
0
|
9405.83
|
|
51
|
1-Feb-15
|
9405.83
|
966.64
|
47.03
|
919.61
|
0
|
8486.22
|
|
52
|
1-Mar-15
|
8486.22
|
966.64
|
42.43
|
924.21
|
0
|
7562.01
|
|
53
|
1-Apr-15
|
7562.01
|
966.64
|
37.81
|
928.83
|
0
|
6633.18
|
|
54
|
1-May-15
|
6633.18
|
966.64
|
33.17
|
933.47
|
0
|
5699.71
|
|
55
|
1-Jun-15
|
5699.71
|
966.64
|
28.5
|
938.14
|
0
|
4761.57
|
|
56
|
1-Jul-15
|
4761.57
|
966.64
|
23.81
|
942.83
|
0
|
3818.74
|
|
57
|
1-Aug-15
|
3818.74
|
966.64
|
19.09
|
947.55
|
0
|
2871.19
|
|
58
|
1-Sep-15
|
2871.19
|
966.64
|
14.36
|
952.28
|
0
|
1918.91
|
|
59
|
1-Oct-15
|
1918.91
|
966.64
|
9.59
|
957.05
|
0
|
961.86
|
|
60
|
1-Nov-15
|
961.86
|
966.67
|
4.81
|
961.86
|
0
|
0
|
Generate the amortization schedule for a 50,000, 5-year loan starting on 1 November with payments of 1,161.41 due on the fifteenth of every month starting 15-Apr-2011.
SELECT *
FROM wct.LPMTSCHED(
50000 --Present Value
,'11/01/2010' --Loan Date
,1161.41 --Pmt
,NULL --Rate
,'04/15/2011' --First Payment Date
,60 --Number of Payments
,12 --Payments per Year
,NULL --Days In Year
,0 --Future Value
,NULL --Intrule
,2 --Decimals
,NULL --Last Payment Number
)
This produces the following result.
|
num_pmt
|
date_pmt
|
amt_prin_init
|
amt_pmt
|
amt_int_pay
|
amt_prin_pay
|
amt_int_def
|
amt_prin_end
|
|
0
|
1-Nov-10
|
0
|
0
|
0
|
0
|
0
|
50000
|
|
1
|
15-Apr-11
|
50000
|
1161.41
|
2733.34
|
-1571.93
|
0
|
51571.93
|
|
2
|
15-May-11
|
51571.93
|
1161.41
|
515.72
|
645.69
|
0
|
50926.24
|
|
3
|
15-Jun-11
|
50926.24
|
1161.41
|
509.26
|
652.15
|
0
|
50274.09
|
|
4
|
15-Jul-11
|
50274.09
|
1161.41
|
502.74
|
658.67
|
0
|
49615.42
|
|
5
|
15-Aug-11
|
49615.42
|
1161.41
|
496.15
|
665.26
|
0
|
48950.16
|
|
6
|
15-Sep-11
|
48950.16
|
1161.41
|
489.5
|
671.91
|
0
|
48278.25
|
|
7
|
15-Oct-11
|
48278.25
|
1161.41
|
482.78
|
678.63
|
0
|
47599.62
|
|
8
|
15-Nov-11
|
47599.62
|
1161.41
|
476
|
685.41
|
0
|
46914.21
|
|
9
|
15-Dec-11
|
46914.21
|
1161.41
|
469.14
|
692.27
|
0
|
46221.94
|
|
10
|
15-Jan-12
|
46221.94
|
1161.41
|
462.22
|
699.19
|
0
|
45522.75
|
|
11
|
15-Feb-12
|
45522.75
|
1161.41
|
455.23
|
706.18
|
0
|
44816.57
|
|
12
|
15-Mar-12
|
44816.57
|
1161.41
|
448.17
|
713.24
|
0
|
44103.33
|
|
13
|
15-Apr-12
|
44103.33
|
1161.41
|
441.03
|
720.38
|
0
|
43382.95
|
|
14
|
15-May-12
|
43382.95
|
1161.41
|
433.83
|
727.58
|
0
|
42655.37
|
|
15
|
15-Jun-12
|
42655.37
|
1161.41
|
426.55
|
734.86
|
0
|
41920.51
|
|
16
|
15-Jul-12
|
41920.51
|
1161.41
|
419.21
|
742.2
|
0
|
41178.31
|
|
17
|
15-Aug-12
|
41178.31
|
1161.41
|
411.78
|
749.63
|
0
|
40428.68
|
|
18
|
15-Sep-12
|
40428.68
|
1161.41
|
404.29
|
757.12
|
0
|
39671.56
|
|
19
|
15-Oct-12
|
39671.56
|
1161.41
|
396.72
|
764.69
|
0
|
38906.87
|
|
20
|
15-Nov-12
|
38906.87
|
1161.41
|
389.07
|
772.34
|
0
|
38134.53
|
|
21
|
15-Dec-12
|
38134.53
|
1161.41
|
381.35
|
780.06
|
0
|
37354.47
|
|
22
|
15-Jan-13
|
37354.47
|
1161.41
|
373.55
|
787.86
|
0
|
36566.61
|
|
23
|
15-Feb-13
|
36566.61
|
1161.41
|
365.67
|
795.74
|
0
|
35770.87
|
|
24
|
15-Mar-13
|
35770.87
|
1161.41
|
357.71
|
803.7
|
0
|
34967.17
|
|
25
|
15-Apr-13
|
34967.17
|
1161.41
|
349.67
|
811.74
|
0
|
34155.43
|
|
26
|
15-May-13
|
34155.43
|
1161.41
|
341.55
|
819.86
|
0
|
33335.57
|
|
27
|
15-Jun-13
|
33335.57
|
1161.41
|
333.36
|
828.05
|
0
|
32507.52
|
|
28
|
15-Jul-13
|
32507.52
|
1161.41
|
325.08
|
836.33
|
0
|
31671.19
|
|
29
|
15-Aug-13
|
31671.19
|
1161.41
|
316.71
|
844.7
|
0
|
30826.49
|
|
30
|
15-Sep-13
|
30826.49
|
1161.41
|
308.27
|
853.14
|
0
|
29973.35
|
|
31
|
15-Oct-13
|
29973.35
|
1161.41
|
299.73
|
861.68
|
0
|
29111.67
|
|
32
|
15-Nov-13
|
29111.67
|
1161.41
|
291.12
|
870.29
|
0
|
28241.38
|
|
33
|
15-Dec-13
|
28241.38
|
1161.41
|
282.41
|
879
|
0
|
27362.38
|
|
34
|
15-Jan-14
|
27362.38
|
1161.41
|
273.62
|
887.79
|
0
|
26474.59
|
|
35
|
15-Feb-14
|
26474.59
|
1161.41
|
264.75
|
896.66
|
0
|
25577.93
|
|
36
|
15-Mar-14
|
25577.93
|
1161.41
|
255.78
|
905.63
|
0
|
24672.3
|
|
37
|
15-Apr-14
|
24672.3
|
1161.41
|
246.72
|
914.69
|
0
|
23757.61
|
|
38
|
15-May-14
|
23757.61
|
1161.41
|
237.58
|
923.83
|
0
|
22833.78
|
|
39
|
15-Jun-14
|
22833.78
|
1161.41
|
228.34
|
933.07
|
0
|
21900.71
|
|
40
|
15-Jul-14
|
21900.71
|
1161.41
|
219.01
|
942.4
|
0
|
20958.31
|
|
41
|
15-Aug-14
|
20958.31
|
1161.41
|
209.58
|
951.83
|
0
|
20006.48
|
|
42
|
15-Sep-14
|
20006.48
|
1161.41
|
200.07
|
961.34
|
0
|
19045.14
|
|
43
|
15-Oct-14
|
19045.14
|
1161.41
|
190.45
|
970.96
|
0
|
18074.18
|
|
44
|
15-Nov-14
|
18074.18
|
1161.41
|
180.74
|
980.67
|
0
|
17093.51
|
|
45
|
15-Dec-14
|
17093.51
|
1161.41
|
170.94
|
990.47
|
0
|
16103.04
|
|
46
|
15-Jan-15
|
16103.04
|
1161.41
|
161.03
|
1000.38
|
0
|
15102.66
|
|
47
|
15-Feb-15
|
15102.66
|
1161.41
|
151.03
|
1010.38
|
0
|
14092.28
|
|
48
|
15-Mar-15
|
14092.28
|
1161.41
|
140.92
|
1020.49
|
0
|
13071.79
|
|
49
|
15-Apr-15
|
13071.79
|
1161.41
|
130.72
|
1030.69
|
0
|
12041.1
|
|
50
|
15-May-15
|
12041.1
|
1161.41
|
120.41
|
1041
|
0
|
11000.1
|
|
51
|
15-Jun-15
|
11000.1
|
1161.41
|
110
|
1051.41
|
0
|
9948.69
|
|
52
|
15-Jul-15
|
9948.69
|
1161.41
|
99.49
|
1061.92
|
0
|
8886.77
|
|
53
|
15-Aug-15
|
8886.77
|
1161.41
|
88.87
|
1072.54
|
0
|
7814.23
|
|
54
|
15-Sep-15
|
7814.23
|
1161.41
|
78.14
|
1083.27
|
0
|
6730.96
|
|
55
|
15-Oct-15
|
6730.96
|
1161.41
|
67.31
|
1094.1
|
0
|
5636.86
|
|
56
|
15-Nov-15
|
5636.86
|
1161.41
|
56.37
|
1105.04
|
0
|
4531.82
|
|
57
|
15-Dec-15
|
4531.82
|
1161.41
|
45.32
|
1116.09
|
0
|
3415.73
|
|
58
|
15-Jan-16
|
3415.73
|
1161.41
|
34.16
|
1127.25
|
0
|
2288.48
|
|
59
|
15-Feb-16
|
2288.48
|
1161.41
|
22.88
|
1138.53
|
0
|
1149.95
|
|
60
|
15-Mar-16
|
1149.95
|
1161.45
|
11.5
|
1149.95
|
0
|
0
|
In this example, the US Rule is applied, so there is no negative amortization. The monthly payment is 1160.82, and all other parameters are from the previous example.
SELECT *
FROM wct.LPMTSCHED(
50000 --Present Value
,'11/01/2010' --Loan Date
,1160.82 --Pmt
,NULL --Rate
,'04/15/2011' --First Payment Date
,60 --Number of Payments
,12 --Payments per Year
,NULL --Days In Year
,0 --Future Value
,'U' --Intrule
,2 --Decimals
,NULL --Last Payment Number
)
This produces the following result.
|
num_pmt
|
date_pmt
|
amt_prin_init
|
amt_pmt
|
amt_int_pay
|
amt_prin_pay
|
amt_int_def
|
amt_prin_end
|
|
0
|
1-Nov-10
|
0.00
|
0.00
|
0.00
|
0.00
|
0.00
|
50000.00
|
|
1
|
15-Apr-11
|
50000.00
|
1160.82
|
2733.36
|
0.00
|
1572.54
|
50000.00
|
|
2
|
15-May-11
|
50000.00
|
1160.82
|
500.01
|
0.00
|
-660.81
|
50000.00
|
|
3
|
15-Jun-11
|
50000.00
|
1160.82
|
500.01
|
0.00
|
-660.81
|
50000.00
|
|
4
|
15-Jul-11
|
50000.00
|
1160.82
|
500.01
|
409.89
|
-250.92
|
49590.11
|
|
5
|
15-Aug-11
|
49590.11
|
1160.82
|
495.91
|
664.91
|
0.00
|
48925.20
|
|
6
|
15-Sep-11
|
48925.20
|
1160.82
|
489.26
|
671.56
|
0.00
|
48253.64
|
|
7
|
15-Oct-11
|
48253.64
|
1160.82
|
482.54
|
678.28
|
0.00
|
47575.36
|
|
8
|
15-Nov-11
|
47575.36
|
1160.82
|
475.76
|
685.06
|
0.00
|
46890.30
|
|
9
|
15-Dec-11
|
46890.30
|
1160.82
|
468.91
|
691.91
|
0.00
|
46198.39
|
|
10
|
15-Jan-12
|
46198.39
|
1160.82
|
461.99
|
698.83
|
0.00
|
45499.56
|
|
11
|
15-Feb-12
|
45499.56
|
1160.82
|
455.00
|
705.82
|
0.00
|
44793.74
|
|
12
|
15-Mar-12
|
44793.74
|
1160.82
|
447.94
|
712.88
|
0.00
|
44080.86
|
|
13
|
15-Apr-12
|
44080.86
|
1160.82
|
440.81
|
720.01
|
0.00
|
43360.85
|
|
14
|
15-May-12
|
43360.85
|
1160.82
|
433.61
|
727.21
|
0.00
|
42633.64
|
|
15
|
15-Jun-12
|
42633.64
|
1160.82
|
426.34
|
734.48
|
0.00
|
41899.16
|
|
16
|
15-Jul-12
|
41899.16
|
1160.82
|
419.00
|
741.82
|
0.00
|
41157.34
|
|
17
|
15-Aug-12
|
41157.34
|
1160.82
|
411.58
|
749.24
|
0.00
|
40408.10
|
|
18
|
15-Sep-12
|
40408.10
|
1160.82
|
404.09
|
756.73
|
0.00
|
39651.37
|
|
19
|
15-Oct-12
|
39651.37
|
1160.82
|
396.52
|
764.30
|
0.00
|
38887.07
|
|
20
|
15-Nov-12
|
38887.07
|
1160.82
|
388.87
|
771.95
|
0.00
|
38115.12
|
|
21
|
15-Dec-12
|
38115.12
|
1160.82
|
381.16
|
779.66
|
0.00
|
37335.46
|
|
22
|
15-Jan-13
|
37335.46
|
1160.82
|
373.36
|
787.46
|
0.00
|
36548.00
|
|
23
|
15-Feb-13
|
36548.00
|
1160.82
|
365.48
|
795.34
|
0.00
|
35752.66
|
|
24
|
15-Mar-13
|
35752.66
|
1160.82
|
357.53
|
803.29
|
0.00
|
34949.37
|
|
25
|
15-Apr-13
|
34949.37
|
1160.82
|
349.50
|
811.32
|
0.00
|
34138.05
|
|
26
|
15-May-13
|
34138.05
|
1160.82
|
341.38
|
819.44
|
0.00
|
33318.61
|
|
27
|
15-Jun-13
|
33318.61
|
1160.82
|
333.19
|
827.63
|
0.00
|
32490.98
|
|
28
|
15-Jul-13
|
32490.98
|
1160.82
|
324.91
|
835.91
|
0.00
|
31655.07
|
|
29
|
15-Aug-13
|
31655.07
|
1160.82
|
316.55
|
844.27
|
0.00
|
30810.80
|
|
30
|
15-Sep-13
|
30810.80
|
1160.82
|
308.11
|
852.71
|
0.00
|
29958.09
|
|
31
|
15-Oct-13
|
29958.09
|
1160.82
|
299.58
|
861.24
|
0.00
|
29096.85
|
|
32
|
15-Nov-13
|
29096.85
|
1160.82
|
290.97
|
869.85
|
0.00
|
28227.00
|
|
33
|
15-Dec-13
|
28227.00
|
1160.82
|
282.27
|
878.55
|
0.00
|
27348.45
|
|
34
|
15-Jan-14
|
27348.45
|
1160.82
|
273.49
|
887.33
|
0.00
|
26461.12
|
|
35
|
15-Feb-14
|
26461.12
|
1160.82
|
264.61
|
896.21
|
0.00
|
25564.91
|
|
36
|
15-Mar-14
|
25564.91
|
1160.82
|
255.65
|
905.17
|
0.00
|
24659.74
|
|
37
|
15-Apr-14
|
24659.74
|
1160.82
|
246.60
|
914.22
|
0.00
|
23745.52
|
|
38
|
15-May-14
|
23745.52
|
1160.82
|
237.46
|
923.36
|
0.00
|
22822.16
|
|
39
|
15-Jun-14
|
22822.16
|
1160.82
|
228.22
|
932.60
|
0.00
|
21889.56
|
|
40
|
15-Jul-14
|
21889.56
|
1160.82
|
218.90
|
941.92
|
0.00
|
20947.64
|
|
41
|
15-Aug-14
|
20947.64
|
1160.82
|
209.48
|
951.34
|
0.00
|
19996.30
|
|
42
|
15-Sep-14
|
19996.30
|
1160.82
|
199.97
|
960.85
|
0.00
|
19035.45
|
|
43
|
15-Oct-14
|
19035.45
|
1160.82
|
190.36
|
970.46
|
0.00
|
18064.99
|
|
44
|
15-Nov-14
|
18064.99
|
1160.82
|
180.65
|
980.17
|
0.00
|
17084.82
|
|
45
|
15-Dec-14
|
17084.82
|
1160.82
|
170.85
|
989.97
|
0.00
|
16094.85
|
|
46
|
15-Jan-15
|
16094.85
|
1160.82
|
160.95
|
999.87
|
0.00
|
15094.98
|
|
47
|
15-Feb-15
|
15094.98
|
1160.82
|
150.95
|
1009.87
|
0.00
|
14085.11
|
|
48
|
15-Mar-15
|
14085.11
|
1160.82
|
140.85
|
1019.97
|
0.00
|
13065.14
|
|
49
|
15-Apr-15
|
13065.14
|
1160.82
|
130.65
|
1030.17
|
0.00
|
12034.97
|
|
50
|
15-May-15
|
12034.97
|
1160.82
|
120.35
|
1040.47
|
0.00
|
10994.50
|
|
51
|
15-Jun-15
|
10994.50
|
1160.82
|
109.95
|
1050.87
|
0.00
|
9943.63
|
|
52
|
15-Jul-15
|
9943.63
|
1160.82
|
99.44
|
1061.38
|
0.00
|
8882.25
|
|
53
|
15-Aug-15
|
8882.25
|
1160.82
|
88.82
|
1072.00
|
0.00
|
7810.25
|
|
54
|
15-Sep-15
|
7810.25
|
1160.82
|
78.10
|
1082.72
|
0.00
|
6727.53
|
|
55
|
15-Oct-15
|
6727.53
|
1160.82
|
67.28
|
1093.54
|
0.00
|
5633.99
|
|
56
|
15-Nov-15
|
5633.99
|
1160.82
|
56.34
|
1104.48
|
0.00
|
4529.51
|
|
57
|
15-Dec-15
|
4529.51
|
1160.82
|
45.30
|
1115.52
|
0.00
|
3413.99
|
|
58
|
15-Jan-16
|
3413.99
|
1160.82
|
34.14
|
1126.68
|
0.00
|
2287.31
|
|
59
|
15-Feb-16
|
2287.31
|
1160.82
|
22.87
|
1137.95
|
0.00
|
1149.36
|
|
60
|
15-Mar-16
|
1149.36
|
1160.85
|
11.49
|
1149.36
|
0.00
|
0.00
|
In this example, we have a 7,125,000 loan that pays monthly and is being amortized over 25 years, but the loan is paid off in full at the end of 10 years. The monthly payment is 41,554.10
SELECT *
FROM wct.LPMTSCHED(
7125000 --Present Value
,'11/18/2008' --Loan Date
,41554.10 --Pmt
,NULL --Rate
,'12/1/2008' --First Payment Date
,300 --Number of Payments
,12 --Payments per Year
,NULL --Days In Year
,0 --Future Value
,NULL --Intrule
,2 --Decimals
,120 --Last Payment Number
)
This produces the following result.
|
num_pmt
|
date_pmt
|
amt_prin_init
|
amt_pmt
|
amt_int_pay
|
amt_prin_pay
|
amt_int_def
|
amt_prin_end
|
|
0
|
18-Nov-08
|
0.00
|
0.00
|
0.00
|
0.00
|
0.00
|
7125000.00
|
|
1
|
1-Dec-08
|
7125000.00
|
41554.10
|
12864.58
|
28689.52
|
0.00
|
7096310.48
|
|
2
|
1-Jan-09
|
7096310.48
|
41554.10
|
29567.96
|
11986.14
|
0.00
|
7084324.34
|
|
3
|
1-Feb-09
|
7084324.34
|
41554.10
|
29518.01
|
12036.09
|
0.00
|
7072288.25
|
|
4
|
1-Mar-09
|
7072288.25
|
41554.10
|
29467.86
|
12086.24
|
0.00
|
7060202.01
|
|
5
|
1-Apr-09
|
7060202.01
|
41554.10
|
29417.50
|
12136.60
|
0.00
|
7048065.41
|
|
6
|
1-May-09
|
7048065.41
|
41554.10
|
29366.93
|
12187.17
|
0.00
|
7035878.24
|
|
7
|
1-Jun-09
|
7035878.24
|
41554.10
|
29316.15
|
12237.95
|
0.00
|
7023640.29
|
|
8
|
1-Jul-09
|
7023640.29
|
41554.10
|
29265.16
|
12288.94
|
0.00
|
7011351.35
|
|
9
|
1-Aug-09
|
7011351.35
|
41554.10
|
29213.96
|
12340.14
|
0.00
|
6999011.21
|
|
10
|
1-Sep-09
|
6999011.21
|
41554.10
|
29162.54
|
12391.56
|
0.00
|
6986619.65
|
|
11
|
1-Oct-09
|
6986619.65
|
41554.10
|
29110.91
|
12443.19
|
0.00
|
6974176.46
|
|
12
|
1-Nov-09
|
6974176.46
|
41554.10
|
29059.06
|
12495.04
|
0.00
|
6961681.42
|
|
13
|
1-Dec-09
|
6961681.42
|
41554.10
|
29007.00
|
12547.10
|
0.00
|
6949134.32
|
|
14
|
1-Jan-10
|
6949134.32
|
41554.10
|
28954.72
|
12599.38
|
0.00
|
6936534.94
|
|
15
|
1-Feb-10
|
6936534.94
|
41554.10
|
28902.22
|
12651.88
|
0.00
|
6923883.06
|
|
16
|
1-Mar-10
|
6923883.06
|
41554.10
|
28849.51
|
12704.59
|
0.00
|
6911178.47
|
|
17
|
1-Apr-10
|
6911178.47
|
41554.10
|
28796.57
|
12757.53
|
0.00
|
6898420.94
|
|
18
|
1-May-10
|
6898420.94
|
41554.10
|
28743.42
|
12810.68
|
0.00
|
6885610.26
|
|
19
|
1-Jun-10
|
6885610.26
|
41554.10
|
28690.04
|
12864.06
|
0.00
|
6872746.20
|
|
20
|
1-Jul-10
|
6872746.20
|
41554.10
|
28636.44
|
12917.66
|
0.00
|
6859828.54
|
|
21
|
1-Aug-10
|
6859828.54
|
41554.10
|
28582.61
|
12971.49
|
0.00
|
6846857.05
|
|
22
|
1-Sep-10
|
6846857.05
|
41554.10
|
28528.57
|
13025.53
|
0.00
|
6833831.52
|
|
23
|
1-Oct-10
|
6833831.52
|
41554.10
|
28474.29
|
13079.81
|
0.00
|
6820751.71
|
|
24
|
1-Nov-10
|
6820751.71
|
41554.10
|
28419.79
|
13134.31
|
0.00
|
6807617.40
|
|
25
|
1-Dec-10
|
6807617.40
|
41554.10
|
28365.07
|
13189.03
|
0.00
|
6794428.37
|
|
26
|
1-Jan-11
|
6794428.37
|
41554.10
|
28310.11
|
13243.99
|
0.00
|
6781184.38
|
|
27
|
1-Feb-11
|
6781184.38
|
41554.10
|
28254.93
|
13299.17
|
0.00
|
6767885.21
|
|
28
|
1-Mar-11
|
6767885.21
|
41554.10
|
28199.52
|
13354.58
|
0.00
|
6754530.63
|
|
29
|
1-Apr-11
|
6754530.63
|
41554.10
|
28143.87
|
13410.23
|
0.00
|
6741120.40
|
|
30
|
1-May-11
|
6741120.40
|
41554.10
|
28088.00
|
13466.10
|
0.00
|
6727654.30
|
|
31
|
1-Jun-11
|
6727654.30
|
41554.10
|
28031.89
|
13522.21
|
0.00
|
6714132.09
|
|
32
|
1-Jul-11
|
6714132.09
|
41554.10
|
27975.55
|
13578.55
|
0.00
|
6700553.54
|
|
33
|
1-Aug-11
|
6700553.54
|
41554.10
|
27918.97
|
13635.13
|
0.00
|
6686918.41
|
|
34
|
1-Sep-11
|
6686918.41
|
41554.10
|
27862.16
|
13691.94
|
0.00
|
6673226.47
|
|
35
|
1-Oct-11
|
6673226.47
|
41554.10
|
27805.11
|
13748.99
|
0.00
|
6659477.48
|
|
36
|
1-Nov-11
|
6659477.48
|
41554.10
|
27747.82
|
13806.28
|
0.00
|
6645671.20
|
|
37
|
1-Dec-11
|
6645671.20
|
41554.10
|
27690.29
|
13863.81
|
0.00
|
6631807.39
|
|
38
|
1-Jan-12
|
6631807.39
|
41554.10
|
27632.53
|
13921.57
|
0.00
|
6617885.82
|
|
39
|
1-Feb-12
|
6617885.82
|
41554.10
|
27574.52
|
13979.58
|
0.00
|
6603906.24
|
|
40
|
1-Mar-12
|
6603906.24
|
41554.10
|
27516.27
|
14037.83
|
0.00
|
6589868.41
|
|
41
|
1-Apr-12
|
6589868.41
|
41554.10
|
27457.78
|
14096.32
|
0.00
|
6575772.09
|
|
42
|
1-May-12
|
6575772.09
|
41554.10
|
27399.05
|
14155.05
|
0.00
|
6561617.04
|
|
43
|
1-Jun-12
|
6561617.04
|
41554.10
|
27340.07
|
14214.03
|
0.00
|
6547403.01
|
|
44
|
1-Jul-12
|
6547403.01
|
41554.10
|
27280.84
|
14273.26
|
0.00
|
6533129.75
|
|
45
|
1-Aug-12
|
6533129.75
|
41554.10
|
27221.37
|
14332.73
|
0.00
|
6518797.02
|
|
46
|
1-Sep-12
|
6518797.02
|
41554.10
|
27161.65
|
14392.45
|
0.00
|
6504404.57
|
|
47
|
1-Oct-12
|
6504404.57
|
41554.10
|
27101.68
|
14452.42
|
0.00
|
6489952.15
|
|
48
|
1-Nov-12
|
6489952.15
|
41554.10
|
27041.46
|
14512.64
|
0.00
|
6475439.51
|
|
49
|
1-Dec-12
|
6475439.51
|
41554.10
|
26980.99
|
14573.11
|
0.00
|
6460866.40
|
|
50
|
1-Jan-13
|
6460866.40
|
41554.10
|
26920.27
|
14633.83
|
0.00
|
6446232.57
|
|
51
|
1-Feb-13
|
6446232.57
|
41554.10
|
26859.30
|
14694.80
|
0.00
|
6431537.77
|
|
52
|
1-Mar-13
|
6431537.77
|
41554.10
|
26798.07
|
14756.03
|
0.00
|
6416781.74
|
|
53
|
1-Apr-13
|
6416781.74
|
41554.10
|
26736.59
|
14817.51
|
0.00
|
6401964.23
|
|
54
|
1-May-13
|
6401964.23
|
41554.10
|
26674.85
|
14879.25
|
0.00
|
6387084.98
|
|
55
|
1-Jun-13
|
6387084.98
|
41554.10
|
26612.85
|
14941.25
|
0.00
|
6372143.73
|
|
56
|
1-Jul-13
|
6372143.73
|
41554.10
|
26550.59
|
15003.51
|
0.00
|
6357140.22
|
|
57
|
1-Aug-13
|
6357140.22
|
41554.10
|
26488.08
|
15066.02
|
0.00
|
6342074.20
|
|
58
|
1-Sep-13
|
6342074.20
|
41554.10
|
26425.30
|
15128.80
|
0.00
|
6326945.40
|
|
59
|
1-Oct-13
|
6326945.40
|
41554.10
|
26362.27
|
15191.83
|
0.00
|
6311753.57
|
|
60
|
1-Nov-13
|
6311753.57
|
41554.10
|
26298.97
|
15255.13
|
0.00
|
6296498.44
|
|
61
|
1-Dec-13
|
6296498.44
|
41554.10
|
26235.41
|
15318.69
|
0.00
|
6281179.75
|
|
62
|
1-Jan-14
|
6281179.75
|
41554.10
|
26171.58
|
15382.52
|
0.00
|
6265797.23
|
|
63
|
1-Feb-14
|
6265797.23
|
41554.10
|
26107.48
|
15446.62
|
0.00
|
6250350.61
|
|
64
|
1-Mar-14
|
6250350.61
|
41554.10
|
26043.12
|
15510.98
|
0.00
|
6234839.63
|
|
65
|
1-Apr-14
|
6234839.63
|
41554.10
|
25978.49
|
15575.61
|
0.00
|
6219264.02
|
|
66
|
1-May-14
|
6219264.02
|
41554.10
|
25913.60
|
15640.50
|
0.00
|
6203623.52
|
|
67
|
1-Jun-14
|
6203623.52
|
41554.10
|
25848.43
|
15705.67
|
0.00
|
6187917.85
|
|
68
|
1-Jul-14
|
6187917.85
|
41554.10
|
25782.99
|
15771.11
|
0.00
|
6172146.74
|
|
69
|
1-Aug-14
|
6172146.74
|
41554.10
|
25717.27
|
15836.83
|
0.00
|
6156309.91
|
|
70
|
1-Sep-14
|
6156309.91
|
41554.10
|
25651.29
|
15902.81
|
0.00
|
6140407.10
|
|
71
|
1-Oct-14
|
6140407.10
|
41554.10
|
25585.03
|
15969.07
|
0.00
|
6124438.03
|
|
72
|
1-Nov-14
|
6124438.03
|
41554.10
|
25518.49
|
16035.61
|
0.00
|
6108402.42
|
|
73
|
1-Dec-14
|
6108402.42
|
41554.10
|
25451.67
|
16102.43
|
0.00
|
6092299.99
|
|
74
|
1-Jan-15
|
6092299.99
|
41554.10
|
25384.58
|
16169.52
|
0.00
|
6076130.47
|
|
75
|
1-Feb-15
|
6076130.47
|
41554.10
|
25317.21
|
16236.89
|
0.00
|
6059893.58
|
|
76
|
1-Mar-15
|
6059893.58
|
41554.10
|
25249.55
|
16304.55
|
0.00
|
6043589.03
|
|
77
|
1-Apr-15
|
6043589.03
|
41554.10
|
25181.62
|
16372.48
|
0.00
|
6027216.55
|
|
78
|
1-May-15
|
6027216.55
|
41554.10
|
25113.40
|
16440.70
|
0.00
|
6010775.85
|
|
79
|
1-Jun-15
|
6010775.85
|
41554.10
|
25044.90
|
16509.20
|
0.00
|
5994266.65
|
|
80
|
1-Jul-15
|
5994266.65
|
41554.10
|
24976.11
|
16577.99
|
0.00
|
5977688.66
|
|
81
|
1-Aug-15
|
5977688.66
|
41554.10
|
24907.03
|
16647.07
|
0.00
|
5961041.59
|
|
82
|
1-Sep-15
|
5961041.59
|
41554.10
|
24837.67
|
16716.43
|
0.00
|
5944325.16
|
|
83
|
1-Oct-15
|
5944325.16
|
41554.10
|
24768.02
|
16786.08
|
0.00
|
5927539.08
|
|
84
|
1-Nov-15
|
5927539.08
|
41554.10
|
24698.08
|
16856.02
|
0.00
|
5910683.06
|
|
85
|
1-Dec-15
|
5910683.06
|
41554.10
|
24627.84
|
16926.26
|
0.00
|
5893756.80
|
|
86
|
1-Jan-16
|
5893756.80
|
41554.10
|
24557.32
|
16996.78
|
0.00
|
5876760.02
|
|
87
|
1-Feb-16
|
5876760.02
|
41554.10
|
24486.50
|
17067.60
|
0.00
|
5859692.42
|
|
88
|
1-Mar-16
|
5859692.42
|
41554.10
|
24415.38
|
17138.72
|
0.00
|
5842553.70
|
|
89
|
1-Apr-16
|
5842553.70
|
41554.10
|
24343.97
|
17210.13
|
0.00
|
5825343.57
|
|
90
|
1-May-16
|
5825343.57
|
41554.10
|
24272.26
|
17281.84
|
0.00
|
5808061.73
|
|
91
|
1-Jun-16
|
5808061.73
|
41554.10
|
24200.25
|
17353.85
|
0.00
|
5790707.88
|
|
92
|
1-Jul-16
|
5790707.88
|
41554.10
|
24127.95
|
17426.15
|
0.00
|
5773281.73
|
|
93
|
1-Aug-16
|
5773281.73
|
41554.10
|
24055.34
|
17498.76
|
0.00
|
5755782.97
|
|
94
|
1-Sep-16
|
5755782.97
|
41554.10
|
23982.43
|
17571.67
|
0.00
|
5738211.30
|
|
95
|
1-Oct-16
|
5738211.30
|
41554.10
|
23909.21
|
17644.89
|
0.00
|
5720566.41
|
|
96
|
1-Nov-16
|
5720566.41
|
41554.10
|
23835.69
|
17718.41
|
0.00
|
5702848.00
|
|
97
|
1-Dec-16
|
5702848.00
|
41554.10
|
23761.86
|
17792.24
|
0.00
|
5685055.76
|
|
98
|
1-Jan-17
|
5685055.76
|
41554.10
|
23687.73
|
17866.37
|
0.00
|
5667189.39
|
|
99
|
1-Feb-17
|
5667189.39
|
41554.10
|
23613.29
|
17940.81
|
0.00
|
5649248.58
|
|
100
|
1-Mar-17
|
5649248.58
|
41554.10
|
23538.53
|
18015.57
|
0.00
|
5631233.01
|
|
101
|
1-Apr-17
|
5631233.01
|
41554.10
|
23463.47
|
18090.63
|
0.00
|
5613142.38
|
|
102
|
1-May-17
|
5613142.38
|
41554.10
|
23388.09
|
18166.01
|
0.00
|
5594976.37
|
|
103
|
1-Jun-17
|
5594976.37
|
41554.10
|
23312.40
|
18241.70
|
0.00
|
5576734.67
|
|
104
|
1-Jul-17
|
5576734.67
|
41554.10
|
23236.39
|
18317.71
|
0.00
|
5558416.96
|
|
105
|
1-Aug-17
|
5558416.96
|
41554.10
|
23160.07
|
18394.03
|
0.00
|
5540022.93
|
|
106
|
1-Sep-17
|
5540022.93
|
41554.10
|
23083.43
|
18470.67
|
0.00
|
5521552.26
|
|
107
|
1-Oct-17
|
5521552.26
|
41554.10
|
23006.46
|
18547.64
|
0.00
|
5503004.62
|
|
108
|
1-Nov-17
|
5503004.62
|
41554.10
|
22929.18
|
18624.92
|
0.00
|
5484379.70
|
|
109
|
1-Dec-17
|
5484379.70
|
41554.10
|
22851.58
|
18702.52
|
0.00
|
5465677.18
|
|
110
|
1-Jan-18
|
5465677.18
|
41554.10
|
22773.65
|
18780.45
|
0.00
|
5446896.73
|
|
111
|
1-Feb-18
|
5446896.73
|
41554.10
|
22695.40
|
18858.70
|
0.00
|
5428038.03
|
|
112
|
1-Mar-18
|
5428038.03
|
41554.10
|
22616.82
|
18937.28
|
0.00
|
5409100.75
|
|
113
|
1-Apr-18
|
5409100.75
|
41554.10
|
22537.92
|
19016.18
|
0.00
|
5390084.57
|
|
114
|
1-May-18
|
5390084.57
|
41554.10
|
22458.68
|
19095.42
|
0.00
|
5370989.15
|
|
115
|
1-Jun-18
|
5370989.15
|
41554.10
|
22379.12
|
19174.98
|
0.00
|
5351814.17
|
|
116
|
1-Jul-18
|
5351814.17
|
41554.10
|
22299.22
|
19254.88
|
0.00
|
5332559.29
|
|
117
|
1-Aug-18
|
5332559.29
|
41554.10
|
22218.99
|
19335.11
|
0.00
|
5313224.18
|
|
118
|
1-Sep-18
|
5313224.18
|
41554.10
|
22138.43
|
19415.67
|
0.00
|
5293808.51
|
|
119
|
1-Oct-18
|
5293808.51
|
41554.10
|
22057.53
|
19496.57
|
0.00
|
5274311.94
|
|
120
|
1-Nov-18
|
5274311.94
|
5296288.24
|
21976.30
|
5274311.94
|
0.00
|
0.00
|
If we wanted to produce a summary by year of the previous example, we could enter:
SELECT YEAR(date_pmt) as [YEAR]
,ROUND(SUM(amt_pmt),2) as [Yearly Payments]
,ROUND(SUM(amt_int_pay), 2) as [Yearly Interest]
,ROUND(SUM(amt_prin_pay), 2) as [Yearly Principal]
FROM wct.LPMTSCHED(
7125000 --Present Value
,'11/18/2008' --Loan Date
,41554.10 --Pmt
,NULL --Rate
,'12/1/2008' --First Payment Date
,300 --Number of Payments
,12 --Payments per Year
,NULL --Days In Year
,0 --Future Value
,NULL --Intrule
,2 --Decimals
,120 --Last Payment Number
)
GROUP BY YEAR(date_pmt)
ORDER BY 1
This produces the following result.
YEAR Yearly Payments Yearly Interest Yearly Principal
----------- ---------------------- ---------------------- ----------------------
2008 41554.1 12864.58 28689.52
2009 498649.2 351473.04 147176.16
2010 498649.2 343943.25 154705.95
2011 498649.2 336028.22 162620.98
2012 498649.2 327708.21 170940.99
2013 498649.2 318962.55 179686.65
2014 498649.2 309769.44 188879.76
2015 498649.2 300106.01 198543.19
2016 498649.2 289948.16 208701.04
2017 498649.2 279270.62 219378.58
2018 5711829.24 246152.06 5465677.18
In this example, we will generate a schedule using an annual rate.
SELECT *
FROM wct.LPMTSCHED(
100000 --Present Value
,'2012-05-02' --Loan Date
,NULL --Pmt
,.18 --Rate
,'2012-06-01' --First Payment Date
,12 --Number of Payments
,12 --Payments per Year
,NULL --Days In Year
,0 --Future Value
,NULL --Intrule
,2 --Decimals
,NULL --Last Payment Number
)
This produces the following result.
|
num_pmt
|
date_pmt
|
amt_prin_init
|
amt_pmt
|
amt_int_pay
|
amt_prin_pay
|
amt_int_def
|
amt_prin_end
|
|
0
|
2-May-12
|
0.00
|
0.00
|
0.00
|
0.00
|
0.00
|
100000.00
|
|
1
|
1-Jun-12
|
100000.00
|
9163.48
|
1450.00
|
7713.48
|
0.00
|
92286.52
|
|
2
|
1-Jul-12
|
92286.52
|
9163.48
|
1384.30
|
7779.18
|
0.00
|
84507.34
|
|
3
|
1-Aug-12
|
84507.34
|
9163.48
|
1267.61
|
7895.87
|
0.00
|
76611.47
|
|
4
|
1-Sep-12
|
76611.47
|
9163.48
|
1149.17
|
8014.31
|
0.00
|
68597.16
|
|
5
|
1-Oct-12
|
68597.16
|
9163.48
|
1028.96
|
8134.52
|
0.00
|
60462.64
|
|
6
|
1-Nov-12
|
60462.64
|
9163.48
|
906.94
|
8256.54
|
0.00
|
52206.10
|
|
7
|
1-Dec-12
|
52206.10
|
9163.48
|
783.09
|
8380.39
|
0.00
|
43825.71
|
|
8
|
1-Jan-13
|
43825.71
|
9163.48
|
657.39
|
8506.09
|
0.00
|
35319.62
|
|
9
|
1-Feb-13
|
35319.62
|
9163.48
|
529.79
|
8633.69
|
0.00
|
26685.93
|
|
10
|
1-Mar-13
|
26685.93
|
9163.48
|
400.29
|
8763.19
|
0.00
|
17922.74
|
|
11
|
1-Apr-13
|
17922.74
|
9163.48
|
268.84
|
8894.64
|
0.00
|
9028.10
|
|
12
|
1-May-13
|
9028.10
|
9163.52
|
135.42
|
9028.10
|
0.00
|
0.00
|
Now, let’s take the same loan, and see what happens if we keep the interest rate the same and round the payment up to 9200.
SELECT *
FROM wct.LPMTSCHED(
100000 --Present Value
,'2012-05-02' --Loan Date
,9200 --Pmt
,.18 --Rate
,'2012-06-01' --First Payment Date
,12 --Number of Payments
,12 --Payments per Year
,NULL --Days In Year
,0 --Future Value
,NULL --Intrule
,2 --Decimals
,NULL --Last Payment Number
)
This produces the following result.
|
num_pmt
|
date_pmt
|
amt_prin_init
|
amt_pmt
|
amt_int_pay
|
amt_prin_pay
|
amt_int_def
|
amt_prin_end
|
|
0
|
2-May-12
|
0.00
|
0.00
|
0.00
|
0.00
|
0.00
|
100000.00
|
|
1
|
1-Jun-12
|
100000.00
|
9200.00
|
1450.00
|
7750.00
|
0.00
|
92250.00
|
|
2
|
1-Jul-12
|
92250.00
|
9200.00
|
1383.75
|
7816.25
|
0.00
|
84433.75
|
|
3
|
1-Aug-12
|
84433.75
|
9200.00
|
1266.51
|
7933.49
|
0.00
|
76500.26
|
|
4
|
1-Sep-12
|
76500.26
|
9200.00
|
1147.50
|
8052.50
|
0.00
|
68447.76
|
|
5
|
1-Oct-12
|
68447.76
|
9200.00
|
1026.72
|
8173.28
|
0.00
|
60274.48
|
|
6
|
1-Nov-12
|
60274.48
|
9200.00
|
904.12
|
8295.88
|
0.00
|
51978.60
|
|
7
|
1-Dec-12
|
51978.60
|
9200.00
|
779.68
|
8420.32
|
0.00
|
43558.28
|
|
8
|
1-Jan-13
|
43558.28
|
9200.00
|
653.37
|
8546.63
|
0.00
|
35011.65
|
|
9
|
1-Feb-13
|
35011.65
|
9200.00
|
525.17
|
8674.83
|
0.00
|
26336.82
|
|
10
|
1-Mar-13
|
26336.82
|
9200.00
|
395.05
|
8804.95
|
0.00
|
17531.87
|
|
11
|
1-Apr-13
|
17531.87
|
9200.00
|
262.98
|
8937.02
|
0.00
|
8594.85
|
|
12
|
1-May-13
|
8594.85
|
8723.77
|
128.92
|
8594.85
|
0.00
|
0.00
|
And here’s what happens with the same loan, if we keep the payment at 9200 and make the rate NULL, so that the function will calculate the rate based on the input
SELECT *
FROM wct.LPMTSCHED(
100000 --Present Value
,'2012-05-02' --Loan Date
,9200 --Pmt
,NULL --Rate
,'2012-06-01' --First Payment Date
,12 --Number of Payments
,12 --Payments per Year
,NULL --Days In Year
,0 --Future Value
,NULL --Intrule
,2 --Decimals
,NULL --Last Payment Number
)
This produces the following result.
|
num_pmt
|
date_pmt
|
amt_prin_init
|
amt_pmt
|
amt_int_pay
|
amt_prin_pay
|
amt_int_def
|
amt_prin_end
|
|
0
|
2-May-12
|
0.00
|
0.00
|
0.00
|
0.00
|
0.00
|
100000.00
|
|
1
|
1-Jun-12
|
100000.00
|
9200.00
|
1512.10
|
7687.90
|
0.00
|
92312.10
|
|
2
|
1-Jul-12
|
92312.10
|
9200.00
|
1443.98
|
7756.02
|
0.00
|
84556.08
|
|
3
|
1-Aug-12
|
84556.08
|
9200.00
|
1322.66
|
7877.34
|
0.00
|
76678.74
|
|
4
|
1-Sep-12
|
76678.74
|
9200.00
|
1199.44
|
8000.56
|
0.00
|
68678.18
|
|
5
|
1-Oct-12
|
68678.18
|
9200.00
|
1074.29
|
8125.71
|
0.00
|
60552.47
|
|
6
|
1-Nov-12
|
60552.47
|
9200.00
|
947.19
|
8252.81
|
0.00
|
52299.66
|
|
7
|
1-Dec-12
|
52299.66
|
9200.00
|
818.09
|
8381.91
|
0.00
|
43917.75
|
|
8
|
1-Jan-13
|
43917.75
|
9200.00
|
686.98
|
8513.02
|
0.00
|
35404.73
|
|
9
|
1-Feb-13
|
35404.73
|
9200.00
|
553.81
|
8646.19
|
0.00
|
26758.54
|
|
10
|
1-Mar-13
|
26758.54
|
9200.00
|
418.57
|
8781.43
|
0.00
|
17977.11
|
|
11
|
1-Apr-13
|
17977.11
|
9200.00
|
281.20
|
8918.80
|
0.00
|
9058.31
|
|
12
|
1-May-13
|
9058.31
|
9200.00
|
141.69
|
9058.31
|
0.00
|
0.00
|