Login     Register

        Contact Us     Search

XLeratorDB/financial Documentation

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

 

See Also

 



Copyright 2008-2017 Westclintech LLC         Privacy Policy        Terms of Service