# The Excel ODDFPRICE Function

Related Functions:
ODDLPRICE
ODDFYIELD

## Function Description

The Excel Oddfprice function calculates the price per \$100 face value of a security with an odd (short or long) first period.

The syntax of the function is:

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

Where the arguments are as follows:

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 security's annual yield.
redemption-The security's redemption value per \$100 face value.
frequency-

The number of coupon payments per year. This must be one of the following:

 1 - Annually 2 - Semi-Annually 4 - Quarterly
[basis]-

An optional integer argument which specifies the financial day count basis that is used by the security. Possible values are:

BasisDay Count Basis
0 (or omitted)US (NASD) 30/360
1actual/actual
2actual/360
3actual/365
4European 30/360
The financial day count basis rules are explained in detail on the Wikipedia Day Count Convention page

Note that the date arguments must satisfy the following:

issue   <   settlement   <   first_coupon   <   maturity

Note also, that the settlement, maturity, issue and first_coupon arguments should be entered into the Oddfprice function as either:

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

Warning: If you input text representations of dates into Excel functions, the interpretion of these can differ, depending to the date system and date interpretation settings on your computer.

## Excel Oddfprice Function Example

In the following spreadsheet, the Excel Oddfprice function is used to calculate the price per \$100 face value of a security with issue date 01-Dec-2016, settlement date 01-Feb-2017, first coupon date 31-Mar-2017 and the maturity date 31-Mar-2021. The rate of interest is 5.5%, the annual yield is 3.5% and the redemption value is \$100. Payments are made quarterly and the US (NASD) 30/360 day count basis is used:

AB
1Settlement Date:01-Feb-2017
2Maturity Date:31-Mar-2021
3Issue Date:01-Dec-2016
4First Coupon Date:31-Mar-2017
5=ODDFPRICE( B1, B2, B3, B4, 5.5%, 3.5%, 100, 4 )

The above function returns the value 106.771695.

I.e. a security with the above terms would be valued at \$106.77.

Note that, in the above example:

• As recommended, the date arguments have been supplied to the Oddfprice function as references to cells containing dates;
• The rate and yld arguments are input as percentages 5.5% and 3.5%. However, these arguments could, instead, be entered as the simple numeric values 0.055 and 0.035;
• As [basis] argument has been omitted, the function uses the default value 0 (denoting the US (NADS) 30/360 day count basis).

For further examples of the Excel Oddfprice function, see the Microsoft Office website.

## Oddfprice Function Errors

If you get an error from the Excel Oddfprice function, this is likely to be one of the following:

Common Errors
 #NUM! - Occurs if either:The supplied issue date ≥ settlement date;The supplied settlement date ≥ first_coupon date;The supplied first_coupon date ≥ maturity date;Invalid numbers are supplied for the rate, yld, redemption, frequency or [basis] arguments.(I.e. if either: rate < 0; yld < 0; redemption ≤ 0; frequency is any number other than 1, 2 or 4; or [basis] is any number other than 0, 1, 2, 3 or 4). #VALUE! - Occurs if either:The supplied settlement, maturity, issue or first_coupon arguments are not a valid Excel dates;Any of the supplied arguments are non-numeric.