Function: PRICE

by | Aug 20, 2019 | Excel Formulas

price function

The Excel PRICE function is a Financial formula that calculates and returns the price per $100 face value of a security that pays periodic interest. In this guide, we’re going to show you how to use the PRICE function and also go over some tips and error handling methods.

Supported versions

  • All Excel versions

PRICE Function Syntax

PRICE(settlement, maturity, rate, yld, redemption, frequency, [basis])

Arguments

settlement The settlement date of the security. The security settlement date is the date after the issue date when the security is traded to the buyer.
maturity The security's maturity date. The maturity date is the date when the security expires.
rate The security's annual coupon rate.
yld Annual yield of the security.
redemption The security's redemption value per $100 face value.
frequency

The number of coupon payments per year. For annual payments,

  • frequency = 1;
  • for semiannual, frequency = 2;
  • for quarterly, frequency = 4.
[basis]

Optional. The type of day count basis to use.

  • 0 or omitted: US (NASD) 30/360
  • 1: Actual/actual
  • 2: Actual/360
  • 3: Actual/365
  • 4: European 30/360

Example

The following example shows how to calculate the price per $100 face value of a security purchased on April 31st 2019, with a maturity date of February 2nd 2024, and a rate of 5%. The yield is 7% and the redemption value is $100. The payments will be made semi-annually by the US (NASD) 30/360 day count basis.

Download Workbook

Tips

  • Microsoft recommends using the DATE or other functions which can return a date serial number.
  • Excel keeps date and time values as numbers. Excel assumes that Jan 1st, 1900 is 1, and every subsequent date value is based on this. While whole numbers represent days, decimals represent time values. For example; 1/1/2018 is equal to 43101, and 12:00 is equal to 5.
  • Please see the Day count convention article in Wikipedia to learn more about [basis] types.
  • Settlement, maturity, frequency, and basis are truncated to integers.
  • Other price-related functions:
    • PRICEDISC returns the price per $100 face value of a discounted security.
    • PRICEMAT returns the price per $100 face value of a security that pays interest at maturity.
    • YIELD returns the yield on a security that pays periodic interest.
    • DURATION returns the Macauley duration for an assumed par value of $100.

Issues

  • If settlement or maturity are not valid dates, the PRICE function returns the #VALUE! error value.
  • PRICE function returns the #NUM! error value, when yld < 0 or if rate < 0
  • If redemption ≤ 0, PRICE returns the #NUM! error value.
  • PRICE returns the #NUM! error value, if frequency is any number other than 1, 2, or 4.
  • If [basis] < 0 or if [basis] > 4, PRICE returns the #NUM! error value.
  • If settlement ≥ maturity, PRICE returns the #NUM! error value.