UNEQUALLOANPAYMENTS
Updated: 13 May 2012
Use the tablevalued 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 20140115 with monthly payments commencing on 20140215 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
'20140115', @LoanDate
12, @IntPmtPerYr
'20140215', @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

20140115

0

0

0

0

1000000

1

20140215

1000000

10000

10000

0

1000000

2

20140315

1000000

10000

10000

0

1000000

3

20140415

1000000

130690.3

10000

120690.3

879309.7

4

20140515

879309.7

8793.097

8793.097

0

879309.7

5

20140615

879309.7

8793.097

8793.097

0

879309.7

6

20140715

879309.7

130690.3

8793.097

121897.2

757412.5

7

20140815

757412.5

7574.125

7574.125

0

757412.5

8

20140915

757412.5

7574.125

7574.125

0

757412.5

9

20141015

757412.5

130690.3

7574.125

123116.2

634296.3

10

20141115

634296.3

6342.963

6342.963

0

634296.3

11

20141215

634296.3

6342.963

6342.963

0

634296.3

12

20150115

634296.3

130690.3

6342.963

124347.3

509949

13

20150215

509949

5099.49

5099.49

0

509949

14

20150315

509949

5099.49

5099.49

0

509949

15

20150415

509949

130690.3

5099.49

125590.8

384358.2

16

20150515

384358.2

3843.582

3843.582

0

384358.2

17

20150615

384358.2

3843.582

3843.582

0

384358.2

18

20150715

384358.2

130690.3

3843.582

126846.7

257511.5

19

20150815

257511.5

2575.115

2575.115

0

257511.5

20

20150915

257511.5

2575.115

2575.115

0

257511.5

21

20151015

257511.5

130690.3

2575.115

128115.2

129396.3

22

20151115

129396.3

1293.963

1293.963

0

129396.3

23

20151215

129396.3

1293.963

1293.963

0

129396.3

24

20160115

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 25year maturity but payoff is at the end of 5 years.
SELECT
*
FROM
wct.UnequalLoanPayments(
1000000, @PV
0.05, @Rate
'20140115', @LoanDate
12, @IntPmtPerYr
'20140215', @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

20140115

0.00

0.00

0.00

0.00

1000000.00

1

20140215

1000000.00

4166.67

4166.67

0.00

1000000.00

2

20140315

1000000.00

4166.67

4166.67

0.00

1000000.00

3

20140415

1000000.00

4166.67

4166.67

0.00

1000000.00

4

20140515

1000000.00

4166.67

4166.67

0.00

1000000.00

5

20140615

1000000.00

4166.67

4166.67

0.00

1000000.00

6

20140715

1000000.00

4166.67

4166.67

0.00

1000000.00

7

20140815

1000000.00

5906.03

4166.67

1739.36

998260.64

8

20140915

998260.64

5906.03

4159.42

1746.61

996514.03

9

20141015

996514.03

5906.03

4152.14

1753.89

994760.14

10

20141115

994760.14

5906.03

4144.83

1761.20

992998.94

11

20141215

992998.94

5906.03

4137.50

1768.53

991230.41

12

20150115

991230.41

5906.03

4130.13

1775.90

989454.51

13

20150215

989454.51

5906.03

4122.73

1783.30

987671.21

14

20150315

987671.21

5906.03

4115.30

1790.73

985880.47

15

20150415

985880.47

5906.03

4107.84

1798.19

984082.28

16

20150515

984082.28

5906.03

4100.34

1805.69

982276.59

17

20150615

982276.59

5906.03

4092.82

1813.21

980463.38

18

20150715

980463.38

5906.03

4085.26

1820.77

978642.62

19

20150815

978642.62

5906.03

4077.68

1828.35

976814.27

20

20150915

976814.27

5906.03

4070.06

1835.97

974978.30

21

20151015

974978.30

5906.03

4062.41

1843.62

973134.68

22

20151115

973134.68

5906.03

4054.73

1851.30

971283.37

23

20151215

971283.37

5906.03

4047.01

1859.02

969424.36

24

20160115

969424.36

5906.03

4039.27

1866.76

967557.60

25

20160215

967557.60

5906.03

4031.49

1874.54

965683.06

26

20160315

965683.06

5906.03

4023.68

1882.35

963800.71

27

20160415

963800.71

5906.03

4015.84

1890.19

961910.52

28

20160515

961910.52

5906.03

4007.96

1898.07

960012.45

29

20160615

960012.45

5906.03

4000.05

1905.98

958106.47

30

20160715

958106.47

5906.03

3992.11

1913.92

956192.55

31

20160815

956192.55

5906.03

3984.14

1921.89

954270.66

32

20160915

954270.66

5906.03

3976.13

1929.90

952340.75

33

20161015

952340.75

5906.03

3968.09

1937.94

950402.81

34

20161115

950402.81

5906.03

3960.01

1946.02

948456.79

35

20161215

948456.79

5906.03

3951.90

1954.13

946502.67

36

20170115

946502.67

5906.03

3943.76

1962.27

944540.40

37

20170215

944540.40

5906.03

3935.58

1970.44

942569.96

38

20170315

942569.96

5906.03

3927.37

1978.65

940591.30

39

20170415

940591.30

5906.03

3919.13

1986.90

938604.40

40

20170515

938604.40

5906.03

3910.85

1995.18

936609.22

41

20170615

936609.22

5906.03

3902.54

2003.49

934605.73

42

20170715

934605.73

5906.03

3894.19

2011.84

932593.89

43

20170815

932593.89

5906.03

3885.81

2020.22

930573.67

44

20170915

930573.67

5906.03

3877.39

2028.64

928545.03

45

20171015

928545.03

5906.03

3868.94

2037.09

926507.94

46

20171115

926507.94

5906.03

3860.45

2045.58

924462.36

47

20171215

924462.36

5906.03

3851.93

2054.10

922408.26

48

20180115

922408.26

5906.03

3843.37

2062.66

920345.60

49

20180215

920345.60

5906.03

3834.77

2071.26

918274.34

50

20180315

918274.34

5906.03

3826.14

2079.89

916194.46

51

20180415

916194.46

5906.03

3817.48

2088.55

914105.90

52

20180515

914105.90

5906.03

3808.77

2097.25

912008.65

53

20180615

912008.65

5906.03

3800.04

2105.99

909902.66

54

20180715

909902.66

5906.03

3791.26

2114.77

907787.89

55

20180815

907787.89

5906.03

3782.45

2123.58

905664.31

56

20180915

905664.31

5906.03

3773.60

2132.43

903531.88

57

20181015

903531.88

5906.03

3764.72

2141.31

901390.57

58

20181115

901390.57

5906.03

3755.79

2150.24

899240.33

59

20181215

899240.33

5906.03

3746.83

2159.19

897081.14

60

20190115

897081.14

900818.97

3737.84

897081.14

0.00
