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:
[basis] – Optional. The day count basis that is used in the calculation. Available options are:
|Basis||Day count basis|
|0 or omitted||US (NASD) 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.
|#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.|
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.