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 Excels PV function. All calculations must use cell references rather than typing in a number into a formula.

For consistency in spreadsheet designuse the template provided in the assignment. NOTE the spreadsheet template has THREE tabsone tab for each question.

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

a) compute the bonds 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 bonds price using a YTM of 5% and a YTM of 6%)

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

c) if Bond 1s coupon rate is 0% rather than 3% (compute the bonds 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 1s original yield to maturity is 2% and it rose to 3% (compute the bonds 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 bonds 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?