What is the PRICE Excel Function Used for?

 

 

Now we are going to learn all about formulas and functions. This is the best way to learn them:

 

PRICE

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

 

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

 

The PRICE function syntax has the following arguments:

  • Settlement Required. The security’s settlement date. The security settlement date is the date after the issue date when the security is traded to the buyer.
  • Maturity Required. The security’s maturity date. The maturity date is the date when the security expires.
  • Rate Required. The security’s annual coupon rate.
  • Yld Required. The security’s annual yield.
  • Redemption Required. The security’s redemption value per $100 face value.
  • Frequency Required. 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.
    BASIS DAY COUNT BASIS
    0 or omitted US (NASD) 30/360
    1 Actual/actual
    2 Actual/360
    3 Actual/365
    4 European 30/360

 

Remarks

 

  • Microsoft Excel stores dates as sequential serial numbers so they can be used in calculations. By default; January 1; 1900 is serial number 1; and January 1; 2008 is serial number 39448 because it is 39;448 days after January 1; 1900.
  • The settlement date is the date a buyer purchases a coupon; such as a bond. The maturity date is the date when a coupon expires. For example; suppose a 30-year bond is issued on January 1; 2008; and is purchased by a buyer six months later. The issue date would be January 1; 2008; the settlement date would be July 1; 2008; and the maturity date would be January 1; 2038; which is 30 years after the January 1; 2008; issue date.
  • Settlement; maturity; frequency; and basis are truncated to integers.
  • If settlement or maturity is not a valid date; PRICE returns the #VALUE! error value.
  • If yld < 0 or if rate < 0; PRICE returns the #NUM! error value.
  • If redemption <= 0; PRICE returns the #NUM! error value.
  • If frequency is any number other than 1; 2; or 4; PRICE returns the #NUM! error value.
  • If basis < 0 or if basis > 4; PRICE returns the #NUM! error value.
  • If settlement => maturity; PRICE returns the #NUM! error value.
  • PRICE is calculated as follows:where:
    • DSC = number of days from settlement to next coupon date.
    • E = number of days in coupon period in which the settlement date falls.
    • N = number of coupons payable between settlement date and redemption date.
    • A = number of days from beginning of coupon period to settlement date.

 

Interactive Example

Change the white cells data and see the result in the yellow cell.