PRICE

• How to use PRICE formula?
• Examples of using PRICE formula
• PRICE formula not working?
• Similar formulas to PRICE

Usage

Use the PRICE formula with the syntax shown below, it has 6 required parameters and 1 optional parameter:

=PRICE(settlement, maturity, rate, yield, redemption, frequency, [day_count_convention])

1. settlement (required):
   The date on which the security is purchased, expressed as a date or a reference to a cell containing a date.
2. maturity (required):
   The maturity date of the security, expressed as a date or a reference to a cell containing a date.
3. rate (required):
   The annual coupon rate of the security as a percentage.
4. yield (required):
   The annual yield of the security as a percentage.
5. redemption (required):
   The redemption value of the security per $100 face value.
6. frequency (required):
   The frequency of coupon payments per year. Must be one of the following values: 1, 2, 4.
7. day_count_convention (optional):
   Optional. The day count convention to use in the calculation. Must be one of the following values: actual/actual, actual/360, actual/365, 30/360. If omitted, the default value is actual/actual.

Examples

Calculating the price of a bond

The PRICE function can be used to determine the current value of a bond based on its annual coupon rate, yield, and maturity date.

Comparing bond prices

The PRICE function can be used to compare the prices of different bonds with different coupon rates and yields.

Calculating the yield of a bond

The PRICE function can be used in conjunction with the YIELD function to calculate the yield of a bond based on its current price.

Common Mistakes

Incorrect number of arguments

The PRICE formula requires at least 5 arguments and up to 7. Make sure you have provided all the required arguments and in the correct order.

Invalid argument type

Make sure you have provided the correct data type for each argument. For example, the settlement and maturity dates should be in date format and the rate, yield, and redemption values should be numbers.

Incorrect date format

If you are getting a #VALUE! error, make sure that the settlement and maturity dates are in date format. You can format a cell as a date by selecting it and going to Format > Number > Date.

Using an annual rate instead of a periodic rate

The rate and yield arguments should be the periodic interest rates. If you have an annual interest rate, divide it by the number of periods in a year.

Incorrect frequency value

Make sure you are using the correct frequency value for your calculation. The frequency argument should be one of 1, 2, 3, 4, or 12, representing the number of coupon payments per year.

Related Formulas

The following functions are similar to PRICE or are often used with it in a formula:

• YIELD

  The YIELD function calculates the yield of a security that pays periodic interest. The yield is the annualized percentage rate returned on the bond, assuming the bond is held until maturity. This function is commonly used in finance and investment analysis.

• DURATION

  The DURATION function calculates the Macauley duration of a security paying periodic interest, such as a US Treasury Bond, based on expected yield. The Macauley duration is a measure of the sensitivity of the price of the security to changes in interest rates. This function is commonly used in finance and investment analysis.

• ACCRINT

  The ACCRINT function calculates the accrued interest of a security that pays periodic interest. It is commonly used in financial analysis to determine the amount of interest earned but not yet paid on a security. The function takes into account the issue date, first payment date, settlement date, rate, redemption value, and frequency of interest payments. The result is the accrued interest at the settlement date.

• TBILLPRICE

  The TBILLPRICE formula calculates the price per $100 face value of a US Treasury bill. This formula is most commonly used by investors to determine the fair value of a Treasury bill before buying or selling it on the secondary market.

• TBILLYIELD

  The TBILLYIELD function calculates the yield of a Treasury bill based on its price. It returns the annual yield of a Treasury bill (a type of short-term government security), based on its price, maturity, and settlement dates. This function is commonly used in financial analysis to compare the yield of Treasury bills with other types of investments.

Learn More

You can learn more about the PRICE Google Sheets function on Google Support.