Using the ODDFPRICE Function in Excel

 

 

Now we are going to learn all about formulas and functions. This is the best way to learn them:

ODDFPRICE

Returns the price per $100 face value of a security having an odd (short or long) first period.

 

ODDFPRICE(settlement; maturity; issue; first_coupon; rate; yld; redemption; frequency; [basis])

 

The ODDFPRICE function syntax has the following arguments:

  • Settlement Required. The security’s settlement date. The security settlement date is the date after the issue date when the security is traded to the buyer.
  • Maturity Required. The security’s maturity date. The maturity date is the date when the security expires.
  • Issue Required. The security’s issue date.
  • First_coupon Required. The security’s first coupon date.
  • Rate Required. The security’s interest rate.
  • Yld Required. The security’s annual yield.
  • Redemption Required. The security’s redemption value per $100 face value.
  • Frequency Required. The number of coupon payments per year. For annual payments; frequency = 1; for semiannual; frequency = 2; for quarterly; frequency = 4.
  • Basis Optional. The type of day count basis to use.
    BASIS DAY COUNT BASIS
    0 or omitted US (NASD) 30/360
    1 Actual/actual
    2 Actual/360
    3 Actual/365
    4 European 30/360

 

Remarks

 

  • Microsoft Excel stores dates as sequential serial numbers so they can be used in calculations. By default; January 1; 1900 is serial number 1; and January 1; 2008 is serial number 39448 because it is 39;448 days after January 1; 1900.
  • The settlement date is the date a buyer purchases a coupon; such as a bond. The maturity date is the date when a coupon expires. For example; suppose a 30-year bond is issued on January 1; 2008; and is purchased by a buyer six months later. The issue date would be January 1; 2008; the settlement date would be July 1; 2008; and the maturity date would be January 1; 2038; which is 30 years after the January 1; 2008; issue date.
  • Settlement; maturity; issue; first_coupon; and basis are truncated to integers.
  • If settlement; maturity; issue; or first_coupon is not a valid date; ODDFPRICE returns the #VALUE! error value.
  • If rate < 0 or if yld < 0; ODDFPRICE returns the #NUM! error value.
  • If basis < 0 or if basis > 4; ODDFPRICE returns the #NUM! error value.
  • The following date condition must be satisfied; otherwise; ODDFPRICE returns the #NUM! error value:maturity > first_coupon > settlement > issue
  • ODDFPRICE is calculated as follows:Odd short first coupon:

    where:

      • A = number of days from the beginning of the coupon period to the settlement date (accrued days).
      • DSC = number of days from the settlement to the next coupon date.
      • DFC = number of days from the beginning of the odd first coupon to the first coupon date.
      • E = number of days in the coupon period.
      • N = number of coupons payable between the settlement date and the redemption date. (If this number contains a fraction; it is raised to the next whole number.)

    Odd long first coupon:

    where:

    • Ai = number of days from the beginning of the ith; or last; quasi-coupon period within odd period.
    • DCi = number of days from dated date (or issue date) to first quasi-coupon (i = 1) or number of days in quasi-coupon (i = 2;…; i = NC).
    • DSC = number of days from settlement to next coupon date.
    • E = number of days in the coupon period.
    • N = number of coupons payable between the settlement date and the redemption date. (If this number contains a fraction; it is raised to the next whole number.)
    • NC = number of quasi-coupon periods that fit in odd period. (If this number contains a fraction; it is raised to the next whole number.)
    • NLi = normal length in days of the full ith; or last; quasi-coupon period within odd period.
    • Nq = number of whole quasi-coupon periods between settlement date and first coupon.

 

Interactive Example

 

 

 

One thought on “Using the ODDFPRICE Function in Excel

Comments are closed.