Bullet

Updated 2024-02-14 16:59:12.867000

Syntax

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

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:

{"columns":[{"field":"column 1"},{"field":"column 2"},{"field":"column 3"}],"rows":[{"column 1":"I","column 2":"=","column 3":"InterestPayment"},{"column 1":"P","column 2":"=","column 3":"@OutstandingAmount"},{"column 1":"R","column 2":"=","column 3":"@InterestRate"},{"column 1":"F","column 2":"=","column 3":"@Frequency"},{"column 1":"T","column 2":"=","column 3":"Time in years"}]}

Arguments

@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.

@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.

@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.

@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.

Return Type

table

{"columns": [{"field": "colName", "headerName": "Name", "header": "name"}, {"field": "colDatatype", "headerName": "Type", "header": "type"}, {"field": "colDesc", "headerName": "Description", "header": "description", "minWidth": 1000}], "rows": [{"id": "cca9d396-bba3-4d50-9aae-1199718f9118", "colName": "Period", "colDatatype": "int", "colDesc": "A reference number uniquely identifying a row in the resultant table."}, {"id": "cef8c015-f1d6-4f7e-913d-8e5cb6c93281", "colName": "PrincipalPayment", "colDatatype": "float", "colDesc": "The amount of the principal payment."}, {"id": "df6d29ef-ba58-40d0-a784-039c3eff6bf7", "colName": "InterestPayment", "colDatatype": "float", "colDesc": "The amount of the interest payment."}, {"id": "f60adb6e-ec65-45a7-809e-364571715ba5", "colName": "CashFlow", "colDatatype": "float", "colDesc": "The amount of the cash flow."}, {"id": "a3db3058-ba74-4843-8449-a3cb752e94e9", "colName": "OutstandingExposure", "colDatatype": "float", "colDesc": "When Period = 0, @OutstandingAmount. When Period = 1, @OutstandingAmount + InterestPayment."}, {"id": "0428e157-dac9-4af1-b7c2-0311e8e1aebb", "colName": "CapitalAmountInDebt", "colDatatype": "float", "colDesc": "When Period = 0, @OutstandingAmount. When Period = 1, 0"}, {"id": "5333f14c-06e4-490e-b936-25c61d064712", "colName": "TotalExposure", "colDatatype": "float", "colDesc": "See below."}, {"id": "962c40f3-bbdb-403f-9531-ce81c0d2f3dc", "colName": "NumberOfMonth", "colDatatype": "int", "colDesc": "The number of months between the @ReferenceDate and the PaymentDate."}, {"id": "bc1836c6-4ec7-45cd-9399-95b5a05b0823", "colName": "PaymentDate", "colDatatype": "datetime", "colDesc": "The end-of-month date when the payment occurs."}, {"id": "65de0c25-8a4f-4c9e-b7aa-cd7ae97dbf2d", "colName": "GraceInterest", "colDatatype": "float", "colDesc": "0"}, {"id": "7862e557-06e5-4806-9461-ef8b7e2840c5", "colName": "InterestRate", "colDatatype": "float", "colDesc": "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.

{"columns":[{"field":"Period","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"PrincipalPayment","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"InterestPayment","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"CashFlow","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"OutstandingExposure","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"CapitalAmountInDebt","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"TotalExposure","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"NumberOfMonth","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"PaymentDate","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"GraceInterest","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"InterestRate","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"Period":"0","PrincipalPayment":"0","InterestPayment":"0","CashFlow":"0","OutstandingExposure":"6000000","CapitalAmountInDebt":"6000000","TotalExposure":"6000000","NumberOfMonth":"0","PaymentDate":"2014-06-30 00:00:00.000","GraceInterest":"0","InterestRate":"0"},{"Period":"1","PrincipalPayment":"6000000","InterestPayment":"475938.103238028","CashFlow":"6475938.10323803","OutstandingExposure":"6475938.10323803","CapitalAmountInDebt":"0","TotalExposure":"6471270.63796738","NumberOfMonth":"13","PaymentDate":"2015-07-31 00:00:00.000","GraceInterest":"0","InterestRate":"0.0793230172063379"}]}