OFL
Updated 2024-02-28 16:32:17.857000
Syntax
SELECT [westclintech].[wct].[OFL](
<@Rate, float,>
,<@Yield, float,>
,<@Price, float,>
,<@RV, float,>
,<@Freq, int,>
,<@A1, float,>
,<@A2, float,>
,<@DSC, float,>
,<@E, float,>
,<@N, int,>
,<@ShortFirst, bit,>
,<@ShortLast, bit,>
,<@DLC1, float,>
,<@DLC2, float,>
,<@NLL1, float,>
,<@NLL2, float,>
,<@DFC1, float,>
,<@DFC2, float,>
,<@NLF1, float,>
,<@NLF2, float,>
,<@Nqf, int,>)
Description
Use the scalar function OFL to calculate the price or yield of a bond with an odd first period, an odd last period, and a par value of 100. The OFL formula for a bond with an odd short first coupon is:
\text{PRICE}=\frac{C*\frac{DFC}{E}+\left(\frac{\frac{-C}{Y}+\frac{RV+C\times\Sigma_{i=1}^{NCL}\frac{DLC_i}{NLL_i}}{\left(1+Y\right)^{\Sigma_{i=1}^{NCL}\frac{DLC_i}{NLL_i}}}}{\left(1+Y\right)^N}-\frac{-C}{Y}\right)}{\left(1+Y\right)^{\frac{DSC}{E}}}-A
WhereA = C * accrued days / EC = 100 * coupon rate / frequencyDFC = the number of days from the issue date to the first coupon dateDLCi = the number of days from the previous coupon date to the lesser of the next coupon date and the maturity date in the ith last quasi-coupon periodDSC = number of days from settlement to couponE = the normal length of the first quasi-coupon periodN = the number of coupons between the first coupon date and the last coupon dateNCL = the number of quasi-coupons from the last coupon date to the quasi-maturity dateNLLi = the normal length in days of the full ith quasi-coupon period in the odd last periodRV = redemption valueY = yield / frequency
The OFL formula for a bond with an odd long first coupon is:
\text{PRICE}=\frac{C*\left[\Sigma_{i=1}^{NCF}\frac{DFC_i}{NLF_i}\right]+\left(\frac{\frac{-C}{Y}+\frac{RV+C\times\Sigma_{i=1}^{NCL}\frac{DLC_i}{NLL_i}}{\left(1+Y\right)^{\Sigma_{i=1}^{NCL}\frac{DLC_i}{NLL_i}}}}{\left(1+Y\right)^N}-\frac{-C}{Y}\right)}{\left(1+Y\right)^{Nqf+\frac{DSC}{E}}}-C\times\left[\sum_{i=1}^{NCF}\frac{A_i}{NLF_i}\right]
Where
Ai = number of accrued days for the ith quasi-coupon periodC = 100 * coupon rate / frequencyDFCi = number of days from the issue date to the first quasi-coupon date or the number of days in the quasi-coupon periodDLCi = the number of days from the previous coupon date to the lesser of the next coupon date and the maturity date in the ith last quasi-coupon periodDSC = number of days from settlement date to the next quasi-coupon date or first coupon dateE = number of days in the quasi-coupon period in which settlement occursN = the number of coupons between the first coupon date and the maturity dateNCF = number of quasi-coupon periods that fit in the odd first periodNCL = the number of quasi-coupons from the last coupon date to the quasi-maturity dateNLFi = normal length in days of the full ith quasi-coupon period within the odd periodNLLi = the normal length in days of the full ith quasi-coupon period in the odd last periodNqf = the number of whole quasi-coupon periods between the settlement date and the first coupon.RV = redemption valueY = yield / frequency
The OFL function allows you to pass value for DFC1, DFC2, A1, A2, NLF1, NLF2, DLC1, DLC2, NLL1, NLL2, NCL, NCF, N, Nqf, DSC, E, and RV directly into the equation and automatically calculates Y and C. OFL does not support bonds with more than 2 quasi-coupons in either the first or last coupon periods.
Arguments
@N
the number of coupons from the first coupon date to the last coupon date. @N is an expression of type int or of a type that can be implicitly converted to int.
@RV
the bond’s redemption value per 100 face value. @RV is an expression of type float or of a type that can 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.
@Rate
the bond's annual coupon rate. @Rate is an expression of type float or of a type that can be implicitly converted to float.
@ShortFirst
a bit value which identifies the bond as having a short first coupon period ( 'True') or a long first coupon period ( 'False'). @ShortFirst is an expression of type bit or of a type that can be implicitly converted to bit.
@DLC1
the number of days from the last coupon date to the maturity date (when @ShortLast = 'True') or the number of days from the last coupon date to the quasi-coupon date (when @ShortLast = 'False'). @DLC1 is an expression of type float or of a type that can be implicitly converted to float.
@NLF2
the normal length of the second quasi-coupon period. If @ShortFirst = 'True' then @NLF2 should be NULL. @NLF2 is an expression of type float or of a type that can be implicitly converted to float.
@NLL2
the normal length of the second quasi-coupon period. If @ShortFirst = 'True' then @NLL2 should be NULL. @NLL2 is an expression of type float or of a type that can be implicitly converted to float.
@NLF1
the normal length of the first quasi-coupon period. @NLF1 is an expression of type float or of a type that can be implicitly converted to float.
@A1
the number of accrued days in the first quasi-coupon period of the odd first period. If @ShortFirst = 'True' or @ShortFirst = 'False' and @Nqf = 1 then this is the number of accrued days for the bond. If @ShortFirst = 'False' and @Nqf = 0 then this should be the same as the value entered in @DFC1. @A1 is an expression of type float or of a type that can be implicitly converted to float.
@ShortLast
a bit value which identifies the bond as having a short last coupon period ( 'True') or a long last coupon period ( 'False'). @ShortLast is an expression of type bit or of a type that can be implicitly converted to.
@DFC2
the number of days from in the second quasi-coupon period. If @ShortFirst = 'True' then @DFC2 should be NULL. @DFC2 is an expression of type float or of a type that can be implicitly converted to float.
@DLC2
the number of days from the quasi-coupon date to the maturity date. If @ShortLast = 'True' then @DLC2 should be NULL. @DLC2 is an expression of type float or of a type that can be implicitly converted to float.
@A2
the number of accrued days in second quasi-coupon period in the odd first period. If @ShortFirst = 'True' then this should be NULL. @A2 is an expression of type float or of a type that can be implicitly converted to float.
@Freq
the number of coupon payments per year. For annual payments, @Freq = 1; for semi-annual, @Freq = 2; for quarterly, @Freq = 4; for bimonthly @Freq = 6; for monthly @Freq = 12. @Freq is an expression of type float or of a type that can be implicitly converted to float.
@Price
the price of the bond. @Price is an expression of type float or of a type that can be implicitly converted to float.
@Nqf
the number of whole coupon periods between the settlement date and the first coupon date. If @ShortFirst = 'True' then @Nqf should be 0. @Nqf is an expression of type int or of a type that can be implicitly converted to int.
@DFC1
the number of days from the issue date to the quasi-coupon date (when @ShortFirst = 'False') or the number of days from the issue date to the first coupon date (when @ShortFirst = 'True'). @DFC1 is an expression of type float or of a type that can be implicitly converted to float.
@NLL1
the normal length of the first quasi-coupon period. @NLL1 is an expression of type float or of a type that can be implicitly converted to float.
@Yield
the bond’s annual yield. @Yield is an expression of type float or of a type that can be implicitly converted to float.
@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.
Return Type
float
Remarks
If @Rate is NULL then @Rate = 0.
If @RV is NULL then @RV = 100.
If @Freq is NULL then @Freq = 2.
If @E is NULL then @E = 180.
If @DSC is NULL then @DSC = 0.
If @N is NULL then @N = 0.
If @ShortFirst is NULL then @ShortFirst = 'True'.
If @ShortLast is NULL then @ShortFirst = 'True'.
If @A1 is NULL then @A1 = 0.
If @A2 is NULL then @A1 = 0.
If @DLC1 is NULL then @DLC1 = 0.
If @DLC2 is NULL then @DLC2 = 0.
If @DFC1 is NULL then @DFC1 = 0.
If @NLL1 is NULL then @NLL1 = 180.
If @NLL2 is NULL then @NLL2 = 180.
If @DFC1 is NULL then @DFC1 = 0.
If @DFC2 is NULL then @DFC2 = 0.
If @NLF1 is NULL then @NLF1 = 0.
If @NLF2 is NULL then @NLF2 = 0.
If @Nqf is NULL then @Nqf = 0.
If @Yield is NULL and @Price is NULL then NULL is returned.
If @E = 0 then NULL is returned.
If @Freq = 0 then NULL is returned.
C = 100 * @Rate/@Freq.
Y = @Yield/@Freq.
If @Yield is NOT NULL then price is calculated from the inputs otherwise yield is calculated from the inputs.
Examples
This is a bond with an odd short first period and odd short last period.
SELECT wct.OFL( .03125, --@Rate
.02875, --@Yield
NULL, --@Price
100, --@RV
2, --@Frequency
87, --@A1
NULL, --@A2
11, --@DSC
181, --@E
18, --@N
'True', --@ShortFirst
'True', --@ShortLast
44, --@DLC1
NULL, --@DLC2
184, --@NLL1
NULL, --@NLL2
98, --@DFC1
NULL, --@DFC2
NULL, --@NLF1
NULL, --@NLF2
0 --@Nqf
) as PRICE;
This produces the following result.
{"columns":[{"field":"PRICE","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"PRICE":"102.00036224598"}]}
This is a bond with an odd long first coupon and an odd long last coupon.
SELECT wct.OFL( .03125, --@Rate
.02875, --@Yield
NULL, --@Price
100, --@RV
2, --@Frequency
100, --@A1
170, --@A2
11, --@DSC
181, --@E
18, --@N
'False', --@ShortFirst
'False', --@ShortLast
184, --@DLC1
74, --@DLC2
184, --@NLL1
181, --@NLL2
100, --@DFC1
181, --@DFC2
184, --@NLF1
181, --@NLF2
0 --@Nqf
) as PRICE;
This produces the following result.
{"columns":[{"field":"PRICE","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"PRICE":"102.103433425767"}]}
This is a bond with an odd long first coupon and an odd short last coupon.
SELECT wct.OFL( .03125, --@Rate
NULL, --@Yield
101.999004756314, --@Price
100, --@RV
2, --@Frequency
100, --@A1
170, --@A2
11, --@DSC
181, --@E
18, --@N
'False', --@ShortFirst
'True', --@ShortLast
44, --@DLC1
NULL, --@DLC2
184, --@NLL1
NULL, --@NLL2
100, --@DFC1
181, --@DFC2
184, --@NLF1
181, --@NLF2
0 --@Nqf
) as YIELD;
This produces the following result.
{"columns":[{"field":"YIELD","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"YIELD":"0.0287500000072949"}]}
This is a bond with an odd short first coupon and an odd long last coupon.
SELECT wct.OFL( .03125, --@Rate
NULL, --@Yield
102.104790915433, --@Price
100, --@RV
2, --@Frequency
87, --@A1
NULL, --@A2
11, --@DSC
181, --@E
18, --@N
'True', --@ShortFirst
'False', --@ShortLast
184, --@DLC1
74, --@DLC2
184, --@NLL1
181, --@NLL2
98, --@DFC1
NULL, --@DFC2
NULL, --@NLF1
NULL, --@NLF2
0 --@Nqf
) as YIELD;
This produces the following result.
{"columns":[{"field":"YIELD","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"YIELD":"0.0287500000109004"}]}
See Also
ODDFINT - Accrued interest for a bond with an odd first coupon
OFLPRICE - Price of a security with an odd last coupon.
OFLYIELD - Yield of a bond with an odd first and an odd last coupon
OFLCONVEXITY - Convexity of a bond with an odd first and odd last coupon
OFLDURATION - Duration of a bond with an odd first and odd last coupon
OFLFACTORS - Returns the components of the OFLPRICE equation
OFLMDURATION - Modified duration of a bond with an odd first and odd last coupon
PRICE - Price of a bond paying regular periodic interest
PRICESTEP - Calculate the Price of a security with step-up rates
RPI - Calculate the price and/or yield of a bond with regular periodic coupons