Login     Register

        Contact Us     Search

XLeratorDB/financial Documentation

SQL Server amortization schedule for odd-first period loans


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