UNEQUALLOANPAYMENTS
Updated: 13 May 2012
Use the table-valued function UNEQUALLOANPAYMENTS to generate a payment schedule for a loan where the interest payment frequency and the principal payment frequency are different, or the loan starts with an interest only schedule with principal repayments commencing after the first interest payment date.
Syntax
SELECT * FROM [wctFinancial].[wct].[UNEQUALLOANPAYMENTS](
<@PV, float,>
,<@Rate, float,>
,<@LoanDate, datetime,>
,<@InterestFrequency, int,>
,<@FirstPaymentDate, datetime,>
,<@DaysInYr, int,>
,<@PrinPaymentMultiple, int,>
,<@FirstPrinPayNo, int,>
,<@NumberOfPayments, int,>
,<@LastPaymentNumber, int,>
,<@FV, float,>
,<@IsRegPay, bit,>)
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.
@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.
@LoanDate
the date that the loan starts accruing interest. @LoanDate is an expression of type datetime or of a type that can be implicitly converted to datetime.
@InterestFrequency
the number if times that interest is paid in a year. @InterestFrequency is an expression of type int or of a type that can be implicitly converted to int.
@FirstPaymentDate
the date that the first payment is due. @FirstPaymentDate is an expression of type datetime or of a type that can be implicitly converted to datetime.
@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.
@PrinPaymentMultiple
the ratio of the frequency of the interest payments to the frequency of the interest payments. For example, a loan with monthly payments of interest and quarterly payments of principal would have a @PrinPaymentMultiple of 3. @PrinPaymentMultiple is an expression of type int or of a type that can be implicitly converted to int.
@FirstPrinPayNo
the payment number of the first principal payment. @FirstPrinPayNo is an expression of type int or of a type that can be implicitly converted to int.
@NumberOfPayments
the total number of payments to be used in the calculation of the periodic payments. This may not be the actual number of payments on the loan, which can be specified by using @LastPaymentNumber. @NumberOfPayments is an expression of type int or of a type that can be implicitly converted to int.
@LastPaymentNumber
the number of the last loan payment if different than the @NumberOfPayments. @LastPaymentNumber 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.
@IsRegularPay
a bit value which specifies whether the first interest period is longer or shorter than the regular payment. If @IsRegularPay is False then the interest payment amount for the first period is calculated using the number of days in the period and the @DaysInYr value.
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_prin_end] [float] NULL
)
|
Column
|
Column Description
|
|
num_pmt
|
The payment number calcaulated chronologically from @FirstPaymentDate.
|
|
date_pmt
|
The date of the payment.
|
|
amt_prin_init
|
The principal amount at the beginning of the period. When num_pmt is equal to 0, the principal amount is the amount of the loan, otherwise the principal amount is the ending principal amount where num_pmt = num_pmt - 1.
|
|
amt_pmt
|
The calculated payment amount
|
|
amt_int_pay
|
The interest portion of amt_pmt. In num_pmt > 1 or num_pmt = 1 and @IsRegularPay = 'TRUE', amt_int_pay = @Rate / @InterestFrequency * amt_prin_init. If num_pmt = 1 and @IsRegularPay = 'FALSE' then amt_int_pay is calculated using the number of days from @IssueDate to @FirstPaymentDate based on @DaysInYr
|
|
amt_prin_pay
|
The principal portion on amt_pmt calculated as amt_pmt – amt_int_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 @PV is NULL then @PV = 0.
· If @Rate is NULL then @Rate = 0.
· If @LoanDate is NULL then @LoanDate = GETDATE().
· If @InterestFrequency is NULL then @InterestFrequency = 12.
· If @DaysInYr is NULL then @DaysInYr = 365.
· If @NumberOfPayments is NULL then @NumberOfPayments = 1
· If @LastPaymentNumber is NULL then @LastPaymentNumber = @NumberOfPayments.
· If @FV is NULL then @FV = 0.
· If @IsRegularPay is NULL then @IsRegularPay = 'TRUE'.
· If @FirstPaymentDate is NULL then @FirstPaymentDate is calculated using @LoanDate and @InterestFrequency.
· @InterestFrequency must be 1, 2, 3, 4, 6, 12, 13, 24, 26, 52, or 365.
· @NumberOfPayments must be greater than 1.
· @Rate must be greater than zero.
· @DaysInYr must be 360 or 365.
· If @NumberOfPayments is less than 1 then an error will be generated.
· If @PrinPaymentMultiple is less than 1 then an error will be generated.
· If @LastPaymentNumber is less than 1 then an error will be generated.
· If @FirstPrinPayNo < 2 then an error will be generated.
Examples
All the examples have been reformatted to make them easier to read.
A 1,000,000 loan dated 2014-01-15 with monthly payments commencing on 2014-02-15 and quarterly principal payments commencing with the third interest payment. Notice that the quarterly payments are constant at 130,690.30 but that interest payments very from quarter to quarter though they are the same for each month between principal payments.
SELECT
*
FROM
wct.UnequalLoanPayments(
1000000, --@PV
0.12, --@Rate
'2014-01-15', --@LoanDate
12, --@IntPmtPerYr
'2014-02-15', --@FirstPaymentDate
NULL, --@DaysInYr
3, --@PrinPayMultiple
3, --@FirstPrinPayNo
24, --@NumberOfPayments
NULL, --@LastPaymentNumber
0, --@FV
NULL --@IsRegPay
)k
This produces the following result.
|
num_pmt
|
date_pmt
|
amt_prin_init
|
amt_pmt
|
amt_int_pay
|
amt_prin_pay
|
amt_prin_end
|
|
0
|
2014-01-15
|
0
|
0
|
0
|
0
|
1000000
|
|
1
|
2014-02-15
|
1000000
|
10000
|
10000
|
0
|
1000000
|
|
2
|
2014-03-15
|
1000000
|
10000
|
10000
|
0
|
1000000
|
|
3
|
2014-04-15
|
1000000
|
130690.3
|
10000
|
120690.3
|
879309.7
|
|
4
|
2014-05-15
|
879309.7
|
8793.097
|
8793.097
|
0
|
879309.7
|
|
5
|
2014-06-15
|
879309.7
|
8793.097
|
8793.097
|
0
|
879309.7
|
|
6
|
2014-07-15
|
879309.7
|
130690.3
|
8793.097
|
121897.2
|
757412.5
|
|
7
|
2014-08-15
|
757412.5
|
7574.125
|
7574.125
|
0
|
757412.5
|
|
8
|
2014-09-15
|
757412.5
|
7574.125
|
7574.125
|
0
|
757412.5
|
|
9
|
2014-10-15
|
757412.5
|
130690.3
|
7574.125
|
123116.2
|
634296.3
|
|
10
|
2014-11-15
|
634296.3
|
6342.963
|
6342.963
|
0
|
634296.3
|
|
11
|
2014-12-15
|
634296.3
|
6342.963
|
6342.963
|
0
|
634296.3
|
|
12
|
2015-01-15
|
634296.3
|
130690.3
|
6342.963
|
124347.3
|
509949
|
|
13
|
2015-02-15
|
509949
|
5099.49
|
5099.49
|
0
|
509949
|
|
14
|
2015-03-15
|
509949
|
5099.49
|
5099.49
|
0
|
509949
|
|
15
|
2015-04-15
|
509949
|
130690.3
|
5099.49
|
125590.8
|
384358.2
|
|
16
|
2015-05-15
|
384358.2
|
3843.582
|
3843.582
|
0
|
384358.2
|
|
17
|
2015-06-15
|
384358.2
|
3843.582
|
3843.582
|
0
|
384358.2
|
|
18
|
2015-07-15
|
384358.2
|
130690.3
|
3843.582
|
126846.7
|
257511.5
|
|
19
|
2015-08-15
|
257511.5
|
2575.115
|
2575.115
|
0
|
257511.5
|
|
20
|
2015-09-15
|
257511.5
|
2575.115
|
2575.115
|
0
|
257511.5
|
|
21
|
2015-10-15
|
257511.5
|
130690.3
|
2575.115
|
128115.2
|
129396.3
|
|
22
|
2015-11-15
|
129396.3
|
1293.963
|
1293.963
|
0
|
129396.3
|
|
23
|
2015-12-15
|
129396.3
|
1293.963
|
1293.963
|
0
|
129396.3
|
|
24
|
2016-01-15
|
129396.3
|
130690.3
|
1293.963
|
129396.3
|
0
|
This loan, has monthly payments and is interest only for the first 6 months, with payments calculated assuming a 25-year maturity but payoff is at the end of 5 years.
SELECT
*
FROM
wct.UnequalLoanPayments(
1000000, --@PV
0.05, --@Rate
'2014-01-15', --@LoanDate
12, --@IntPmtPerYr
'2014-02-15', --@FirstPaymentDate
NULL, --@DaysInYr
1, --@PrinPayMultiple
7, --@FirstPrinPayNo
300, --@NumberOfPayments
60, --@LastPaymentNumber
0, --@FV
NULL --@IsRegPay
)k
This produces the following result.
|
num_pmt
|
date_pmt
|
amt_prin_init
|
amt_pmt
|
amt_int_pay
|
amt_prin_pay
|
amt_prin_end
|
|
0
|
2014-01-15
|
0.00
|
0.00
|
0.00
|
0.00
|
1000000.00
|
|
1
|
2014-02-15
|
1000000.00
|
4166.67
|
4166.67
|
0.00
|
1000000.00
|
|
2
|
2014-03-15
|
1000000.00
|
4166.67
|
4166.67
|
0.00
|
1000000.00
|
|
3
|
2014-04-15
|
1000000.00
|
4166.67
|
4166.67
|
0.00
|
1000000.00
|
|
4
|
2014-05-15
|
1000000.00
|
4166.67
|
4166.67
|
0.00
|
1000000.00
|
|
5
|
2014-06-15
|
1000000.00
|
4166.67
|
4166.67
|
0.00
|
1000000.00
|
|
6
|
2014-07-15
|
1000000.00
|
4166.67
|
4166.67
|
0.00
|
1000000.00
|
|
7
|
2014-08-15
|
1000000.00
|
5906.03
|
4166.67
|
1739.36
|
998260.64
|
|
8
|
2014-09-15
|
998260.64
|
5906.03
|
4159.42
|
1746.61
|
996514.03
|
|
9
|
2014-10-15
|
996514.03
|
5906.03
|
4152.14
|
1753.89
|
994760.14
|
|
10
|
2014-11-15
|
994760.14
|
5906.03
|
4144.83
|
1761.20
|
992998.94
|
|
11
|
2014-12-15
|
992998.94
|
5906.03
|
4137.50
|
1768.53
|
991230.41
|
|
12
|
2015-01-15
|
991230.41
|
5906.03
|
4130.13
|
1775.90
|
989454.51
|
|
13
|
2015-02-15
|
989454.51
|
5906.03
|
4122.73
|
1783.30
|
987671.21
|
|
14
|
2015-03-15
|
987671.21
|
5906.03
|
4115.30
|
1790.73
|
985880.47
|
|
15
|
2015-04-15
|
985880.47
|
5906.03
|
4107.84
|
1798.19
|
984082.28
|
|
16
|
2015-05-15
|
984082.28
|
5906.03
|
4100.34
|
1805.69
|
982276.59
|
|
17
|
2015-06-15
|
982276.59
|
5906.03
|
4092.82
|
1813.21
|
980463.38
|
|
18
|
2015-07-15
|
980463.38
|
5906.03
|
4085.26
|
1820.77
|
978642.62
|
|
19
|
2015-08-15
|
978642.62
|
5906.03
|
4077.68
|
1828.35
|
976814.27
|
|
20
|
2015-09-15
|
976814.27
|
5906.03
|
4070.06
|
1835.97
|
974978.30
|
|
21
|
2015-10-15
|
974978.30
|
5906.03
|
4062.41
|
1843.62
|
973134.68
|
|
22
|
2015-11-15
|
973134.68
|
5906.03
|
4054.73
|
1851.30
|
971283.37
|
|
23
|
2015-12-15
|
971283.37
|
5906.03
|
4047.01
|
1859.02
|
969424.36
|
|
24
|
2016-01-15
|
969424.36
|
5906.03
|
4039.27
|
1866.76
|
967557.60
|
|
25
|
2016-02-15
|
967557.60
|
5906.03
|
4031.49
|
1874.54
|
965683.06
|
|
26
|
2016-03-15
|
965683.06
|
5906.03
|
4023.68
|
1882.35
|
963800.71
|
|
27
|
2016-04-15
|
963800.71
|
5906.03
|
4015.84
|
1890.19
|
961910.52
|
|
28
|
2016-05-15
|
961910.52
|
5906.03
|
4007.96
|
1898.07
|
960012.45
|
|
29
|
2016-06-15
|
960012.45
|
5906.03
|
4000.05
|
1905.98
|
958106.47
|
|
30
|
2016-07-15
|
958106.47
|
5906.03
|
3992.11
|
1913.92
|
956192.55
|
|
31
|
2016-08-15
|
956192.55
|
5906.03
|
3984.14
|
1921.89
|
954270.66
|
|
32
|
2016-09-15
|
954270.66
|
5906.03
|
3976.13
|
1929.90
|
952340.75
|
|
33
|
2016-10-15
|
952340.75
|
5906.03
|
3968.09
|
1937.94
|
950402.81
|
|
34
|
2016-11-15
|
950402.81
|
5906.03
|
3960.01
|
1946.02
|
948456.79
|
|
35
|
2016-12-15
|
948456.79
|
5906.03
|
3951.90
|
1954.13
|
946502.67
|
|
36
|
2017-01-15
|
946502.67
|
5906.03
|
3943.76
|
1962.27
|
944540.40
|
|
37
|
2017-02-15
|
944540.40
|
5906.03
|
3935.58
|
1970.44
|
942569.96
|
|
38
|
2017-03-15
|
942569.96
|
5906.03
|
3927.37
|
1978.65
|
940591.30
|
|
39
|
2017-04-15
|
940591.30
|
5906.03
|
3919.13
|
1986.90
|
938604.40
|
|
40
|
2017-05-15
|
938604.40
|
5906.03
|
3910.85
|
1995.18
|
936609.22
|
|
41
|
2017-06-15
|
936609.22
|
5906.03
|
3902.54
|
2003.49
|
934605.73
|
|
42
|
2017-07-15
|
934605.73
|
5906.03
|
3894.19
|
2011.84
|
932593.89
|
|
43
|
2017-08-15
|
932593.89
|
5906.03
|
3885.81
|
2020.22
|
930573.67
|
|
44
|
2017-09-15
|
930573.67
|
5906.03
|
3877.39
|
2028.64
|
928545.03
|
|
45
|
2017-10-15
|
928545.03
|
5906.03
|
3868.94
|
2037.09
|
926507.94
|
|
46
|
2017-11-15
|
926507.94
|
5906.03
|
3860.45
|
2045.58
|
924462.36
|
|
47
|
2017-12-15
|
924462.36
|
5906.03
|
3851.93
|
2054.10
|
922408.26
|
|
48
|
2018-01-15
|
922408.26
|
5906.03
|
3843.37
|
2062.66
|
920345.60
|
|
49
|
2018-02-15
|
920345.60
|
5906.03
|
3834.77
|
2071.26
|
918274.34
|
|
50
|
2018-03-15
|
918274.34
|
5906.03
|
3826.14
|
2079.89
|
916194.46
|
|
51
|
2018-04-15
|
916194.46
|
5906.03
|
3817.48
|
2088.55
|
914105.90
|
|
52
|
2018-05-15
|
914105.90
|
5906.03
|
3808.77
|
2097.25
|
912008.65
|
|
53
|
2018-06-15
|
912008.65
|
5906.03
|
3800.04
|
2105.99
|
909902.66
|
|
54
|
2018-07-15
|
909902.66
|
5906.03
|
3791.26
|
2114.77
|
907787.89
|
|
55
|
2018-08-15
|
907787.89
|
5906.03
|
3782.45
|
2123.58
|
905664.31
|
|
56
|
2018-09-15
|
905664.31
|
5906.03
|
3773.60
|
2132.43
|
903531.88
|
|
57
|
2018-10-15
|
903531.88
|
5906.03
|
3764.72
|
2141.31
|
901390.57
|
|
58
|
2018-11-15
|
901390.57
|
5906.03
|
3755.79
|
2150.24
|
899240.33
|
|
59
|
2018-12-15
|
899240.33
|
5906.03
|
3746.83
|
2159.19
|
897081.14
|
|
60
|
2019-01-15
|
897081.14
|
900818.97
|
3737.84
|
897081.14
|
0.00
|