PRICEACT
Updated: 31 October 2014
Use the scalar function PRICEACT to calculate the price from yield of a bond where the coupon amounts are calculated as the actual number of days in the coupon period divided by the number of days in the year. This means that the coupon amounts will vary from period. The number of days in the year is either 360, 365, or 366 based upon the daycount convention. PRICEACT also allows the entry of a forced redemption schedule.
The price of the bond is the discounted cash flow value of all the remaining payments minus the accrued interest.
The formula for the price of a bond with more than one coupon period to redemption using the actual number of days in each coupon period is:
Where:
A

=

Actual number of days from the previous coupon date to the settlement date

CF_{n}

=

Cash Flow for period n

DF_{n}

=

Discount factor for period n

DIY

=

Number of days in the year in which the coupon payment occurs

n

=

Number of coupons from settlement date to maturity date

P

=

Par value of the security

R

=

Coupon rate

and
Where
F

=

The number of coupon payments per year

t_{1}

=

Time, in years, from the settlement date to the first coupon date

t_{n}

=

Time, in years, of the coupon period

Y

=

Annual yield

and
Where
P

=

Par value of the security

R

=

Coupon rate

t_{n}

=

Time, in years, of the coupon period

In the case where there are forced redemptions (i.e. partial repayments of principal prior to the maturity date of security), then the formula needs to be adjusted to reflect those redemptions in the cash flows (CF_{n}) and the price calculation then becomes:
Where P/Prin is the par value divided by the outstanding principal balance as of the coupon date immediately prior to the settlement date.
In the case where the settlement date is in the final coupon then the formula for the price of the bond is the same as the formula used in PRICE function.
Syntax
SELECT [wct].[PRICEACT](
<@Settlement, datetime,>
,<@Maturity, datetime,>
,<@Rate, float,>
,<@Par, float,>
,<@Yield, float,>
,<@Frequency, float,>
,<@Basis, nvarchar(4000),>
,<@Repayments, 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.
@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.
@Par
the par value of the security. Any forced redemptions are subtracted from the par value on the redemption date and the adjusted balance is used in calculating the subsequent coupon interest. @Par is an expression of type float or of a type that can be implicitly converted to float.
@Yield
the securityâ€™s annual yield. @Yield 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 semiannual, @Frequency = 2; for quarterly, @Frequency = 4; for bimonthly, @Frequency = 6, for monthly, @Frequency = 12. @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

1, 'ACTUAL'

Actual/Actual

2, 'A360'

Actual/360

3, 'A365'

Actual/365

11, 'ACTUAL NONEOM'

Actual/Actual nonendofmonth

12, 'A360 NONEOM'

Actual/360 nonendofmonth

13, 'A365 NONEOM'

Actual/365 nonendofmonth

@Repayments
a SELECT statement, as a string, which identifies the coupon dates and the forced redemption amounts to be used in the price calculation.
Return Type
float
Remarks
Â· If @Basis is invalid then PRICEACT returns an error.
Â· If @Frequency is invalid then PRICEACT returns an error.
Â· If @Maturity < @Settlement then NULL is returned.
Â· If @Repayments returns NULL then @Par is used for all interest calculations and as the redemption value.
Â· If @Settlement is NULL, @Settlement = GETDATE().
Â· If @Frequency is NULL, @Frequency = 2.
Â· If @Basis is NULL, @Basis = 1.
Â· PRICEACT forces the principal balance of the bond to zero at maturity.
Â· If @Par is NULL then @Par = 100.
Â· If @Rate is NULL then @Rate = 0.
Â· If @Yield is NULL then @Yield = 0.
Â· If @Maturity is NULL then PRICEACT returns NULL.
Â· If @Basis = 3 or @Basis = 13 then the number of days in a year is always 365.
Â· If @Basis =2 or @Basis = 12 then the number of days in a year is always 360.
Â· If @Basis =1 or @Basis = 1 then the number of days in a year is determined by the actual number of days in the year of coupon period end date.
Examples
In this example we calculate the price of a bond maturity on 20341101 with a coupon interest rate of 11.0% paying interest semiannually. The bond is priced at a yield of 12.5% and is settling on 20141029. The bond uses the actual/actual daycount convention.
SELECT
wct.PRICEACT(
'20141029' @Settlement
,'20341101' @Maturity
,0.11 @Rate
,100 @Par
,0.125 @Yield
,2 @Frequency
,1 @Basis
,NULL @Repayments
) as PRICE
This produces the following result.
PRICE

89.0583463371609
Let's compare this to the result returned by the PRICE function.
SELECT
wct.PRICEACT(
'20141029' @Settlement
,'20341101' @Maturity
,0.11 @Rate
,100 @Par
,0.125 @Yield
,2 @Frequency
,1 @Basis
,NULL @Repayments
) as PRICEACT
,wct.PRICE(
'20141029' @Settlement
,'20341101' @Maturity
,0.11 @Rate
,0.125 @Yield
,100 @Redemption
,2 @Frequency
,1 @Basis
) as PRICE
This produces the following result.
PRICEACT PRICE
 
89.0583463371609 89.0580044972901
In this example we have a bond maturing on 20191031 with a 12.5% coupon paid semiannually. The bond has 16 equal forced redemptions starting with the 20120430 coupon. The bond is priced at a yield of 12.5% settling on 20141029.
SELECT
wct.PRICEACT(
'20141029' @Settlement
,'20191031' @Maturity
,0.125 @Rate
,100 @Par
,0.125 @Yield
,2 @Frequency
,1 @Basis
,'SELECT
*
FROM (VALUES
(''20120430'',6.25)
,(''20121031'',6.25)
,(''20130430'',6.25)
,(''20131031'',6.25)
,(''20140430'',6.25)
,(''20141031'',6.25)
,(''20150430'',6.25)
,(''20151031'',6.25)
,(''20160430'',6.25)
,(''20161031'',6.25)
,(''20170430'',6.25)
,(''20171031'',6.25)
,(''20180430'',6.25)
,(''20181031'',6.25)
,(''20190430'',6.25)
,(''20191031'',6.25)
)n(dt_ppay, amt_ppay)' @Repayments
) as PRICE
This produces the following result.
PRICE

99.9985011972914
See Also