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:

12/30/2XX3 1.00 99:2425 99:2475 -0:12 ???????

T-Note

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

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.)

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.) 