Easy Explanation Notes with Examples

PRICE function in Excel


If you purchased a security that pays periodic interest, you can calculate the price per $100 face value of that security by using the PRICE function in Excel.


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

The PRICE function has the following arguments:

settlement – Required. The security’s settlement date, it comes after the issuance date when the security is traded to the buyer.

maturity – Required. The expiry date of the security.

rate – Required. The annual coupon rate of the security.

yld – Required. The annual yield of the security.

redemption – Required. The redemption value per $100 face value of the security.

frequency – Required. The frequency of coupon payments per year. It must be one of the following:

For Frequency
Annual Payments1

[basis] – Optional. The day count basis that is used in the calculation. Available options are:

BasisDay count basis
0 or omittedUS (NASD) 30/360
4European 30/360


Suppose, a 10-year bond (with a redemption value of $100 on maturity) is issued on February 10, 2020. It pays 6.5% coupon annually and has an annual yield of 6.9%. If you purchased the bond on June 30, 2020, the price of the bond can be calculated as mentioned hereunder:

The bond with the above terms has the price of $97.204.

Function Errors

#VALUE! If the settlement or maturity date is not valid.
#NUM!If the annual yield or the coupon rate is smaller than zero.
#NUM!If the redemption value is equal to or smaller than zero.
#NUM!If frequency is any number other than 1, 2, or 4
#NUM!If the [basic] argument is either smaller than zero or greater than 4.
#NUM!If settlement ≥ maturity.

Related Functions

The PRICE function is a Financial function, it has the following similar functions:

PRICEDISC function calculates the price per $100 face value of a discounted security.

PRICEMAT function calculates the price per $100 face value of a security (e.g. a bond) that pays interest at maturity.

TBILLPRICE function calculates the price of a Treasury bill.

DOLLARDE function converts a dollar price, expressed as a fraction, into a dollar price, expressed as a decimal number.

Copyright © 2016 - 2018 Explainry.com | All Rights Reserved