 # SQL Server pricing function for odd-last coupon bonds

OLC

Updated: 30 June 2014

Use 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: Where

 C = 100 * coupon rate / frequency Y = yield / frequency RV = redemption value DSC = number of days from settlement to coupon N = the number of coupons between the settlement date and the last coupon date E = the number of days in the current coupon period A = C * accrued days / E NCL = the number of quasi-coupons from the last coupon date to the quasi-maturity date DLCi = 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 period NLLi = the normal length in days of the full ith quasi-coupon period in the odd last period LC = C *

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

 C = 100 * coupon rate / frequency Y = yield / frequency RV = redemption value NCL = the number of quasi-coupons from the last coupon date to the quasi-maturity date DSCi = 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 period Ai = number of accrued days for the ith quasi-coupon period within odd period counting forward from the last interest date before redemption DLCi = 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 period NLLi = the normal length in days of the full ith quasi-coupon period in the odd last period LC = 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.
Syntax
SELECT [wctFinancial].[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,>)
Arguments
@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.
@Price
the price of the bond. @Price 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.
@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.
@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.
@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.
@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.
@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.
@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.
@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.
@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.
@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.
@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.
@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.
@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.
@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.
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.
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.
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.
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.
Yield
----------------------
0.0399999999998383

### Support  Copyright 2008-2023 Westclintech LLC         Privacy Policy        Terms of Service