MS Excel: ODDLPRICE function when the last period is irregular

ODDLPRICE stands for "Odd Last Period Price". It calculates the price per $100 face value of a bond when the last period is irregular — that is, the final payment doesn’t align perfectly with a normal coupon schedule.

In plain English:If a bond’s final interest payment happens earlier or later than expected, ODDLPRICE gives you the correct bond price.

✅ Normal maturity ➔ Use PRICE

✅ Odd final period ➔ Use ODDLPRICE

When bonds have a shortened or extended final period, the price you should pay for it changes. Ignoring this adjustment can lead to paying too much or too little — a costly mistake for investors.

Proper use of ODDLPRICE = better, smarter, and more accurate bond investing.

Syntax

ODDLPRICE(settlement, maturity, last_interest, rate, pr, redemption, frequency, [basis])

Argument	Description
settlement	Date you buy the bond.
maturity	Date bond matures (final payment date).
last_interest	Last coupon payment date before maturity.
rate	Annual coupon interest rate.
pr	Yield (annual return expected).
redemption	Redemption value per $100 face value (usually 100).
frequency	Interest payment frequency (1 = annual, 2 = semi-annual, 4 = quarterly).
basis	[Optional] Day count basis (US 30/360, Actual/Actual, etc.).

Key Concept: "Odd Last Period"

• Bonds typically pay interest at regular intervals.

• But sometimes, the last interest period is not a full period — e.g., bond matures 3 months after a 6-month coupon.

• ODDLPRICE adjusts for this non-standard period.

Real-World Example

Imagine:

• You buy a bond on May 1, 2025.

• Bond matures on August 1, 2025 (just 3 months!).

• Last full coupon paid on February 1, 2025.

• Coupon rate = 4%.

• Yield = 3.8%.

• Redemption = $100.

• Frequency = 2 (semi-annual).

Excel Formula:

=ODDLPRICE(DATE(2025,5,1), DATE(2025,8,1), DATE(2025,2,1), 4%, 3.8%, 100, 2)

✅ Result: Excel gives you a price, for example, $100.25.

Meaning: You should pay $100.25 per $100 face value, accounting for the shorter-than-normal last period.

More Unique ODDLPRICE Scenarios

Scenario	Formula	Meaning
Short final period	=ODDLPRICE(DATE(2025,6,1), DATE(2025,8,1), DATE(2025,2,1), 5%, 4.5%, 100, 2)	Price slightly adjusts upward
Long final period	=ODDLPRICE(DATE(2025,3,1), DATE(2026,2,1), DATE(2025,2,1), 6%, 6.2%, 100, 2)	Price slightly adjusts downward

Secrets to Using ODDLPRICE Correctly

Tip	Why It Matters
Always match the last_interest to the last regular coupon	Incorrect date = wrong price
Yield is NOT the coupon rate	Coupon rate = paid interest; yield = expected return
Understand frequency deeply	Most government bonds = semi-annual (2)
Day count basis subtly shifts the price	Small but real differences matter in large investments

ODDLPRICE vs PRICE

Feature	PRICE	ODDLPRICE
Regular periods only	✅	❌
Handles odd final periods	❌	✅
Easier to use	✅	Slightly more detailed
Real-world investment precision	Good	Best

Summary

Feature	Details
Purpose	Calculate bond price with an irregular last payment
When to use	Bonds with non-standard final period
Critical Inputs	Yield, Last Interest Date, Settlement Date
Common Mistake	Confusing yield with rate

Precision matters.When bonds behave irregularly — especially near maturity — naive pricing can cost you thousands in a portfolio.

✅ ODDLPRICE guarantees your bond price is correct — even with strange final periods.

✅ Professional bond traders and serious investors use ODDLPRICE to fine-tune investment strategies.

When investing in corporate bonds that often retire earlier than scheduled (early redemption), using ODDLPRICE ensures you never overpay for a maturing asset. Small miscalculations compound over time — smart investors know every cent counts.