Question

Consider three bonds, Bond Three, Bond Four, Bond Five, with the following characteristics:

            Feature                       Bond Three                Bond Four                  Bond Five

            Maturity                     10 years                      20 years                      15 years

            Coupon Rate              8%                              5%                              0%

            Coupon frequency     Semi-annual              Semi-annual               N/A

A. Calculate the value of each bond if the yield to maturity for each is 7 percent.

B. Using a spreadsheet program, calculate the value of each bond for each yield to maturity from 0 percent to 15 percent.

C. Using a spreadsheet program, graph the values of each bond for yields to maturity ranging from 0 percent to 15 percent on one graph.

D. What conclusions can you draw regarding the sensitivity of the bonds’ values to changes in the yield to maturity?

Answer 1

a)

The first part of the formula refers to the present value of coupon payments and second value to the present value of the par value.

So, Value of Bond Three = $107.11, Value of Bond Four = $78.64 and Value of Bond Five = $36.24

b)

Create a table with YTM values on the left hand side and the Value on the right hand side.

Go to Data-> What if analysis -> Data Table and select the YTM cell as the column input cell. Click OK.

Repeat this for all the bonds.

c)

d) Based on the plot, we can see that bond five is not very sensitive to change in YTM

On the other hand, Bond four is most sensitive to change in YTM. This is due to longer maturity

So, lesser coupon decreases the sensitivity of bond value to YTM changes and more maturity bonds are more sensitive to changes in YTM