SQL Server YIELDDISC Function

Updated 2024-02-29 21:43:29.033000

Description

Use the scalar function YIELDDISC to calculate the annual yield for a discounted security; for example, a treasury bill. The

YIELDDISC formula is:

\rm{Y=\frac{RV-P}{P}\times\frac{B}{DSM}}

Where:

Syntax

SELECT [westclintech].[wct].[YIELDDISC] (
  <@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 YIELDDISC 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 yield is quoted using the Actual/365 day-count convention.

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

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 yield is quoted using the Actual/360 day-count convention.

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

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 yield is quoted using the Actual/364 day-count convention.

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

This produces the following result.

See Also

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

DISC - Discount rate 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

YIELDMAT - Calculate the YIELD of an Interest-at-Maturity Security

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