Login     Register

        Contact Us     Search

XLeratorDB/financial Documentation

SQL Server amortization where interest and principal schedules differ


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

 


Copyright 2008-2017 Westclintech LLC         Privacy Policy        Terms of Service