Bullet
Updated: 18 September 2014
Use the tablevalued 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:
Where:
I

=

InterestPayment

P

=

@OutstandingAmount

R

=

@InterestRate

F

=

@Frequency

T

=

Time in years

Syntax
SELECT * FROM [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
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.

InterestPayment

The amount of the interest payment.

CashFlow

The amount of the cash flow.

OutstandingExposure

When Period = 0, @OutstandingAmount. When Period = 1, @OutstandingAmount + InterestPayment.

CapitalAmountInDebt

When Period = 0, @OutstandingAmount. When Period = 1, 0

TotalExposure

See below.

NumberOfMonth

The number of months between the @ReferenceDate and the PaymentDate.

PaymentDate

The endofmonth date when the payment occurs.

GraceInterest

0

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 using the daycount convention specified by @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()
Â· TotalExposure is calculated as:
Examples
SELECT
*
FROM wct.Bullet(
6000000 @OutstandingAmount
,'Actual/360' @InterestBasis
,.07 @InterestRate
,3 @Frequency
,'20150705' @MaturityDate
,'20140630' @ReferenceDate
)
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

6000000.00

6000000.00

6000000.00

0

20140630

0.00

0.000000

1

6000000.00

475938.10

6475938.10

6475938.10

0.00

6471270.64

13

20150731

0.00

0.079323

See Also