Excel PRICE Function
PRICE is an Excel function that calculates the price of a bond. A bond’s price is determined by discounting the bond’s future cash flows, which comprise of periodic coupon payments and/or redemption value, using the bond’s yield (termed as YLD) over the remaining term of the bond.
The periodic coupon payments are based on the coupon RATE, the annual percentage rate at which the bond pays periodic interest payments and payment FREQUENCY, the number of coupon payments per year.
Price of a bond is expressed by the following algebraic function:
$$ \text{Price}=\text{Redemption} \times\frac{\text{Rate}}{\text{Frequency}}\times\frac{\text{1}-{(\text{1}+\frac{\text{Yld}}{\text{2}})}^{-\text{n}}}{\frac{\text{Yld}}{\text{2}}}+\frac{\text{Redemption}}{{(\text{1}+\frac{\text{Yld}}{\text{2}})}^\text{n}} $$
Syntax
PRICE function syntax is:
PRICE(settlement, maturity, rate, redemption, frequency, [basis])
Excel PRICE function works out a bond’s price per $100 face value of a bond given the bond’s
SETTLEMENT is the date on which we intend to work out the price (i.e. the purchase/pricing date), MATURITY (date) is the date on which the bond expires and the principal amount is paid back by the issuer to the bond-holder, (coupon) RATE is the annual interest rate at which coupon payments are made , yield referred to as YLD is the annual market interest rate representative of the risk of the bond, REDEMPTION (value) is the value per $100 of the principal amount that is paid back at the redemption date, FREQUENCY is the number of coupon payments per year and (day-counting) [BASIS].
Example
The following screenshot illustrates how PRICE function can be used to price a bond:
Since the PRICE function works out the price per $100 par value of a bond, we have manually worked out the price per $1,000 of par value by dividing the result of the PRICE function by the $100 par value and multiplying it with the actual par value of $1,000.
by Obaidullah Jan, ACA, CFA and last modified on