While most debt securities feature regular, synchronized coupon distributions until contractual maturity, financial institutions frequently issue bonds structured with an odd last coupon period. In these instruments, the timeframe between the second-to-last coupon payment and the final maturity redemption date does not align with a standard semi-annual or annual coupon cycle. A bond might conclude with a short last coupon (e.g. only two months of interest accrued) or a long last coupon (e.g. seven months of interest lumped into final redemption).
Because traditional pricing formulas (such as PRICE and YIELD) mandate perfectly equal periods throughout the security’s lifespan, applying them to bonds approaching an irregular final period creates substantial valuation errors. To compute mathematically defensible market prices and yields, Google Sheets provides the dedicated ODDLPRICE and ODDLYIELD functions. This guide explains their mathematical architecture, parameter requirements, and spreadsheet implementation.
Understanding Odd Last Period Cash Flows
When a bond enters its odd last period, the final cash disbursement combines two distinct financial elements:
- Contractual Principal Redemption: The par face value returned to the investor (typically 100).
- Pro-Rated Final Coupon Interest: An adjusted interest payment calculated specifically for the irregular elapsed days between the last regular coupon date and final maturity.
The ODDLPRICE formula accurately discounts this combined terminal cash flow back to secondary market settlement using standard institutional day-count standards.
Syntax and Parameter Breakdown
1. The ODDLPRICE Function (Solving Price)
=ODDLPRICE(settlement, maturity, last_interest, rate, yield, redemption, frequency, [day_count_convention])2. The ODDLYIELD Function (Solving Annual Yield)
=ODDLYIELD(settlement, maturity, last_interest, rate, price, redemption, frequency, [day_count_convention])Parameter Reference
- settlement: Secondary market settlement date (e.g.
DATE(2026, 4, 1)). - maturity: Final contractual maturity date.
- last_interest: The exact date of the last regular coupon payment before maturity.
- rate: Contractual annual coupon interest rate (decimal or percentage, e.g.
5.25%). - yield (ODDLPRICE): Annualized target yield to maturity.
- price (ODDLYIELD): Quoted price per $100 face value.
- redemption: Redemption 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
Suppose you are evaluating a corporate debenture trading in the secondary market with an irregular final maturity period:
- Settlement Date: 1 April 2026
- Last Regular Coupon Date: 15 January 2026
- Maturity Date: 15 August 2026 (a 7-month long final coupon period)
- Coupon Rate: 5.50%
- Market Yield Target: 5.20%
Set up your Google Sheets cells as follows:
A1: Settlement Date | B1: =DATE(2026, 4, 1)
A2: Maturity Date | B2: =DATE(2026, 8, 15)
A3: Last Interest Date | B3: =DATE(2026, 1, 15)
A4: Annual Coupon Rate | B4: 5.50%
A5: Market Yield Target | B5: 5.20%
A6: Redemption Value | B6: 100
A7: Frequency | B7: 2
A8: Basis | B8: 0
A9: Odd Last Price | B9: =ODDLPRICE(B1, B2, B3, B4, B5, B6, B7, B8, B9)Google Sheets evaluates the elapsed days and terminal irregular interest, returning a price of $101.12 per $100 par value.
Comparison: ODDFPRICE vs ODDLPRICE
| Comparison Attribute | ODDFPRICE | ODDLPRICE |
|---|---|---|
| Irregular Period Location | First coupon period after issuance | Final coupon period before maturity |
| Required Historical Date | Issue date & First coupon date | Last interest (penultimate coupon) date |
| Typical Security Stage | Newly issued primary market debt | Mature secondary market bonds nearing redemption |