Project 1: Bond Valuation, Duration, Money Market, and Bond Market
1
Project 1: Bond Valuation, Duration, Money Market, and Bond Market
Fall 2017 (Total points = 93 pts.)
Problem 1: (39 pts.) The quotes on December 30, 2XX1 for two Treasury securities are shown below. These securities are
non callable and pay interest semiannually. Assume an interest payment was just paid on the bond and
note (i.e., there is no accrued interest). The face value of each security is $1,000.
December 30, 2XX1:
Maturity Coupon Bid Asked Chg Asked Yield
12/30/2XX3 1.00 99:2425 99:2475 -0:12 ???????
T-Note
Maturity Coupon Bid Asked Chg Asked Yield
12/30/2X31 5.375 135:1500 135:1700 unch ???????
T-Bond
For your information: Treasury bonds and notes are quoted in 32nds. That is, a quote of 101:1650 is 101
and 16.50/32 = 101.515625% x $1,000 = a price of $1,015.15625 or $1,015.16.
Part A: (19 pts.) Using the above information, for each security perform the following in EXCEL:
1. Calculate the yield to maturity (i.e., the Asked Yield or BEY). This should be an annual rate.
Round to 4 decimal places. Rates are expressed as a %. (4 pts.)
2. Assume the yield to maturity rises a full 1 percent. Calculate the new asked price. (2 pts.) 3. As I did in your notes, calculate the security’s’ Macaulay duration (in years) using the YTM
calculated in #1 (round to 4 decimal places). (4 pts.)
a. Show that you used the Duration function in Excel to check your answer. (2 pts.) 4. As I did in your notes, calculate the security’s modified duration (round to 4 decimal places).
(2 pts.) a. Show that you used the Modified duration function in Excel to check your answer.
(1 pt.) 5. Assuming the YTM of the security increases a full 1 percent,
Determine the “duration-estimated” new price. Compare with your results in #2. Explain
what has happened. (4 pts.)
Project 1: Bond Valuation, Duration, Money Market, and Bond Market
2
Part B: (20 pts.) Using the above information, perform the following in EXCEL:
1. Complete the following chart, showing the relationship between market interest rates and bond
prices, for each security: (4 pts.)
Bond Prices
rm 1% 2yr. T-Note 5.375% 30yr. T-Bond
0.00%
1%
1.1149%
2%
3%
3.4617%
4%
5%
5.375%
6%
7%
8%
9%
10%
11%
12%
13%
14%
15%
16%
17%
18%
19%
20%
2. Prepare a graph showing the bond/note values at varying market rates of interest for the two
securities. (6 pts.)
Here is a sample graph for two securities: a 2-yr, 4% T-note with a current yield of 3%, and a 20-
yr, 5% T-bond with a current yield of 7%. Your graph should look similar.
Project 1: Bond Valuation, Duration, Money Market, and Bond Market
3
3. What can you say about the price relationships and duration between these two securities? (2
pts.)
4. For the 30-yr, 5.375% T-bond, construct a tangent to the yield curve. The tangent line should be tangent to (i.e., touch) the yield curve at the YTM data point. To do this, find the modified
duration estimate of the new price at 0%, 3.4617%, and 8%. (6 pts.)
Here is a sample graph for a 20-yr, 5% T-bond with a current yield of 7%. The modified duration
estimate of the new price was found at 0%, 7%, and 14%. Your graph should look similar.
5. What can you say about the duration, the tangent line, and the price-yield curve? (2 pts.)
Project 1: Bond Valuation, Duration, Money Market, and Bond Market
4
Problem 2: (29 pts.) On 1/5/2XX1, Joiner Bank purchased the following securities:
U.S. T-bill
Maturity Bid Asked Chg Asked Yield
92 days 1.020 1.010 -.013 ?????
Corporate Bond
Issuer Coupon Maturity Price YTM(%)
Allstate Corp 4.500 1/5/2X16 109.75 ?????
Interest paid quarterly.
Part A: (15 pts.) Using the above information, for each security perform the following in EXCEL:
1. Calculate the yield to maturity (i.e., the Asked Yield or BEY). This should be an annual rate.
Round to 4 decimal places. Rates are expressed as a %. (4 pts.) 2. Assume the yield to maturity (Asked Yield) rises a full 1 percent. Calculate the new price.
(2 pts.) 3. As I did in your notes, calculate the security’s’ Macaulay duration (in years) using the YTM
calculated in #1 (round to 4 decimal places). (4 pts.)
a. Show that you used the Duration function in Excel to check your answer. (2 pts.) 4. As I did in your notes, calculate the security’s modified duration (round to 4 decimal places).
(2 pts.) a. Show that you used the Modified duration function in Excel to check your answer.
(1 pt.)
Part B: (14 pts.) Using the above information, perform the following in EXCEL:
1. Complete the following chart, showing the relationship between market interest rates and security
prices, for each security: (6 pts.) Security Prices
rm T-Bill Corp Bond
0.00%
1%
1.0267%
2%
3%
3.6529%
4%
4.5%
5%
6%
7%
8%
9%
10%
11%
12%
13%
14%
15%
16%
17%
18%
19%
20%
Project 1: Bond Valuation, Duration, Money Market, and Bond Market
5
2. Prepare a graph showing the T-bill/Corporate bond values at varying market rates of interest for
the two securities. (See Problem 1, Part B.2. for example.) (6 pts.)
3. What can you say about the price relationships and duration between these two securities? What
was unique or problematic about calculating the prices and durations of these securities? (2 pts.)
Problem 3: (12 pts.) Go to the Bureau of Public Debt website at www.treasurydirect.gov. Find the Treasury Auction Results
for the 13-week T-bills issued August 10, 2017. Insert this page into your Excel worksheet. (1 pt.)
Answer the following questions:
1. What are the high, low, and median rates on this issue? (1.5 pts.)
2. What is the “stop-out” rate? (0.5 pts.) 3. What is the total dollar value each of tendered bids and accepted bids? (1 pt.) 4. What is the total dollar value each of noncompetitive bids and competitive bids? (1 pt.) 5. What is the bid-to-cover ratio? (1 pt.) 6. What is the price investors paid for $1,000 face value of these T-bills? Show how this was
calculated? (Use equation editor in EXCEL to show formula.) (3 pts.) 7. What is the Yield to maturity (or BEY) on the issue? Show how this was calculated. (Use
equation editor in EXCEL to show formula.) (3 pts.)
Problem 4: (13 pts.) Go to the Bureau of Public Debt website at www.treasurydirect.gov. Find the Treasury Auction Results
for the 30-year T-Bonds issued August 15, 2017. Insert this page into your Excel worksheet. (1 pt.)
Answer the following questions:
1. What are the high, low, and median yields on this issue? (1.5 pts.) 2. What is the “stop-out” rate? (0.5 pt.) 3. What is the total dollar value each of tendered bids and accepted bids? (exclude SOMA) (1 pt.)
a. What is SOMA and why is it excluded in the bid-to-cover ratio? (1 pt.) 4. What is the total dollar value each of noncompetitive bids and competitive bids? (1 pt.) 5. What is the bid-to-cover ratio? (1 pt.) 6. What is the coupon rate offered on this issue? (1 pt.)
7. What is the price investors paid for $1,000 face value of these T-bonds? Show how this was calculated? (3 pts.)
8. What is the Yield to maturity on the issue? Explain how this was calculated. (2 pts.)
