MS Excel: ODDFPRICE to calculate the bond price

ODDFPRICE stands for "Odd First Period Price". It calculates the price of a bond when the first period of the bond is shorter or longer than a regular period.

In simple words: If a bond doesn’t start on a perfect schedule (like exactly every 6 months or 1 year), ODDFPRICE helps you figure out what it’s really worth today.

✅ Regular bonds ➔ Use PRICE

✅ Bonds with "weird" first periods ➔ Use ODDFPRICE

Real-world bonds don't always fit neatly into textbook schedules! Companies often issue bonds with uneven starting periods to match:

• Fiscal calendars

• Special events

• Customized financing needs

If you don't adjust for an odd first period, you will misprice the bond — leading to bad investment decisions.

Syntax

ODDFPRICE(settlement, maturity, issue, first_coupon, rate, yld, redemption, frequency, [basis])

Argument	Description
settlement	Date you buy the bond.
maturity	Date when the bond matures (you get back your principal).
issue	Issue date (when bond was originally sold).
first_coupon	Date of the first coupon payment.
rate	Annual coupon rate (interest rate paid).
yld	Yield (expected return based on purchase price).
redemption	Redemption value (usually $100 or 100% face value).
frequency	How often interest is paid (1 = annual, 2 = semiannual, 4 = quarterly).
basis	[Optional] Day count convention (0-4) to calculate interest.

UNIQUE Example: Real-World Scenario

Imagine:

• You buy a bond on March 15, 2025.

• Bond matures on June 30, 2030.

• Bond was issued on January 1, 2025.

• First coupon is on June 30, 2025.

• Annual coupon rate = 5%.

• Your required yield = 6%.

• Redemption = 100.

• Coupons are paid semi-annually (2 times per year).

Excel Formula:

=ODDFPRICE(DATE(2025,3,15), DATE(2030,6,30), DATE(2025,1,1), DATE(2025,6,30), 5%, 6%, 100, 2)

Result: The formula will return the price you should pay per $100 of face value.

Maybe you’ll see a result like $97.85, meaning the bond is slightly discounted.

Practical Examples

Scenario	Formula Example	Meaning
Bond with short first period	=ODDFPRICE(DATE(2025,5,1), DATE(2030,5,1), DATE(2025,2,1), DATE(2025,8,1), 4%, 5%, 100, 2)	First period is shorter ➔ slightly cheaper
Bond with long first period	=ODDFPRICE(DATE(2025,5,1), DATE(2030,5,1), DATE(2024,11,1), DATE(2025,8,1), 4%, 5%, 100, 2)	First period is longer ➔ adjust the price higher

Key Things to Watch Out For (Unique Insights)

Tip	Why It Matters
Always format dates correctly	Wrong dates ➔ Excel returns error or wrong price
Check coupon frequency carefully	Semi-annual (2) is most common for US bonds
Use correct day count basis	Especially important for international bonds (Europe often uses Actual/Actual)
Double-check issue vs. first coupon	Misplacing these dates skews your entire calculation!

How ODDFPRICE is Different from PRICE

Feature	PRICE	ODDFPRICE
Regular periods	✅	❌
Handles odd first period	❌	✅
Easier to use	✅	Slightly harder
Real-world accuracy	Good	Best

In other words:If the bond has a normal schedule, use PRICE.If the bond has a weird first payment, you MUST use ODDFPRICE.

Summary Table

Feature	Details
Purpose	Calculate bond price when the first period is odd
Best For	Bonds with unusual start dates
Important Inputs	Settlement, Issue, First Coupon, Rate, Yield
Common Mistakes	Mixing up date order, ignoring frequency

In the world of bond investing, details make millionaires. If you miscalculate a bond price by even 0.5%, it can cost thousands of dollars over time.

✅ ODDFPRICE lets you see the true, adjusted price when bonds don’t follow a textbook schedule.

✅ If you master ODDFPRICE, you’ll avoid costly mistakes that even some professionals make.