Microsoft Office Tutorials and References

In Depth Information

**Using PRICE to Back into a Bond Price**

Using

Using
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.

Syntax

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

The PRICE function returns the price per $100 face value of a security that

pays periodic interest. This function takes the following arguments:

•
settlement

settlement
—
This is the security
’
s settlement date, which is the

date on which you purchased the bond.

•
maturity

maturity
—
This is the security
’
s maturity date, which is the date

when the security expires.

•
rate

rate
—
This is the security
’
s annual coupon rate.

•
yld

yld
—
This is the security
’
s annual yield.

•
redemption

redemption
—
This is the security
’
s redemption value per $100 face

value.

•
frequency

frequency
—
This is the number of coupon payments per year. For ex-

ample, use 2 for semiannual.

•
basis

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.