Practice free

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.

Practice this in the app

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.

FormulaExcel showsCase
=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 .