Quick Navigation
ODDLPRICE Function
Summary
The ODDLPRICE function calculates the price per $100 face value of a security with an irregular final coupon period. Perfect for fixed-income analysts pricing bonds with non-standard last interest payments.
Syntax
ODDLPRICE(settlement, maturity, last_interest, rate, yld, redemption, frequency, [basis])Parameters
| Parameter | Type | Required | Description |
|---|---|---|---|
| settlement | Date |
Yes | Security settlement date (post-issue trading date) |
| maturity | Date |
Yes | Security maturity/expiration date |
| last_interest | Date |
Yes | Most recent interest payment date |
| rate | Number |
Yes | Security's annual coupon interest rate |
| yld | Number |
Yes | Security's annual yield to maturity |
| redemption | Number |
Yes | Redemption value per $100 face value |
| frequency | Number |
Yes | Coupon payments per year (1=annual, 2=semiannual, 4=quarterly) |
| basis | Number |
No | Day count basis (0=30/360 US, 1=Actual/actual, etc.) |
Using the ODDLPRICE Function
ODDLPRICE is crucial for bond traders and portfolio managers valuing securities with irregular final coupon periods. Use it when the settlement date falls between regular coupon dates, creating an odd last period.
Common ODDLPRICE Examples
Pricing Bond with Odd Last Coupon
=ODDLPRICE(DATE(2008,2,7), DATE(2008,6,15), DATE(2007,10,15), 0.0375, 0.0405, 100, 2, 0)Calculates $99.88 price per $100 face value for semiannual bond using 30/360 basis
Short Last Coupon Period
=ODDLPRICE("1/15/2024", "6/30/2024", "12/31/2023", 0.05, 0.048, 100, 2, 1)Prices bond with actual/actual day count for short final period
Annual Coupon Bond
=ODDLPRICE(A2, B2, C2, D2, E2, 100, 1, 0)Annual pay bond using cell references and default basis
Frequently Asked Questions
Common Errors and Solutions
#NUM!
Cause: rate < 0 or yld < 0
Solution: Ensure both rate and yield are positive numbers
#NUM!
Cause: basis < 0 or basis > 4
Solution: Use valid basis values: 0,1,2,3, or 4
#NUM!
Cause: maturity ≤ settlement or settlement ≤ last_interest
Solution: Verify chronological order: maturity > settlement > last_interest
#VALUE!
Cause: Invalid dates for settlement, maturity, or last_interest
Solution: Use DATE() function or valid date serial numbers
Notes
- Dates must satisfy: maturity > settlement > last_interest
- Excel stores dates as serial numbers (1/1/1900 = 1)
- Frequency: 1=annual, 2=semiannual, 4=quarterly
- Redemption typically equals 100 for par value bonds
- Always use DATE() function for dates to avoid text errors
Compatibility
Available in: Excel 2007, Excel 2010, Excel 2013, Excel 2016, Excel 2019, Excel 2021, Excel 365
Not available in: Excel 2003, Excel XP, Excel 2000, Excel 97
Content last reviewed: December 9, 2025
Update frequency: As needed
Excel versions tested: Excel 2007+