SQL Server PRICEDISC Function
Updated 2024-02-29 13:52:02.933000
Description
Use the scalar function PRICEDISC to calculate the price per 100 face value for a discounted security. The PRICEDISC formula is:
\rm{P=RV-DR\times{RV}\times\frac{DSM}{B}}
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].[PRICEDISC] (
<@Settlement, datetime,>
,<@Maturity, datetime,>
,<@Discount, 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.
@Discount
the security's discount rate. @Discount 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 PRICEDISC returns an error.
Examples
This is a security maturing on 2014-12-15 with a 100 redemption value and a discount rate of 1.5%. The discount rate is quoted using the Actual/365 day-count convention.
SELECT wct.PRICEDISC( '2014-10-07', --@Settlement
'2014-12-15', --@Maturity
0.015, --@Discount
100, --@Redemption
3 --@Basis
) as Price;
This produces the following result.
| Price | |
|---|---|
| 99.7164383561644 |
This is a security maturing on 2015-02-15 with a 10000 redemption value and a discount rate of 1.9%. The discount rate is quoted using the Actual/360 day-count convention.
SELECT wct.PRICEDISC( '2014-10-07', --@Settlement
'2015-02-15', --@Maturity
0.019, --@Discount
10000, --@Redemption
2 --@Basis
) as Price;
This produces the following result.
| Price | |
|---|---|
| 9930.86111111111 |
This is a security maturing on 2015-04-15 with a 1,000,000 redemption value and a discount rate of 5.5%. The discount rate is quoted using the Actual/364 day-count convention.
SELECT wct.PRICEDISC( '2014-10-07', --@Settlement
'2015-04-15', --@Maturity
0.055, --@Discount
1000000, --@Redemption
9 --@Basis
) as Price;
This produces the following result.
| Price | |
|---|---|
| 971291.208791209 |
See Also
DIS - Price, discount rate, and/or yield of a discount security
DISC - Discount rate of a discount security
DISFACTORS - Factors for the price calculation of a discount security
ODDFPRICE - Price of a security with an odd first coupon
ODDLPRICE - Price of a bond with an odd last coupon
OFLPRICE - Price of a security with an odd last coupon.
PRICE - Price of a bond paying regular periodic interest
PRICEMAT - Price of an interest-at-maturity security
PRICESTEP - Calculate the Price of a security with step-up rates