OLC

Updated 2024-02-28 16:51:52.873000

Syntax

SELECT [westclintech].[wct].[OLC](
  <@Rate, float,>
 ,<@Yield, float,>
 ,<@Price, float,>
 ,<@RV, float,>
 ,<@Freq, int,>
 ,<@A, float,>
 ,<@E, float,>
 ,<@DSC, float,>
 ,<@N, int,>
 ,<@ShortLast, bit,>
 ,<@A1, float,>
 ,<@DSC1, float,>
 ,<@DLC1, float,>
 ,<@NLL1, float,>
 ,<@A2, float,>
 ,<@DSC2, float,>
 ,<@DLC2, float,>
 ,<@NLL2, float,>)

Description

Use the scalar function OLC to calculate the price or yield of a bond with an odd last period and a par value of 100. The OLC formula for a bond settling before the last coupon date is:

PRICE=\left(\frac{\frac{-C}{Y}+\frac{RV+LC}{\left(1+Y\right)^{\Sigma_{i=1}^{NCL}\frac{DLC_i}{NLL_i}}}}{\left(1+Y\right)^N}-\frac{-C}{Y}\right)\times\left(1+Y\right)^{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 last coupon dateE = the number of days in the current coupon periodA = C * accrued days / ENCL = the number of quasi-coupons from the last coupon date to the quasi-maturity 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 quasi-coupon periodNLLi = the normal length in days of the full ith quasi-coupon period in the odd last periodLC = C *

The OLC formula for a bond settling on or after the last coupon date is:

ODDLPRICE=\frac{RV+LC}{\left(1+Y\times{\Sigma_{i=1}^{NCL}\frac{DLC_i}{NLL_i}}\right)}-C*\sum_{i=1}^{NCL}\frac{A_i}{NLL_i}

WhereC = 100 * coupon rate / frequencyY = yield / frequencyRV = redemption valueNCL = the number of quasi-coupons from the last coupon date to the quasi-maturity dateDSCi = number of days from settlement date (or beginning of quasi-coupon period) to the next quasi-coupon within odd period (or to redemption date) for the ith quasi-coupon periodAi = number of accrued days for the ith quasi-coupon period within odd period counting forward from the last interest date before redemptionDLCi = the number of days from the previous coupon date to the lesser of the next coupon date and the maturity date in the ith quasi-coupon periodNLLi = the normal length in days of the full ith quasi-coupon period in the odd last periodLC = C *

The OLC function allows you to pass value for A, DSC, E, A1, A2, DLC1, DLC2, DSC1, DSC2, NLL1, NLL2, NCL, N, and RV directly into the equation and automatically calculates Y and C. OLC does not support bonds with more than 2 quasi-coupon periods.

Arguments

@N

the number of coupons from the settlement date to the last coupon date. If the settlement occurs on or after the last coupon date, the @N = 0. @N is an expression of type int or of a type that can be implicitly converted to int.

@A

the number of accrued days in the settlement period if the settlement is prior to the last coupon date. @A 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. @A1 is an expression of type float or of a type that can be implicitly converted to float.

@RV

the security’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.

@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.

@A2

the number of accrued days in the second quasi-coupon period. If @ShortFirst = 'True' then @A2 should be NULL*.@A2* 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 if the settlement is prior to the last coupon date. @E is an expression of type float or of a type that can be implicitly converted to float.

@DSC2

the number of days from the greater of the quasi-coupon date and settlement date to the maturity date. If @ShortFirst = 'True' then @DSC2 should be NULL. @DSC2 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.

@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.

@Yield

the security’s annual yield. @Yield 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.

@DSC1

the number of days from the settlement date to the maturity date (when @ShortFirst = 'True') when the settlement occurs in the last coupon period, or the number of days from the settlement date to the quasi-coupon date (when @ShortFirst = 'False'). If the settlement date is greater than or equal to the quasi-coupon date then @DSC1 should be zero. @DSC1 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 bit.

@DLC1

the number of days from the last coupon date to the maturity date (when @ShortFirst = 'True') or the number of days from the last coupon date to the quasi-coupon date (when @ShortFirst = 'False'). @DLC1 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 the settlement is prior to the last coupon date. @DSC 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 @ShortFirst = 'True' then @DLC2 should be NULL. @DLC2 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.

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 @A is NULL then @A = 0.

If @E is NULL then @E = 180.

If @DSC is NULL then @DSC = 0.

If @N is NULL then @N = 0.

If @ShortLast is NULL then @ShortLast = 'True'.

If @A1 is NULL then @A1 = 0.

If @DFC1 is NULL then @DFC1 = 0.

If @DLC1 is NULL then @DLC1 = 0.

If @NLL1 is NULL then @NLL1 = 180.

If @A2 is NULL then @A2 = 0.

If @DLC2 is NULL then @DLC2 = 0.

If @DSC2 is NULL then @DSC2 = 0.

If @NLL2 is NULL then @NLL2 = 0.

If @Yield is NULL and @Price is NULL 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 last coupon period where the settlement date in the last coupon period.

SELECT wct.OLC(   0.0225, --@Rate

                  0.001,  --@Yield

                  NULL,   --@Price

                  100,    --@RV

                  2,      --@Freq

                  NULL,   --@A

                  NULL,   --@E

                  NULL,   --@DSC

                  0,      --@N

                  'True', --@ShortLast

                  16,     --@A1

                  75,     --@DSC1

                  91,     --@DLC1

                  181,    --@NLL1

                  NULL,   --@A2

                  NULL,   --@DSC2

                  NULL,   --@DLC2

                  NULL    --@NLL2

              ) as Price;

This produces the following result.

{"columns":[{"field":"Price","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"Price":"100.445329120863"}]}

This bond has odd long last coupon with a settlement date in the last period.

SELECT wct.OLC(   0.0225,  --@Rate

                  0.001,   --@Yield

                  NULL,    --@Price

                  100,     --@RV

                  2,       --@Freq

                  NULL,    --@A

                  NULL,    --@E

                  NULL,    --@DSC

                  0,       --@N

                  'False', --@ShortLast

                  184,     --@A1

                  0,       --@DSC1

                  184,     --@DLC1

                  184,     --@NLL1

                  16,      --@A2

                  75,      --@DSC2

                  91,      --@DLC2

                  181      --@NLL2

              ) as Price;

This produces the following result.

{"columns":[{"field":"Price","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"Price":"100.445096089033"}]}

This is a bond with odd short last coupon with a settlement date prior to the last coupon date.

SELECT wct.OLC(   0.0425, --@Rate

                  0.0400, --@Yield

                  NULL,   --@Price

                  100,    --@RV

                  2,      --@Freq

                  16,     --@A

                  181,    --@E

                  165,    --@DSC

                  40,     --@N

                  'True', --@ShortLast

                  NULL,   --@A1

                  NULL,   --@DSC1

                  91,     --@DLC1

                  181,    --@NLL1

                  NULL,   --@A2

                  NULL,   --@DSC2

                  NULL,   --@DLC2

                  NULL    --@NLL2

              ) as Price;

This produces the following result.

{"columns":[{"field":"Price","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"Price":"103.443237928673"}]}

This is a bond with an odd long last coupon period with a settlement date prior to the last coupon date.

SELECT wct.OLC(   0.0425,         --@Rate

                  NULL,           --@Yield

                  103.4336871715, --@Price

                  100,            --@RV

                  2,              --@Freq

                  16,             --@A

                  181,            --@E

                  165,            --@DSC

                  39,             --@N

                  'False',        --@ShortLast

                  NULL,           --@A1

                  NULL,           --@DSC1

                  184,            --@DLC1

                  184,            --@NLL1

                  NULL,           --@A2

                  NULL,           --@DSC2

                  91,             --@DLC2

                  181             --@NLL2

              ) as Yield;

This produces the following result.

{"columns":[{"field":"Yield","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"Yield":"0.0399999999998383"}]}

OLC

Syntax

Description

Arguments

@N

@A

@A1

@RV

@Freq

@A2

@E

@DSC2

@NLL1

@Rate

@Yield

@NLL2

@DSC1

@ShortLast

@DLC1

@DSC

@DLC2

@Price

Return Type

Remarks

Examples

See Also