Practice free

ODDFYIELD function

ODDFYIELD returns the yield of a bond whose first coupon period is irregular, working backwards from its price. It is the inverse of ODDFPRICE, and it is solved by iteration.

Example

Read it as a sentence: find the yield of a bond settling November 11, 2008 and maturing March 1, 2021, priced at 84.5 with a 5.75% coupon, then round to 4 decimal places, which gives 0.0772.

Practice this in the app

Syntax

ODDFYIELD(settlement, maturity, issue, first_coupon, rate, pr, 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.
pr (required)
Price per 100 face value.
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: Yield 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
=ODDFYIELD(DATE(2024,11,11),DATE(2036,3,1),DATE(2024,10,15),DATE(2025,3,1),0.0575,84.5,100,2,1) 0.078431871 Typical
=ODDFYIELD(DATE(2024,11,11),DATE(2036,3,1),DATE(2024,10,15),DATE(2025,3,1),0.0575,84.5,100,2) 0.078430017 Basis omitted
=ODDFYIELD(DATE(2024,11,11),DATE(2036,3,1),DATE(2024,10,15),DATE(2025,3,1),0.0575,84.5,100,1,1) 0.078761558 Frequency 1
=ODDFYIELD(DATE(2024,11,11),DATE(2036,3,1),DATE(2024,10,15),DATE(2025,3,1),0.0575,84.5,100,4,1) 0.07826662 Frequency 4
=ODDFYIELD(DATE(2024,11,11),DATE(2036,3,1),DATE(2024,10,15),DATE(2025,3,1),0.0575,84.5,100,2,0) 0.078430017 Basis 0
=ODDFYIELD(DATE(2024,11,11),DATE(2036,3,1),DATE(2024,10,15),DATE(2025,3,1),0.0575,84.5,100,2,4) 0.078430017 Basis 4
=ODDFYIELD(DATE(2024,11,11),DATE(2036,3,1),DATE(2024,10,15),DATE(2025,3,1),0.0575,84.5,100,3,1) #NUM! Frequency 3
=ODDFYIELD(DATE(2024,11,11),DATE(2036,3,1),DATE(2024,10,15),DATE(2025,3,1),0.0575,84.5,100,2,5) #NUM! Basis 5

Watch out for

  • Solved by iteration; a failure to converge returns #NUM!.

Where it works

Excel
ODDFYIELD is in every version of Excel from 2007 on.
Google Sheets
No direct equivalent. No ODDFYIELD in Google Sheets.

Part of Financial functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .