SQL Server DISC Function

Updated 2024-02-22 20:59:28.967000

Description

Use the scalar function DISC to calculate the discount rate for a discount security. The DISC formula is:

\mathrm{DR=\frac{RV-P}{RV}\times{\frac{B}{DSM}}

Where:

Syntax

SELECT [westclintech].[wct].[DISC] (
  <@Settlement, datetime,>
 ,<@Maturity, datetime,>
 ,<@Pr, float,>
 ,<@Redemption, float,>
 ,<@Basis, nvarchar(4000),>)

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 maturity date of the security. @Maturity is an expression that returns a datetime or smalldatetime value, or a character string in date format.

@Pr

the security's price per 100 face value. @Pr is an expression of type float or of a type that can be implicitly converted to float.

@Redemption

the security'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.

@Basis

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

Return Type

float

Remarks

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

If @Basis is NULL then @Basis = 0.

If @Basis is invalid then DISC returns an error.

Examples

This is a security maturing on 2014-12-15 with a 100 redemption value and a price of 99.72. The discount rate is quoted using the Actual/365 day-count convention.

SELECT wct.DISC(   '2014-10-07', --@Settlement
                   '2014-12-15', --@Maturity
                   99.72,        --@Pr
                   100,          --@Redemption
                   3             --@Basis
               ) as [Discount Rate];

This produces the following result.

This is a security maturing on 2015-02-15 with a 10000 redemption value and a price of 9930.86 . The discount rate is quoted using the Actual/360 day-count convention.

SELECT wct.DISC(   '2014-10-07', --@Settlement
                   '2015-02-15', --@Maturity
                   9930.86,      --@Pr
                   10000,        --@Redemption
                   2             --@Basis
               ) as [Discount Rate];

This produces the following result.

This is a security maturing on 2015-04-15 with a 1,000,000 redemption value and a price of 971291.21 . The discount rate is quoted using the Actual/364 day-count convention.

SELECT wct.DISC(   '2014-10-07', --@Settlement
                   '2015-04-15', --@Maturity
                   971291.21,    --@Pr
                   1000000,      --@Redemption
                   9             --@Basis
               ) as [Discount Rate];

This produces the following result.

See Also

DIS - Price, discount rate, and/or yield of a discount security

DISFACTORS - Factors for the price calculation of a discount security

ODDFYIELD - Calculate the YIELD with an odd first period

ODDLYIELD - Calculate the YIELD with an odd last period

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

PRICEDISC - Price of a discount security

YIELD - Yield of a bond paying regular periodic coupon

YIELDDISC - Discount rate of a discount security

YIELDSTEP - Calculate the Yield of a security with step-up rates