Share
Explore BrainMass

# Excel - PV function

As an investor, you are considering an investment in the bonds of the Conifer Coal Company. The bonds, which pay interest semiannually, will mature in eight years, and have a coupon rate of 7.5% on a face value of \$1,000. Currently, the bonds are selling for \$900.

a. If your required return is 9% for bonds in this risk class, what is the highest price you would be willing to pay? (Use PV function)

b. What is the current yield of these bonds? If you hold the bonds for one year, what total rate of return will you earn? Why are these two numbers different?

c. What is the yield to maturity on these bonds if you purchase them at the current price? (Use RATE function)

d. If the bonds can be called in three years with a call premium of 4% of the face value, what is the yield to call on these bonds? (Use the RATE function)

e. Now assume that the settlement date for your purchase would be 7/30/2010, the maturity date is 7/30/2015, and the first call date is 7/30/2010. Using the PRICE and Yield functions, recalculate your answers to parts A, C, and D.

#### Solution Preview

a. If your required return is 9% for bonds in this risk class, what is the highest price you would be willing to pay? (Use PV function)

For the bond, future value is par, i.e., FV = 1000
The semiannual payment = FV * coupon rate / 2 = 1000 * 7.5% / 2 = \$37.5
Number of periods = 8*2 =16
return rate per period = 9%/2 = 4.5%
By a financial calculator, or EXCEL command (=-PV(4.5%,16,37.5,1000)), we can compute the present value: PV = 915.74
This is the highest price you should pay for the bond.

b. What is the current yield of these bonds? If you hold the bonds for one year, what total rate of return will you earn? Why are these two numbers different?

The current yield = coupon payment / current price = 1000*7.5% ...