# SQL Server PRICEMAT Function

PRICEMAT

Updated: 10 October 2014

Use the scalar function PRICEMAT to calculate the price (expressed per 100 par value) of a security that pays interest at maturity. The PRICEMAT 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].[PRICEMAT] (
<@Settlement, datetime,>
,<@Maturity, datetime,>
,<@Issue, datetime,>
,<@Rate, float,>
,<@Yld, 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 annual coupon rate. @Rate is an expression of type float or of a type that can be implicitly converted to float.
@Yld
the securityâ€™s annual yield. @Yld 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 PRICEMAT 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 yield of 0.2%. Interest is calculated using the Actual/365 day-count convention.
SELECT
wct.PRICEMAT(
'2014-10-07'  --@Settlement
,'2014-12-15'  --@Maturity
,'2014-07-31'  --@Issue
,0.005         --@Rate
,0.002         --@Yield
,3             --@Basis
) as Price
This produces the following result.
Price
----------------------
100.056655689645

This security issued on 2014-08-15 matures on 2014-12-01 with an interest rate of -0.05% and a yield of 0.1%. Interest is calculated using the Actual/360 day-count convention.
SELECT
wct.PRICEMAT(
'2014-10-07'  --@Settlement
,'2014-12-01'  --@Maturity
,'2014-08-15'  --@Issue
,-0.0005       --@Rate
,0.001         --@Yield
,2             --@Basis
) as Price
This produces the following result.
Price
----------------------
99.9770879583983

This security issued on 2014-08-10 matures on 2014-11-15 with a yield of -0.05% and an interest rate of 0.2%. Interest is calculated using the 30/E 360 (ISDA) day-count convention.
SELECT
wct.PRICEMAT(
'2014-10-07'  --@Settlement
,'2014-11-15'  --@Maturity
,'2014-08-10'  --@Issue
,0.002         --@Rate
,-0.0005       --@Yield
,4             --@Basis
) as Price
This produces the following result.
Price
----------------------
100.026391953094

This security issued on 2014-07-01 matures on 2014-12-29 with an interest rate of 7.0% and a yield of 8.5%. Interest is calculated using the Actual/364 day-count convention.
SELECT
wct.PRICEMAT(
'2014-10-07' --@Settlement
,'2014-12-29' --@Maturity
,'2014-07-01' --@Issue
,0.07          --@Rate
,0.085         --@Yield
,9             --@Basis
) as Price
This produces the following result.
Price
----------------------
99.628637367672