Answer the following question:
The Calculations must be done on Excel with actual calculations in the cells.
Question 1:
Using a Spreadsheet to Calculate Yield to Maturity. What is the yield to maturity on the following bonds; all have a maturity of 10 years, a face value of $1,000, and a coupon rate of 9 percent (paid semiannually). The bonds' current market values are $945.50, $987.50, $1,090.00, and $1,225.875, respectively
| Market value |
Total Payments |
Periodic coupon Payment
|
Face Value |
The yield to maturity will be
|
| 945.50 |
10 x 2 =20
|
1,000(.09)/2=45
|
$1,000 |
9.87% |
| 987.50 |
20 |
45 |
1,000 |
9.19 |
|
1,090.00
|
20
|
45
|
1,000
|
7.69
|
| 1,225.875 |
20 |
45 |
1,000 |
5.97 |
Question 2:
Calculate the yield to maturity on the following bonds.
a. A 9 percent coupon (paid semiannually) bond, with a $1,000 face value and 15 years remaining to maturity. The bond is selling at $985.
b. An 8 percent coupon (paid quarterly) bond, with a $1,000 face value and 10 years remaining to maturity. The bond is selling at $915.
c. An 11 percent coupon (paid annually) bond, with a $1,000 face value and 6 years remaining to maturity. The bond is selling at $1,065.
Question 3:
A stock you are evaluating just paid an annual dividend of $2.50. Dividends have grown at a constant rate of 1.5 percent over the last 15 years and you expect this to continue.
a. If the required rate of return on the stock is 12 percent, what is its fair present value?
b. If the required rate of return on the stock is 15 percent, what is its expected price four years from today?
Question 4:
Consider the following two banks:
Bank 1 has assets composed solely of a 10-year, 12 percent coupon, $1 million loan with a 12 percent yield to maturity. It is financed with a 10-year, 10 percent coupon, $1 million CD with a 10 percent yield to maturity.
Bank 2 has assets composed solely of a 7-year, 12 percent, zero-coupon bond with a current value of $894,006.20 and a maturity value of $1,976,362.88. It is financed by a 10-year, 8.275 percent coupon, $1,000,000 face value CD with a yield to maturity of 10 percent.
All securities except the zero-coupon bond pay interest annually.
a. If interest rates rise by 1 percent (100 basis points), how do the values of the assets and liabilities of each bank change?
b. What accounts for the differences between the two banks' accounts?
Question 5:
What is the duration of a five-year, $1,000 Treasury bond with a 10 percent semiannual coupon selling at par? Selling with a yield to maturity of 12 percent? 14 percent? What can you conclude about the relationship between duration and yield to maturity? Plot the relationship. Why does this relationship exist?