While most coupon-paying bonds follow regular periodic cash flow schedules throughout their lifetime, debt issuers occasionally structure debt instruments with an irregular final coupon period. This scenario arises when a bond’s maturity date does not align with its established coupon cycle, resulting in either a short last coupon or an extended long last coupon. In such situations, standard pricing functions like PRICE or YIELD yield inaccurate mathematical results.
To accurately model these non-standard fixed-income securities, Microsoft Excel provides two specialised functions: ODDLPRICE (which computes the clean price per £100/€100 par value) and ODDLYIELD (which computes the true annualised yield to maturity). This tutorial explains the syntax, date logic, and financial parameters required to evaluate odd last coupon bonds in Excel.
Syntax and Parameters of ODDLPRICE and ODDLYIELD
Both functions require historical and contractual calendar dates, coupon interest rates, and frequency inputs to adjust day counts for the abnormal terminal period.
ODDLPRICE Formula Structure
=ODDLPRICE(settlement, maturity, last_interest, rate, yld, redemption, frequency, [basis])ODDLYIELD Formula Structure
=ODDLYIELD(settlement, maturity, last_interest, rate, pr, redemption, frequency, [basis])| Argument | Data Type | Definition and Parameter Rules |
|---|---|---|
| settlement | Date | The trade settlement date (when the buyer takes ownership of the bond). |
| maturity | Date | The security’s ultimate maturity date when the par value is redeemed. |
| last_interest | Date | The last coupon payment date prior to the odd final maturity period. |
| rate | Decimal / Percentage | The annual contractual coupon rate (e.g. 0.0475 or 4.75%). |
| yld / pr | Decimal / Numeric | The required annual yield (for ODDLPRICE) or clean market price per 100 face value (for ODDLYIELD). |
| redemption | Numeric | The redemption value per 100 face value (typically 100). |
| frequency | Integer | Number of coupon payments per year: 1 (Annual), 2 (Semi-annual), or 4 (Quarterly). |
| basis (optional) | Integer (0–4) | Day-count system: 0 (US 30/360), 1 (Actual/actual), 2 (Actual/360), 3 (Actual/365), 4 (European 30/360). |
Step-by-Step Practical Example: Pricing an Odd Last Coupon Bond
Imagine evaluating a gilt or corporate bond with regular semi-annual coupon payments whose penultimate coupon was distributed on 15 October 2025. Rather than maturing exactly six months later on 15 April 2026, the bond is scheduled to mature on 15 July 2026 (creating an irregular 9-month odd last coupon period). The coupon rate is 5.00%, market yield expectation is 5.50%, and the settlement date is 15 January 2026.
Set up the following values in your Excel worksheet:
B1(Settlement Date):=DATE(2026, 1, 15)B2(Maturity Date):=DATE(2026, 7, 15)B3(Last Interest Date):=DATE(2025, 10, 15)B4(Coupon Rate):0.05B5(Market Yield):0.055B6(Redemption Value):100B7(Frequency):2B8(Basis):1(Actual/actual)
To determine the fair clean price per £100 par value, enter the following formula in cell B10:
=ODDLPRICE(B1, B2, B3, B4, B5, B6, B7, B8)Excel accounts for the extended final accrual period and computes a clean price of 99.76. Because the required yield (5.50%) is higher than the annual coupon rate (5.00%), the security trades at an anticipated modest discount below par.
Odd First vs Odd Last: Choosing the Right Tool
If an instrument has an irregular initial coupon at issuance but regular coupons until maturity, use ODDFPRICE / ODDFYIELD. If the irregularity occurs solely in the final period leading to redemption, use ODDLPRICE / ODDLYIELD. Applying the wrong function leads to severe miscalculations in accrued interest.
Calculating Annualised Yield with ODDLYIELD
If the bond is actively traded on the secondary market and you want to calculate its yield to maturity based on its market quote, use ODDLYIELD.
Suppose the bond described above is currently offered at a clean market price of 100.25 (entered in cell B9). Calculate the internal yield by entering this formula in cell B11:
=ODDLYIELD(B1, B2, B3, B4, B9, B6, B7, B8)Format cell B11 as a percentage with two decimal places. Excel evaluates the net cash flows across the remaining 6 months of ownership and outputs 4.51%.
Troubleshooting ODDLPRICE and ODDLYIELD Errors
When implementing odd coupon formulas, Excel throws specific error codes if chronological or numerical constraints are breached:
- #NUM! Error: Occurs if dates violate sequential order. The chronology must strictly satisfy:
maturity > settlement > last_interest. This error also occurs ifrate < 0,yield < 0,redemption <= 0, or iffrequencyis set to any number other than 1, 2, or 4. - #VALUE! Error: Triggered when any date argument references non-numeric text strings or invalid calendar dates (such as 31 February). Always generate dates via
DATE(year, month, day). - Accrued Interest Confusion: Remember that both
ODDLPRICEandODDFPRICEreturn the clean price of the bond. To determine the total settlement cash outlay (the dirty price), you must add the accrued interest accumulated since the last coupon date.