Logo

YIELD

Updated 2024-02-29 21:40:57.797000

Syntax

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

Description

Use the scalar function YIELD to calculate the yield, given the price, for a security that pays periodic interest and has a par value of 100. There is no closed-form solution for calculating the yield when there is more than one coupon period to redemption; the solution is found by iteration.

When the settlement date is in the final coupon period the formula for yield is:

YIELD=\left(\frac{RV+C}{P+A}-1\right)*\frac{E}{DSR}*F

WhereC = 100 * coupon rate / frequencyP = priceRV = redemption valueDSR = number of days from settlement to redemptionE = the number of days in the current coupon periodF = FrequencyA = C * accrued days / EP = Clean Price of the bond

Arguments

@Pr

the clean price of the security. @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":"6 , 'NL/ACT'","Day count basis":"No Leap Year/ACT"},{"@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"},{"@Basis":"10 , 'BOND NON-EOM'","Day count basis":"US (NASD) 30/360 non-end-of-month"},{"@Basis":"11 , 'ACTUAL NON-EOM'","Day count basis":"Actual/Actual non-end-of-month"},{"@Basis":"12 , 'A360 NON-EOM'","Day count basis":"Actual/360 non-end-of-month"},{"@Basis":"13 , 'A365 NON-EOM'","Day count basis":"Actual/365 non-end-of-month"},{"@Basis":"14 , '30E/360 NON-EOM' , '30E/360 ICMA NON-EOM' , 'EBOND NON-EOM'","Day count basis":"European 30/360 non-end-of-month"},{"@Basis":"15 , '30/360 NON-EOM' , '30/360 ISDA NON-EOM' , 'GERMAN NON-EOM'","Day count basis":"30/360 ISDA non-end-of-month"},{"@Basis":"16 , 'NL/ACT NON-EOM'","Day count basis":"No Leap Year/ACT non-end-of-month"},{"@Basis":"17 , 'NL/365 NON-EOM'","Day count basis":"No Leap Year/365 non-end-of-month"},{"@Basis":"18 , 'NL/360 NON-EOM'","Day count basis":"No Leap Year/360 non-end-of-month"},{"@Basis":"19 , 'A/364 NON-EOM'","Day count basis":"Actual/364 non-end-of-month"}]}

@Frequency

the number of coupon payments per year. For annual payments, @Frequency = 1; for semi-annual, @Frequency = 2; for quarterly, @Frequency = 4; 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.

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

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

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

Return Type

float

Remarks

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

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

If @Rate is NULL then @Rate = 0.

If @Price is NULL then @Price = 100.

If @Redemption is NULL then @Redemption = 100.

If @Frequency is NULL then @Frequency = 2.

If @Basis is NULL then @Basis = 0.

If @Frequency is any number other than 1, 2, 4, 6 or 12, or for @Basis = 'A/364' any number other than 1, 2, 4, 6 or 12 as well as 7, 14, 28, 91, 182 or 364 YIELD returns an error.

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

Examples

In this example we calculate the yield given a price of 96.004 for a bond maturing on 2034-06-15. The settlement date is 2014-05-01, the coupon rate is 2.50%, the redemption value is 100, the coupon is paid twice-yearly, and the basis code is 1.

SELECT wct.YIELD(   '2014-05-01', --@Settlement

                    '2034-06-15', --@Maturity

                    0.025,        --@Rate

                    96.004,       --@Price

                    100,          --@Redemption

                    2,            --@Frequency

                    1             --@Basis

                ) as YIELD;

This produces the following result.

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

In this example, we calculate the yield of a zero-coupon bond.

SELECT wct.YIELD(   '2014-05-01', --@Settlement

                    '2044-06-15', --@Maturity

                    0.00,         --@Rate

                    40.6584,      --@Price

                    100,          --@Redemption

                    2,            --@Frequency

                    1             --@Basis

                ) as YIELD;

This produces the following result.

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

In this example we calculate the yield of a bond settling in the final coupon period.

SELECT wct.YIELD(   '2014-05-01', --@Settlement

                    '2014-07-15', --@Maturity

                    0.0190,       --@Rate

                    100.3802,     --@Price

                    100,          --@Redemption

                    2,            --@Frequency

                    0             --@Basis

                ) as YIELD;

This produces the following result.

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

Here we calculate the yield of a bond maturing on the 30th of September 2034, with semi-annual coupons payable on March 30th and September 30th.

SELECT wct.YIELD(   '2014-05-01', --@Settlement

                    '2034-09-30', --@Maturity

                    0.0257,       --@Rate

                    98.1233,      --@Price

                    100,          --@Redemption

                    2,            --@Frequency

                    11            --@Basis

                ) as YIELD;

This produces the following result.

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

Here's an example of a bond with a negative yield.

SELECT wct.YIELD(   '2014-05-01', --@Settlement

                    '2014-09-30', --@Maturity

                    0.0257,       --@Rate

                    101,          --@Price

                    98,           --@Redemption

                    2,            --@Frequency

                    0             --@Basis

                ) as YIELD;

This produces the following result.

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

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

SELECT wct.YIELD(   '2014-10-01',     --@Settlement

                    '2023-03-13',     --@Maturity

                    0.1250,           --@Rate

                    108.126105929164, --@Price

                    100,              --@Redemption

                    182,              --@Frequency

                    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.109999999999346"}]}

See Also

BONDAMORT - Bond amortization schedule using constant effective daily interest method

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

PRICE - Price of a bond paying regular periodic interest

YIELDDISC - Discount rate of a discount security

YIELDMAT - Calculate the YIELD of an Interest-at-Maturity 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

TBILLYIELD - Yield of a US Treasury Bill

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