PRICEfunction calculates the price per $100 face value of a security that pays periodic interest. It is commonly used to determine the current value of a bond. The function takes the settlement date, maturity date, annual coupon rate, yield, redemption value, and frequency of coupon payments as input. It returns the price of the security, which is the sum of the present value of the coupon payments and the present value of the redemption value.
- How to use
- Examples of using
PRICEformula not working?
- Similar formulas to
PRICE formula with the syntax shown below, it has 6 required parameters and 1 optional parameter:
- settlement (required):
The date on which the security is purchased, expressed as a date or a reference to a cell containing a date.
- maturity (required):
The maturity date of the security, expressed as a date or a reference to a cell containing a date.
- rate (required):
The annual coupon rate of the security as a percentage.
- yield (required):
The annual yield of the security as a percentage.
- redemption (required):
The redemption value of the security per $100 face value.
- frequency (required):
The frequency of coupon payments per year. Must be one of the following values: 1, 2, 4.
- 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.
ExamplesHere are a few example use cases that explain how to use the
PRICEformula in Google Sheets.
Calculating the price of a bond
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
PRICE function can be used to compare the prices of different bonds with different coupon rates and yields.
Calculating the yield of a bond
PRICE function can be used in conjunction with the
YIELD function to calculate the yield of a bond based on its current price.
PRICEnot working? Here are some common mistakes people make when using the
PRICEGoogle Sheets Formula:
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.
The following functions are similar to
PRICE or are often used with it in a formula:
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.
DURATIONfunction 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.
ACCRINTfunction 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.
TBILLPRICEformula 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.
TBILLYIELDfunction 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.
You can learn more about the
PRICE Google Sheets function on Google Support.