Login     Register

        Contact Us     Search

XLeratorDB/financial Documentation

SQL Server YIELDMAT function


YIELDMAT

Updated: 10 October 2014


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

Where:

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
Example
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
·         RATE - Periodic rate of annuity

 



Copyright 2008-2024 Westclintech LLC         Privacy Policy        Terms of Service