The ODDLPRICE function in Excel is used to calculate the price per $100 face value of a security with an odd last period. This function is particularly useful for bonds that do not follow a standard coupon payment schedule for the last period. Here’s how you can use the ODDLPRICE function:
Syntax
ODDLPRICE(settlement, maturity, last_interest, rate, yld, redemption, frequency, [basis])
Parameters
- settlement: The settlement date of the security. This is the date after the issue date when the security is traded to the buyer. It should be entered as a serial date number or as a date string.
- maturity: The maturity date of the security. This is when the security expires.
- last_interest: The date of the last interest payment.
- rate: The annual coupon rate of the security.
- yld: The annual yield of the security.
- redemption: The redemption value per $100 face value.
- frequency: The number of coupon payments per year. It can be:
- 1 for annual,
- 2 for semi-annual,
- 4 for quarterly.
- basis (optional): The day count basis to use. It defaults to 0 if not specified. The options are:
- 0 or omitted: US (NASD) 30/360
- 1: Actual/actual
- 2: Actual/360
- 3: Actual/365
- 4: European 30/360
Example
Suppose you have the following information for a security:
- Settlement date: March 15, 2021
- Maturity date: November 30, 2021
- Last interest date: November 30, 2020
- Rate: 5%
- Yield: 6%
- Redemption: $100
- Frequency: 2 (semi-annual payments)
- Basis: 0 (US 30/360)
You can use the ODDLPRICE function as follows:
=ODDLPRICE(DATE(2021, 3, 15), DATE(2021, 11, 30), DATE(2020, 11, 30), 0.05, 0.06, 100, 2, 0)
Tips
- Ensure that all date entries are valid and the settlement date is before the maturity date.
- Ensure consistency in units for the rate and yield (e.g., both should be in annual terms).
- The function will return the price per $100 face value, which means if the face value is different, you’ll need to adjust your calculations accordingly.
This function helps handle specific cases in bond pricing where the standard assumptions of regular intervals do not apply in the bond’s final period.
Unlock Your Potential
Excel
Access
Power BI
