MS Excel: ODDLYIELD function the annual yield

ODDLYIELD stands for "Odd Last Period Yield". It calculates the annual yield of a bond when the last period before maturity is irregular — either shorter or longer than a normal coupon schedule.

In simple words:If a bond’s last payment doesn't fit into a clean, regular schedule, ODDLYIELD helps you find the true return you will earn.

✅ Normal bond ➔ Use YIELD

✅ Odd last period ➔ Use ODDLYIELD

Bonds often have irregular maturity schedules due to early retirement, callable features, or special issuance terms.Ignoring these irregularities gives you a wrong yield estimate — potentially misleading your investment decisions.

Smart investors use ODDLYIELD to know the exact return they are earning, not just an approximation.

Syntax

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

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

Example

Imagine:

• You buy a bond on April 1, 2025.

• The bond matures on July 1, 2025 (only 3 months later!).

• Last regular coupon payment was on January 1, 2025.

• Coupon rate = 5%.

• Purchase price = $99.50 (per $100).

• Redemption value = $100.

• Interest paid semi-annually (frequency = 2).

Excel Formula:

=ODDLYIELD(DATE(2025,4,1), DATE(2025,7,1), DATE(2025,1,1), 5%, 99.5, 100, 2)

Result: Excel will calculate a yield like 5.7%.

Meaning: Even though the bond says "5% coupon," your real annualized return considering the short odd period is 5.7%!

Unique ODDLYIELD Scenarios

Scenario	Formula	What Happens
Short final period	=ODDLYIELD(DATE(2025,5,1), DATE(2025,8,1), DATE(2025,2,1), 4%, 99, 100, 2)	Yield adjusts upward
Long final period	=ODDLYIELD(DATE(2025,3,1), DATE(2026,2,1), DATE(2025,2,1), 6%, 101, 100, 2)	Yield adjusts downward

Secrets to Master ODDLYIELD in Real-Life Investing

Tip	Why It Matters
Be precise with settlement and last_interest dates	Incorrect dates = wrong yield
Remember: price ≠ redemption value	Discounts/premiums impact yield
Choose frequency wisely	Most US corporate bonds = 2 (semi-annual)
Select the correct basis	Changes slightly but matters in precise investments

ODDLYIELD vs YIELD

Feature	YIELD	ODDLYIELD
Regular periods only	✅	❌
Handles odd last periods	❌	✅
Simpler calculation	✅	Slightly more advanced
True accuracy with complex bonds	Good	Best

Summary

Feature	Details
Purpose	Calculate bond yield with irregular last payment
When to use	Non-standard final coupon periods
Key Inputs	Settlement date, Last Interest Date, Price
Critical Insight	Yield is different from coupon rate

Bond investing isn’t just about high coupons or low prices — it’s about true returns.Without accounting for odd last periods, you might overestimate your investment yield — a classic amateur mistake.

ODDLYIELD ensures your investment analysis is professional and precise.

With ODDLYIELD, you see the real picture — not just marketing numbers.

Investor Tip

Professional traders often deal with bonds that are called early or have irregular final coupons.Using ODDLYIELD not only protects your investments but also makes your bond pricing strategies far superior to competitors.