PRICE Function in Google Sheets

Calculate the price of a security that pays periodic interest, based on expected yield.

PRICE Function

The PRICE function in Google Sheets calculates the price per $100 face value of a security that pays periodic interest, based on expected yield.

Syntax

PRICE(settlement, maturity, rate, yield, redemption, frequency, [day_count_convention])
  • settlement: The security’s settlement date.
  • maturity: The security’s maturity date.
  • rate: The security’s annual coupon rate.
  • yield: The security’s annual yield.
  • redemption: The security’s redemption value per $100 face value.
  • frequency: The number of coupon payments per year (1, 2, or 4).
  • day_count_convention: (Optional) The day count basis to use. Defaults to 0 (US (NASD) 30/360).

Examples

  1. Basic Bond Price Calculation

Calculate the price of a bond with semi-annual payments:

=PRICE(DATE(2023, 1, 1), DATE(2033, 1, 1), 0.05, 0.04, 100, 2)

This will output 107.54.

Notes

  • The result is expressed as a percentage of face value.
  • Different day count conventions affect the price.
  • YIELD: Calculate the yield of a security that pays periodic interest.
  • PRICEDISC: Calculate the price of a discounted security.