AMORTSCHED
Updated: 11 May 2012
Use AMORTSCHED to generate an amortization schedule for a loan. 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.
AMORTSCHED 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.
Syntax
SELECT * FROM [wctFinancial].[wct].[AMORTSCHED] (
<@PV, float,>
,<@LoanDate, datetime,>
,<@Rate, float,>
,<@FirstPayDate, datetime,>
,<@NumPmts, int,>
,<@Pmtpyr, int,>
,<@DaysInYr, int,>
,<@FV, float,>
,<@IntRule, nvarchar(4000),>)
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.
@Rate
the annual interest rate. @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.
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.
|
|
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).
|
|
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, 365360, 365365 or 365
· @PV must be greater than zero
Examples
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 rate on the loan is 6 per cent.
SELECT *
FROM wct.AMORTSCHED(
50000 --Loan Amount
,'11/01/2010' --Loan Start Date
,.06 --Annual Interest Rate
,'12/01/2010' --First Payment Date
,60 --Number of payments (5*12)
,12 --Number of payments per year
,NULL --Days in year (defaults to 360)
,NULL --FV (defaults to 0)
,NULL --IntRule (defaults to 'A' meaning actuarial)
)
This produces the following results, which have been reformatted for ease of viewing.
|
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.0000
|
0.0000
|
0.0000
|
0.0000
|
0.0000
|
50000.0000
|
|
1
|
1-Dec-10
|
50000.0000
|
966.6401
|
250.0000
|
716.6401
|
0.0000
|
49283.3599
|
|
2
|
1-Jan-11
|
49283.3599
|
966.6401
|
246.4168
|
720.2233
|
0.0000
|
48563.1366
|
|
3
|
1-Feb-11
|
48563.1366
|
966.6401
|
242.8157
|
723.8244
|
0.0000
|
47839.3123
|
|
4
|
1-Mar-11
|
47839.3123
|
966.6401
|
239.1966
|
727.4435
|
0.0000
|
47111.8687
|
|
5
|
1-Apr-11
|
47111.8687
|
966.6401
|
235.5593
|
731.0807
|
0.0000
|
46380.7880
|
|
6
|
1-May-11
|
46380.7880
|
966.6401
|
231.9039
|
734.7361
|
0.0000
|
45646.0519
|
|
7
|
1-Jun-11
|
45646.0519
|
966.6401
|
228.2303
|
738.4098
|
0.0000
|
44907.6421
|
|
8
|
1-Jul-11
|
44907.6421
|
966.6401
|
224.5382
|
742.1019
|
0.0000
|
44165.5402
|
|
9
|
1-Aug-11
|
44165.5402
|
966.6401
|
220.8277
|
745.8124
|
0.0000
|
43419.7278
|
|
10
|
1-Sep-11
|
43419.7278
|
966.6401
|
217.0986
|
749.5414
|
0.0000
|
42670.1864
|
|
11
|
1-Oct-11
|
42670.1864
|
966.6401
|
213.3509
|
753.2891
|
0.0000
|
41916.8972
|
|
12
|
1-Nov-11
|
41916.8972
|
966.6401
|
209.5845
|
757.0556
|
0.0000
|
41159.8416
|
|
13
|
1-Dec-11
|
41159.8416
|
966.6401
|
205.7992
|
760.8409
|
0.0000
|
40399.0008
|
|
14
|
1-Jan-12
|
40399.0008
|
966.6401
|
201.9950
|
764.6451
|
0.0000
|
39634.3557
|
|
15
|
1-Feb-12
|
39634.3557
|
966.6401
|
198.1718
|
768.4683
|
0.0000
|
38865.8874
|
|
16
|
1-Mar-12
|
38865.8874
|
966.6401
|
194.3294
|
772.3106
|
0.0000
|
38093.5768
|
|
17
|
1-Apr-12
|
38093.5768
|
966.6401
|
190.4679
|
776.1722
|
0.0000
|
37317.4046
|
|
18
|
1-May-12
|
37317.4046
|
966.6401
|
186.5870
|
780.0531
|
0.0000
|
36537.3515
|
|
19
|
1-Jun-12
|
36537.3515
|
966.6401
|
182.6868
|
783.9533
|
0.0000
|
35753.3982
|
|
20
|
1-Jul-12
|
35753.3982
|
966.6401
|
178.7670
|
787.8731
|
0.0000
|
34965.5251
|
|
21
|
1-Aug-12
|
34965.5251
|
966.6401
|
174.8276
|
791.8125
|
0.0000
|
34173.7127
|
|
22
|
1-Sep-12
|
34173.7127
|
966.6401
|
170.8686
|
795.7715
|
0.0000
|
33377.9411
|
|
23
|
1-Oct-12
|
33377.9411
|
966.6401
|
166.8897
|
799.7504
|
0.0000
|
32578.1908
|
|
24
|
1-Nov-12
|
32578.1908
|
966.6401
|
162.8910
|
803.7491
|
0.0000
|
31774.4417
|
|
25
|
1-Dec-12
|
31774.4417
|
966.6401
|
158.8722
|
807.7679
|
0.0000
|
30966.6738
|
|
26
|
1-Jan-13
|
30966.6738
|
966.6401
|
154.8334
|
811.8067
|
0.0000
|
30154.8671
|
|
27
|
1-Feb-13
|
30154.8671
|
966.6401
|
150.7743
|
815.8657
|
0.0000
|
29339.0013
|
|
28
|
1-Mar-13
|
29339.0013
|
966.6401
|
146.6950
|
819.9451
|
0.0000
|
28519.0563
|
|
29
|
1-Apr-13
|
28519.0563
|
966.6401
|
142.5953
|
824.0448
|
0.0000
|
27695.0115
|
|
30
|
1-May-13
|
27695.0115
|
966.6401
|
138.4751
|
828.1650
|
0.0000
|
26866.8465
|
|
31
|
1-Jun-13
|
26866.8465
|
966.6401
|
134.3342
|
832.3058
|
0.0000
|
26034.5406
|
|
32
|
1-Jul-13
|
26034.5406
|
966.6401
|
130.1727
|
836.4674
|
0.0000
|
25198.0732
|
|
33
|
1-Aug-13
|
25198.0732
|
966.6401
|
125.9904
|
840.6497
|
0.0000
|
24357.4235
|
|
34
|
1-Sep-13
|
24357.4235
|
966.6401
|
121.7871
|
844.8530
|
0.0000
|
23512.5706
|
|
35
|
1-Oct-13
|
23512.5706
|
966.6401
|
117.5629
|
849.0772
|
0.0000
|
22663.4933
|
|
36
|
1-Nov-13
|
22663.4933
|
966.6401
|
113.3175
|
853.3226
|
0.0000
|
21810.1707
|
|
37
|
1-Dec-13
|
21810.1707
|
966.6401
|
109.0509
|
857.5892
|
0.0000
|
20952.5815
|
|
38
|
1-Jan-14
|
20952.5815
|
966.6401
|
104.7629
|
861.8772
|
0.0000
|
20090.7043
|
|
39
|
1-Feb-14
|
20090.7043
|
966.6401
|
100.4535
|
866.1866
|
0.0000
|
19224.5178
|
|
40
|
1-Mar-14
|
19224.5178
|
966.6401
|
96.1226
|
870.5175
|
0.0000
|
18354.0003
|
|
41
|
1-Apr-14
|
18354.0003
|
966.6401
|
91.7700
|
874.8701
|
0.0000
|
17479.1302
|
|
42
|
1-May-14
|
17479.1302
|
966.6401
|
87.3957
|
879.2444
|
0.0000
|
16599.8858
|
|
43
|
1-Jun-14
|
16599.8858
|
966.6401
|
82.9994
|
883.6406
|
0.0000
|
15716.2451
|
|
44
|
1-Jul-14
|
15716.2451
|
966.6401
|
78.5812
|
888.0589
|
0.0000
|
14828.1863
|
|
45
|
1-Aug-14
|
14828.1863
|
966.6401
|
74.1409
|
892.4991
|
0.0000
|
13935.6872
|
|
46
|
1-Sep-14
|
13935.6872
|
966.6401
|
69.6784
|
896.9616
|
0.0000
|
13038.7255
|
|
47
|
1-Oct-14
|
13038.7255
|
966.6401
|
65.1936
|
901.4464
|
0.0000
|
12137.2791
|
|
48
|
1-Nov-14
|
12137.2791
|
966.6401
|
60.6864
|
905.9537
|
0.0000
|
11231.3254
|
|
49
|
1-Dec-14
|
11231.3254
|
966.6401
|
56.1566
|
910.4834
|
0.0000
|
10320.8419
|
|
50
|
1-Jan-15
|
10320.8419
|
966.6401
|
51.6042
|
915.0359
|
0.0000
|
9405.8061
|
|
51
|
1-Feb-15
|
9405.8061
|
966.6401
|
47.0290
|
919.6110
|
0.0000
|
8486.1950
|
|
52
|
1-Mar-15
|
8486.1950
|
966.6401
|
42.4310
|
924.2091
|
0.0000
|
7561.9859
|
|
53
|
1-Apr-15
|
7561.9859
|
966.6401
|
37.8099
|
928.8301
|
0.0000
|
6633.1558
|
|
54
|
1-May-15
|
6633.1558
|
966.6401
|
33.1658
|
933.4743
|
0.0000
|
5699.6815
|
|
55
|
1-Jun-15
|
5699.6815
|
966.6401
|
28.4984
|
938.1417
|
0.0000
|
4761.5398
|
|
56
|
1-Jul-15
|
4761.5398
|
966.6401
|
23.8077
|
942.8324
|
0.0000
|
3818.7074
|
|
57
|
1-Aug-15
|
3818.7074
|
966.6401
|
19.0935
|
947.5465
|
0.0000
|
2871.1609
|
|
58
|
1-Sep-15
|
2871.1609
|
966.6401
|
14.3558
|
952.2843
|
0.0000
|
1918.8766
|
|
59
|
1-Oct-15
|
1918.8766
|
966.6401
|
9.5944
|
957.0457
|
0.0000
|
961.8309
|
|
60
|
1-Nov-15
|
961.8309
|
966.6401
|
4.8092
|
961.8309
|
0.0000
|
0.0000
|
This is an example of a loan that negatively amortizes principal using the actuarial rule.
SELECT *
FROM wct.AMORTSCHED(
50000 --Loan Amount
,'11/01/2010' --Loan Start Date
,.12 --Annual Interest Rate
,'04/15/2011' --First Payment Date
,60 --Number of payments (5*12)
,12 --Number of payments per year
,NULL --Days in year (defaults to 360)
,NULL --FV (defaults to 0)
,NULL --IntRule (defaults to 'A' meaning actuarial)
)
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.0000
|
0.0000
|
0.0000
|
0.0000
|
0.0000
|
50000.0000
|
|
1
|
15-Apr-11
|
50000.0000
|
1161.4098
|
2733.3333
|
-1571.9236
|
0.0000
|
51571.9236
|
|
2
|
15-May-11
|
51571.9236
|
1161.4098
|
515.7192
|
645.6905
|
0.0000
|
50926.2330
|
|
3
|
15-Jun-11
|
50926.2330
|
1161.4098
|
509.2623
|
652.1474
|
0.0000
|
50274.0856
|
|
4
|
15-Jul-11
|
50274.0856
|
1161.4098
|
502.7409
|
658.6689
|
0.0000
|
49615.4166
|
|
5
|
15-Aug-11
|
49615.4166
|
1161.4098
|
496.1542
|
665.2556
|
0.0000
|
48950.1610
|
|
6
|
15-Sep-11
|
48950.1610
|
1161.4098
|
489.5016
|
671.9082
|
0.0000
|
48278.2529
|
|
7
|
15-Oct-11
|
48278.2529
|
1161.4098
|
482.7825
|
678.6272
|
0.0000
|
47599.6256
|
|
8
|
15-Nov-11
|
47599.6256
|
1161.4098
|
475.9963
|
685.4135
|
0.0000
|
46914.2121
|
|
9
|
15-Dec-11
|
46914.2121
|
1161.4098
|
469.1421
|
692.2677
|
0.0000
|
46221.9444
|
|
10
|
15-Jan-12
|
46221.9444
|
1161.4098
|
462.2194
|
699.1903
|
0.0000
|
45522.7541
|
|
11
|
15-Feb-12
|
45522.7541
|
1161.4098
|
455.2275
|
706.1822
|
0.0000
|
44816.5719
|
|
12
|
15-Mar-12
|
44816.5719
|
1161.4098
|
448.1657
|
713.2441
|
0.0000
|
44103.3278
|
|
13
|
15-Apr-12
|
44103.3278
|
1161.4098
|
441.0333
|
720.3765
|
0.0000
|
43382.9513
|
|
14
|
15-May-12
|
43382.9513
|
1161.4098
|
433.8295
|
727.5803
|
0.0000
|
42655.3711
|
|
15
|
15-Jun-12
|
42655.3711
|
1161.4098
|
426.5537
|
734.8561
|
0.0000
|
41920.5150
|
|
16
|
15-Jul-12
|
41920.5150
|
1161.4098
|
419.2051
|
742.2046
|
0.0000
|
41178.3104
|
|
17
|
15-Aug-12
|
41178.3104
|
1161.4098
|
411.7831
|
749.6267
|
0.0000
|
40428.6837
|
|
18
|
15-Sep-12
|
40428.6837
|
1161.4098
|
404.2868
|
757.1229
|
0.0000
|
39671.5608
|
|
19
|
15-Oct-12
|
39671.5608
|
1161.4098
|
396.7156
|
764.6942
|
0.0000
|
38906.8666
|
|
20
|
15-Nov-12
|
38906.8666
|
1161.4098
|
389.0687
|
772.3411
|
0.0000
|
38134.5255
|
|
21
|
15-Dec-12
|
38134.5255
|
1161.4098
|
381.3453
|
780.0645
|
0.0000
|
37354.4609
|
|
22
|
15-Jan-13
|
37354.4609
|
1161.4098
|
373.5446
|
787.8652
|
0.0000
|
36566.5958
|
|
23
|
15-Feb-13
|
36566.5958
|
1161.4098
|
365.6660
|
795.7438
|
0.0000
|
35770.8520
|
|
24
|
15-Mar-13
|
35770.8520
|
1161.4098
|
357.7085
|
803.7013
|
0.0000
|
34967.1507
|
|
25
|
15-Apr-13
|
34967.1507
|
1161.4098
|
349.6715
|
811.7383
|
0.0000
|
34155.4124
|
|
26
|
15-May-13
|
34155.4124
|
1161.4098
|
341.5541
|
819.8557
|
0.0000
|
33335.5568
|
|
27
|
15-Jun-13
|
33335.5568
|
1161.4098
|
333.3556
|
828.0542
|
0.0000
|
32507.5026
|
|
28
|
15-Jul-13
|
32507.5026
|
1161.4098
|
325.0750
|
836.3348
|
0.0000
|
31671.1678
|
|
29
|
15-Aug-13
|
31671.1678
|
1161.4098
|
316.7117
|
844.6981
|
0.0000
|
30826.4697
|
|
30
|
15-Sep-13
|
30826.4697
|
1161.4098
|
308.2647
|
853.1451
|
0.0000
|
29973.3246
|
|
31
|
15-Oct-13
|
29973.3246
|
1161.4098
|
299.7332
|
861.6765
|
0.0000
|
29111.6481
|
|
32
|
15-Nov-13
|
29111.6481
|
1161.4098
|
291.1165
|
870.2933
|
0.0000
|
28241.3548
|
|
33
|
15-Dec-13
|
28241.3548
|
1161.4098
|
282.4135
|
878.9962
|
0.0000
|
27362.3586
|
|
34
|
15-Jan-14
|
27362.3586
|
1161.4098
|
273.6236
|
887.7862
|
0.0000
|
26474.5724
|
|
35
|
15-Feb-14
|
26474.5724
|
1161.4098
|
264.7457
|
896.6641
|
0.0000
|
25577.9083
|
|
36
|
15-Mar-14
|
25577.9083
|
1161.4098
|
255.7791
|
905.6307
|
0.0000
|
24672.2777
|
|
37
|
15-Apr-14
|
24672.2777
|
1161.4098
|
246.7228
|
914.6870
|
0.0000
|
23757.5907
|
|
38
|
15-May-14
|
23757.5907
|
1161.4098
|
237.5759
|
923.8339
|
0.0000
|
22833.7568
|
|
39
|
15-Jun-14
|
22833.7568
|
1161.4098
|
228.3376
|
933.0722
|
0.0000
|
21900.6846
|
|
40
|
15-Jul-14
|
21900.6846
|
1161.4098
|
219.0068
|
942.4029
|
0.0000
|
20958.2816
|
|
41
|
15-Aug-14
|
20958.2816
|
1161.4098
|
209.5828
|
951.8270
|
0.0000
|
20006.4547
|
|
42
|
15-Sep-14
|
20006.4547
|
1161.4098
|
200.0645
|
961.3452
|
0.0000
|
19045.1095
|
|
43
|
15-Oct-14
|
19045.1095
|
1161.4098
|
190.4511
|
970.9587
|
0.0000
|
18074.1508
|
|
44
|
15-Nov-14
|
18074.1508
|
1161.4098
|
180.7415
|
980.6683
|
0.0000
|
17093.4825
|
|
45
|
15-Dec-14
|
17093.4825
|
1161.4098
|
170.9348
|
990.4750
|
0.0000
|
16103.0075
|
|
46
|
15-Jan-15
|
16103.0075
|
1161.4098
|
161.0301
|
1000.3797
|
0.0000
|
15102.6278
|
|
47
|
15-Feb-15
|
15102.6278
|
1161.4098
|
151.0263
|
1010.3835
|
0.0000
|
14092.2443
|
|
48
|
15-Mar-15
|
14092.2443
|
1161.4098
|
140.9224
|
1020.4873
|
0.0000
|
13071.7570
|
|
49
|
15-Apr-15
|
13071.7570
|
1161.4098
|
130.7176
|
1030.6922
|
0.0000
|
12041.0648
|
|
50
|
15-May-15
|
12041.0648
|
1161.4098
|
120.4106
|
1040.9991
|
0.0000
|
11000.0657
|
|
51
|
15-Jun-15
|
11000.0657
|
1161.4098
|
110.0007
|
1051.4091
|
0.0000
|
9948.6566
|
|
52
|
15-Jul-15
|
9948.6566
|
1161.4098
|
99.4866
|
1061.9232
|
0.0000
|
8886.7333
|
|
53
|
15-Aug-15
|
8886.7333
|
1161.4098
|
88.8673
|
1072.5424
|
0.0000
|
7814.1909
|
|
54
|
15-Sep-15
|
7814.1909
|
1161.4098
|
78.1419
|
1083.2679
|
0.0000
|
6730.9230
|
|
55
|
15-Oct-15
|
6730.9230
|
1161.4098
|
67.3092
|
1094.1005
|
0.0000
|
5636.8225
|
|
56
|
15-Nov-15
|
5636.8225
|
1161.4098
|
56.3682
|
1105.0416
|
0.0000
|
4531.7809
|
|
57
|
15-Dec-15
|
4531.7809
|
1161.4098
|
45.3178
|
1116.0920
|
0.0000
|
3415.6890
|
|
58
|
15-Jan-16
|
3415.6890
|
1161.4098
|
34.1569
|
1127.2529
|
0.0000
|
2288.4361
|
|
59
|
15-Feb-16
|
2288.4361
|
1161.4098
|
22.8844
|
1138.5254
|
0.0000
|
1149.9107
|
|
60
|
15-Mar-16
|
1149.9107
|
1161.4098
|
11.4991
|
1149.9107
|
0.0000
|
0.0000
|
The same loan, except that the US Rule is applied, so there is no negative amortization.
SELECT *
FROM wct.AMORTSCHED(
50000 --Loan Amount
,'11/01/2010' --Loan Start Date
,.12 --Annual Interest Rate
,'04/15/2011' --First Payment Date
,60 --Number of payments (5*12)
,12 --Number of payments per year
,NULL --Days in year (defaults to 360)
,NULL --FV (defaults to 0)
,'U' --IntRule (defaults to 'A' meaning actuarial)
)
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.0000
|
0.0000
|
0.0000
|
0.0000
|
0.0000
|
50000.0000
|
|
1
|
15-Apr-11
|
50000.0000
|
1160.8164
|
2733.3333
|
0.0000
|
1572.5169
|
50000.0000
|
|
2
|
15-May-11
|
50000.0000
|
1160.8164
|
500.0000
|
0.0000
|
-660.8164
|
50000.0000
|
|
3
|
15-Jun-11
|
50000.0000
|
1160.8164
|
500.0000
|
0.0000
|
-660.8164
|
50000.0000
|
|
4
|
15-Jul-11
|
50000.0000
|
1160.8164
|
500.0000
|
409.9323
|
-250.8841
|
49590.0677
|
|
5
|
15-Aug-11
|
49590.0677
|
1160.8164
|
495.9007
|
664.9157
|
0.0000
|
48925.1520
|
|
6
|
15-Sep-11
|
48925.1520
|
1160.8164
|
489.2515
|
671.5649
|
0.0000
|
48253.5871
|
|
7
|
15-Oct-11
|
48253.5871
|
1160.8164
|
482.5359
|
678.2805
|
0.0000
|
47575.3066
|
|
8
|
15-Nov-11
|
47575.3066
|
1160.8164
|
475.7531
|
685.0633
|
0.0000
|
46890.2432
|
|
9
|
15-Dec-11
|
46890.2432
|
1160.8164
|
468.9024
|
691.9140
|
0.0000
|
46198.3293
|
|
10
|
15-Jan-12
|
46198.3293
|
1160.8164
|
461.9833
|
698.8331
|
0.0000
|
45499.4962
|
|
11
|
15-Feb-12
|
45499.4962
|
1160.8164
|
454.9950
|
705.8214
|
0.0000
|
44793.6747
|
|
12
|
15-Mar-12
|
44793.6747
|
1160.8164
|
447.9367
|
712.8797
|
0.0000
|
44080.7951
|
|
13
|
15-Apr-12
|
44080.7951
|
1160.8164
|
440.8080
|
720.0085
|
0.0000
|
43360.7866
|
|
14
|
15-May-12
|
43360.7866
|
1160.8164
|
433.6079
|
727.2085
|
0.0000
|
42633.5781
|
|
15
|
15-Jun-12
|
42633.5781
|
1160.8164
|
426.3358
|
734.4806
|
0.0000
|
41899.0975
|
|
16
|
15-Jul-12
|
41899.0975
|
1160.8164
|
418.9910
|
741.8254
|
0.0000
|
41157.2720
|
|
17
|
15-Aug-12
|
41157.2720
|
1160.8164
|
411.5727
|
749.2437
|
0.0000
|
40408.0283
|
|
18
|
15-Sep-12
|
40408.0283
|
1160.8164
|
404.0803
|
756.7361
|
0.0000
|
39651.2922
|
|
19
|
15-Oct-12
|
39651.2922
|
1160.8164
|
396.5129
|
764.3035
|
0.0000
|
38886.9887
|
|
20
|
15-Nov-12
|
38886.9887
|
1160.8164
|
388.8699
|
771.9465
|
0.0000
|
38115.0422
|
|
21
|
15-Dec-12
|
38115.0422
|
1160.8164
|
381.1504
|
779.6660
|
0.0000
|
37335.3762
|
|
22
|
15-Jan-13
|
37335.3762
|
1160.8164
|
373.3538
|
787.4626
|
0.0000
|
36547.9136
|
|
23
|
15-Feb-13
|
36547.9136
|
1160.8164
|
365.4791
|
795.3373
|
0.0000
|
35752.5763
|
|
24
|
15-Mar-13
|
35752.5763
|
1160.8164
|
357.5258
|
803.2906
|
0.0000
|
34949.2857
|
|
25
|
15-Apr-13
|
34949.2857
|
1160.8164
|
349.4929
|
811.3235
|
0.0000
|
34137.9622
|
|
26
|
15-May-13
|
34137.9622
|
1160.8164
|
341.3796
|
819.4368
|
0.0000
|
33318.5254
|
|
27
|
15-Jun-13
|
33318.5254
|
1160.8164
|
333.1853
|
827.6311
|
0.0000
|
32490.8942
|
|
28
|
15-Jul-13
|
32490.8942
|
1160.8164
|
324.9089
|
835.9075
|
0.0000
|
31654.9868
|
|
29
|
15-Aug-13
|
31654.9868
|
1160.8164
|
316.5499
|
844.2665
|
0.0000
|
30810.7202
|
|
30
|
15-Sep-13
|
30810.7202
|
1160.8164
|
308.1072
|
852.7092
|
0.0000
|
29958.0110
|
|
31
|
15-Oct-13
|
29958.0110
|
1160.8164
|
299.5801
|
861.2363
|
0.0000
|
29096.7747
|
|
32
|
15-Nov-13
|
29096.7747
|
1160.8164
|
290.9677
|
869.8487
|
0.0000
|
28226.9261
|
|
33
|
15-Dec-13
|
28226.9261
|
1160.8164
|
282.2693
|
878.5471
|
0.0000
|
27348.3789
|
|
34
|
15-Jan-14
|
27348.3789
|
1160.8164
|
273.4838
|
887.3326
|
0.0000
|
26461.0463
|
|
35
|
15-Feb-14
|
26461.0463
|
1160.8164
|
264.6105
|
896.2059
|
0.0000
|
25564.8404
|
|
36
|
15-Mar-14
|
25564.8404
|
1160.8164
|
255.6484
|
905.1680
|
0.0000
|
24659.6724
|
|
37
|
15-Apr-14
|
24659.6724
|
1160.8164
|
246.5967
|
914.2197
|
0.0000
|
23745.4527
|
|
38
|
15-May-14
|
23745.4527
|
1160.8164
|
237.4545
|
923.3619
|
0.0000
|
22822.0908
|
|
39
|
15-Jun-14
|
22822.0908
|
1160.8164
|
228.2209
|
932.5955
|
0.0000
|
21889.4953
|
|
40
|
15-Jul-14
|
21889.4953
|
1160.8164
|
218.8950
|
941.9214
|
0.0000
|
20947.5739
|
|
41
|
15-Aug-14
|
20947.5739
|
1160.8164
|
209.4757
|
951.3407
|
0.0000
|
19996.2332
|
|
42
|
15-Sep-14
|
19996.2332
|
1160.8164
|
199.9623
|
960.8541
|
0.0000
|
19035.3791
|
|
43
|
15-Oct-14
|
19035.3791
|
1160.8164
|
190.3538
|
970.4626
|
0.0000
|
18064.9165
|
|
44
|
15-Nov-14
|
18064.9165
|
1160.8164
|
180.6492
|
980.1672
|
0.0000
|
17084.7493
|
|
45
|
15-Dec-14
|
17084.7493
|
1160.8164
|
170.8475
|
989.9689
|
0.0000
|
16094.7804
|
|
46
|
15-Jan-15
|
16094.7804
|
1160.8164
|
160.9478
|
999.8686
|
0.0000
|
15094.9118
|
|
47
|
15-Feb-15
|
15094.9118
|
1160.8164
|
150.9491
|
1009.8673
|
0.0000
|
14085.0445
|
|
48
|
15-Mar-15
|
14085.0445
|
1160.8164
|
140.8504
|
1019.9660
|
0.0000
|
13065.0785
|
|
49
|
15-Apr-15
|
13065.0785
|
1160.8164
|
130.6508
|
1030.1656
|
0.0000
|
12034.9129
|
|
50
|
15-May-15
|
12034.9129
|
1160.8164
|
120.3491
|
1040.4673
|
0.0000
|
10994.4457
|
|
51
|
15-Jun-15
|
10994.4457
|
1160.8164
|
109.9445
|
1050.8719
|
0.0000
|
9943.5737
|
|
52
|
15-Jul-15
|
9943.5737
|
1160.8164
|
99.4357
|
1061.3807
|
0.0000
|
8882.1930
|
|
53
|
15-Aug-15
|
8882.1930
|
1160.8164
|
88.8219
|
1071.9945
|
0.0000
|
7810.1986
|
|
54
|
15-Sep-15
|
7810.1986
|
1160.8164
|
78.1020
|
1082.7144
|
0.0000
|
6727.4842
|
|
55
|
15-Oct-15
|
6727.4842
|
1160.8164
|
67.2748
|
1093.5416
|
0.0000
|
5633.9426
|
|
56
|
15-Nov-15
|
5633.9426
|
1160.8164
|
56.3394
|
1104.4770
|
0.0000
|
4529.4656
|
|
57
|
15-Dec-15
|
4529.4656
|
1160.8164
|
45.2947
|
1115.5217
|
0.0000
|
3413.9439
|
|
58
|
15-Jan-16
|
3413.9439
|
1160.8164
|
34.1394
|
1126.6770
|
0.0000
|
2287.2669
|
|
59
|
15-Feb-16
|
2287.2669
|
1160.8164
|
22.8727
|
1137.9437
|
0.0000
|
1149.3232
|
|
60
|
15-Mar-16
|
1149.3232
|
1160.8164
|
11.4932
|
1149.3232
|
0.0000
|
0.0000
|
In this example, we have a loan with weekly payments, but we want to summarize the payment information into by month and year.
SELECT
MONTH(date_pmt) as [MONTH]
,YEAR(date_pmt) as [YEAR]
,ROUND(SUM(amt_pmt),2) as [Monthly Payments]
,ROUND(SUM(amt_int_pay), 2) as [Monthly Interest]
,ROUND(SUM(amt_prin_pay), 2) as [Monthly Principal]
FROM wct.AMORTSCHED(
50000 --Loan Amount
,'10/28/2010' --Loan Start Date
,.12 --Annual Interest Rate
,'11/05/2010' --First Payment Date
,156 --Number of payments
,52 --Number of payments per year
,NULL --Days in year (defaults to 360)
,NULL --FV (defaults to 0)
,NULL --IntRule (defaults to 'A' meaning actuarial)
)
GROUP BY MONTH(date_pmt), YEAR(date_pmt)
ORDER BY 2, 1
This produces the following result.
|
MONTH
|
YEAR
|
Monthly Payments
|
Monthly Interest
|
Monthly Principal
|
|
10
|
2010
|
0
|
0
|
0
|
|
11
|
2010
|
1528.59
|
474.07
|
1054.52
|
|
12
|
2010
|
1910.74
|
558.53
|
1352.21
|
|
1
|
2011
|
1528.59
|
435.55
|
1093.05
|
|
2
|
2011
|
1528.59
|
425.42
|
1103.17
|
|
3
|
2011
|
1528.59
|
415.2
|
1113.39
|
|
4
|
2011
|
1910.74
|
504.49
|
1406.25
|
|
5
|
2011
|
1528.59
|
391.86
|
1136.73
|
|
6
|
2011
|
1528.59
|
381.34
|
1147.26
|
|
7
|
2011
|
1910.74
|
461.71
|
1449.03
|
|
8
|
2011
|
1528.59
|
357.29
|
1171.31
|
|
9
|
2011
|
1910.74
|
431.34
|
1479.4
|
|
10
|
2011
|
1528.59
|
332.73
|
1195.86
|
|
11
|
2011
|
1528.59
|
321.66
|
1206.93
|
|
12
|
2011
|
1910.74
|
386.34
|
1524.4
|
|
1
|
2012
|
1528.59
|
296.36
|
1232.23
|
|
2
|
2012
|
1528.59
|
284.94
|
1243.65
|
|
3
|
2012
|
1910.74
|
339.97
|
1570.77
|
|
4
|
2012
|
1528.59
|
258.87
|
1269.72
|
|
5
|
2012
|
1528.59
|
247.11
|
1281.48
|
|
6
|
2012
|
1910.74
|
292.19
|
1618.55
|
|
7
|
2012
|
1528.59
|
220.25
|
1308.34
|
|
8
|
2012
|
1910.74
|
258.26
|
1652.48
|
|
9
|
2012
|
1528.59
|
192.82
|
1335.77
|
|
10
|
2012
|
1528.59
|
180.45
|
1348.14
|
|
11
|
2012
|
1910.74
|
207.99
|
1702.75
|
|
12
|
2012
|
1528.59
|
152.19
|
1376.4
|
|
1
|
2013
|
1528.59
|
139.44
|
1389.15
|
|
2
|
2013
|
1528.59
|
126.58
|
1402.02
|
|
3
|
2013
|
1910.74
|
139.94
|
1770.8
|
|
4
|
2013
|
1528.59
|
97.19
|
1431.4
|
|
5
|
2013
|
1910.74
|
102.82
|
1807.92
|
|
6
|
2013
|
1528.59
|
67.18
|
1461.41
|
|
7
|
2013
|
1528.59
|
53.64
|
1474.95
|
|
8
|
2013
|
1910.74
|
47.83
|
1862.91
|
|
9
|
2013
|
1528.59
|
22.73
|
1505.86
|
|
10
|
2013
|
1528.59
|
8.78
|
1519.81
|