The PRICE function in Excel is a valuable tool for investors and financial analysts, as it computes the price of a security that pays periodic interest based on its face value. This function is particularly beneficial when determining the fair market value of bonds and similar investments.

Syntax

PRICE(settlement, maturity, rate, yld, redemption, frequency, [basis])

• settlement: The date when the security is purchased.
• maturity: The date when the security matures.
• 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 interest payments made per year.
• [basis]: An optional parameter that specifies the day count basis to use.

Example #1

PRICE("2023-10-01", "2025-10-01", 0.05, 0.04, 100, 2)

This function call calculates the price of a bond purchased on October 1, 2023, that matures on October 1, 2025, with a coupon rate of 5% and a yield of 4%. The result is approximately $102.34.

Example #2

PRICE("2023-01-15", "2024-01-15", 0.03, 0.025, 100, 1)

This example calculates the price of a bond bought on January 15, 2023, maturing on January 15, 2024, with a 3% coupon rate and a yield of 2.5%. The resulting price is roughly $102.94.

Example #3

PRICE("2023-06-01", "2030-06-01", 0.04, 0.045, 100, 2, 0)

Here, the function evaluates the price of a bond that is purchased on June 1, 2023, maturing on June 1, 2030, with a coupon rate of 4% and a yield of 4.5%, using a 0-based day count basis. This yields a price of approximately $96.78.

Error handling

• VALUE! This error occurs if any of the parameters are not numeric or if the dates are invalid.
• NUM! This error appears if the input values fall outside acceptable ranges (e.g., settlement date is on or after the maturity date).
• DIV/0! This indicates that the yield is zero, which would make it impossible to calculate a price.

Conclusion

The PRICE function is an essential tool for professionals in finance and investment, enabling accurate calculations of bond prices based on various parameters. By understanding its syntax and potential errors, users can effectively determine the value of securities in their portfolios.