Login     Register

        Contact Us     Search

XLeratorDB/financial Documentation

SQL Server price function for stepped-rate bonds


PRICESTEP

Updated: 09 April 2013


Use PRICESTEP to calculate the price from yield per 100 face value of a security with multiple interest coupon rates, also known as step-up rates.
Syntax
SELECT [wctFinancial].[wct].[PRICESTEP](
  <@Settlement, datetime,>
 ,<@Maturity, datetime,>
 ,<@Yld, float,>
 ,<@Redemption, float,>
 ,<@Frequency, float,>
 ,<@Basis, nvarchar(4000),>
 ,<@Coupons, nvarchar(max),>)
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. 
@Yld
the security’s annual yield. @Yld 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.
@Frequency
the number of coupon payments per year. For annual payments, @Frequency = 1; for semi-annual, @Frequency = 2; for quarterly, @Frequency = 4. @Frequency 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 or omitted
US (NASD) 30/360
1
Actual/Actual
2
Actual/360
3
Actual/365
4
European 30/360
 
 
@Coupons
a SELECT statement, as a string, which identifies the coupon dates and rates to be used in the price calculation. The coupon rate is assumed to be in effect from the associated coupon date to the next greater coupon date returned by the select statement. The last rate is assumed to be in effect from the last date until the maturity date of the security
Return Type
float
Remarks
·         If @Basis < 0 or if @Basis > 4, then PRICESTEP returns an error.
·         @Settlement must be <= @Maturity.
·         If @Settlement is NULL, @Settlement = GETDATE().
·         If @Frequency is NULL, @Frequency = 2.
·         If @Basis is NULL, @Basis = 0.
·         If @Redemption is NULL, @Redemption = 100.
Example
In this example we calculate the price of a bond maturing on 2019-01-15 with the following step-up schedule:
                2010-01-15          5.0%
                2013-01-15          5.5%
                2016-01-15          6.0%
The yield is 5% and the settlement date is April 15, 2013.
SELECT wct.PRICESTEP(
 '2013-04-15'           --@Settlement
,'2019-01-15'           --@Maturity
,.05                    --@Yield
,100                    --@Redemption
,2                      --@Frequency
,0                      --@Basis
,'SELECT wct.CALCDATE(2010,1,15), 0.05 UNION ALL
 SELECT wct.CALCDATE(2013,1,15), 0.055 UNION ALL
 SELECT wct.CALCDATE(2016,1,15), 0.06'   --@Coupons
 ) as PRICE
This produces the following result.
                 PRICE
----------------------
      103.665743246561

The SELECT statement in @Coupons can make reference to another table, as in the following example.
SELECT *
INTO #coups
FROM (
      SELECT wct.CALCDATE(2010,1,15), 0.05 UNION ALL
      SELECT wct.CALCDATE(2013,1,15), 0.055 UNION ALL
      SELECT wct.CALCDATE(2016,1,15), 0.06
      )n(coupdate, couprate)
 
SELECT wct.PRICESTEP(
 '2013-04-15'           --@Settlement
,'2019-01-15'           --@Maturity
,.05                    --@Yield
,100                    --@Redemption
,2                      --@Frequency
,0                      --@Basis
,'SELECT * FROM #coups' --@Coupons
 ) as PRICE

This produces the following result.
                 PRICE
----------------------
      103.665743246561
 

In this examplewe have multiple securities with different step-up schedules. For purposes of this example, the coupon schedules are stored in a temporary table, #coups, and the rest of the pricing information is stored in the derived table n. We also use the XLeratorDB
QUOTES function simplifying the @Coupons variable.
SELECT *
INTO #coups
FROM (
      SELECT 'ABC', '2010-01-15', 0.050 UNION ALL
      SELECT 'ABC', '2013-01-15', 0.055 UNION ALL
      SELECT 'ABC', '2016-01-15', 0.060 UNION ALL
      SELECT 'GHI', '2031-07-22', 0.070 UNION ALL
      SELECT 'GHI', '2026-07-22', 0.0675 UNION ALL
      SELECT 'GHI', '2021-07-22', 0.0650 UNION ALL
      SELECT 'GHI', '2016-07-22', 0.0625 UNION ALL
      SELECT 'GHI', '2011-07-22', 0.0600 UNION ALL
      SELECT 'XYZ', '2023-03-01', 0.0600 UNION ALL
      SELECT 'XYZ', '2019-03-01', 0.0575 UNION ALL
      SELECT 'XYZ', '2015-03-1', 0.0550 UNION ALL
      SELECT 'XYZ', '2011-03-1', 0.0
      )n(secid, coupdate, couprate)
 
SELECT secid
,wct.PRICESTEP('2013-04-09'
,maturity
,yield
,redemption
,frequency
,basis
,'SELECT coupdate
 ,couprate
 FROM #coups
 WHERE secid = ' + westclintech.wct.QUOTES(secid)
 ) as PRICE
 
FROM (
      SELECT 'ABC', '2019-01-15', .05, 100, 2, 0 UNION ALL
      SELECT 'GHI', '2036-07-22', .05, 103, 2, 1 UNION ALL
      SELECT 'XYZ', '2027-03-01', .05, 101, 1, 0
      )n(secid, maturity, yield, redemption, frequency, basis)

This produces the following result.
secid                  PRICE
----- ----------------------
ABC         103.670987805261
GHI         120.467993586393
XYZ         97.4783247467923
 

 

See Also

 



Copyright 2008-2017 Westclintech LLC         Privacy Policy        Terms of Service