The Odd Last Period Problem
In standard bond finance, a corporate bond is mathematically symmetrical. If it pays interest twice a year, the time between every single payment is exactly six months. This perfect symmetry makes it incredibly easy to calculate the value of the bond on the secondary market. However, sometimes a corporation needs a bond to mature (expire) on a highly specific date that does not align with the standard six-month payment calendar.
If a bond pays interest on June 1st and December 1st, but the corporation forces the bond to officially mature and pay out its massive face value on January 15th, the very last period of the bond’s life (from December 1st to January 15th) is mathematically shorter than the standard six-month cycle.
This is known as an “Odd Last Period.” If you try to calculate the price of this bond using the standard Excel PRICE function, the formula will violently crash because it assumes perfect chronological symmetry until the very last day. To mathematically calculate the true, fair market price of a bond with an irregular, asymmetrical final payout, you must use the highly specialized ODDLPRICE (Odd Last Price) function.
Understanding the Syntax
The ODDLPRICE function requires you to map out the exact chronology of the final anomaly to prevent mathematical failure.
=ODDLPRICE(settlement, maturity, last_interest, rate, yld, 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 massive face value is paid out (this is the end line of the anomaly).
- last_interest: The exact date of the final regular interest payment (this establishes the starting 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 Finish
Assume you are analyzing a corporate bond on October 1, 2024. The bond pays interest semi-annually on June 30th and December 31st. The bond guarantees a massive 8% interest rate. The current market yield has dropped to 5%.
However, the bond has an irregular maturity date. It does not mature on December 31st. It officially matures on February 28, 2025.
This creates an Odd Last Period. The final regular payment occurs on December 31, 2024. The period from December 31, 2024, to February 28, 2025, is a truncated anomaly. Because the bond is paying 8% while the market only pays 5%, the bond is incredibly valuable. But exactly how valuable, considering the broken final payout?
Let’s map out the massive variable list in a spreadsheet:
- Cell A1 (Settlement):
=DATE(2024, 10, 1) - Cell A2 (Maturity):
=DATE(2025, 2, 28) - Cell A3 (Last Interest):
=DATE(2024, 12, 31) - Cell A4 (Rate):
8% - Cell A5 (Yield):
5% - Cell A6 (Redemption):
100 - Cell A7 (Frequency):
2
To calculate the exact fair market price, click on cell B1 and type:
=ODDLPRICE(A1, A2, A3, A4, A5, A6, A7)
How this works:
- Excel isolates the odd period between December 31 and February 28.
- It calculates the exact fraction of interest earned during that specific, broken timeframe.
- It then calculates the standard, symmetrical value of the remaining period between your Settlement date (October 1) and the Last Interest date (December 31).
- It fuses the two calculations together, discounting the total by the 5% market yield.
- It instantly outputs 101.44.
Interpreting the Output
Like all professional bond pricing tools in Excel, the number 101.44 is the price per $100 of face value.
If the bond you are buying has a total face value of $10,000, you must convert Excel’s output. Take 101.44, divide it by 100, and multiply it by $10,000. The result is $10,144.
You must hand the investor exactly $10,144 today to acquire the bond. By mastering the ODDLPRICE function, you gain the ability to accurately value complex, asymmetrical financial instruments that destroy standard calculators.