The `ODDFPRICE` function in Excel is used to calculate the price per $100 face value of a security with an odd first period. This is commonly applied to bonds or fixed-income securities where the first interest period is not typical, for example, if the issuance or settlement doesn’t align neatly with the normal coupon payment schedule.
Here’s how you can use the `ODDFPRICE` function:
Syntax
ODDFPRICE(settlement, maturity, issue, first_coupon, rate, yld, redemption, frequency, [basis])
Parameters
- settlement: The security’s settlement date. This is the date after the issue date when the security is traded to the buyer.
- maturity: The date when the security expires, or matures.
- issue: The security’s issue date. This is the date when the security was originally issued.
- first_coupon: The date when the security pays its first coupon.
- rate: The security’s coupon interest rate.
- yld: The annual yield of the security.
- redemption: The redemption value of the security per $100 face value.
- frequency: The number of coupon payments per year. For annual payments, use 1; semiannual, use 2; quarterly, use 4.
- basis (optional): The day count basis to use (0 to 4). The default is 0, which is US (NASD) 30/360.
Day Count Basis Options
- 0 or omitted: US (NASD) 30/360
- 1: Actual/actual
- 2: Actual/360
- 3: Actual/365
- 4: European 30/360
Example
Suppose you want to calculate the price of a bond with the following details:
- Settlement date: January 15, 2023
- Maturity date: October 15, 2026
- Issue date: October 15, 2022
- First coupon date: April 15, 2023
- Coupon rate: 5%
- Yield: 4.5%
- Redemption value: $100
- Payment frequency: Semiannual (2)
- Day count basis: US (NASD) 30/360 (0)
You would use the `ODDFPRICE` function like this:
=ODDFPRICE(DATE(2023,1,15), DATE(2026,10,15), DATE(2022,10,15), DATE(2023,4,15), 0.05, 0.045, 100, 2, 0)
Steps to Implement in Excel
- Open Excel and select a cell where you want the result.
- Type the function `=ODDFPRICE(`.
- Enter each parameter value separated by a comma, as shown above.
- Close the parentheses and press `Enter`.
- The calculated price per $100 face value of the bond will appear in the selected cell.
Make sure the dates are entered as valid date formats in Excel, for example using the `DATE(year, month, day)` function, to avoid errors. Always verify that the `basis` and `frequency` align with the security’s terms to ensure accurate pricing.
Unlock Your Potential
Excel
Access
Power BI
