How to Use the ODDLPRICE Function in Excel
Below is a detailed guide on how to use the ODDLPRICE function in Microsoft Excel and Google Sheets.
Overview
The ODDLPRICE function calculates the price per $100 face value of a security with either an irregular first or last period that pays periodic interest at a constant rate. It is commonly utilized in finance and accounting for determining bond pricing.
Syntax
The syntax for the ODDLPRICE function is:
ODDLPRICE(settlement, maturity, issue, first_coupon, rate, yld, redemption, frequency, [basis])
Settlement
: The date on which the buyer acquires the security.Maturity
: The date when the security expires.Issue
: The date when the security was issued.First_coupon
: The date when the first interest payment is made.Rate
: The annual interest rate of the security.Yld
: The annual yield of the security.Redemption
: The redemption amount per $100 face value of the security.Frequency
: The number of coupon payments made each year.Basis
(optional): The type of day count basis used in the calculation (0 – US (NASD) 30/360, 1 – Actual/Actual, etc.).
Examples
Example 1: Basic ODDLPRICE Calculation
Calculate the price per $100 face value of a security with the following details:
- Settlement Date: 01-Jan-2022
- Maturity Date: 01-Jan-2027
- Issue Date: 01-Jan-2022
- First Coupon Date: 01-Jul-2022
- Interest Rate: 5%
- Annual Yield: 6%
- Redemption Value: $100
- Payment Frequency: Semi-annual (2 payments per year)
Excel
Cell | Formula | Result |
---|---|---|
A1 | 01-Jan-2022 | |
B1 | 01-Jan-2027 | |
C1 | 01-Jan-2022 | |
D1 | 01-Jul-2022 | |
E1 | 5% | |
F1 | 6% | |
G1 | $100 | |
H1 | 2 | |
I1 | =ODDLPRICE(A1, B1, C1, D1, E1, F1, G1, H1) | Result |
Google Sheets
=ODDLPRICE("01-Jan-2022", "01-Jan-2027", "01-Jan-2022", "01-Jul-2022", 0.05, 0.06, 100, 2)
Example 2: ODDLPRICE with Custom Day Count Basis
Calculate the price per $100 face value using the Actual/Actual day count basis:
- Day Count Basis: Actual/Actual
Excel
Cell | Formula | Result |
---|---|---|
J1 | 2 | |
K1 | =ODDLPRICE(A1, B1, C1, D1, E1, F1, G1, H1, J1) | Result |
Google Sheets
=ODDLPRICE("01-Jan-2022", "01-Jan-2027", "01-Jan-2022", "01-Jul-2022", 0.05, 0.06, 100, 2, 1)
By understanding the parameters and utilizing the ODDLPRICE function as demonstrated, you can effectively calculate the price per $100 face value of bonds in Microsoft Excel and Google Sheets, especially when dealing with securities that have an irregular first or last period.
More information: https://support.microsoft.com/en-us/office/oddlprice-function-fb657749-d200-4902-afaf-ed5445027fc4