When debt securities—such as corporate notes, infrastructure bonds, or municipal obligations—are newly issued, the duration between the initial settlement date and the first formal coupon payment date rarely matches a standard regular coupon interval. A bond paying semi-annual coupons might feature a short first coupon (e.g. only 3 months elapsed before the first payment) or a long first coupon (e.g. 8 months before the first payment). In bond markets, these are officially classified as odd first period securities.
Because standard pricing functions like PRICE and YIELD strictly assume perfectly uniform, identical coupon spacing across the entire bond lifecycle, applying them to newly issued odd-period bonds results in severe pricing distortions. To handle these non-standard cash flows accurately, Google Sheets provides the specialized ODDFPRICE and ODDFYIELD functions. This guide explains their mathematical architecture, parameter requirements, and financial modeling applications.
Understanding Odd First Period Cash Flows
Fixed-income instruments with irregular initial periods introduce two distinct temporal phases:
- The Odd First Interval: The period spanning between the initial issue date, the secondary market settlement date, and the first coupon date. Accrued interest and discount factors during this window are weighted differently based on the chosen day-count convention.
- The Regular Amortisation Horizon: All subsequent coupon dates following the first coupon date until final contractual maturity, which revert to regular equal periodic intervals.
Syntax and Parameter Breakdown
1. The ODDFPRICE Function (Solving Purchase Price)
=ODDFPRICE(settlement, maturity, issue, first_coupon, rate, yield, redemption, frequency, [day_count_convention])2. The ODDFYIELD Function (Solving Annual Yield)
=ODDFYIELD(settlement, maturity, issue, first_coupon, rate, price, redemption, frequency, [day_count_convention])Parameter Reference
- settlement: The transaction settlement date (e.g.
DATE(2026, 2, 15)). - maturity: Contractual maturity redemption date.
- issue: The original security issuance date when interest initially began accruing.
- first_coupon: The contractual date when the initial (irregular) coupon is disbursed.
- rate: Annualized coupon rate (decimal or percentage, e.g.
5.5%). - yield (ODDFPRICE): Target annual yield to maturity.
- price (ODDFYIELD): Purchase price per $100 face value.
- redemption: Redemption value per $100 face value (typically
100). - frequency: Annual coupon frequency (
1,2, or4). - day_count_convention (optional):
0(US 30/360),1(Actual/Actual),2(Actual/360),3(Actual/365).
Step-by-Step Practical Implementation
Consider an irregular corporate bond issued on 1 January 2026, where the first coupon does not pay until 30 September 2026 (a 9-month long first coupon for a semi-annual bond):
Configure your spreadsheet cells:
A1: Settlement Date | B1: =DATE(2026, 3, 1)
A2: Maturity Date | B2: =DATE(2036, 12, 31)
A3: Issue Date | B3: =DATE(2026, 1, 1)
A4: First Coupon Date | B4: =DATE(2026, 9, 30)
A5: Annual Coupon Rate | B5: 6.00%
A6: Market Yield Target | B6: 5.75%
A7: Redemption Value | B7: 100
A8: Frequency | B8: 2
A9: Basis | B9: 0
A10: Odd First Price | B10: =ODDFPRICE(B1, B2, B3, B4, B5, B6, B7, B8, B9)Google Sheets calculates the compound present value across both the 9-month irregular phase and remaining 10-year regular phase, returning a clean institutional price of $101.88 per $100 par value.
Comparison: Standard PRICE vs ODDFPRICE
| Feature | Standard PRICE Function | ODDFPRICE Function |
|---|---|---|
| First Period Structure | Strictly uniform equal intervals | Supports both short and long irregular first coupons |
| Requires Issue Date | No | Yes (Mandatory parameter) |
| Requires First Coupon Date | No | Yes (Mandatory parameter) |
| Ideal Use Case | Seasoned secondary market bonds | Newly issued primary market debt offerings |