ODDFPRICE function
ODDFPRICE returns the price per 100 of face value for a bond whose first coupon period is shorter or longer than normal. Bond desks use it for new issues that start mid-cycle.
Example
Read it as a sentence: price a bond settling November 11, 2008 and maturing March 1, 2021, with a 7.85% coupon and 6.25% yield, then round to 4 decimal places, which gives 113.5977.
Syntax
ODDFPRICE(settlement, maturity, issue, first_coupon, rate, yld, redemption, frequency, [basis])
Arguments in [square brackets] are optional.
-
settlement(required) - Date the security is bought (after issue, before maturity).
-
maturity(required) - Date the security expires and is repaid.
-
issue(required) - Issue date of the security.
-
first_coupon(required) - Date of the first (irregular) coupon.
-
rate(required) - Annual coupon rate.
-
yld(required) - Annual yield.
-
redemption(required) - Redemption value per 100 face value.
-
frequency(required) - Coupon payments per year: 1 annual, 2 semiannual, 4 quarterly.
-
basis(optional) - Day-count convention: 0 or omitted = US 30/360, 1 = actual/actual, 2 = actual/360, 3 = actual/365, 4 = European 30/360.
Returns: Price per 100 face value of a bond with an odd first period.
What Excel actually returns
Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.
| Formula | Excel shows | Case |
|---|---|---|
=ODDFPRICE(DATE(2024,11,11),DATE(2036,3,1),DATE(2024,10,15),DATE(2025,3,1),0.0785,0.0625,100,2,1) | 112.8356445 | Typical |
=ODDFPRICE(DATE(2024,11,11),DATE(2036,3,1),DATE(2024,10,15),DATE(2025,3,1),0.0785,0.0625,100,2) | 112.8372121 | Basis omitted |
=ODDFPRICE(DATE(2024,11,11),DATE(2036,3,1),DATE(2024,10,15),DATE(2025,3,1),0.0785,0.0625,100,1,1) | 112.7355922 | Frequency 1 |
=ODDFPRICE(DATE(2024,11,11),DATE(2036,3,1),DATE(2024,10,15),DATE(2025,3,1),0.0785,0.0625,100,4,1) | 112.886415 | Frequency 4 |
=ODDFPRICE(DATE(2024,11,11),DATE(2036,3,1),DATE(2024,10,15),DATE(2025,3,1),0.0785,0.0625,100,2,0) | 112.8372121 | Basis 0 |
=ODDFPRICE(DATE(2024,11,11),DATE(2036,3,1),DATE(2024,10,15),DATE(2025,3,1),0.0785,0.0625,100,2,4) | 112.8372121 | Basis 4 |
=ODDFPRICE(DATE(2024,11,11),DATE(2036,3,1),DATE(2024,10,15),DATE(2025,3,1),0.0785,0.0625,100,3,1) | #NUM! | Frequency 3 |
=ODDFPRICE(DATE(2024,11,11),DATE(2036,3,1),DATE(2024,10,15),DATE(2025,3,1),0.0785,0.0625,100,2,5) | #NUM! | Basis 5 |
Watch out for
- Dates must run in order: issue, settlement, first coupon, maturity.
- Pass dates with DATE() or cell references; text dates can misbehave and invalid dates return #VALUE!.
Where it works
- Excel
- ODDFPRICE is in every version of Excel from 2007 on.
- Google Sheets
- No direct equivalent. No ODDFPRICE in Google Sheets.
Part of Financial functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .