Excel ODDFPRICE Function
Calculate bond prices with odd first periods. Learn ODDFPRICE syntax, examples, and securities valuation in Excel and Google Sheets.
Excel
Google SheetsExcelGoogle Sheets=ODDFPRICE(settlement, maturity, issue, first_coupon, rate, yld, redemption, frequency, [basis])Quick Answer
ODDFPRICE function ODDFPRICE function calculates the price per $100 face value of a security that has an odd (short or long) first period. An odd first period occurs when the time between the issue date and first coupon payment differs from the standard coupon payment intervals.
=ODDFPRICE(settlement, maturity, issue, first_coupon, rate, yld, redemption, frequency, [basis])Practical Examples
Basic Bond Pricing with Odd First Period
Calculate the price of a corporate bond with an irregular first coupon period
Municipal Bond with Short First Period
Price a municipal bond where the first coupon period is shorter than subsequent periods
Treasury Bond with Long First Period
Calculate price for a treasury bond with an extended first coupon period
Quarterly Coupon Bond Pricing
Price a bond with quarterly coupon payments and an odd first period
European Basis Bond Calculation
Calculate bond price using European 30/360 day count convention
Common Errors and Solutions
ODDFPRICE returns #NUM! error
Invalid date sequence - dates must follow the order: issue < settlement < first_coupon < maturity. This error also occurs if settlement equals or exceeds maturity date.
Verify all dates are in correct chronological order. Check that the issue date comes before settlement, settlement before first coupon, and first coupon before maturity. Use helper cells to validate date sequences before applying ODDFPRICE.
Use conditional validation: =IF(AND(issue<settlement, settlement<first_coupon, first_coupon<maturity), ODDFPRICE(...), "Invalid dates")
Example:
ODDFPRICE returns #VALUE! error
Non-numeric values entered for rate, yield, redemption, frequency, or basis parameters. This commonly happens when percentage signs are included or text is accidentally entered.
Enter rates and yields as decimal numbers (e.g., 0.05 for 5%, not 5 or 5%). Ensure frequency is exactly 1, 2, or 4. Remove any text or special characters from numeric parameters.
Format cells as numbers before referencing. Use ISNUMBER() to validate inputs before calculation.
Example:
ODDFPRICE returns #NUM! for parameter values
Invalid basis argument (not in range 0-4), negative rate or yield values, or redemption value of zero or less. Frequency must be exactly 1, 2, or 4.
Use only basis values 0, 1, 2, 3, or 4. Ensure rate and yield are positive numbers. Verify redemption value is greater than zero (typically 100 for bonds). Check that frequency is 1, 2, or 4.
Add parameter validation checks before calculation to ensure all values are within valid ranges.
Example:
Result seems incorrect or unrealistic
Using wrong day count basis for the security type, or entering rate/yield as whole numbers instead of decimals.
Choose appropriate basis: 0 (30/360) for US corporate bonds, 1 (actual/actual) for treasury notes, 4 (European 30/360) for European bonds. Always use DATE() function for dates. Verify rates are decimal format.
Document which basis convention applies. Create a reference table for different security types and their standard basis values.
Example:
Best Practices and Pro Tips
Day Count Basis Selection Strategy
Choosing the correct day count basis is critical for accurate bond pricing and varies by security type and market. US Treasury bonds typically use basis 0 (30/360 US), while corporate bonds may use either 0 or 1 (actual/actual) depending on the specific bond terms. Municipal bonds often use actual/actual (basis 1). European bonds typically use basis 4 (European 30/360). Government agency securities may use actual/365 (basis 3). Always verify the day count convention in the bond's prospectus or indenture agreement. Using the wrong basis can result in pricing errors of several basis points, which can be significant in large portfolios. Create a reference table in your workbook documenting which basis to use for different security types to maintain consistency.
Date Input Methods and Format Consistency
Always use the DATE() function for date parameters rather than entering dates as text strings. This eliminates issues with regional date format differences (MM/DD/YYYY vs DD/MM/YYYY) and ensures cross-platform compatibility between Excel and Google Sheets. Use DATE(2025,3,15) instead of '3/15/2025' for consistent results across all systems.
Understanding Yield vs Rate Relationship
The relationship between yield and coupon rate determines bond pricing: When yield > rate, bond trades at discount (price < 100). When yield < rate, bond trades at premium (price > 100). When yield = rate, bond trades at par (price = 100). This fundamental relationship helps validate ODDFPRICE results regardless of the odd first period.
Performance Optimization for Portfolio Calculations
For large bond portfolios, optimize ODDFPRICE performance by: (1) Using helper columns for inputs instead of nesting functions, (2) Setting calculation mode to manual during data entry, (3) Using Excel tables with structured references, (4) Caching results and only recalculating when parameters change. These strategies significantly reduce calculation time in portfolio management.
Comprehensive Validation and Error Handling
Build robust models with multi-layer validation: Check date sequences are valid, ensure rates/yields are in decimal format (0-0.20 range), verify frequency is 1, 2, or 4, confirm basis is 0-4, and validate redemption is positive. Combine with IFERROR for user-friendly error messages.
Integration with Other Financial Functions
Combine ODDFPRICE with other functions for complete bond analysis: Use ODDFYIELD for yield calculations, DURATION/MDURATION for interest rate sensitivity, ACCRINT for accrued interest, and PRICE/YIELD for comparison with regular period bonds. Build integrated dashboards that automatically update valuations as market conditions change.
Need Help with Excel ODDFPRICE Function?
Stop struggling with formula syntax. Use AskFormulas to generate validated formulas instantly with our AI-powered tool.
Example Excel formula:
Related Formulas
The ACCRINT function calculates accrued interest for a security that pays periodic interest, enabling accurate financial reporting and bond valuation.
ExcelSheetsThe ACCRINTM function calculates accrued interest for securities that pay interest at maturity, essential for bond valuation and analysis.
ExcelSheetsThe DURATION function calculates Macaulay duration of a security with periodic interest payments. Learn syntax, examples, and best practices.
ExcelSheetsConvert fractional dollar prices to decimals with DOLLARDE. Essential for bond trading, stock quotes, and financial analysis.
ExcelSheets