Login     Register

        Contact Us     Search

Menu

XLeratorDB/financial Documentation

SQL Server amortization for balloon loans


Balloon

Updated: 18 September 2014

Use the table-valued function Balloon to return the cash flow schedule for a loan with periodic payments of interest (only) and with the principal paid at maturity.
The interest payment period is entered in Balloon as the number of months between interest payments. For example, a loan with monthly interest payments would have a frequency of 1. A loan with quarterly interest payments would have frequency of 3. A loan with annual principal payments would have a frequency of 12.
Balloon 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 a reference date supplied to the function (which should not be confused with the start date). If an initial grace period is entered in Balloon 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 not 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 Ballon loan schedule function for SQL Server
Where:

I
=
InterestPayment
P
=
@OutstandingAmount
R
=
@InterestRate
F
=
@Frequency
T
=
Time, in years, from PaymentDate(Period -1) to PaymentDate

If an interest payment occurs at the end of the initial grace period or at the end of the interim grace period and the length of the period (in months) if greater than the frequency, then Balloon calculates a 'grace' interest amount and a regular periodic interest amount.
Syntax
SELECT * FROM [wct].[Balloon](
  <@OutstandingAmount, float,>
 ,<@InterestBasis, nvarchar(4000),>
 ,<@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.
@InterestBasis
the day count convention used to calculate the interest amount. @InterestBasis can be 30/360, Actual/360, Actual/365, or Actual/Actual. @InterestBasis is an expression of the character string data type category.
@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 interest payment. @PaymentFrequency is an expression of type int or of a type that can be implicitly converted to int.
@MaturityDate
the maturity 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 interest 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. @GracePeriodStratDate 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.
PrinicpalPayment
The amount of the principal payment. The only principal payment is on the maturity date of the loan.
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 rate from the @ReferenceDate to the @MaturityDate. See formula above.

 
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.
·         The time value (see formula above) is calculated based on @InterestBasis:
o        For Actual/360 it is the number of days between the 2 PaymentDate values divided by 360 .
o        For Actual/365 it is the number of days between the 2 PaymentDate values divided by 365.
o        For Actual/Actual it is the number of days between the 2 PaymentDate values divided by the number of days in the year of the later PaymentDate.
o        For 30/360 is the number of months between the 2 PaymentDate values divided by 12.
·         If @InterestBasis is NULL then @InterestBasis = 30/360
·         If @InterestBasis is not 30/360, ACTUAL/360, ACTUAL/365, or ACTUAL/ACTUAL then an error message will be generated.
·         If @Frequency is NULL then @Frequency = 1
·         If @InterestRate is NULL then @InterestRate = 0
·         If @ReferenceDate is NULL then @ReferenceDate = GETDATE()
·         If @MaturityDate is NULL then @MaturityDate = GETDATE()
·         GraceInterest is only calculated on @FirstPayDate and @GracePeriodEndDate.
·         GraceInterest is only calculated if NumberOfMonth – NumberOfMonth(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
·         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 5-year loan with quarterly payments of interest.
SELECT
       *
FROM wct.Balloon(
  100000            --@OutstandingAmount
 ,'Actual/365'      --@InterestBasis
 ,.04               --@InterestRate
 ,3                 --@PaymentFrequency
 ,'2019-09-15'      --@MaturityDate
 ,'2014-09-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
100000.00
100000.00
100000.00
0
2014-09-30
0.00
0.000000
1
0.00
1008.26
1008.26
101008.26
100000.00
101008.26
3
2014-12-31
0.00
0.010083
2
0.00
986.23
986.23
101994.49
100000.00
100986.23
6
2015-03-31
0.00
0.009862
3
0.00
997.25
997.25
102991.74
100000.00
100997.25
9
2015-06-30
0.00
0.009972
4
0.00
1008.26
1008.26
104000.00
100000.00
101008.26
12
2015-09-30
0.00
0.010083
5
0.00
1008.26
1008.26
105008.26
100000.00
101008.26
15
2015-12-31
0.00
0.010083
6
0.00
997.25
997.25
106005.51
100000.00
100997.25
18
2016-03-31
0.00
0.009972
7
0.00
997.25
997.25
107002.76
100000.00
100997.25
21
2016-06-30
0.00
0.009972
8
0.00
1008.26
1008.26
108011.02
100000.00
101008.26
24
2016-09-30
0.00
0.010083
9
0.00
1008.26
1008.26
109019.28
100000.00
101008.26
27
2016-12-31
0.00
0.010083
10
0.00
986.23
986.23
110005.51
100000.00
100986.23
30
2017-03-31
0.00
0.009862
11
0.00
997.25
997.25
111002.76
100000.00
100997.25
33
2017-06-30
0.00
0.009972
12
0.00
1008.26
1008.26
112011.02
100000.00
101008.26
36
2017-09-30
0.00
0.010083
13
0.00
1008.26
1008.26
113019.28
100000.00
101008.26
39
2017-12-31
0.00
0.010083
14
0.00
986.23
986.23
114005.51
100000.00
100986.23
42
2018-03-31
0.00
0.009862
15
0.00
997.25
997.25
115002.76
100000.00
100997.25
45
2018-06-30
0.00
0.009972
16
0.00
1008.26
1008.26
116011.02
100000.00
101008.26
48
2018-09-30
0.00
0.010083
17
0.00
1008.26
1008.26
117019.28
100000.00
101008.26
51
2018-12-31
0.00
0.010083
18
0.00
986.23
986.23
118005.51
100000.00
100986.23
54
2019-03-31
0.00
0.009862
19
0.00
997.25
997.25
119002.76
100000.00
100997.25
57
2019-06-30
0.00
0.009972
20
100000.00
1008.26
101008.26
120011.02
0.00
101008.26
60
2019-09-30
0.00
0.010083

 
In this example, we modify the SQL by adding a first payment date so that the interest will not be paid until 31-Mar-2015.
SELECT
       *
FROM wct.Balloon(
  100000            --@OutstandingAmount
 ,'Actual/365'      --@InterestBasis
 ,.4                --@InterestRate
 ,3                 --@PaymentFrequency
 ,'2019-09-15'      --@MaturityDate
 ,'2014-09-15'      --@ReferenceDate
 ,NULL              --@PrevPayDate
 ,NULL              --@StartDate
 ,'2015-03-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
100000.00
100000.00
100000.00
0
2014-09-30
0.00
0.000000
1
0.00
986.23
2004.44
100986.23
100000.00
100986.23
6
2015-03-31
1018.20
0.020044
2
0.00
997.25
997.25
101983.48
100000.00
100997.25
9
2015-06-30
0.00
0.009972
3
0.00
1008.26
1008.26
102991.74
100000.00
101008.26
12
2015-09-30
0.00
0.010083
4
0.00
1008.26
1008.26
104000.00
100000.00
101008.26
15
2015-12-31
0.00
0.010083
5
0.00
997.25
997.25
104997.25
100000.00
100997.25
18
2016-03-31
0.00
0.009972
6
0.00
997.25
997.25
105994.49
100000.00
100997.25
21
2016-06-30
0.00
0.009972
7
0.00
1008.26
1008.26
107002.76
100000.00
101008.26
24
2016-09-30
0.00
0.010083
8
0.00
1008.26
1008.26
108011.02
100000.00
101008.26
27
2016-12-31
0.00
0.010083
9
0.00
986.23
986.23
108997.25
100000.00
100986.23
30
2017-03-31
0.00
0.009862
10
0.00
997.25
997.25
109994.50
100000.00
100997.25
33
2017-06-30
0.00
0.009972
11
0.00
1008.26
1008.26
111002.76
100000.00
101008.26
36
2017-09-30
0.00
0.010083
12
0.00
1008.26
1008.26
112011.02
100000.00
101008.26
39
2017-12-31
0.00
0.010083
13
0.00
986.23
986.23
112997.25
100000.00
100986.23
42
2018-03-31
0.00
0.009862
14
0.00
997.25
997.25
113994.50
100000.00
100997.25
45
2018-06-30
0.00
0.009972
15
0.00
1008.26
1008.26
115002.76
100000.00
101008.26
48
2018-09-30
0.00
0.010083
16
0.00
1008.26
1008.26
116011.02
100000.00
101008.26
51
2018-12-31
0.00
0.010083
17
0.00
986.23
986.23
116997.25
100000.00
100986.23
54
2019-03-31
0.00
0.009862
18
0.00
997.25
997.25
117994.50
100000.00
100997.25
57
2019-06-30
0.00
0.009972
19
100000.00
1008.26
101008.26
119002.76
0.00
101008.26
60
2019-09-30
0.00
0.010083

 
We modify the SQL so that there are no interest payments in 2018.
SELECT
       *
FROM wct.Balloon(
  100000            --@OutstandingAmount
 ,'Actual/365'      --@InterestBasis
 ,.04               --@InterestRate
 ,3                 --@PaymentFrequency
 ,'2019-09-15'      --@MaturityDate
 ,'2014-09-15'      --@ReferenceDate
 ,NULL              --@PrevPayDate
 ,NULL              --@StartDate
 ,'2015-03-15'      --@FirstPayDate
 ,'2018-01-01'      --@GracePeriodStartDate
 ,'2019-01-01'      --@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
100000.00
100000.00
100000.00
0
2014-09-30
0.00
0.000000
1
0.00
986.23
2004.44
100986.23
100000.00
100986.23
6
2015-03-31
1018.20
0.020044
2
0.00
997.25
997.25
101983.48
100000.00
100997.25
9
2015-06-30
0.00
0.009972
3
0.00
1008.26
1008.26
102991.74
100000.00
101008.26
12
2015-09-30
0.00
0.010083
4
0.00
1008.26
1008.26
104000.00
100000.00
101008.26
15
2015-12-31
0.00
0.010083
5
0.00
997.25
997.25
104997.25
100000.00
100997.25
18
2016-03-31
0.00
0.009972
6
0.00
997.25
997.25
105994.49
100000.00
100997.25
21
2016-06-30
0.00
0.009972
7
0.00
1008.26
1008.26
107002.76
100000.00
101008.26
24
2016-09-30
0.00
0.010083
8
0.00
1008.26
1008.26
108011.02
100000.00
101008.26
27
2016-12-31
0.00
0.010083
9
0.00
986.23
986.23
108997.25
100000.00
100986.23
30
2017-03-31
0.00
0.009862
10
0.00
997.25
997.25
109994.50
100000.00
100997.25
33
2017-06-30
0.00
0.009972
11
0.00
1008.26
1008.26
111002.76
100000.00
101008.26
36
2017-09-30
0.00
0.010083
12
0.00
1008.26
1008.26
112011.02
100000.00
101008.26
39
2017-12-31
0.00
0.010083
13
0.00
1008.26
4412.76
113019.28
100000.00
101008.26
52
2019-01-31
3404.50
0.044128
14
0.00
975.22
975.22
113994.50
100000.00
100975.22
55
2019-04-30
0.00
0.009752
15
0.00
1008.26
1008.26
115002.76
100000.00
101008.26
58
2019-07-31
0.00
0.010083
16
100000.00
667.39
100667.39
115670.15
0.00
100667.39
60
2019-09-30
0.00
0.006674

 
In this example we modify the SQL to get rid of the first payment date and calculate the first interest payment using the previous payment date.
SELECT
       *
FROM wct.Balloon(
  100000            --@OutstandingAmount
 ,'Actual/365'      --@InterestBasis
 ,.04               --@InterestRate
 ,3                 --@PaymentFrequency
 ,'2019-09-15'      --@MaturityDate
 ,'2014-09-15'      --@ReferenceDate
 ,'2014-08-15'      --@PrevPayDate
 ,NULL              --@StartDate
 ,NULL              --@FirstPayDate
 ,'2018-01-01'      --@GracePeriodStartDate
 ,'2019-01-01'      --@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
100000.00
100000.00
100000.00
0
2014-09-30
0.00
0.000000
1
0.00
667.39
667.39
100667.39
100000.00
100667.39
2
2014-11-30
0.00
0.006674
2
0.00
986.23
986.23
101653.62
100000.00
100986.23
5
2015-02-28
0.00
0.009862
3
0.00
1008.26
1008.26
102661.88
100000.00
101008.26
8
2015-05-31
0.00
0.010083
4
0.00
1008.26
1008.26
103670.14
100000.00
101008.26
11
2015-08-31
0.00
0.010083
5
0.00
997.25
997.25
104667.39
100000.00
100997.25
14
2015-11-30
0.00
0.009972
6
0.00
997.25
997.25
105664.64
100000.00
100997.25
17
2016-02-29
0.00
0.009972
7
0.00
1008.26
1008.26
106672.90
100000.00
101008.26
20
2016-05-31
0.00
0.010083
8
0.00
1008.26
1008.26
107681.16
100000.00
101008.26
23
2016-08-31
0.00
0.010083
9
0.00
997.25
997.25
108678.41
100000.00
100997.25
26
2016-11-30
0.00
0.009972
10
0.00
986.23
986.23
109664.64
100000.00
100986.23
29
2017-02-28
0.00
0.009862
11
0.00
1008.26
1008.26
110672.90
100000.00
101008.26
32
2017-05-31
0.00
0.010083
12
0.00
1008.26
1008.26
111681.16
100000.00
101008.26
35
2017-08-31
0.00
0.010083
13
0.00
997.25
997.25
112678.41
100000.00
100997.25
38
2017-11-30
0.00
0.009972
14
0.00
1008.26
4766.31
113686.67
100000.00
101008.26
52
2019-01-31
3758.05
0.047663
15
0.00
975.22
975.22
114661.89
100000.00
100975.22
55
2019-04-30
0.00
0.009752
16
0.00
1008.26
1008.26
115670.15
100000.00
101008.26
58
2019-07-31
0.00
0.010083
17
100000.00
667.39
100667.39
116337.54
0.00
100667.39
60
2019-09-30
0.00
0.006674

 
In this example we modify the SQL to eliminate the grace period and the previous payment date and add a start date.
SELECT
       *
FROM wct.Balloon(
  100000            --@OutstandingAmount
 ,'Actual/365'      --@InterestBasis
 ,.04               --@InterestRate
 ,3                 --@PaymentFrequency
 ,'2019-09-15'      --@MaturityDate
 ,'2014-09-15'      --@ReferenceDate
 ,NULL              --@PrevPayDate
 ,'2011-09-15'      --@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
100000.00
100000.00
100000.00
0
2014-09-30
0.00
0.000000
1
0.00
1008.26
1008.26
101008.26
100000.00
101008.26
3
2014-12-31
0.00
0.010083
2
0.00
986.23
986.23
101994.49
100000.00
100986.23
6
2015-03-31
0.00
0.009862
3
0.00
997.25
997.25
102991.74
100000.00
100997.25
9
2015-06-30
0.00
0.009972
4
0.00
1008.26
1008.26
104000.00
100000.00
101008.26
12
2015-09-30
0.00
0.010083
5
0.00
1008.26
1008.26
105008.26
100000.00
101008.26
15
2015-12-31
0.00
0.010083
6
0.00
997.25
997.25
106005.51
100000.00
100997.25
18
2016-03-31
0.00
0.009972
7
0.00
997.25
997.25
107002.76
100000.00
100997.25
21
2016-06-30
0.00
0.009972
8
0.00
1008.26
1008.26
108011.02
100000.00
101008.26
24
2016-09-30
0.00
0.010083
9
0.00
1008.26
1008.26
109019.28
100000.00
101008.26
27
2016-12-31
0.00
0.010083
10
0.00
986.23
986.23
110005.51
100000.00
100986.23
30
2017-03-31
0.00
0.009862
11
0.00
997.25
997.25
111002.76
100000.00
100997.25
33
2017-06-30
0.00
0.009972
12
0.00
1008.26
1008.26
112011.02
100000.00
101008.26
36
2017-09-30
0.00
0.010083
13
0.00
1008.26
1008.26
113019.28
100000.00
101008.26
39
2017-12-31
0.00
0.010083
14
0.00
986.23
986.23
114005.51
100000.00
100986.23
42
2018-03-31
0.00
0.009862
15
0.00
997.25
997.25
115002.76
100000.00
100997.25
45
2018-06-30
0.00
0.009972
16
0.00
1008.26
1008.26
116011.02
100000.00
101008.26
48
2018-09-30
0.00
0.010083
17
0.00
1008.26
1008.26
117019.28
100000.00
101008.26
51
2018-12-31
0.00
0.010083
18
0.00
986.23
986.23
118005.51
100000.00
100986.23
54
2019-03-31
0.00
0.009862
19
0.00
997.25
997.25
119002.76
100000.00
100997.25
57
2019-06-30
0.00
0.009972
20
100000.00
1008.26
101008.26
120011.02
0.00
101008.26
60
2019-09-30
0.00
0.010083

 

See Also

 



View Topic History
Copyright 2008-2024 Westclintech LLC         Privacy Policy        Terms of Service