Question

Use the following filename: yourlastname.FIL242Assignment3; there will be one file submission, an Excel spreadsheet. ALL calculations must use Excel functions or Excel formulas. For example, bond prices can be computed using Excel’s PV function. All calculations must use cell references rather than typing in a number into a formula.

For consistency in spreadsheet design—use the template provided in the assignment. NOTE the spreadsheet template has THREE tabs—one tab for each question.

1. (10 points) For each of the bonds described below,

a) compute the bond’s price as the yield to maturity (YTM) varies from 1% to 20% (in increments of 1 percentage point).

b) Graph the price-YTM curves; put the curves of each bond in the same graph and label the graph.

	Bond 1	Bond 2
Coupon rate	3%	8%
Annual coupon frequency	1	2
Par	$1,000	$1,000
Time to maturity (years)	10	20

2. (8 points) Compute the bond price and the percent change in price for Bond 1 when the yield to maturity changes from 5% to 6% for the following:

a) the original Bond 1 (compute the bond’s price using a YTM of 5% and a YTM of 6%)

b) if Bond 1’s time to maturity is 20 years rather than 10 (that is, compute the bond’s price using a YTM of 5% and a YTM of 6% given Bond 1’s inputs above but use a time to maturity of 20 years rather than 10 years)

c) if Bond 1’s coupon rate is 0% rather than 3% (compute the bond’s price using a YTM of 5% and a YTM of 6% using the original Bond 1 inputs except for these coupon rates)

d) if Bond 1’s original yield to maturity is 2% and it rose to 3% (compute the bond’s price using the original inputs but now using a YTM of 2% and a YTM of 3%)

NOTE: compute the percent change using the lower YTM price as the “base” or beginning value. For example, part a), the percent change is:

price with 6% YTM-price with 5% YTMprice with 5% YTM. Doing so will result in negative values of the percent change.

3. (7 points) Use Excel to compute prices and modified duration:

a) Compute duration and modified duration for Bond 1 using a discount rate (YTM) of 5%. Use the template to compute this the “long way.”

b) If the bond has a YTM of 5%, what is the estimated price, using modified duration, if the YTM falls to 4.75%?

c) What is the bond’s actual price at a YTM of 4.75%? (use this YTM to compute bond price)

d) What is the percentage difference between the actual and estimated price you computed?

Answer 1