Problem 6-13 Consider the following bonds: Coupon Rate Maturity Bond (annual pay
ID: 2791357 • Letter: P
Question
Problem 6-13
Consider the following bonds:
Coupon Rate
Maturity
Bond
(annual payments)
(years)
A
0%
15
B
0%
10
C
4%
15
D
8%
10
a.
What is the percentage change in the price of each bond if its yield to maturity falls from 6% to 5%?
b.
Which of the bonds A–D is most sensitive to a 1% drop in interest rates from 6% to 5%? Which bond is the least sensitive?
Par value
$1,000
Old YTM
6%
New YTM
5%
a.
What is the percentage change in the price of each bond if its yield to maturity falls from 6% to 5%?
Bond
Coupon Rate
Maturity
Annual Coupon
Old Price
New Price
Percentage Change
Rank
A
0%
15
B
0%
10
C
4%
15
D
8%
10
b.
Which of the bonds A–D is most sensitive to a 1% drop in interest rates from 6% to 5%? Which bond is the least sensitive?
Bond
is the most sensitive to changes in bond yields.
Bond
is the least sensitive to changes in bond yields.
Requirements
1.
Start Excel – completed.
2.
In cell F23, by using relative and absolute cell references, calculate the annual coupon payment of bond A (1 pt.).
Note: Do not type a numeric value.
3.
To calculate the annual coupon payment of bonds B thru D, copy cell F23 and paste it onto cells F24:F26 (1 pt.).
Note: Do not type a numeric value.
4.
To calculate the price of bond A before the fall in the yield to maturity, you will use the function PV. In cell G23, by using the function PV and absolute and relative cell references, calculate the price of bond A before the fall in the yield to maturity (1 pt.).
Note: The output of the function in cell G23 is expected as a positive value. Use cell reference to the annual coupon payment from Step 2 in your calculations.
5.
To calculate the price of bonds B thru D before the fall in the yield to maturity, copy cell G23 and paste it onto cells G24:G26 (1 pt.).
6.
To calculate the price of bond A after the fall in the yield to maturity, you will use the function PV. In cell H23, by using the function PV and absolute and relative cell references, calculate the price of bond A after the fall in the yield to maturity (1 pt.).
Note: The output of the function in cell H23 is expected as a positive value. Use cell reference to the annual coupon payment from Step 2 in your calculations.
7.
To calculate the price of bonds B thru D after the fall in the yield to maturity, copy cell H23 and paste it onto cells H24:H26 (1 pt.).
8.
In cell I23, by using cell references, calculate the percentage change in price due to the drop in the yield to maturity of bond A (1 pt.).
9.
To calculate the percentage change in price due to the drop in the yield to maturity of bonds B thru D, copy cell I23 and paste it onto cells I24:I26 (1 pt.).
10.
You will rank the percentage change in price due to the drop in the yield to maturity of each bond by using the function RANK.EQ. In J23, rank the change in price due to the drop in the yield to maturity of bond A by using the function RANK.EQ and absolute and relative cell references (1 pt.).
Note: The function RANK.EQ returns the rank of a number in a list of numbers. Its size is relative to other values in the list; if more than one value has the same rank, the top rank of that set of values is returned. Use a value of 0 for the Order argument of the function RANK.EQ. Use absolute cell references for the Ref argument of the function RANK.EQ.
Problem 6-13
Consider the following bonds:
Coupon Rate
Maturity
Bond
(annual payments)
(years)
A
0%
15
B
0%
10
C
4%
15
D
8%
10
a.
What is the percentage change in the price of each bond if its yield to maturity falls from 6% to 5%?
b.
Which of the bonds A–D is most sensitive to a 1% drop in interest rates from 6% to 5%? Which bond is the least sensitive?
Par value
$1,000
Old YTM
6%
New YTM
5%
a.
What is the percentage change in the price of each bond if its yield to maturity falls from 6% to 5%?
Bond
Coupon Rate
Maturity
Annual Coupon
Old Price
New Price
Percentage Change
Rank
A
0%
15
B
0%
10
C
4%
15
D
8%
10
b.
Which of the bonds A–D is most sensitive to a 1% drop in interest rates from 6% to 5%? Which bond is the least sensitive?
Bond
is the most sensitive to changes in bond yields.
Bond
is the least sensitive to changes in bond yields.
Requirements
1.
Start Excel – completed.
2.
In cell F23, by using relative and absolute cell references, calculate the annual coupon payment of bond A (1 pt.).
Note: Do not type a numeric value.
3.
To calculate the annual coupon payment of bonds B thru D, copy cell F23 and paste it onto cells F24:F26 (1 pt.).
Note: Do not type a numeric value.
4.
To calculate the price of bond A before the fall in the yield to maturity, you will use the function PV. In cell G23, by using the function PV and absolute and relative cell references, calculate the price of bond A before the fall in the yield to maturity (1 pt.).
Note: The output of the function in cell G23 is expected as a positive value. Use cell reference to the annual coupon payment from Step 2 in your calculations.
5.
To calculate the price of bonds B thru D before the fall in the yield to maturity, copy cell G23 and paste it onto cells G24:G26 (1 pt.).
6.
To calculate the price of bond A after the fall in the yield to maturity, you will use the function PV. In cell H23, by using the function PV and absolute and relative cell references, calculate the price of bond A after the fall in the yield to maturity (1 pt.).
Note: The output of the function in cell H23 is expected as a positive value. Use cell reference to the annual coupon payment from Step 2 in your calculations.
7.
To calculate the price of bonds B thru D after the fall in the yield to maturity, copy cell H23 and paste it onto cells H24:H26 (1 pt.).
8.
In cell I23, by using cell references, calculate the percentage change in price due to the drop in the yield to maturity of bond A (1 pt.).
9.
To calculate the percentage change in price due to the drop in the yield to maturity of bonds B thru D, copy cell I23 and paste it onto cells I24:I26 (1 pt.).
10.
You will rank the percentage change in price due to the drop in the yield to maturity of each bond by using the function RANK.EQ. In J23, rank the change in price due to the drop in the yield to maturity of bond A by using the function RANK.EQ and absolute and relative cell references (1 pt.).
Note: The function RANK.EQ returns the rank of a number in a list of numbers. Its size is relative to other values in the list; if more than one value has the same rank, the top rank of that set of values is returned. Use a value of 0 for the Order argument of the function RANK.EQ. Use absolute cell references for the Ref argument of the function RANK.EQ.
Explanation / Answer
a. What is the percentage change in the price of each bond if its yield to maturity falls from 6% to 5%? Bond Coupon Rate Maturity Annual Coupon Old Price New Price Percentage Change Rank A 0.00% 15 $0.00 $417.27 $481.02 15.28% 1 B 0.00% 10 $0.00 $558.39 $613.91 9.94% 3 C 4.00% 15 $40.00 $805.76 $896.20 11.23% 2 D 8.00% 10 $80.00 $1,147.20 $1,231.65 7.36% 4 Annual Coupon = $1000 x Coupon Rate Old price = PV(6%,maturity, - annual coupon ,-1000) New price = PV(5%,maturity,-annual coupon ,-1000) b. Which of the bonds A–D is most sensitive to a 1% drop in interest rates from 6% to 5%? Which bond is the least sensitive? Bond A is the most sensitive to changes in bond yields. Bond D is the least sensitive to changes in bond yields