YIELDACT
Updated 2023-10-05 20:49:22.027000
Syntax
SELECT [[westclintech].wct].[YIELDACT](
<@Settlement, datetime,>
,<@Maturity, datetime,>
,<@Rate, float,>
,<@Par, float,>
,<@Price, float,>
,<@Frequency, float,>
,<@Basis, nvarchar(4000),>
,<@Repayments, nvarchar(max),>)
Description
Use the scalar function YIELDACT to calculate the yield on a bond given the price, 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 day-count convention. YIELDACT also allows the entry of a forced redemption schedule. There is no closed-form solution for the calculation of yield from price if there is more than one coupon period to redemption.
Arguments
@Basis
{"columns":[{"field":"@Basis","width":305},{"field":"Day count basis","width":270}],"rows":[{"@Basis":"1 , 'ACTUAL'","Day count basis":"Actual/Actual"},{"@Basis":"2 , 'A360'","Day count basis":"Actual/360"},{"@Basis":"3 , 'A365'","Day count basis":"Actual/365"},{"@Basis":"11 , 'ACTUAL NON-EOM'","Day count basis":"Actual/Actual non-end-of-month"},{"@Basis":"12 , 'A360 NON-EOM'","Day count basis":"Actual/360 non-end-of-month"},{"@Basis":"13 , 'A365 NON-EOM'","Day count basis":"Actual/365 non-end-of-month"}]}
@Frequency
the number of coupon payments per year. For annual payments, @Frequency = 1; for semi-annual, @Frequency = 2; for quarterly, @Frequency = 4; for bi-monthly, @Frequency = 6, for monthly, @Frequency = 12. @Frequency is an expression of type float or of a type that can be implicitly converted to float.
@Repayments
a SELECT statement, as a string, which identifies the coupon dates and the forced redemption amounts to be used in the price calculation.
@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.
@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.
@Price
the clean price of the security. @Price is an expression of type float or of a type that can be implicitly converted to float.
Return Type
float
Remarks
If @Basis is invalid then YIELDACT returns an error.
If @Frequency is invalid then YIELDACT 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.
YIELDACT 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 @Price is NULL then @Price = @Par.
If @Maturity is NULL then YIELDACT 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 yield on a bond maturity on 2034-11-01 with a coupon interest rate of 11.0% paying interest semi-annually. The price of the bond is 89.058346 and is settling on 2014-10-29. The bond uses the actual/actual day-count convention.
SELECT wct.YIELDACT( '2014-10-29', --@Settlement
'2034-11-01', --@Maturity
0.11, --@Rate
100, --@Par
89.058346, --@Price
2, --@Frequency
1, --@Basis
NULL --@Repayments
) as YIELD;
This produces the following result.
{"columns":[{"field":"YIELD","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"YIELD":"0.125000000506147"}]}
Let's compare this to the result returned by the YIELD function.
SELECT wct.YIELDACT( '2014-10-29', --@Settlement
'2034-11-01', --@Maturity
0.11, --@Rate
100, --@Par
89.058346, --@Price
2, --@Frequency
1, --@Basis
NULL --@Repayments
) as YIELDACT,
wct.YIELD( '2014-10-29', --@Settlement
'2034-11-01', --@Maturity
0.11, --@Rate
89.058346, --@Price
100, --@Redemption
2, --@Frequency
1 --@Basis
) as YIELD;
This produces the following result.
{"columns":[{"field":"YIELDACT","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"YIELD","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"YIELDACT":"0.125000000506147","YIELD":"0.124999483945568"}]}
In this example we have a bond maturing on 2019-10-31 with a 12.5% coupon paid semi-annually. The bond has 16 equal forced redemptions starting with the 2012-04-30 coupon. The bond is priced at 99.998501 settling on 2014-10-29.
SELECT wct.YIELDACT(
'2014-10-29', --@Settlement
'2019-10-31', --@Maturity
0.125, --@Rate
100, --@Par
99.998501, --@Price
2, --@Frequency
1, --@Basis
'SELECT
*
FROM (VALUES
(''2012-04-30'',6.25)
,(''2012-10-31'',6.25)
,(''2013-04-30'',6.25)
,(''2013-10-31'',6.25)
,(''2014-04-30'',6.25)
,(''2014-10-31'',6.25)
,(''2015-04-30'',6.25)
,(''2015-10-31'',6.25)
,(''2016-04-30'',6.25)
,(''2016-10-31'',6.25)
,(''2017-04-30'',6.25)
,(''2017-10-31'',6.25)
,(''2018-04-30'',6.25)
,(''2018-10-31'',6.25)
,(''2019-04-30'',6.25)
,(''2019-10-31'',6.25)
)n(dt_ppay, amt_ppay)' --@Repayments
) as YIELD;
This produces the following result.
{"columns":[{"field":"YIELD","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"YIELD":"0.125000000993551"}]}
See Also
ODDFYIELD - Calculate the YIELD with an odd first period
ODDLYIELD - Calculate the YIELD with an odd last period
OFLYIELD - Yield of a bond with an odd first and an odd last coupon
YIELD - Yield of a bond paying regular periodic coupon
YIELDDISC - Discount rate of a discount security
YIELDFR - Yield of a bond with forced redemptions
YIELDMAT - Calculate the YIELD of an Interest-at-Maturity Security
YIELDSTEP - Calculate the Yield of a security with step-up rates