SQL Server BONDCF Function

Updated 2023-10-06 13:39:08.400000

Description

Use the table-valued function BONDCF to return the cash flows of a bond with regular periodic coupon payments. BONDCF also supports odd first and odd last coupon bonds with up to 2 quasi-coupon periods each.

The first row in the resultant table is dated with settlement date passed into the function and is for the amount of the accrued interest. All the remaining rows are dated for the subsequent coupon dates and the amounts are the coupon amount. The row for the maturity date includes the coupon amount and the redemption amount.

The resultant table also includes the discount factor for each period as will as the discounted cash flow value for each period. The sum of the discounted cash flow values across all the periods is equal to the clean price of the bond.

Syntax

SELECT * FROM [[westclintech].wct].[BONDCF](
  <@Settlement, datetime,>
 ,<@Maturity, datetime,>
 ,<@Rate, float,>
 ,<@Yield, float,>
 ,<@Redemption, float,>
 ,<@Frequency, float,>
 ,<@Basis, nvarchar(4000),>
 ,<@Issue, datetime,>
 ,<@FirstCoupon, datetime,>
 ,<@LastCoupon, datetime,>)

Arguments

@Settlement

the settlement date of the bond. @Settlement is an expression that returns a datetime or smalldatetime value, or a character string in date format.

@Maturity

the maturity date of the bond. @Maturity is an expression that returns a datetime or smalldatetime value, or a character string in date format.

@Rate

the bond's annual coupon rate. @Rate is an expression of type float or of a type that can be implicitly converted to float.

@Yield

the bond's annual yield. @Yield is an expression of type float or of a type that can be implicitly converted to float.

@Redemption

the bond's redemption value per 100 face value. @Redemption is an expression of type float or of a type that can be implicitly converted to float.

@Frequency

the number of coupon payments per year. For annual payments, @Frequency = 1; for semi-annual, @Frequency = 2; for quarterly, @Frequency = 4; for bi-monthly, @Frequency = 6; for monthly, @Frequency = 12. For bonds with @Basis = 'A/364' or 9, you can enter 364 for payments made every 52 weeks, 182 for payments made every 26 weeks, 91 for payments made every 13 weeks, 28 for payments made every 4 weeks, 14 for payments made every 2 weeks, and 7 for weekly payments. @Frequency is an expression of type float or of a type that can be implicitly converted to float.

@Basis

is the type of day count to use. @Basis is an expression of the character string data type category.

@Issue

the first coupon date of the bond. The period from the issue date until the first coupon date defines the odd interest period. All subsequent coupon dates are assumed to occur at regular periodic intervals as defined by @Frequency. @FirstCoupon is an expression that returns a datetime or smalldatetime value, or a character string in date format.

@FirstCoupon

the first coupon date of the bond. The period from the issue date until the first coupon date defines the odd interest period. All subsequent coupon dates are assumed to occur at regular periodic intervals as defined by @Frequency. @FirstCoupon is an expression that returns a datetime or smalldatetime value, or a character string in date format.

@LastCoupon

the last coupon date of the bond prior to the maturity. The period from the last interest date until the maturity date defines the odd interest period. All coupon dates from @FirstCoupon to @LastCoupon are assumed to occur at regular periodic intervals as defined by @Frequency. @LastCoupon is an expression that returns a datetime or smalldatetime value, or a character string in date format.

Return Type

Remarks

If @Settlement is NULL then @Settlement = GETDATE().

If @Rate is NULL then @Rate = 0.

If @Redemption is NULL then @Redemption = 100.

If @Frequency is NULL then @Frequency = 2.

If @Basis is NULL then @Basis = 1.

If @Maturity <= @Settlement then no rows are returned.

If @Frequency invalid BONDCF returns an error.

If @Basis invalid (see above list), BONDCF returns an error.

If @Maturity is NULL then no rows are returned.

To calculate the cash flows for a bond paying regular periodic interest just enter @Maturity and @Settlement.

To calculate the cash flows for bond with an odd first coupon where the settlement date is before the first coupon date, enter @Issue, @FirstCoupon, @Settlement, and @Maturity. If the settlement date is on or after the first coupon date just enter @Maturity and @Settlement.

To calculate the cash flows for bond with an odd first coupon and an odd last coupon where the settlement date is before the first coupon date, enter @Issue, @FirstCoupon, @LastCoupon, @Settlement, and @Maturity. If the settlement date is on or after the first coupon date just enter @LastCoupon, @Maturity and @Settlement.

To calculate the cash flows for a bond with an odd last coupon enter @LastCoupon, @Settlement, and @Maturity.

Examples

In this example we generate the cash flows for bond which pays regular period interest and is maturing on 2034-06-15. The settlement date is 2014-05-01, the yield is 2.76%, the coupon rate is 2.50%, the redemption value is 100, the coupon is paid twice-yearly and the day-count convention is actual/actual.

SELECT *
FROM wct.BONDCF(   '2014-05-01', --@Settlement
                   '2034-06-15', --@Maturity
                   0.025,        --@Rate
                   0.0276,       --@Yield
                   100,          --@Redemption
                   2,            --@Frequency
                   1,            --@Basis
                   NULL,         --@Issue
                   NULL,         --@FirstCoupon
                   NULL          --@LastCoupon
               );

This produces the following result.

In this example we generate the cash flows for a zero-coupon bond.

SELECT *
FROM wct.BONDCF(   '2014-05-01', --@Settlement
                   '2044-06-15', --@Maturity
                   0.0000,       --@Rate
                   0.0301,       --@Yield
                   100,          --@Redemption
                   2,            --@Frequency
                   1,            --@Basis
                   NULL,         --@Issue
                   NULL,         --@FirstCoupon
                   NULL          --@LastCoupon
               )
WHERE amt_cashflow != 0;

This produces the following result.

In this example we generate the cash flow of a bond paying regular periodic interest settling in the final coupon period.

SELECT *
FROM wct.BONDCF(   '2014-05-01', --@Settlement
                   '2014-07-15', --@Maturity
                   0.0190,       --@Rate
                   0.0005,       --@Yield
                   100,          --@Redemption
                   2,            --@Frequency
                   0,            --@Basis
                   NULL,         --@Issue
                   NULL,         --@FirstCoupon
                   NULL          --@LastCoupon
               );

This produces the following result.

Here we generate the cash flows for a bond paying regular periodic interest which matures on the 30^th of September 2034, with semi-annual coupons payable on March 30^th and September 30^th.

SELECT *
FROM wct.BONDCF(   '2014-05-01', --@Settlement
                   '2034-09-30', --@Maturity
                   0.0257,       --@Rate
                   0.0269,       --@Yield
                   100,          --@Redemption
                   2,            --@Frequency
                   11,           --@Basis
                   NULL,         --@Issue
                   NULL,         --@FirstCoupon
                   NULL          --@LastCoupon
               );

This produces the following result.

Here's an example with a negative yield.

SELECT *
FROM wct.BONDCF(   '2014-05-01', --@Settlement
                   '2014-09-30', --@Maturity
                   0.0257,       --@Rate
                   -0.046219,    --@Yield
                   98,           --@Redemption
                   2,            --@Frequency
                   0,            --@Basis
                   NULL,         --@Issue
                   NULL,         --@FirstCoupon
                   NULL          --@LastCoupon
               );

This produces the following result.

This is an example of bond paying interest every 26 weeks.

SELECT *
FROM wct.BONDCF(   '2014-10-01', --@Settlement
                   '2023-03-13', --@Maturity
                   0.1250,       --@Rate
                   0.1100,       --@Yield
                   100,          --@Redemption
                   182,          --@Frequency
                   9,            --@Basis
                   NULL,         --@Issue
                   NULL,         --@FirstCoupon
                   NULL          --@LastCoupon
               );

This produces the following result.

Here's an example of a bond with odd short first coupon settling on the issue date of the bond.

SELECT *
FROM wct.BONDCF(   '2014-05-01', --@Settlement
                   '2034-06-15', --@Maturity
                   0.0250,       --@Rate
                   0.0276,       --@Yield
                   100,          --@Redemption
                   2,            --@Frequency
                   1,            --@Basis
                   '2014-05-01', --@Issue
                   '2014-06-15', --@FirstCoupon
                   NULL          --@LastCoupon
               );

This produces the following result.

This is a bond with an odd long first coupon, also settling on the issue date.

SELECT *
FROM wct.BONDCF(   '2014-05-01', --@Settlement
                   '2034-06-15', --@Maturity
                   0.0250,       --@Rate
                   0.0276,       --@Yield
                   100,          --@Redemption
                   2,            --@Frequency
                   1,            --@Basis
                   '2014-05-01', --@Issue
                   '2014-12-15', --@FirstCoupon
                   NULL          --@LastCoupon
               );

This produces the following result.

In this example, the settlement date of the bond is after the issue date and the coupon payments are due on the 30^th of March and the 30^th of September.

SELECT *
FROM wct.BONDCF(   '2014-03-15', --@Settlement
                   '2034-09-30', --@Maturity
                   0.0257,       --@Rate
                   0.0269,       --@Yield
                   100,          --@Redemption
                   2,            --@Frequency
                   11,           --@Basis
                   '2014-03-01', --@Issue
                   '2014-03-30', --@FirstCoupon
                   NULL          --@LastCoupon
               );

This produces the following result.

This is an example of bond paying interest every 26 weeks.

SELECT *
FROM wct.BONDCF(   '2014-10-04', --@Settlement
                   '2029-12-12', --@Maturity
                   0.1250,       --@Rate
                   0.1100,       --@Yield
                   100,          --@Redemption
                   182,          --@Frequency
                   9,            --@Basis
                   '2014-03-26', --@Issue
                   '2014-12-31', --@FirstCoupon
                   NULL          --@LastCoupon
               );

This produces the following result.

This is a bond with an odd short last coupon settling in the last coupon period.

SELECT *
FROM wct.BONDCF(   '2014-10-01', --@Settlement
                   '2014-12-15', --@Maturity
                   0.0225,       --@Rate
                   0.0010,       --@Yield
                   100,          --@Redemption
                   2,            --@Frequency
                   1,            --@Basis
                   NULL,         --@Issue
                   NULL,         --@FirstCoupon
                   '2014-09-15'  --@LastCoupon
               );

This produces the following result.

This is a bond with an odd long last coupon settling in the final period.

SELECT *
FROM wct.BONDCF(   '2014-10-01', --@Settlement
                   '2014-12-15', --@Maturity
                   0.0225,       --@Rate
                   0.0010,       --@Yield
                   100,          --@Redemption
                   2,            --@Frequency
                   1,            --@Basis
                   NULL,         --@Issue
                   NULL,         --@FirstCoupon
                   '2014-03-15'  --@LastCoupon
               );

This produces the following result.

This is a bond with an odd short last coupon settling before the last coupon date.

SELECT *
FROM wct.BONDCF(   '2014-10-01', --@Settlement
                   '2034-12-15', --@Maturity
                   0.0425,       --@Rate
                   0.0400,       --@Yield
                   100,          --@Redemption
                   2,            --@Frequency
                   1,            --@Basis
                   NULL,         --@Issue
                   NULL,         --@FirstCoupon
                   '2034-09-15'  --@LastCoupon
               );

This produces the following result.

This is a bond with an odd long last coupon settling before the last coupon date.

SELECT *
FROM wct.BONDCF(   '2014-10-01', --@Settlement
                   '2034-12-15', --@Maturity
                   0.0425,       --@Rate
                   0.0400,       --@Yield
                   100,          --@Redemption
                   2,            --@Frequency
                   1,            --@Basis
                   NULL,         --@Issue
                   NULL,         --@FirstCoupon
                   '2034-03-15'  --@LastCoupon
               );

This produces the following result.

This is a bond with an odd long first coupon and an odd long last coupon.

SELECT *
FROM wct.BONDCF(   '2013-03-04', --@Settlement
                   '2022-11-28', --@Maturity
                   0.03125,      --@Rate
                   0.02875,      --@Yield
                   100,          --@Redemption
                   2,            --@Frequency
                   1,            --@Basis
                   '2012-06-07', --@Issue
                   '2013-03-15', --@FirstCoupon
                   '2022-03-15'  --@LastCoupon
               );

This produces the following result.

This is a bond with an odd long first coupon and an odd short last coupon.

SELECT *
FROM wct.BONDCF(   '2013-03-04', --@Settlement
                   '2022-04-28', --@Maturity
                   0.03125,      --@Rate
                   0.02875,      --@Yield
                   100,          --@Redemption
                   2,            --@Frequency
                   1,            --@Basis
                   '2012-06-07', --@Issue
                   '2013-03-15', --@FirstCoupon
                   '2022-03-15'  --@LastCoupon
               );

This produces the following result.

This is a bond with an odd short first coupon and an odd long last coupon.

SELECT *
FROM wct.BONDCF(   '2013-03-04', --@Settlement
                   '2022-11-28', --@Maturity
                   0.03125,      --@Rate
                   0.02875,      --@Yield
                   100,          --@Redemption
                   2,            --@Frequency
                   1,            --@Basis
                   '2012-12-07', --@Issue
                   '2013-03-15', --@FirstCoupon
                   '2022-03-15'  --@LastCoupon
               );

This produces the following result.

See Also

BONDINT - Accrued interest on a bond paying regular, periodic interest

ODDFINT - Accrued interest for a bond with an odd first coupon

ODDFPRICE - Price of a security with an odd first coupon

ODDFYIELD - Calculate the YIELD with an odd first period

ODDLINT - Accrued interest for a bond with an odd last coupon

ODDLPRICE - Price of a bond with an odd last coupon

ODDLYIELD - Calculate the YIELD with an odd last period

OFLPRICE - Price of a security with an odd last coupon.

OFLYIELD - Yield of a bond with an odd first and an odd last coupon

PRICE - Price of a bond paying regular periodic interest

YIELD - Yield of a bond paying regular periodic coupon