Microsoft Office Tutorials and References
In Depth Information
Using PRICE to Back into a Bond Price
PRICE to Back into a Bond Price
to Back into a Bond Price
If you know the yield for a bond, you can use PRICE to calculate the price
per $100 of face value.
The PRICE function returns the price per $100 face value of a security that
pays periodic interest. This function takes the following arguments:
settlement — This is the security ’ s settlement date, which is the
date on which you purchased the bond.
maturity — This is the security ’ s maturity date, which is the date
when the security expires.
rate — This is the security ’ s annual coupon rate.
yld — This is the security ’ s annual yield.
redemption — This is the security ’ s redemption value per $100 face
frequency — This is the number of coupon payments per year. For ex-
ample, use 2 for semiannual.
basis — This is the type of day count basis to use. For example, use 0
for U.S. bonds.
The settlement, maturity, frequency, and basisarguments are truncated to in-
tegers. If settlementor maturityis not a valid date, PRICE returns a #NUM!
error. If yldis less than 0 or if rateis less than 0, PRICE returns a #NUM!
error. If redemptionis less than or equal to 0, PRICE returns a #NUM! er-
ror. If frequencyis any number other than 1, 2, or 4, PRICE returns a #NUM!
error. If basisis less than 0 or if basisis greater than 4, PRICE returns a
#NUM! error. If settlementis greater than or equal to maturity, PRICE re-
turns a #NUM! error.
In Figure 13.17 , the yield for the bond exceeds the coupon rate. This indicates
that the price will be less than $100.