Question

CHAPTER 6 ASSIGNMENT Ch6Amnt
Let's use the example in your Chapter 6 Valuing Bonds Guide spreadsheet from the Resource section for this chapter, to do a few Questions and Problems. You will see that problems and questions #3, #6, #4 and #5 from the textbook are solved for you in the Valuing Bonds Guide as follows:

#3 addresses PV where we solve for the way to Price a bond
#6 - same - we solve for PV to get the current Price of a bond
#4 addresses solving for the Yield to Maturity
#5 is solved on that spreadsheet to help understand the Payment, or coupon payment, from which we derive the coupon rate, which is a percentage, always on a 1000 par

With this in mind, let's setup an Excel spreadsheet which you will upload when complete, and solve a couple of problems that are like #s 3 and 6. Let's just assume calculation on annual payments of interest rather than semi-annual, to simplify. Set them up the way you see in the guidelines- then solve!

1. You are given the following data: A bond has a coupon rate of 7.225, the latest yield reported of 6.23 and 8 years to maturity. What is the price of the bond?

2. Let's do another for good measure. A bond has a coupon rate of 6.95, the latest yield reported of 8.7 and 16 years to maturity. What is the price of this bond?

Now let's solve 2 for Yield to Maturity, as in the example for #4 from your Chapter 6 Valuing Bonds Guide. NOTE: You MUST carry these rates out to 4 decimal places for precision!!!

3. The years to maturity on Bond Beta are 12, the coupon rate is 4.5%, and the bond current sells for $1,032.04. What is the current YTM?

4. Bond Gamma will mature 18 years from now, pays a coupon rate of 8.25%, and currently is on the market for $998.14. What is the current YTM?

Finally, let's solve for the coupon rate, using the example setup for #5 for PMT from your Chapter 6 Valuing Bonds Guide spreadsheet. Remember to use your dollar results to interpret the percentage (to 4 decimal places - as your example shows!

5. A bond issued by Beverly Hillbilly Company currently sells for $1,040, has 15 years to maturity, and shows a current YTM of 5.76%. What is the coupon rate on the bond?

6. A bond for Gilligan's Famous Island Adventure, Inc., has a current market price of $974.32, matures in 5 years, and has a current YTM or 7.89%. What is the coupon rate on this bond?

Answer 1

a) Coupon rate = 7.225%, Yield to maturity = 6.23%, No of years to maturity = 8, Par value = 1000

Coupon = Coupon rate x par value = 7.225% x 1000 = 72.25

We will use pv function in excel to calculate price of the bond

Formula to be used in excel: =pv(rate,nper,-pmt,-fv)

                                          =pv(6.23%,8,-72.25,-1000)

Calculating price of bond
Par value (fv)	1000
Coupon (pmt)	72.25
Yield to maturity (rate)	6.23%
Years to maturity (nper)	8
Price (pv)	1061.23

Using pv function in excel, we get price of bond = 1061.23

b) Coupon rate = 6.95%, Yield to maturity = 8.7%, No of years to maturity = 16, Par value = 1000

Coupon = Coupon rate x par value = 6.95% x 1000 = 69.50

We will use pv function in excel to calculate price of the bond

Formula to be used in excel: =pv(rate,nper,-pmt,-fv)

                                          =pv(8.70%,16,-69.50,-1000)

Calculating price of bond
Par value (fv)	1000
Coupon (pmt)	69.50
Yield to maturity (rate)	8.7%
Years to maturity (nper)	16
Price (pv)	851.80

Using pv function in excel we get price of bond = 851.80

c) Coupon rate = 4.50%, Price of bond = 1032.04, No of years to maturity = 12, Par value = 1000

Coupon = Coupon rate x par value = 4.50% x 1000 = 45

We will use rate function in excel to calculate Yield to maturity of the bond

Formula to be used in excel: =rate(nper,-pmt,pv,-fv)

                                         =rate(12,-45,1032.04,-1000)

Calculating Yield to maturity of Bond Beta
Current price (pv)	1032.04
Par value (fv)	1000
No of years to maturity (nper)	12
Coupon (pmt)	45
Yield to maturity (rate)	4.1555%

Using rate function in excel, we get yield to maturity of bond Beta = 4.1555%

d) Coupon rate = 8.25%, Price of bond = 998.14, No of years to maturity = 18, Par value = 1000

Coupon = Coupon rate x par value = 8.25% x 1000 = 82.50

We will use rate function in excel to calculate Yield to maturity of the bond

Formula to be used in excel: =rate(nper,-pmt,pv,-fv)

                                         =rate(18,-82.50,998.14,-1000)

Calculating Yield to maturity of Bond Gamma
Current price (pv)	998.14
Par value (fv)	1000
No of years to maturity (nper)	18
Coupon (pmt)	82.50
Yield to maturity (rate)	8.2702%

Using rate function in excel, we get YTM of bond Gamma = 8.2702%

e) YTM = 5.76%, Price of bond = 1040, No of years to maturity = 15, Par value = 1000

First we will use pmt function in excel to calculate coupon of the bond

Formula to be used in excel: =pmt(rate,nper,-pv,fv)

                                         =pmt(5.76%,15,-1040,1000)

Calculating Coupon of the bond
Current price (pv)	1040
Par value (fv)	1000
No of years to maturity (nper)	15
Yield to maturity (rate)	5.76%
Coupon (pmt)	61.654

Using pmt function excel, we get coupon = 61.654

Coupon rate = Coupon / par value = 61.654/1000 = 0.061654 = 6.1654%

f) YTM = 7.89%%, Price of bond = 974.32, No of years to maturity = 5, Par value = 1000

First we will use pmt function in excel to calculate coupon of the bond

Formula to be used in excel: =pmt(rate,nper,-pv,fv)

                                         =pmt(7.89%,5,-974.32,1000)

Calculating Coupon of the bond
Current price (pv)	974.32
Par value (fv)	1000
No of years to maturity (nper)	5
Yield to maturity (rate)	7.89%
Coupon (pmt)	72.487

Using pmt function in excel we get coupon = 72.487

Coupon rate = coupon / par value = 72.487 / 1000 = 0.072487 = 7.2487%