Login     Register

        Contact Us     Search

XLeratorDB/financial Documentation

SQL Server amortization for constant cash flow loans


ConstantCashFlow

Updated: 18 September 2014

Use the table-valued function ConstantCashFlow to return the cash flow schedule for a loan with with a fixed maturity date and annuity-style payments. ConstantCashFlow computes the periodic interest and principal amounts through to the maturity date.
The payment period is entered in ConstantCashFlows as the number of months between payments. For example, a loan with monthly payments would have a frequency of 1. A loan with quarterly payments would have frequency of 3. A loan with annual payments would have a frequency of 12.
ConstantCashFlow supports both an initial grace period and an additional grace period during the life of the loan. All payments and their associated dates are calculated with respect to the reference date supplied to the function (which should not be confused with the start date). If an initial grace period is entered in ConstantCashFlow and it is greater than the reference date, then it becomes the first interest payment date and subsequent interest payments are calculated from that date forward.
If any payments would otherwise occur in the specified grace period, then that payment is moved to the end of the grace period and all remaining payments are calculated from the end of the grace period.
If no initial grace period is specified then the first payment date is calculated using the interest payment frequency. If the start date has been entered and the number of months between the start date and the reference date is less than the frequency, then the first payment date is calculated by adding the frequency (as a number of months) to the start date.
If no start date has been entered but a previous payment date has been entered and the number of months between the previous payment date and the reference date is less than the frequency, then the first payment date is calculated by adding the frequency (as a number of months) to the previous payment date.
If there is no start date and previous payment dates or the number of months between those dates and the reference date is greater than the frequency, then the first payment date is calculated by adding the frequency (as a number of months) to the reference date.
All payments in the resultant table are moved to the end of the month and interest is calculated using these end-of-month dates.
The interest payment is calculated as:

Formula for XLeratorDB Constant Cashflow loan schedule function for SQL Server
Where:

I
=
InterestPayment
P
=
@OutstandingAmount
R
=
@InterestRate
F
=
@Frequency
T
=
(NumberOfMonthNumberOfMonth(Period-1)) / 12

If the irregular period is longer than the regular period then the interest amount is broken out into the regular interest amount and a 'grace' interest amount.
Syntax
SELECT * FROM [wct].[ConstantCashFlow](
  <@OutstandingAmount, float,>
 ,<@LastPrinPayAmount, float,>
 ,<@InterestRate, float,>
 ,<@PaymentFrequency, int,>
 ,<@MaturityDate, datetime,>
 ,<@ReferenceDate, datetime,>
 ,<@PrevPayDate, datetime,>
 ,<@StartDate, datetime,>
 ,<@FirstPayDate, datetime,>
 ,<@GracePeriodStartDate, datetime,>
 ,<@GracePeriodEndDate, datetime,>)
Arguments
@OutstandingAmount
the principal amount of the loan. @OutstandingAmount is an expression of type float or of a type that can be implicitly converted to float.
@LastPrinPayAmount
the amount of principal to be paid off on the the maturity date. @OutstandingAmount is an expression of type float or of a type that can be implicitly converted to float.
@InterestRate
the annual rate of interest for the loan. @InterestRate is an expression of type float or of a type that can be implicitly converted to float
@PaymentFrequency
the number of months in a regular payment. @PaymentFrequency is an expression of type int or of a type that can be implicitly converted to int.
@MaturityDate
the last payment date of the loan. @MaturityDate is an expression that returns a datetime or smalldatetime value, or a character string in date format.
@ReferenceDate
the starting date for the number of months with respect to all other dates. @ReferenceDate is an expression that returns a datetime or smalldatetime value, or a character string in date format. 
@PrevPayDate
the last payment date prior to the reference date. @PrevPayDate is an expression that returns a datetime or smalldatetime value, or a character string in date format. 
@StartDate
the start date of the loan. @StartDate is an expression that returns a datetime or smalldatetime value, or a character string in date format. 
@FirstPayDate
the first payment date of the loan if other than a regular periodic payment. @FirstPayDate is an expression that returns a datetime or smalldatetime value, or a character string in date format. 
@GracePeriodStartDate
the date on which the (interim) grace period commences. @GracePeriodStartDate is an expression that returns a datetime or smalldatetime value, or a character string in date format. 
@GracePeriodEndDate
the date on which the (interim) grace period concludes. @GracePeriodEndDate is an expression that returns a datetime or smalldatetime value, or a character string in date format. 
Return Type
RETURNS TABLE (
       [Period] [int] NULL,
       [PrincipalPayment] [float] NULL,
       [InterestPayment] [float] NULL,
       [CashFlow] [float] NULL,
       [OutstandingExposure] [float] NULL,
       [CapitalAmountInDebt] [float] NULL,
       [TotalExposure] [float] NULL,
       [NumberOfMonth] [int] NULL,
       [PaymentDate] [datetime] NULL,
       [GraceInterest] [float] NULL,
       [InterestRate] [float] NULL
)

Column
Description
Period
A reference number uniquely identifying a row in the resultant table.
PrincipalPayment
The amount of the principal payment.
InterestPayment
The amount of the regular interest payment.
CashFlow
PrincipalPayment + InterestPayment + GraceInterest.
OutstandingExposure
When Period = 0 then @OutstandingAmount. For Period > 0 then OutstandingExposure(Period-1) + InterestPayment.
CapitalAmountInDebt
When Period = 0, @OutstandingAmount. For Period > 0 then CapitalAmountInDebt(Period-1) – PrincipalPayment
TotalExposure
When Period = 0, @OutstandingAmount. For Period > 0 then CapitalAmountInDebt(Period-1) + InterestPayment
NumberOfMonth
The number of months between the @ReferenceDate and the PaymentDate.
PaymentDate
The end-of-month date of the payment.
GraceInterest
The amount of the grace interest
InterestRate
The interest amount from PaymentDate(Period-1) to PaymentDate assuming a principal amount of 1

 
Remarks
·         The PaymentDate for all rows is generated as the last day of the month.
·         For Period = 0, PrincipalPayment, InterestPayment, CashFlow, NumberOfMonth, GraceInterest, and InterestRate are set to 0.
·         If @Frequency is NULL then @Frequency = 1
·         If @InterestRate is NULL then @InterestRate = 0
·         If @ReferenceDate is NULL then @ReferenceDate = GETDATE()
·         GraceInterest is only calculated on @FirstPayDate and @GracePeriodEndDate.
·         GraceInterest is only calculated if NumberOfMonthNumberOfMonth(Period-1) > @PaymentFrequency
·         GraceInterest is the difference between the interest for the period from the previous row to the current row minus interest that would have been calculated for a period with length equal to @PaymentFrequency
·         CashFlow may change on @FirstPayDate or @GracePeriodEndDate due to GraceInterest.
·         The final payment is adjusted for CapitalAmountInDebt(Period-1) and the length of the period if it is less than @PaymentFrequency.
·         The last row returned will always be for the maturity date and may be shorter than a regular period depending on the combination of dates and @PaymentFrequency
Examples
This is a simple a 300,000 loan with quarterly payments and an interest rate of 6.0%.
SELECT
       *
FROM wct.ConstantCashFlow(
  300000            --@OutstandingAmount
 ,0                 --@LastPrinPayAmount
 ,.06               --@InterestRate
 ,3                 --@PaymentFrequency
 ,'2019-12-15'      --@MaturityDate
 ,'2014-12-15'      --@ReferenceDate
 ,NULL              --@PrevPayDate
 ,NULL              --@StartDate
 ,NULL              --@FirstPayDate
 ,NULL              --@GracePeriodStartDate
 ,NULL              --@GracePeriodEndDate
)
This produces the following result (which has been reformatted for ease of viewing).

 Period
Principal Payment
Interest Payment
Cash Flow
Outstanding Exposure
Capital Amount In Debt
Total Exposure
Number Of Month
Payment Date
Grace Interest
Interest Rate
0
0.00
0.00
0.00
300000.00
300000.00
300000.00
0
2014-12-31
0.00
0.000000
1
12973.72
4500.00
17473.72
304500.00
287026.28
304500.00
3
2015-03-31
0.00
0.015000
2
13168.33
4305.39
17473.72
308805.39
273857.95
291331.67
6
2015-06-30
0.00
0.015000
3
13365.85
4107.87
17473.72
312913.26
260492.10
277965.82
9
2015-09-30
0.00
0.015000
4
13566.34
3907.38
17473.72
316820.64
246925.76
264399.48
12
2015-12-31
0.00
0.015000
5
13769.83
3703.89
17473.72
320524.53
233155.93
250629.65
15
2016-03-31
0.00
0.015000
6
13976.38
3497.34
17473.72
324021.87
219179.55
236653.27
18
2016-06-30
0.00
0.015000
7
14186.03
3287.69
17473.72
327309.56
204993.52
222467.24
21
2016-09-30
0.00
0.015000
8
14398.82
3074.90
17473.72
330384.47
190594.70
208068.42
24
2016-12-31
0.00
0.015000
9
14614.80
2858.92
17473.72
333243.39
175979.90
193453.62
27
2017-03-31
0.00
0.015000
10
14834.02
2639.70
17473.72
335883.09
161145.88
178619.60
30
2017-06-30
0.00
0.015000
11
15056.53
2417.19
17473.72
338300.27
146089.35
163563.07
33
2017-09-30
0.00
0.015000
12
15282.38
2191.34
17473.72
340491.61
130806.96
148280.69
36
2017-12-31
0.00
0.015000
13
15511.62
1962.10
17473.72
342453.72
115295.35
132769.07
39
2018-03-31
0.00
0.015000
14
15744.29
1729.43
17473.72
344183.15
99551.06
117024.78
42
2018-06-30
0.00
0.015000
15
15980.45
1493.27
17473.72
345676.41
83570.60
101044.32
45
2018-09-30
0.00
0.015000
16
16220.16
1253.56
17473.72
346929.97
67350.44
84824.16
48
2018-12-31
0.00
0.015000
17
16463.46
1010.26
17473.72
347940.23
50886.98
68360.70
51
2019-03-31
0.00
0.015000
18
16710.42
763.30
17473.72
348703.53
34176.56
51650.28
54
2019-06-30
0.00
0.015000
19
16961.07
512.65
17473.72
349216.18
17215.49
34689.21
57
2019-09-30
0.00
0.015000
20
17215.49
258.23
17473.72
349474.42
0.00
17473.72
60
2019-12-31
0.00
0.015000



In this example, we modify the SQL to reflect a final principal payment of 172,000.
SELECT
       *
FROM wct.ConstantCashFlow(
  300000            --@OutstandingAmount
 ,172000            --@LastPrinPayAmount
 ,.06               --@InterestRate
 ,3                 --@PaymentFrequency
 ,'2019-12-15'      --@MaturityDate
 ,'2014-12-15'      --@ReferenceDate
 ,NULL              --@PrevPayDate
 ,NULL              --@StartDate
 ,NULL              --@FirstPayDate
 ,NULL              --@GracePeriodStartDate
 ,NULL              --@GracePeriodEndDate
)
This produces the following result.

 Period
Principal Payment
Interest Payment
Cash Flow
Outstanding Exposure
Capital Amount In Debt
Total Exposure
Number Of Month
Payment Date
Grace Interest
Interest Rate
0
0.00
0.00
0.00
300000.00
300000.00
300000.00
0
2014-12-31
0.00
0.000000
1
5872.44
4500.00
10372.44
304500.00
294127.56
304500.00
3
2015-03-31
0.00
0.015000
2
5960.53
4411.91
10372.44
308911.91
288167.02
298539.47
6
2015-06-30
0.00
0.015000
3
6049.94
4322.51
10372.44
313234.42
282117.09
292489.53
9
2015-09-30
0.00
0.015000
4
6140.69
4231.76
10372.44
317466.18
275976.40
286348.84
12
2015-12-31
0.00
0.015000
5
6232.80
4139.65
10372.44
321605.82
269743.60
280116.04
15
2016-03-31
0.00
0.015000
6
6326.29
4046.15
10372.44
325651.97
263417.31
273789.75
18
2016-06-30
0.00
0.015000
7
6421.18
3951.26
10372.44
329603.23
256996.13
267368.57
21
2016-09-30
0.00
0.015000
8
6517.50
3854.94
10372.44
333458.18
250478.62
260851.07
24
2016-12-31
0.00
0.015000
9
6615.26
3757.18
10372.44
337215.36
243863.36
254235.80
27
2017-03-31
0.00
0.015000
10
6714.49
3657.95
10372.44
340873.31
237148.86
247521.31
30
2017-06-30
0.00
0.015000
11
6815.21
3557.23
10372.44
344430.54
230333.65
240706.10
33
2017-09-30
0.00
0.015000
12
6917.44
3455.00
10372.44
347885.54
223416.21
233788.66
36
2017-12-31
0.00
0.015000
13
7021.20
3351.24
10372.44
351236.79
216395.01
226767.46
39
2018-03-31
0.00
0.015000
14
7126.52
3245.93
10372.44
354482.71
209268.49
219640.94
42
2018-06-30
0.00
0.015000
15
7233.42
3139.03
10372.44
357621.74
202035.08
212407.52
45
2018-09-30
0.00
0.015000
16
7341.92
3030.53
10372.44
360652.27
194693.16
205065.60
48
2018-12-31
0.00
0.015000
17
7452.05
2920.40
10372.44
363572.66
187241.11
197613.56
51
2019-03-31
0.00
0.015000
18
7563.83
2808.62
10372.44
366381.28
179677.28
190049.73
54
2019-06-30
0.00
0.015000
19
7677.28
2695.16
10372.44
369076.44
172000.00
182372.44
57
2019-09-30
0.00
0.015000
20
172000.00
2580.00
174580.00
371656.44
0.00
174580.00
60
2019-12-31
0.00
0.015000



In this example we modify the SQL by adding a first payment date of 15-June-2015.
SELECT
       *
FROM wct.ConstantCashFlow(
  300000            --@OutstandingAmount
 ,172000            --@LastPrinPayAmount
 ,.06               --@InterestRate
 ,3                 --@PaymentFrequency
 ,'2019-12-15'      --@MaturityDate
 ,'2014-12-15'      --@ReferenceDate
 ,NULL              --@PrevPayDate
 ,NULL              --@StartDate
 ,'2015-06-15'      --@FirstPayDate
 ,NULL              --@GracePeriodStartDate
 ,NULL              --@GracePeriodEndDate
)
This produces the following result.

Period
Principal Payment
Interest Payment
Cash Flow
Outstanding Exposure
Capital Amount In Debt
Total Exposure
Number Of Month
Payment Date
Grace Interest
Interest Rate
0
0.00
0.00
0.00
300000.00
300000.00
300000.00
0
2014-12-31
0.00
0.000000
1
6247.14
4500.00
15314.64
304500.00
293752.86
304500.00
6
2015-06-30
4567.50
0.030225
2
6340.85
4406.29
10747.14
308906.29
287412.01
298159.15
9
2015-09-30
0.00
0.015000
3
6435.96
4311.18
10747.14
313217.47
280976.05
291723.19
12
2015-12-31
0.00
0.015000
4
6532.50
4214.64
10747.14
317432.11
274443.55
285190.69
15
2016-03-31
0.00
0.015000
5
6630.49
4116.65
10747.14
321548.77
267813.07
278560.21
18
2016-06-30
0.00
0.015000
6
6729.94
4017.20
10747.14
325565.96
261083.12
271830.26
21
2016-09-30
0.00
0.015000
7
6830.89
3916.25
10747.14
329482.21
254252.23
264999.37
24
2016-12-31
0.00
0.015000
8
6933.36
3813.78
10747.14
333295.99
247318.87
258066.01
27
2017-03-31
0.00
0.015000
9
7037.36
3709.78
10747.14
337005.78
240281.52
251028.66
30
2017-06-30
0.00
0.015000
10
7142.92
3604.22
10747.14
340610.00
233138.60
243885.74
33
2017-09-30
0.00
0.015000
11
7250.06
3497.08
10747.14
344107.08
225888.54
236635.68
36
2017-12-31
0.00
0.015000
12
7358.81
3388.33
10747.14
347495.41
218529.73
229276.87
39
2018-03-31
0.00
0.015000
13
7469.19
3277.95
10747.14
350773.35
211060.53
221807.67
42
2018-06-30
0.00
0.015000
14
7581.23
3165.91
10747.14
353939.26
203479.30
214226.44
45
2018-09-30
0.00
0.015000
15
7694.95
3052.19
10747.14
356991.45
195784.35
206531.49
48
2018-12-31
0.00
0.015000
16
7810.37
2936.77
10747.14
359928.21
187973.97
198721.11
51
2019-03-31
0.00
0.015000
17
7927.53
2819.61
10747.14
362747.82
180046.44
190793.58