Use the following information for your bond: 11% coupon, Starting YTM of 11%, and 24 years to maturity. Assume a $1000 par value and annual coupon payments.

Create a column of increasing YTMs from 0% to 24% in increments of 1%.

Using a data table approach, calculate the price of the bond at each YTM.

Calculate the percentage price change in the bond based on changes in YTM. So, you would have a price change with an increase in YTM from 0% to 1%, and so on. Note: there will not be a price change for the 0% YTM.

Calculate the modified duration of the bond at the Starting YTM (11%), using the MDURATION function. You must use the bond functions for duration calculations. You can use a settlement date of September 24, 2015 and a maturity date of September 24, 2039.

Note: only calculate one value for duration based on the Starting YTMdo not calculate duration for the range of YTM created in Step 1 (above).

To the right of your calculations for the 24 year bond, calculate the predicted percentage price change of the bond (according to duration) at each level of YTM.

The formula for predicted percentage price change is:

So, for example, at the new yield of 0% and a Starting YTM of 11%, the change in YTM is (0%-11%), and the change in price is MDURATION *(0%-11%). So, if the modified duration is 7 (and its not), the change in price would be calculated as: -7*(-.11) = 77% (using cell references and not absolute values, of course).

Be sure to use absolute references for your MDURATION value and your starting YTM as you copy the formula down.

Now, calculate the predicted price to the right of your predicted percentage price change. Multiply (1+% Change in Price) by the bond price at 11%. The starting price is $1000, so the new price would be $1000*(1.77) = $1770.

Now plot the bond prices you obtained in Step 2 along with the predicted prices you calculated in Step 6 in a line graph. Be sure to label the axes, create a title, and use appropriate legend labels.