SQL Server YIELD Function

Updated 2024-02-29 21:40:57.797000

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

Where

C = 100 * coupon rate / frequency

P = price

RV = redemption value

DSR = number of days from settlement to redemption

E = the number of days in the current coupon period

F = Frequency

A = C * accrued days / E

P = Clean Price of the bond

Syntax

SELECT [westclintech].[wct].[YIELD](
  <@Settlement, datetime,>
 ,<@Maturity, datetime,>
 ,<@Rate, float,>
 ,<@Pr, float,>
 ,<@Redemption, float,>
 ,<@Frequency, 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.

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

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

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

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

@Basis

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

@Basis	Day count basis
0 , 'BOND'	US (NASD) 30/360
1 , 'ACTUAL'	Actual/Actual
2 , 'A360'	Actual/360
3 , 'A365'	Actual/365
4 , '30E/360 (ISDA)' , '30E/360' , 'ISDA' , '30E/360 ISDA' , 'EBOND'	European 30/360
5 , '30/360' , '30/360 ISDA' , 'GERMAN'	30/360 ISDA
6 , 'NL/ACT'	No Leap Year/ACT
7 , 'NL/365'	No Leap Year /365
8 , 'NL/360'	No Leap Year /360
9 , 'A/364'	Actual/364
10 , 'BOND NON-EOM'	US (NASD) 30/360 non-end-of-month
11 , 'ACTUAL NON-EOM'	Actual/Actual non-end-of-month
12 , 'A360 NON-EOM'	Actual/360 non-end-of-month
13 , 'A365 NON-EOM'	Actual/365 non-end-of-month
14 , '30E/360 NON-EOM' , '30E/360 ICMA NON-EOM' , 'EBOND NON-EOM'	European 30/360 non-end-of-month
15 , '30/360 NON-EOM' , '30/360 ISDA NON-EOM' , 'GERMAN NON-EOM'	30/360 ISDA non-end-of-month
16 , 'NL/ACT NON-EOM'	No Leap Year/ACT non-end-of-month
17 , 'NL/365 NON-EOM'	No Leap Year/365 non-end-of-month
18 , 'NL/360 NON-EOM'	No Leap Year/360 non-end-of-month
19 , 'A/364 NON-EOM'	Actual/364 non-end-of-month

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;