The Mathematical Distortion
In standard bond finance, consistency is the foundation of every single formula. If a corporate bond pays interest semi-annually, the mathematical period between every single payment is exactly six months. This perfect chronological symmetry allows standard financial software to calculate the annual yield of the bond instantly.
However, the real world of corporate debt is often messy. Sometimes, a corporation wants to issue a bond with a highly specific, irregular expiration date. For example, the bond might pay interest symmetrically on June 1st and December 1st for five years, but the corporation decides the bond must officially mature (expire) on February 15th. Because the bond ends on February 15th, the very last period of the bond’s life (from December 1st to February 15th) is severely truncated. It is not a full six-month period.
This creates an “Odd Last Period.” The truncated ending completely distorts the mathematical value of the bond. If you attempt to calculate the yield of this bond using the standard Excel Yield formula, the formula will assume perfect symmetry and output a wildly inaccurate percentage. To accurately isolate the final anomaly and generate a perfect annualized yield, you must use the highly specialized ODDLYIELD function.
Understanding the Syntax
Because the ODDLYIELD function must mathematically bridge the gap between perfect symmetry and a truncated ending, you must feed it a massive array of chronological data.
=ODDLYIELD(settlement, maturity, last_interest, rate, pr, redemption, frequency, [basis])
- settlement: The exact date you are physically purchasing the bond on the secondary market.
- maturity: The exact date the bond officially expires and the face value is paid out (the end line of the anomaly).
- last_interest: The exact date of the final regular interest payment (the starting line of the anomaly).
- rate: The annual interest rate guaranteed by the bond’s contract.
- pr (Price): The discounted price you are paying for the bond, expressed per $100 of face value.
- redemption: The final payout amount per $100 of face value (almost always
100). - frequency: The number of payments per year (1=annual, 2=semi-annual, 4=quarterly).
- [basis]: (Optional) The day-count methodology (e.g.,
0for standard US banking,1for Actual/Actual).
Example 1: Calculating the Asymmetrical Yield
Assume you are analyzing a corporate bond for acquisition on September 1, 2024. The bond guarantees a highly lucrative 7% interest rate, paid semi-annually on June 30th and December 31st.
However, the bond has an irregular maturity date. It does not expire on December 31st. It officially matures on March 15, 2025.
The final regular interest payment occurs on December 31, 2024. The period from December 31, 2024, to March 15, 2025, is a truncated anomaly. You are buying this bond at a premium price of 104.50 (per $100 of face value). Because you are paying a premium, your true annualized yield will be significantly lower than the guaranteed 7%.
Let’s map out the massive variable list in a spreadsheet:
- Cell A1 (Settlement):
=DATE(2024, 9, 1) - Cell A2 (Maturity):
=DATE(2025, 3, 15) - Cell A3 (Last Interest):
=DATE(2024, 12, 31) - Cell A4 (Rate):
7% - Cell A5 (Price):
104.50 - Cell A6 (Redemption):
100 - Cell A7 (Frequency):
2
To reveal the true annualized yield, click on cell B1 and type:
=ODDLYIELD(A1, A2, A3, A4, A5, A6, A7)
How this works:
- Excel isolates the odd period between December 31 and March 15.
- It mathematically calculates the exact fraction of interest earned during that specific, broken timeframe.
- It then calculates the standard, symmetrical yield of the remaining period between your Settlement date (September 1) and the Last Interest date (December 31).
- It fuses the two calculations together against the 104.50 premium price to create a mathematically perfect annualized percentage.
- It instantly outputs 0.0076.
If you highlight cell B1 and click the “%” button on the Excel toolbar, it will format the number as 0.76%.
You can confidently report to your portfolio manager that despite the 7% coupon rate, buying the bond at that massive premium with a truncated ending destroys the profitability, yielding less than 1%. By mastering the ODDLYIELD function, you gain the ability to accurately audit complex, asymmetrical financial instruments and avoid terrible investments.