Google Sheets PRICE Function: Syntax, Examples & Tips

settlement:
The date on which the security is purchased or settled. This is the date when the buyer takes ownership of the security.

maturity:
The date on which the security will mature, meaning the date when the principal amount is due to be paid back to the investor.

rate:
The annual coupon rate of the security, expressed as a decimal. This is the interest rate that the issuer pays to the bondholder.

yield:
The annual yield of the security, expressed as a decimal. This represents the return an investor expects to earn if the bond is held until maturity.

redemption:
The redemption value of the security, typically the face value or par value that will be paid back at maturity.

frequency:
The number of coupon payments made per year. This can be 1 for annual payments, 2 for semi-annual payments, or 4 for quarterly payments.

[day_count_convention]:
An optional parameter that specifies the day count convention to use for calculating the number of days between dates. Common conventions include 0 (US (NASD) 30/360), 1 (Actual/Actual), 2 (Actual/360), and others.

What is the PRICE Function?

The PRICE function in Google Sheets is a financial function used to calculate the price per $100 face value of a security that pays periodic interest. This function is particularly useful for investors and financial analysts who need to evaluate the price of bonds or other fixed-income securities based on their characteristics, such as the settlement date, maturity date, coupon rate, and yield.

In simpler terms, the PRICE function helps you determine how much you would pay for a bond given its specific parameters.

A Practical Example

Imagine you are evaluating a bond investment with the following characteristics:

• Settlement Date: January 1, 2023
• Maturity Date: January 1, 2033
• Coupon Rate: 5%
• Yield: 4%
• Frequency: 2 (indicating semiannual payments)
• Basis: 0 (indicating the US (NASD) 30/360 day count convention)

PRICE Formula

To calculate the price of the bond, you would use the PRICE function as follows:

=PRICE("2023-01-01", "2033-01-01", 0.05, 0.04, 2, 0)

In this formula:

• "2023-01-01" is the settlement date.
• "2033-01-01" is the maturity date.
• 0.05 is the coupon rate (5%).
• 0.04 is the yield (4%).
• 2 indicates that interest is paid semiannually.
• 0 indicates the day count basis.

Result of the Formula

When you apply the PRICE formula, it will return the price per $100 face value of the bond. For example, the output might look like this:

This means that you would pay $102.50 for a bond with a face value of $100.

Why Use PRICE?

The PRICE function is beneficial because it allows investors to quickly assess the value of bonds based on their characteristics. This can aid in making informed investment decisions and comparing different bond options. Additionally, it simplifies the calculations that would otherwise require complex financial modeling.

Key Takeaways:

• PRICE: A financial function that calculates the price of a bond based on its characteristics.
• Investment Analysis: Useful for evaluating bond investments and making informed decisions.
• Simplified Calculations: Eliminates the need for complex financial formulas, making it accessible for users at all levels.
• Common Use Cases: Ideal for financial analysts, investors, and anyone involved in fixed-income securities.

The PRICE function is an essential tool for anyone working with bonds, providing a straightforward way to assess their value and make informed investment choices.

Happy investing!