SQL Server Bullet Function

Updated 2024-02-14 16:59:12.867000

Description

Use the table-valued function Bullet to return the cash flow schedule for a loan with a single payment of principal and interest at maturity. Only 2 rows are returned: one for the commencement of the loan and one for the maturity date of the loan.

The interest payment is calculated as:

I=P\times\left[\left(\left(1+\frac{R\times{F}}{12}\right)^{12\slash{F}}\right)^T\right]-1

Where:

column 1	column 2	column 3
I	=	InterestPayment
P	=	@OutstandingAmount
R	=	@InterestRate
F	=	@Frequency
T	=	Time in years

Syntax

SELECT * FROM [westclintech].[wct].[Bullet](
  <@OutstandingAmount, float,>
 ,<@InterestBasis, nvarchar(4000),>
 ,<@InterestRate, float,>
 ,<@Frequency, int,>
 ,<@MaturityDate, datetime,>
 ,<@ReferenceDate, 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.

@Frequency

the number of months in a regular interest payment. @Frequency 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 start date of the loan. @ReferenceDate is an expression that returns a datetime or smalldatetime value, or a character string in date format.

Return Type

table

colName	colDatatype	colDesc
Period	int	A reference number uniquely identifying a row in the resultant table.
PrincipalPayment	float	The amount of the principal payment.
InterestPayment	float	The amount of the interest payment.
CashFlow	float	The amount of the cash flow.
OutstandingExposure	float	When Period = 0, @OutstandingAmount. When Period = 1, @OutstandingAmount + InterestPayment.
CapitalAmountInDebt	float	When Period = 0, @OutstandingAmount. When Period = 1, 0
TotalExposure	float	See below.
NumberOfMonth	int	The number of months between the @ReferenceDate and the PaymentDate.
PaymentDate	datetime	The end-of-month date when the payment occurs.
GraceInterest	float	0
InterestRate	float	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 using the day-count convention specified by @InterestBasis:

For Actual/360 it is the number of days between the 2 PaymentDate values divided by 360.

For Actual/365 it is the number of days between the 2 PaymentDate values divided by 365.

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.

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().

TotalExposure is calculated as:

@OutstandingAmount*(1+\frac{@InterestRate}{@Frequency})^{NumberOfMonth}

Examples

SELECT *
FROM wct.Bullet(   6000000,      --@OutstandingAmount
                   'Actual/360', --@InterestBasis
                   .07,          --@InterestRate
                   3,            --@Frequency
                   '2015-07-05', --@MaturityDate
                   '2014-06-30'  --@ReferenceDate
               );

This produces the following result.

Period	PrincipalPayment	InterestPayment	CashFlow	OutstandingExposure	CapitalAmountInDebt	TotalExposure	NumberOfMonth	PaymentDate	GraceInterest	InterestRate
0	0	0	0	6000000	6000000	6000000	0	2014-06-30 00:00:00.000	0	0
1	6000000	475938.103238028	6475938.10323803	6475938.10323803	0	6471270.63796738	13	2015-07-31 00:00:00.000	0	0.0793230172063379