SQL Server PRICEACTTV Function

Updated 2023-10-05 13:19:05.863000

Description

Use the table-valued function PRICEACTTV to return the cash flows of a bond where coupon payments are calculated using the actual number of days in the coupon period and vary from period to period. PRICEACTTV also supports bonds with forced redemptions.

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 plus any forced redemptions occurring on the coupon date. The row for the maturity date includes the coupon amount and the final 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

Syntax
SELECT * FROM [westclintech].[wct].[PRICEACTTV](
  <@Settlement, datetime,>
 ,<@Maturity, datetime,>
 ,<@Rate, float,>
 ,<@Par, float,>
 ,<@Yield, float,>
 ,<@Frequency, float,>
 ,<@Basis, nvarchar(4000),>
 ,<@Repayments, nvarchar(max),>)

Arguments

@Settlement

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

@Maturity

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

@Rate

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

@Par

the par value of the security. Any forced redemptions are subtracted from the par value on the redemption date and the adjusted balance is used in calculating the subsequent coupon interest. @Par is an expression of type float or of a type that can be implicitly converted to float.

@Yield

the security's annual yield. @Yield 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. @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.

@Repayments

a SELECT statement, as a string, which identifies the coupon dates and the forced redemption amounts to be used in the price calculation.

Return Type

table

Remarks

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

If @Rate is NULL then @Rate = 0.

If @Redemption is NULL then @Par = 100.

If @Yield is NULL then @Yield = 0.

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 PRICEACTTV returns an error.

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

If @Maturity is NULL then no rows are returned.

Examples

In this example we generate the cash flow for a bond with a maturity on 2034-11-01 and with a coupon interest rate of 11.0% paying interest semi-annually. The bond is priced at a yield of 12.5% and is settling on 2014-10-29. The bond uses the actual/actual day-count convention.

SELECT *
FROM wct.PRICEACTTV(   '2014-10-29', --@Settlement
                       '2034-11-01', --@Maturity
                       0.1100,       --@Rate
                       100,          --@Par
                       0.1250,       --@Yield
                       2,            --@Frequency
                       1,            --@Basis
                       NULL          --@Repayments
                   );

This produces the following result .

In this example we have a bond maturing on 2019-10-31 with a 12.5% coupon paid semi-annually. The bond has 16 equal forced redemptions starting with the 2012-04-30 coupon. The bond is priced at a yield of 12.5% settling on 2014-10-29.

SELECT *
FROM wct.PRICEACTTV(
                       '2014-10-29', --@Settlement
                       '2019-10-31', --@Maturity
                       0.125,        --@Rate
                       100,          --@Par
                       0.125,        --@Yield
                       2,            --@Frequency
                       1,            --@Basis
                       'SELECT
          *
       FROM (VALUES
           (''2012-04-30'',6.25)
          ,(''2012-10-31'',6.25)
          ,(''2013-04-30'',6.25)
          ,(''2013-10-31'',6.25)
          ,(''2014-04-30'',6.25)
          ,(''2014-10-31'',6.25)
          ,(''2015-04-30'',6.25)
          ,(''2015-10-31'',6.25)
          ,(''2016-04-30'',6.25)
          ,(''2016-10-31'',6.25)
          ,(''2017-04-30'',6.25)
          ,(''2017-10-31'',6.25)
          ,(''2018-04-30'',6.25)
          ,(''2018-10-31'',6.25)
          ,(''2019-04-30'',6.25)
          ,(''2019-10-31'',6.25)
          )n(dt_ppay, amt_ppay)'     --@Repayments
                   );

This produces the following result.

See Also

BONDCF - Cash flows for a bond paying regular periodic interest

PRICEACT - Price of a bond where the coupon amounts are calculated as the actual number of days in the coupon period divided by the number of days in the year.

YIELDACT - Yield on a bond where the coupon amounts are calculated as the actual number of days in the coupon period divided by the number of days in the year

ACCINTACT - Accrued interest on a bond where the coupon amounts are calculated as the actual number of days in the coupon period divided by the number of days in the year.