The ODDLPRICE function in Excel is a powerful tool used in finance to determine the price per $100 face value of a security that features an odd last period. This function is particularly useful for analyzing bond pricing, especially when dealing with securities that do not adhere to standard maturity periods, providing financial analysts and investors with essential information for making informed decisions.

Syntax

=ODDLPRICE(settlement, maturity, last_interest, coupon, yield, frequency, [basis])

• settlement: The date when the security is purchased.
• maturity: The date when the security will mature.
• last_interest: The date of the last interest payment prior to the settlement date.
• coupon: The annual coupon rate of the security.
• yield: The annual yield of the security.
• frequency: The number of interest payments per year.
• [basis]: (optional) The day count basis to be used. If omitted, Excel defaults to 0.

Example #1

=ODDLPRICE("2023-01-01", "2025-01-01", "2022-07-01", 0.05, 0.04, 2)

This function calculates the price for a security purchased on January 1, 2023, maturing on January 1, 2025, with the last interest payment on July 1, 2022, a coupon rate of 5% and a yield of 4% compounded semi-annually. The resulting price could be, for instance, $102.50.

Example #2

=ODDLPRICE("2023-06-01", "2026-06-01", "2025-01-01", 0.06, 0.05, 1, 1)

This function determines the price for a bond purchased on June 1, 2023, with a maturity date of June 1, 2026. The last interest payment occurred on January 1, 2025, with a 6% coupon rate and a yield of 5% compounded annually. The output might yield a price of $98.75.

Example #3

=ODDLPRICE("2023-03-01", "2024-03-01", "2023-01-01", 0.04, 0.03, 2, 0)

This calculation assesses the price for a security acquired on March 1, 2023, maturing on March 1, 2024, following a last interest payment on January 1, 2023, with a coupon rate of 4%, yield of 3%, and semi-annual payments. The price could be, for example, $101.20.

Error handling

• VALUE!: This error occurs if any of the input parameters are invalid, such as text instead of a date.
• NUM!: This error indicates that a numerical value is inappropriate, perhaps due to a negative bond yield or coupon rate.
• N/A: This error suggests that the function cannot compute a price due to the basis or frequency not being appropriate.

Conclusion

Overall, the ODDLPRICE function provides valuable insights for bond investors looking to assess the pricing of securities with non-standard interest payment periods. By correctly utilizing this function, analysts can ensure they make informed financial decisions based on precise data regarding their investments. Understanding its parameters and potential errors can further enhance the accuracy of financial analyses.