SQL Server YIELDMAT Function
Updated 2024-02-29 21:59:59.200000
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:
| column 1 | column 2 | column 3 |
|---|---|---|
| A | = | Number of days from issue date to settlement date |
| B | = | Number of days in the year |
| DIM | = | Number of days from issue date to maturity date |
| DSM | = | Number of days from settlement date to maturity date |
| P | = | Price per 100 par value |
| R | = | Annual interest rate in decimal terms |
| Y | = | Annual Yield |
Syntax
SELECT [westclintech].[wct].[YIELDMAT] (
<@Settlement, datetime,>
,<@Maturity, datetime,>
,<@Issue, datetime,>
,<@Rate, float,>
,<@Pr, 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.
@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.
@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.
@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.
| @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 |
| 7 , 'NL/365' | No Leap Year /365 |
| 8 , 'NL/360' | No Leap Year /360 |
| 9 , 'A/364' | Actual/364 |
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.
| 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.
| 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.
| 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.
| Yield |
|---|
| 0.0850000161919074 |
See Also
ACCRINTM - Calculate the accrued interest for a security that pays interest at maturity.
IAM - Price and/or yield of a security paying interest at maturity
IAMFACTORS - Factors for the price calculation of a security paying interest at maturity
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
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
PRICEMAT - Price of an interest-at-maturity security
RATE - Rate of an annuity given number of periods, periodic payment, present value, and future value
LRATE - annual interest rate for an annuity with an odd first period