The PRICE function in Google Sheets is a powerful tool for investors and financial analysts. It calculates the price of a security that pays periodic interest, such as bonds, based on the expected yield. This function helps users assess the fair value of a bond, allowing for informed investment decisions.

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 interest rate of the security.
• yld: The annual yield of the security.
• redemption: The security’s value at maturity.
• frequency: The number of interest payments per year.
• basis: (Optional) The day count basis to use.

Example #1

PRICE("2023-10-01", "2030-10-01", 0.05, 0.04, 1000, 2)

In this example, the function calculates the price of a bond purchased on October 1, 2023, with a maturity date of October 1, 2030, an annual interest rate of 5%, an expected yield of 4%, a redemption value of $1,000, and semiannual payments. The result might yield a price of approximately $1,028.45.

Example #2

PRICE("2023-09-01", "2028-09-01", 0.06, 0.05, 1000, 1)

Here, the function determines the price of a bond purchased on September 1, 2023, maturing on September 1, 2028, with a 6% interest rate, a yield of 5%, a redemption value of $1,000, and annual payments. The resulting price could be, for example, $1,055.67.

Example #3

PRICE("2023-12-01", "2025-12-01", 0.03, 0.02, 500, 2)

In this case, the function estimates the price of a security bought on December 1, 2023, maturing on December 1, 2025, with a 3% interest rate, a yield of 2%, a redemption amount of $500, and semiannual payments. This might produce a price of around $515.78.

Error handling

• NUM! This error indicates that the settlement or maturity dates are invalid or in an improper format.
• VALUE! This error suggests that there is an invalid value for one of the parameters, such as a non-numeric interest rate.
• REF! This error shows that a reference is not valid, usually occurring if referring to non-existing cells or data.

Conclusion

The PRICE function in Google Sheets is essential for evaluating bond investments and understanding their market value. By inputting appropriate parameters, users can effectively compare different securities and make better financial decisions. Whether you are a seasoned investor or a newcomer to finance, mastering this function can enhance your analytical capabilities.