This guide will explain how to use the PRICE function in Google Sheets.

##### Table of Contents

Securities such as US Treasury bonds are popular investments because of their low risk and periodic interest payments to the investor in the form of coupon payments.

You can use the built-in `PRICE`

function in Google Sheets to calculate the price of the security before purchasing it. The output of the `PRICE`

function is the price per $100 face value of a security that pays periodic interest.

In this guide, we will provide a step-by-step tutorial on how to use the `PRICE`

function to determine the value of a security that pays periodic interest. We will also cover a few concrete examples of the function in use.

**The Anatomy of the PRICE Function**

The syntax of the `PRICE`

function is as follows:

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

Let’s look at each argument to understand how to use the `PRICE`

function.

**settlement**refers to the settlement date of the given security. This is the date when the security is delivered to the buyer.- The
**maturity**parameter is the end date of the security. **rate**refers to the annualized rate of interest of the security.**yield**is the expected annual yield of the security**redemption**refers to the redemption value of the security.**frequency**refers to the number of interest payments made per year.**day_count_convention**is an optional argument allowing you to choose the day-count method to use.- A value of 0 indicates the US (NASD) 30/360 standard that assumes months are 30 days long and a year is exactly 360 days.
- A value of 1 indicates the Actual/Actual standard where the actual number of days is used
- A value of 2 indicates the Actual/360 method which calculates using the actual number of days but assumes a 360-day year
- A value of 3 indicates the Actual/365 method which calculates using the actual number of days and assumes a 365-day year.
- A value of 4 indicates the European 30/360 method which calculates dates using a 30-day month and a 360-day year with adjustments for European financial conventions.

**A Real Example of Using the PRICE Function in Google Sheets**

Let’s explore a simple example where we can use the `PRICE`

function to calculate the price of a security paying periodic interest.

Suppose you have a bond with the following details:

- a settlement date of
**January 5th, 2023** - a maturity date of
**December 31, 2033** - an annual rate of
**5.5%** - an annual yield of
**6.5%** - and a redemption value of
**$100**

Given that the bond has a face value of **$10,000** and pays semi-annual payments to the investor, how much is the bond’s price?

In the table above, we’ve created a table that lists all the data we need to compute for the price of the bond. Since semi-annual payments are paid twice a year, we’ll input a value of 2 for the frequency.

We’ll input these details as arguments to our `PRICE`

function to determine the price of our bond. We can use the following formula:

=PRICE(B1,B2,B3,B4,B5,B6)

The function reveals that our bond is priced at 92.23% of the face value. Since the optional **day_count_convention** field is not filled, our `PRICE`

function will use the 30/360 method by default.

To determine the actual dollar value of the bond, we can use the following formula:

=B9/100 * B1

The formula above divides the result of the `PRICE`

function by 100 to create a percentage value. We’ll multiply this percentage by the face value of our bond to find the actual dollar value.

Click on the link below to create your own copy of our examples.

Head to the next section to read our step-by-step tutorial on how to create a weighted sales pipeline.

**How to Use the PRICE Function in Google Sheets**

- Create a table that lists the required information you need to calculate the price of the security. The settlement date and maturity date must be valid dates. The annual rate and annual yield must be a percentage and the frequency value must be either 1,2, or 4.

To ensure that the dates are valid, we recommend using the`DATE`

function to input the settlement and maturity dates.

- Type the
`PRICE`

function and enter the arguments starting from the settlement date up to the frequency value. In this example, we’ll use the formula =**PRICE(B2,B3,B4,B5,B6,B7)**.

- Hit the
**Enter**key to evaluate the`PRICE`

function.

- To get the dollar value of the bond, divide the result of the
`PRICE`

function by 100 and multiply by the bond’s face value.

These are all the steps you need to use the `PRICE`

function to determine the value of a bond with periodic payments.

To learn more about using Google Sheets for financial securities, you can read our post on how to determine the price of discount securities.

That’s all for this guide! Check out our library for more spreadsheet resources, tips, and tricks!