Question

Use the following information for your bond: 12% coupon, Starting YTM of 12%, and 20 years to maturity. Assume a $10,000 par value and annual coupon payments.

1. Create a column of increasing YTM’s from 0% to 25% in increments of 1%.

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

3. Calculate the modified duration of the bond at the Starting YTM, using the MDURATION function. You must use the bond functions for duration calculations. You can use a settlement date of May 30, 2018 and a maturity date of May 30, 2038. ? Note: only calculate one value for duration based on the Starting YTM…do not calculate duration for the range of YTM created in Step 1 (above).

4. Calculate the predicted percentage price change of the bond (according to duration) at each level of YTM.

So, if for example, the Starting YTM was 6% and the NEW YTM was 0%, the change in YTM is (0%-6%), and the change in price is –MDURATION *(0%-6%). So, if the modified duration is 7 (and it’s not), the change in price would be calculated as: -7*(-.06) = 42.50%. The Starting YTM will remain the same for each row’s predicted % change—you are determining what the percent change in price is from 6% to a new YTM (0%, 1%, etc.) Of course, cell references need to be used for all calculations. Other than the “0%” starting point, all other information should be entered via cell references.

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

5. Calculate the predicted price to the right of your predicted percentage price change. Multiply (1+Predicted % Change) by the bond price at the Starting YTM. If the starting price is $1,000, the predicted price (according to duration) would be $1,000*(1.77) = $1,770. The value here should be in dollars.

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

Answer 1

Ans 1 to 5 is given in following table.

Coupon	12%
Redemptoion Value	1000
Settlement date	6/14/2018
Maturity Date	6/14/2038
Nper	20
1	2	3	4	5
YTM	Bond Price	Modified Duration	Change in Predicted Price	Predicted Price
0%	$3,400.00	13.29	159.53%	$2,595.29
1%	$2,985.01	12.74	140.15%	$2,401.51
2%	$2,635.14	12.19	121.93%	$2,219.33
3%	$2,338.97	11.65	104.88%	$2,048.80
4%	$2,087.23	11.12	88.99%	$1,889.89
5%	$1,872.35	10.61	74.24%	$1,742.45
6%	$1,688.20	10.10	60.62%	$1,606.21
7%	$1,529.70	9.62	48.08%	$1,480.85
8%	$1,392.73	9.15	36.59%	$1,365.92
9%	$1,273.86	8.70	26.09%	$1,260.94
10%	$1,170.27	8.27	16.54%	$1,165.36
11%	$1,079.63	7.86	7.86%	$1,078.58
12%	$1,000.00	7.47	0.00%	$1,000.00
13%	$929.75	7.10	-7.10%	$928.99
14%	$867.54	6.75	-13.51%	$864.92
15%	$812.22	6.43	-19.28%	$807.20
16%	$762.85	6.12	-24.48%	$755.23
17%	$718.61	5.83	-29.15%	$708.46
18%	$678.84	5.56	-33.36%	$666.36
19%	$642.94	5.31	-37.15%	$628.46
20%	$610.43	5.07	-40.57%	$594.30
21%	$580.90	4.85	-43.65%	$563.48
22%	$553.97	4.64	-46.44%	$535.62
23%	$529.35	4.45	-48.96%	$510.40
24%	$506.77	4.27	-51.25%	$487.50
25%	$486.00	4.10	-53.33%	$466.68