The ODDFPRICE function calculates the price, per $100 face value of a security with an odd, short or long, first period.

Syntax

=ODDFPRICE(settlement,maturity,issue,first_coupon,rate,old,redemption, frequency,[basis])

Arguments

Argument Description
settlement The settlement date of the security, i.e. the date that the coupon is purchased
maturity The maturity date of the security, i.e. the date that the coupon expires
issue The issue date of the security
first_coupon The date of the security’s first coupon
rate The security’s interest rate
yld The annual yield of the security
redemption Redemption value per $100 face value
frequency Number of coupon payments per year. This must be one of the following:

  1 Annually
  2 Semi-Annually
  3 Quarterly
[basis] Optional. Defines the day count basis to be used in the calculation

  [basis] Day Count Basis
  0 (or omitted) US (NASD) 30/360
  1 actual/actual
  2 actual/360
  3 actual/365
  4 European 30/360

The financial day count basis rules are explained in detail on the Wikipedia Day Count Convention page

Note: The date arguments must satisfy the following:

issue < settlement < first_coupon < maturity

Note: The settlementmaturity, issue, and first_coupon dates should be input as either:

  • References to cells containing dates, or
  • Dates returned from formulas

If you attempt to input these date arguments as text, Excel may misinterpret them, due to different date systems, or date interpretation settings.

Warning: Although you can input the date arguments as date serial numbers, this is not recommended as date serial numbering does vary across different computer systems.

Examples

  A B C D
1 Data      
2 01-Feb-11 Settlement date    
3 31-Mar-15 Maturity date    
4 01-Dec-10 Issue date    
5 31-Mar-11 first_coupon date    
6 5.5% Interest rate    
7 3.5% Annual yield    
8 $100.00 Redemption value    
9 4 Frequency (quarterly)    
10        
11 Formula Result Notes
12 =ODDFPRICE(A2,A3,A4,A5,A6,A7,A8,A9) $106.77 The price per $100 face value of a security having an odd (short or long) first period, for the bond using the terms in cells A2:A9 as arguments for the function, [basis] was omitted and therefore defaults to 0

Common Function Error(s)

Problem What went wrong
#VALUE! Occurs if either:

  any of the supplied arguments are non-numeric
  the supplied settlement, maturity, issue or first_coupon arguments are not a valid date
#NUM! Occurs if:

  the issue date is ≥ settlement date
  the settlement date is ≥ first_coupon date
  the first_coupon date is ≥ maturity date
  invalid numbers are supplied for the rate, yld, redemption, frequency or [basis] arguments
#NAME? Occurs when Analysis ToolPak add-in is not enabled