What Is PRICE Function in Google Sheets?
The PRICE function in Google Sheets is used to calculate the price of a security that pays periodic interest, like Treasury bills and bonds. In other words, it calculates the price of a security, such as a US Treasury Bond, based on an expected yield. The function has quite a few input parameters. These include the settlement and maturity date, the coupon rate, yield, the final redemption value, and the frequency of coupon payments per year. There is an optional parameter, the day count convention, that specifies how we must calculate the number of days between the settlement and maturity.
In the example below, we have entered the required details, and you can check how we calculate the bond’s PRICE. It considers several factors, including the security’s:
- Face value
- Coupon rate
- Yield
- Maturity date
- Settlement date
- Redemption value
- Frequency of coupon payments per year

Investors use the PRICE function to evaluate fixed-income securities.
Key Takeaways
- The PRICE function in Google Sheets is used to calculate the price of a bond or a Treasury bill.
- The formula for the PRICE function is:
- =PRICE(settlement, maturity, rate, yield, redemption, frequency, [basis])
Here,
- settlement is the date on which you plan to buy or sell the security.
- maturity is the date on which the security matures.
- rate is the annual coupon rate of the security.
- yield is the annual yield of the security.
- redemption is the redemption value of the security.
- frequency is the number of coupon payments per year.
- basis (optional): It is the day count basis to use for calculating the price.
- It is very useful when making investment decisions.
- For instance, if the calculated price of a bond is higher than its current market price, the bond could be a good investment opportunity as it is undervalued. However, if the calculated price is lower than the market price, the bond could be overvalued, and investors should consider selling it.
PRICE Syntax
To use the PRICE function in Google Sheets, you need to understand its exact syntax. The syntax for the PRICE function is:
=PRICE(settlement, maturity, rate, yield, redemption, frequency, [basis])
Let’s look at the parameters in detail.
- settlement: The settlement date of the security. It is the date on which you plan to buy or sell the security.
- maturity: The maturity date of the security. It is the date on which the security will mature.
- rate: The annual coupon rate for the security.
- yield: The annual yield of the security.
- redemption: The redemption value of the security.
- frequency: The number of coupon payments per year.
- basis (optional): The day count basis is used to calculate the price. If omitted, it defaults to 0 (Actual/Actual).
How to Use PRICE Function in Google Sheets?
Now that we understand the basics of the PRICE function let’s learn how to use it step by step. There are two ways to enter the PRICE function.
Using PRICE in Google Sheets is very easy. Following are the steps.
Entering the PRICE Function manually
Step 1: Open a new Google Sheets document or navigate to the cell where you want to display the result of the PRICE function and enter the details.

Step 2: To find the bond’s price, type the equal sign (=) followed by the function name PRICE. We do this in cell B7.

Step 3: Enter the required arguments within the parentheses. These include the settlement, maturity, rate, yield, redemption, frequency, and optional basis. Separate each argument with a comma. Here, we give the cell references as we have already entered the data in a table.

Step 4: Press Enter to evaluate the function and display the calculated price.

Thus, this is one method of entering the PRICE function in Google Sheets.
Understanding the parameters of the PRICE function in Google Sheets is important for accurate financial calculations. The settlement parameter represents the bond/security’s settlement date, while the maturity parameter represents its maturity date.
Using the Google Menu bar
- Choose the cell where you want to enter the PRICE formula.
2. Go to the menu bar and click on “Insert” -> “Function” -> “Financial” -> “PRICE”
3. Enter the required argument. Close the bracket and press the “Enter” key.
Examples
Though the formula looks complicated, we provide below a few examples to help you clearly understand what the parameters represent and how the function works. Here’s the first one.
Example #1
Now that we understand the basics of the PRICE function, the below example will teach us how to use it step by step. In this example, let us consider a scenario where you are analyzing a corporate bond with the following details.
- Settlement date: January 1, 2020
- Maturity date: December 31, 2025
- Coupon rate: 6%
- Yield: 5%
- Redemption value: $2000
- Coupon payments per year: 2
Now, let us input these parameters into the PRICE function. This function computes the price of the bond, helping you make informed investment decisions.
Step 1: Open a new Google Sheets document and enter the above details. Now, navigate to the cell where you want to display the result of the PRICE function.

Step 2: Type the equal sign (=) followed by the function name PRICE. Within the parentheses, enter the required arguments; here we enter the cell references as shown below. Separate each argument with a comma.

Step 3: Press Enter to get the result and display the calculated price.

Example #2
Now, consider a short-term investment instrument by calculating the price of a Treasury bill with the following details:
- Settlement date: February 1, 2023
- Maturity date: May 1, 2023
- Coupon rate: 0%
- Yield: 2%
- Redemption value: $8,000
- Coupon payments per year: 1
Utilizing the PRICE function, you can precisely determine the price of the Treasury bill, providing valuable insights into its current market value and potential returns.
Step 1: Enter the above details in a Google Sheets document. Now, enter the price function in cell B7.

Step 2: Type the function name PRICE and enter the required parameters.

Step 3: Press Enter to get the result and display the calculated price.

Example #3
Again, let us look at another price function in Google Sheets examples where we wish to calculate the price of a bond using the following details. The settlement date, which indicates the date you purchase the bond, is January 1, 2024, and the maturity date, the date the bond expires, is January 1, 2030. The coupon rate is 5%, and the yield, the expected return on the bond, is 4%. We have a redemption value of $1000, which is the amount you receive at maturity. The frequency is semi-annual.
Step 1: Copy all the details in a spreadsheet.

Step 2: Enter the following formula in Google Sheets. Here, we enter the values directly in the formula to show how they work the same way.
=PRICE(“2024-01-01”, “2029-01-01”, 0.05, 0.04, 1000, 2)

Step 3: Press Enter. Based on the input parameters, it will return the bond’s price per $1000 face value.

PRICE vs GOOGLEFINANCE functions
There are two similar functions in Google Sheets: PRICE and GOOGLEFINANCE.
The main difference between the PRICE function in Google Sheets and the GOOGLEFINANCE function is that the GOOGLEFINANCE function can retrieve many attributes, including PRICE.
PRICE:
- A real-time price quote. PRICE is a financial function in Google Sheets that helps calculate the price of a security, such as a bond or a treasury bill.
- It requires the following parameters:
- Settlement date
- Maturity date
- Coupon rate
- Yield
- Redemption value
- Coupon payments per year
- Basis
- The PRICE function is helpful for valuing fixed-income securities, such as bonds or Treasury bills. It helps you to determine the fair price of a security based on the characteristics and market conditions.
GOOGLEFINANCE:
- A function that can retrieve a variety of financial data, including stock prices, market indices, and other financial metrics.
- The parameters of GOOGLEFINANCE include:
- ticker: For example, “GOOGL” for Alphabet Inc. or “AAPL” for Apple Inc.
- attribute (optional): The specific data you want to retrieve. Example,
- “price”: Current price.
- “high”: Today’s high price.
- “low”: Today’s low price.
- “volume”: Volume of shares traded.
- “marketcap”: Market capitalization.
- You can also use “all” to get all available data.
- start_date
- end_date|num_days (optional)
- interval (optional)
- Using GOOGLEFINANCE is a powerful way to pull live and historical financial data directly into your Google Sheets for analysis and tracking!
Important Things to Note
- If the basis < 0 or is > 4, the PRICE function returns the #NUM! Error value.
- If settlement the settlement date is greater than or equal to the maturity, PRICE returns the #NUM! Error value.
- In all scenarios, the initial six parameters are essentially the same.
Frequently Asked Questions (FAQs)
Supplying incorrect arguments: The PRICE formula requires at least 5 arguments. Ensure you have typed all the required arguments in the correct order.
Invalid argument type: Provide the correct data type for all the arguments.
Incorrect date format: If you are getting a #VALUE! Error, make sure that the settlement and maturity dates are in date format.
Using an annual rate: The rate and yield arguments should be the periodic interest rates.
Incorrect frequency value: The frequency argument should be one of 1, 2, 3, 4, or 12, representing the number of coupon payments per year.
If the settlement or maturity date formats are not valid, PRICE returns the #VALUE! Error value.
• If the yield or rate is less than zero, you get the #NUM! Error value.
• If 0, PRICE returns, you get the #NUM! Error value.
• If the frequency is not a valid number like 1,2,4, etc, you get the #NUM! Error.
The “Frequency” is a required parameter. It indicates the number of coupon payments per year. For annual payments, the frequency is 1; for semi-annual payments, it is 2; and for quarterly payments, it is 4.
Download Template
This article must help understand Randomize list in Google Sheets with its formulas and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What Is PRICE Function in Google Sheets. We learn its syntax & how to use it to calculate the price of a security with examples. You can learn more from the following articles.
Leave a Reply