How to use ODDFPRICE Function in Excel

The Excel ODDFPRICE function returns the price per $100 face value of a security with an odd (irregular) first period.

Syntax: 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

  

Example: Let’s look at some Excel ODDFPRICE function examples and explore how to use the ODDFPRICE function as a worksheet function in Microsoft Excel:

ODDFPRICE Function - How to use ODDFPRICE Function in Excel

Syntax:  = ODDFPRICE (B1, B2, B3, B4, B5, B6, B7, B8, B9)

Result:

ODDFPRICE Function in Excel 1 - How to use ODDFPRICE Function in Excel

Based on the Excel spreadsheet above, the following ODDFPRICE examples would return:

Syntax= ODDFPRICE (B1, B2, B3, B4, B5, B6, B7, B8, B9)
Result: 113.4828989

Syntax= ODDFPRICE (DATE (2008,11,11), DATE (2021,1,3), DATE (2008,10,15), DATE (2009,1,3), 0,0785,0,0625,2000000,2,1 )
Result: 113.4828989

Syntax: = ODDFPRICE (DATE (2008,11,11), DATE (2021,1,3), DATE (2008,10,15), DATE (2009,1,3), 7,85%, 6,25%, B7, B8 , B9)
Result113.4828989

Note:

  1. #NUM! error – Occurs when:
    • The given issue date is greater than or equal to the settlement date.
    • Given settlement date is greater than or equal to the first coupon date.
    • The first coupon date given is greater than or equal to the maturity date.
    • We have provided invalid numbers for the rate, yield, redemption, frequency or [basis] arguments. That is, if either rate is less than 0; yld is less than 0; redemption is less than or equal to 0; frequency is any number other than 1, 2, or 4; or [basis] is any number other than 0, 1, 2, 3, or 4).
  2. #VALUE! error – Occurs when:
    • The given settlement, maturity, issue or first_coupon arguments are not valid Excel dates.
    • Any of the given arguments is non-numeric.

Add a Comment

Your email address will not be published. Required fields are marked *