Login     Register

        Contact Us     Search

XLeratorDB/financial Documentation

SQL Server loan amortization schedule


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

 


Copyright 2008-2024 Westclintech LLC         Privacy Policy        Terms of Service