YIELDMAT

Updated 2024-02-29 21:59:59.200000

Syntax

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

Description

Use the scalar function YIELDMAT to calculate the annual yield of a security that pays interest at maturity. The YIELDMAT formula is:

Y=\left[\frac{\left(1+\left(\frac{DIM}{B}\times{R}\right)\right)\times\left(\frac{P}{100}+\frac{A}{B}\times{R}\right)}{\frac{P}{100}+\left(\frac{A}{B}\times{R}\right)}\right]\times\frac{B}{DSM}

Where:

{"columns":[{"field":"column 1"},{"field":"column 2"},{"field":"column 3"}],"rows":[{"column 1":"A","column 2":"=","column 3":"Number of days from issue date to settlement date"},{"column 1":"B","column 2":"=","column 3":"Number of days in the year"},{"column 1":"DIM","column 2":"=","column 3":"Number of days from issue date to maturity date"},{"column 1":"DSM","column 2":"=","column 3":"Number of days from settlement date to maturity date"},{"column 1":"P","column 2":"=","column 3":"Price per 100 par value"},{"column 1":"R","column 2":"=","column 3":"Annual interest rate in decimal terms"},{"column 1":"Y","column 2":"=","column 3":"Annual Yield"}]}

Arguments

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

@Basis

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

{"columns":[{"field":"@Basis","width":313},{"field":"Day count basis","width":277}],"rows":[{"@Basis":"0 , 'BOND'","Day count basis":"US (NASD) 30/360"},{"@Basis":"1 , 'ACTUAL'","Day count basis":"Actual/Actual"},{"@Basis":"2 , 'A360'","Day count basis":"Actual/360"},{"@Basis":"3 , 'A365'","Day count basis":"Actual/365"},{"@Basis":"4 , '30E/360 (ISDA)' , '30E/360' , 'ISDA' , '30E/360 ISDA' , 'EBOND'","Day count basis":"European 30/360"},{"@Basis":"5 , '30/360' , '30/360 ISDA' , 'GERMAN'","Day count basis":"30/360 ISDA"},{"@Basis":"7 , 'NL/365'","Day count basis":"No Leap Year /365"},{"@Basis":"8 , 'NL/360'","Day count basis":"No Leap Year /360"},{"@Basis":"9 , 'A/364'","Day count basis":"Actual/364"}]}

@Rate

the security’s interest rate as of the date of issue. @Rate is an expression of type float or of a type that can be implicitly converted to float.

@Issue

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

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

Return Type

float

Remarks

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

If @Basis is NULL then @Basis = 0.

If @Basis is invalid then YIELDMAT returns an error.

Examples

This security issued on 2014-07-31 matures on 2014-12-15 with an interest rate of 0.5% and a price of 100.0566557. Interest is calculated using the Actual/365 day-count convention.

SELECT wct.YIELDMAT(   '2014-10-07', --@Settlement

                       '2014-12-15', --@Maturity

                       '2014-07-31', --@Issue   

                       0.005,        --@Rate

                       100.0566557,  --@Price

                       3             --@Basis

                   ) as Yield;

This produces the following result.

{"columns":[{"field":"Yield","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"Yield":"0.00199999945283127"}]}

This security issued on 2014-08-15 matures on 2014-12-01 with an interest rate of -0.05% and a price of 99.977088. Interest is calculated using the Actual/360 day-count convention.

SELECT wct.YIELDMAT(   '2014-10-07', --@Settlement

                       '2014-12-01', --@Maturity

                       '2014-08-15', --@Issue   

                       -0.0005,      --@Rate

                       99.977088,    --@Price

                       2             --@Basis

                   ) as Yield;

This produces the following result.

{"columns":[{"field":"Yield","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"Yield":"0.000999997275740647"}]}

This security issued on 2014-08-10 matures on 2014-11-15 with an interest rate of 0.2% and a price of 100.026392. Interest is calculated using the 30/E 360 (ISDA) day-count convention.

SELECT wct.YIELDMAT(   '2014-10-07', --@Settlement

                       '2014-11-15', --@Maturity

                       '2014-08-10', --@Issue   

                       0.002,        --@Rate

                       100.026392,   --@Price

                       4             --@Basis

                   ) as Yield;

This produces the following result.

{"columns":[{"field":"Yield","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"Yield":"-0.000500004440930901"}]}

This security issued on 2014-07-01 matures on 2014-12-29 with an interest rate of 7.0% and a price of 99.628637. Interest is calculated using the Actual/364 day-count convention.

SELECT wct.YIELDMAT(   '2014-10-07', --@Settlement

                       '2014-12-29', --@Maturity

                       '2014-07-01', --@Issue   

                       0.07,         --@Rate

                       99.628637,    --@Price

                       9             --@Basis

                   ) as Yield;

This produces the following result.

{"columns":[{"field":"Yield","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"Yield":"0.0850000161919074"}]}

YIELDMAT

Syntax

Description

Arguments

@Pr

@Basis

@Rate

@Issue

@Settlement

@Maturity

Return Type

Remarks

Examples

See Also