RPI
Updated 2024-02-29 14:26:40.730000
Syntax
SELECT [westclintech].[wct].[RPI](
<@A, float,>
,<@DSC, float,>
,<@E, float,>
,<@N, float,>
,<@R, float,>
,<@Y, float,>
,<@P, float,>
,<@F, int,>
,<@RV, float,>)
Description
Use the scalar function RPI to calculate the price or yield for a bond that pays periodic interest and has a par value of 100. The formula for price with more than one coupon period to redemption is:
PRICE=\ \left(\frac{\frac{-C}{Y}+RV}{\left(1+Y\right)^N}-\frac{-C}{Y}\right)\ast{(1+Y)}^{1-\frac{DSC}{E}}-A
WhereC = 100 * coupon rate / frequencyY = yield / frequencyRV = redemption valueDSC = number of days from settlement to couponN = the number of coupons between the settlement date and the maturity dateE = the number of days in the current coupon periodA = C * accrued days / E
When the next coupon is paid at maturity the formula for price is:
PRICE=\ \frac{RV+C}{1+\ \left(Y\ast\ \frac{DSR}{E}\right)}-A
WhereC = 100 * coupon rate / frequencyY = yield / frequencyRV = redemption valueDSR = number of days from settlement to redemptionE = the number of days in the current coupon periodA = C * accrued days / E
The RPI function allows you to pass values for A, DSC/DSR, E, RV, and N directly into the equation and automatically calculates Y and C.
Arguments
@N
the number of coupons between settlement and maturity. @Yld is an expression of type float or of a type that can be implicitly converted to float.
@A
the accrued days. @A is an expression of type float or of a type that can be implicitly converted to float.
@P
the price of the bond. @P is an expression of type float or of a type that can be implicitly converted to float.
@RV
the redemption value of the security. @RV is an expression of type float or of a type that be implicitly converted to float.
@E
the number of days in the settlement period. @E is an expression of type float or of a type that can be implicitly converted to float.
@R
the annual coupon rate. @R is an expression of type float or of a type that can be implicitly converted to float.
@F
the number of coupon payments per year. For annual payments, @F = 1; for semi-annual, @F = 2; for quarterly, @F = 4; for bi-monthly, @F = 6; for monthly @F = 12. @F is an expression of type int or of a type that can be implicitly converted to int.
@DSC
the days from settlement to next coupon date (when @N > 1) or the days from settlement to redemption (when @N = 1). @DSC is an expression of type float or of a type that can be implicitly converted to float.
@Y
the yield on the bond. @Y is an expression of type float or of a type that can be implicitly converted to float.
Return Type
float
Remarks
If @A is NULL then @A = 0.
If @E is NULL then @E =180.
If @DSC is NULL then @DSC = @E - @A.
If @N is NULL then @N = 2.
If @R is NULL then @R = 0.
If @F is NULL then @F = 2.
If @RV is NULL then @RV = 100.
If @Y is NULL and @P is NULL then NULL is returned.
If @E = 0 then NULL is returned.
If @F = 0 then NULL is returned.
C = 100 * @R/@F.
Y = @Y/@F.
If @Y is not NULL then the function calculates the price from the inputs otherwise the function calculates the yield.
Examples
In this example we calculate the price for a bond with an annual coupon rate of 2.50% paid twice a year with a redemption value of 100. There are 137 accrued days, 45 days until the next coupon, 182 days in the coupon period and 41 coupons remaining. The yield on the bond is 2.76%.
SELECT wct.RPI( 137, --@A
45, --@DSC
182, --@E
41, --@N
.025, --@R
0.0276, --@Y
NULL, --@P
2, --@F
100 --@RV
) as PRICE;
This produces the following result.
{"columns":[{"field":"PRICE","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"PRICE":"96.0043799057024"}]}
In this example, we calculate the price of a zero-coupon bond with 137 accrued days, 45 days to the next coupon date, 182 days in the settlement period, and 61 coupon periods until maturity. The yield on the bond is 3.01%.
SELECT wct.RPI( 137, --@A
45, --@DSC
182, --@E
61, --@N
0.0, --@R
0.0301, --@Y
NULL, --@P
2, --@F
100 --@RV
) as PRICE;
This produces the following result.
{"columns":[{"field":"PRICE","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"PRICE":"40.6583576113141"}]}
In this example we calculate the price of a bond settling in the final coupon period.
SELECT wct.RPI( 106, --@A
74, --@DSC
180, --@E
1, --@N
0.019, --@R
0.0005, --@Y
NULL, --@P
2, --@F
100 --@RV
) as PRICE;
This produces the following result.
{"columns":[{"field":"PRICE","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"PRICE":"100.380181205142"}]}
Here we calculate the yield of a bond.
SELECT wct.RPI( 32, --@A
152, --@DSC
184, --@E
41, --@N
0.0257, --@R
NULL, --@Y
98.123291, --@P
2, --@F
100 --@RV
) as YIELD;
This produces the following result.
{"columns":[{"field":"YIELD","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"YIELD":"0.0268999998648661"}]}
Here's an example of the yield calculation returning a negative yield.
SELECT wct.RPI( 31, --@A
149, --@DSC
180, --@E
1, --@N
0.0257, --@R
NULL, --@Y
101, --@P
2, --@F
98 --@RV
) as YIELD;
This produces the following result.
{"columns":[{"field":"YIELD","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"YIELD":"-0.0462187493233163"}]}
See Also
BONDAMORT - Bond amortization schedule using constant effective daily interest method
BONDINT - Accrued interest on a bond paying regular, periodic interest
PRICE - Price of a bond paying regular periodic interest
RPICONVEXITY - Convexity of a bond paying regular periodic interest
RPIDURATION - Duration of a bond paying regular periodic interest
RPIFACTORS - Factors for the calculation of the price of a bond that pays regular periodic interest
RPIMDURATION - Modified duration of a bond paying regular period interest