Share
Explore BrainMass

Bond Valuation- Yield to maturity and yield to call

7-8 Bond yields:
A 10-year, 12 percent semiannual coupon bond, with a par value of $1,000, may be called in 4 years at a call price of $1,060. The bond sells for $1,100. (Assume that the bond has just been issued.)
a. What is the bond's yield to maturity?
b. What is the bond's current yield?
c. What is the bond's capital gain or loss yield?
d. What is the bond's yield to call?

7-22 Bond Valuation
Rework Problem 7-8 using a spreadsheet model. After completeing parts a through d, answer the following related questions.
e. How would the price of the bond be affected by changing interest rates? Hint: Conduct a sensitivity analysis of price to change in the yield to maturity, which is also the going market interest rate of interest falls below the coupon rate. That is an oversimplification, but assume it anyway for purposes of this problem.)

f. Now assume that the date is 10/25/2002. Assume further that our 12 percent, 10-year bond was issued on 7/01/2002, is callable on 7/01/2006 at $1,060, will mature on 6/30/2012, pays interest semiannually (January 1 and July 1), and sells for $1,100. Use your spreadsheet to find (1) the bond's yield to maturity and (2) its yield to call.

I need help with problem set 7-8 questions a through d in excel and problem set 7-22 e and f. No need to re-write the questions. Just show Question "a", Question "b"...Question "f" with excel.

Thanks.

Attachments

Solution Preview

Please see the excel file for complete answers:
SPREADSHEET PROBLEMS

7-8 Bond yields
A 10-year, 12 percent semiannual coupon bond, with a par value of $1,000, may be called in 4 years at a call price of $1,060. The bond sells for $1,100. (Assume that the bond has just been issued.)

a. What is the bond's yield to maturity?

Yield to maturity

Yield to maturity can be calculated using Excel worksheet function RATE

Data
No of years to maturity= 10
Coupon rate= 12.00%
Face value= $1,000
Frequency = S Semi annual coupon payments
Redemption (Maturity) value = $1,000
Price of the bond= $1,100.00 (Given)
Interest payment per year= $120.00 =12.% x 1000
Interest payment per period= $60.00 =120/2
No of Periods =n= 20 =2x10

Yield= 10.37% (Using EXCEL Function RATE)

=2x RATE(20,60,-1100,1000)

We multiply by 2 as the rate calculated is for semiannual period

Yield can also be calculated using the approximation formula:

Coupon @ 12% = 120
Par /Face value= 1000
Redemption value= $1,000
Maturity= 10 years
Price= $1,100.00

Therefore , yield= 10.48% =(120+(1000-1100)/10)/(0.5*(1000+1100))

Answer: YTM= 10.37%

b. What is the bond's current yield?

Current yield = annual coupon / current price
Annual Coupon = $120.00
Current Price= $1,100.00

Current yield: Annual coupon / Current price= 10.91% =120/1100

Answer: Current yield= 10.91%

c. What is the bond's capital gain or loss yield?

The question has not specified what is the period for which the capital gain or loss has to be calculated.
Let us assume that one year capital gain/ loss has to be calculated.

We calculate the price next year using YTM= 10.37%

Price can be calculated using excel worksheet function ...

Solution Summary

Yield to maturity and yield to call have been calculated both manually and using Excel spreadsheet functions. The solution also conducts a sensitivity analysis of bond price to change in the yield to maturity.

$2.19