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, 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