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 a number into a formula.

• Remember about absolute (e.g., C$2 or $C2 or $C$2) vs relative cell addresses (C2)!
• For consistency in spreadsheet designuse the template provided in the assignment. NOTE the spreadsheet template has THREE tabsone tab for each question.
• NOTE ALSOall bonds in this assignment have semi-annual coupons!

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	6%	6.8%
Annual coupon frequency	2	2
Par	$1,000	$1,000
Time to maturity (years)	10	5

2. (10 points) Compute the following yields:

a) the yield to maturity for Bond 1, above, if the current bond price is $875.

b) the yield to call for Bond 1 if its current price is $1050 and it is callable in 4 years at a value of par plus one years coupon interest.

c) the realized compound yield for Bond 2 if the current price is $1000 and the semi-annual coupons are reinvested at an annual rate of 2% until the bond matures.

d) the realized compound yield for Bond 2 if the current price is $1000 and the semi-annual coupons are reinvested at an annual rate of 2% until the bond is sold in 3 years for $1,050.

e) Suppose the yield to maturity for Bond 2 changes from 5% to 4.75%. Compute the bonds price at each YTM. What is the percentage difference in price? Compute the percentage as the (price when YTM is 4.75% - price when YTM is 5%) / price when YTM is 5%.

3. (5 points) Use Excel formulas to compute prices and modified duration (NOTE: no need to use Excels DURATION or MDURATION functionsuse Excels time value of money functions and arithmetic functions (add, subtract, multiply, divide) to do this part of the assignment)

a) Compute duration and modified duration for Bond 2 using a discount rate (YTM) of 5%.

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

c) What is the percentage difference between the actual price when YTM is 4.75% (in part 2e) and estimated price (when YTM falls to 4.75%, part 3b) you just computed? Compute the percentage change as (actual price estimated price) / actual price