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.