The Broken Bond Calendar
In standard bond finance, everything is perfectly symmetrical. If a corporation issues a bond that pays interest twice a year (semi-annually), the time between every single payment is exactly six months. This perfect symmetry makes it incredibly easy for financial software to calculate the value of the bond.
However, the real world is rarely perfectly symmetrical. Sometimes, a corporation needs to issue a bond on a weird, random Tuesday in the middle of March, but they want all future interest payments to align with a clean corporate calendar (e.g., June 1st and December 1st). Because the bond was issued in March, the very first period of the bond’s life (March to June) is mathematically shorter than the standard six-month period.
This is known as a bond with an “Odd First Period.” If you try to calculate the price of this bond using the standard Excel PRICE function, the formula will violently crash or return a wildly inaccurate number because it assumes perfect symmetry. To mathematically calculate the true, fair market price of a bond with an irregular first payment cycle, you must use the highly specialized ODDFPRICE function.
Understanding the Syntax
The ODDFPRICE function is one of the most complex financial tools in Excel because it requires you to map out the exact chronological anomaly of the first period.
=ODDFPRICE(settlement, maturity, issue, first_coupon, rate, yld, redemption, frequency, [basis])
- settlement: The exact date you are physically purchasing the bond.
- maturity: The exact date the bond officially expires.
- issue: The exact date the bond was originally created (this establishes the starting line of the anomaly).
- first_coupon: The exact date of the very first interest payment (this establishes the end line of the anomaly).
- rate: The annual interest rate guaranteed by the bond’s contract.
- yld: The current market interest rate (the yield).
- 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: Pricing the Asymmetrical Bond
Assume an unorthodox corporate bond was issued on March 15, 2024. The corporation wants to pay interest semi-annually on June 30 and December 31. Therefore, the very first payment will happen on June 30, 2024 (a short, “odd” period).
The bond pays a highly lucrative 7% interest rate, and it matures on December 31, 2030. You are buying this bond on the secondary market on May 1, 2024. The current market yield has dropped to 4%.
Because the bond pays 7% while the market only pays 4%, the bond is incredibly valuable. You must pay a premium for it. But how much of a premium, considering the first payment is mathematically broken?
Let’s map out the massive variable list in a spreadsheet:
- Cell A1 (Settlement):
=DATE(2024, 5, 1) - Cell A2 (Maturity):
=DATE(2030, 12, 31) - Cell A3 (Issue):
=DATE(2024, 3, 15) - Cell A4 (First Coupon):
=DATE(2024, 6, 30) - Cell A5 (Rate):
7% - Cell A6 (Yield):
4% - Cell A7 (Redemption):
100 - Cell A8 (Frequency):
2
To calculate the exact fair market price, click on cell B1 and type:
=ODDFPRICE(A1, A2, A3, A4, A5, A6, A7, A8)
How this works:
- Excel isolates the odd period between March 15 and June 30.
- It calculates the exact fraction of interest earned during that specific anomaly.
- It then calculates the standard, symmetrical value of the remaining six years.
- It fuses the two calculations together, discounting the total by the 4% market yield.
- It instantly outputs 120.45.
Interpreting the Output
Like all professional bond pricing tools in Excel, the number 120.45 is the price per $100 of face value.
If the bond you are buying has a total face value of $50,000, you must convert Excel’s output. Take 120.45, divide it by 100, and multiply it by $50,000. The result is $60,225.
You must hand the investor exactly $60,225 today to acquire the bond. By mastering the ODDFPRICE function, you gain the ability to accurately value complex, asymmetrical financial instruments that destroy standard calculators.