Login     Register

        Contact Us     Search

XLeratorDB/financial Documentation

SQL Server DISC function


DISC

Updated: 23 May 2016


Use DISC to calculate the discount rate for a discount security. The DISC formula is:

Where:

B
=
Number of days in the year
DR
=
Discount Rate
DSM
=
Number of days from settlement date to maturity date
P
=
Price per 100 par value
RV
=
Redemption Value

Syntax
SELECT [westclintech].[wct].[DISC] (
  <@Settlement, datetime,>
 ,<@Maturity, datetime,>
 ,<@Pr, float,>
 ,<@Redemption, 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.
@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.
@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.
@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
21, 'Actual/ISDA'
Actual/ISDA

Return Type
float
Remarks
·         If @Settlement IS NULL then @Settlement = GETDATE()
·         If @Basis is NULL then @Basis = 0
·         If @Basis is invalid then DISC returns an error
Examples
This is a security maturing on 2014-12-15 with a 100 redemption value and a price of 99.72. The discount rate is quoted using the Actual/365 day-count convention.
SELECT
   wct.DISC(
    '2014-10-07'  --@Settlement
   ,'2014-12-15'  --@Maturity
   ,99.72         --@Pr
   ,100           --@Redemption
   ,3             --@Basis
   ) as [Discount Rate]
This produces the following result.
         Discount Rate
----------------------
    0.0148115942028987


This is a security maturing on 2015-02-15 with a 10000 redemption value and a price of
9930.86. The discount rate is quoted using the Actual/360 day-count convention.
SELECT
   wct.DISC(
    '2014-10-07'  --@Settlement
   ,'2015-02-15'  --@Maturity
   ,9930.86       --@Pr
   ,10000         --@Redemption
   ,2             --@Basis
   ) as [Discount Rate]
This produces the following result.
         Discount Rate
----------------------
    0.0190003053435114


This is a security maturing on 2015-04-15 with a 1,000,000 redemption value and a price of
971291.21. The discount rate is quoted using the Actual/364 day-count convention.
SELECT
   wct.DISC(
    '2014-10-07'  --@Settlement
   ,'2015-04-15'  --@Maturity
   ,971291.21     --@Pr
   ,1000000       --@Redemption
   ,9             --@Basis
   ) as [Discount Rate]
This produces the following result.
         Discount Rate
----------------------
    0.0549999976842106

 

See Also


Copyright 2008-2013 WestClinTech LLC         Privacy Policy        Terms of Service