How to Use the ODDFPRICE and ODDFYIELD Functions in Microsoft Excel for Odd First Coupon Bonds

In standard fixed-income valuation models, bond coupons are assumed to follow regular, equidistant schedules (such as exactly every six months or once per year). However, real-world corporate and sovereign bonds are frequently issued mid-cycle, creating an initial coupon period that is either unusually short (a short first coupon) or unusually long (a long first coupon). Standard valuation functions like PRICE or YIELD produce erroneous figures under these circumstances.

To evaluate these instruments accurately, Microsoft Excel provides two dedicated financial functions: ODDFPRICE (calculates clean price per £100/€100 face value) and ODDFYIELD (calculates annualised yield to maturity). This tutorial explains how to formulate and interpret both functions for fixed-income analysis.

Syntax and Parameters of ODDFPRICE and ODDFYIELD

Both functions require precise calendar dates, coupon interest rates, and frequency specifications to calculate day-count accruals correctly.

ODDFPRICE Formula Structure

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

ODDFYIELD Formula Structure

=ODDFYIELD(settlement, maturity, issue, first_coupon, rate, pr, redemption, frequency, [basis])

Argument	Data Type	Definition and Constraints
settlement	Date	The security’s settlement date (trade date plus standard clearing cycle, e.g. T+2).
maturity	Date	The security’s expiration or maturity date when principal is repaid.
issue	Date	The original issue date of the security.
first_coupon	Date	The scheduled date of the security’s first coupon payment.
rate	Percentage / Decimal	The annual nominal coupon rate (e.g. 5.25% or 0.0525).
yld / pr	Percentage / Decimal	Expected annual yield (for ODDFPRICE) or clean market price per 100 face value (for ODDFYIELD).
redemption	Numeric	Redemption value per 100 face value (typically 100 for par bonds).
frequency	Integer	Annual coupon distributions: 1 (Annual), 2 (Semi-annual), or 4 (Quarterly).
basis (optional)	Integer (0 to 4)	Day count convention: 0 (US 30/360), 1 (Actual/actual), 2 (Actual/360), 3 (Actual/365), 4 (European 30/360).

Step-by-Step Example: Pricing an Odd First Coupon Bond

Consider a corporate bond issued on 15 January 2026 with an odd first coupon payable on 15 September 2026 (an 8-month long first coupon period instead of the standard 6 months). The bond matures on 15 September 2035 with semi-annual coupon distributions at a 6.00% annual coupon rate. The settlement date is 15 February 2026, and the market requires a yield of 6.50%.

Set up the ledger in Excel with the following cells:

• B1 (Settlement): =DATE(2026, 2, 15)
• B2 (Maturity): =DATE(2035, 9, 15)
• B3 (Issue): =DATE(2026, 1, 15)
• B4 (First Coupon): =DATE(2026, 9, 15)
• B5 (Coupon Rate): 0.06
• B6 (Required Yield): 0.065
• B7 (Redemption): 100
• B8 (Frequency): 2
• B9 (Basis): 1 (Actual/actual)

To calculate the fair clean price per £100 par value, enter this formula in cell B11:

=ODDFPRICE(B1, B2, B3, B4, B5, B6, B7, B8, B9)

Excel outputs 96.48. Because the required market yield (6.50%) exceeds the stated coupon rate (6.00%), the bond trades at an expected discount beneath par.

Calculating Annualised Yield with ODDFYIELD

If you know the bond’s current trading clean price on secondary markets and want to discover the exact annualised yield to maturity, deploy ODDFYIELD.

Using the parameters above, suppose the bond is trading at a clean price of 97.50 (entered in cell B10). Enter the following formula:

=ODDFYIELD(B1, B2, B3, B4, B5, B10, B7, B8, B9)

Format the output cell as a percentage with two decimal places. Excel evaluates the compound yield across the irregular initial window and returns 6.35%.

Troubleshooting Common Errors in ODDFPRICE and ODDFYIELD

When working with complex debt schedules, Excel returns specific formula errors if parameter sequences violate calendar logic:

• #NUM! Error: Occurs when chronological order is violated. The dates must strictly satisfy: maturity > first_coupon > settlement > issue. Furthermore, ensure rate < 0, yield < 0, or invalid numbers for frequency (anything other than 1, 2, or 4) are avoided.
• #VALUE! Error: Triggered when one or more date cells contain invalid non-numeric text strings, or if dates fall before the system calendar baseline (1 January 1900).
• Odd Last Coupon Instruments: If the bond’s irregular payment occurs at the end of the bond’s lifecycle rather than the beginning, do not use ODDF functions. Instead, implement ODDLPRICE and ODDLYIELD.