How to Use the ODDFPRICE Function in Excel
The ODDFPRICE function in Excel and Google Sheets calculates the price per $100 face value of a security that possesses an odd first period. Utilized predominantly in financial analysis and investment planning, this function is essential for valuing bonds, notes, or other securities that do not follow a standard first period.
Syntax
The syntax for the ODDFPRICE function is consistent across both Excel and Google Sheets:
ODDFPRICE(settlement, maturity, issue, first_coupon, rate, yield, redemption, frequency, [basis])
- settlement: The date on which the security is bought.
- maturity: The date on which the security will mature or expire.
- issue: The date on which the security was issued.
- first_coupon: The date on which the first interest payment will be made.
- rate: The annual coupon rate of the security.
- yield: The annual yield or return of the security.
- redemption: The value at which the security will be redeemed at maturity.
- frequency: The frequency of the coupon payments per year (1 for annual, 2 for semi-annual, etc.).
- basis (optional): The day count convention for interest calculation (0 for US (NASD) 30/360, 1 for Actual/Actual, etc.).
Examples
Let’s look at an example of using ODDFPRICE in Excel:
Input | Values |
---|---|
Settlement | 1-Jan-2022 |
Maturity | 1-Jan-2025 |
Issue | 1-Jul-2021 |
First Coupon | 1-Jan-2023 |
Rate | 5% |
Yield | 4.2% |
Redemption | $1000 |
Frequency | 2 |
=ODDFPRICE("1/1/2022", "1/1/2025", "1/7/2021", "1/1/2023", 0.05, 0.042, 1000, 2, 0)
This formula calculates the price per $100 face value of the security based on the given inputs.
The ODDFPRICE function can also be useful in comparing bonds with differing terms to maturity and coupon rates, thus aiding in making informed investment decisions.
In conclusion, the ODDFPRICE function serves as an indispensable tool for financial analysts, investors, and those engaged in the management of fixed-income securities. It assists in assessing the fair value of securities that exhibit odd first periods, thereby facilitating comprehensive investment analysis and decision-making.
More information: https://support.microsoft.com/en-us/office/oddfprice-function-d7d664a8-34df-4233-8d2b-922bcf6a69e1