Excel's Solver utility can also find an optimum solution involving more than one variable. We will use Solver to find the best fit of a straight line yield curve for some bond prices (more information on Solver is available in the previous assignment, or in the online tutorial). please use Excel to solve.

Problem # 2: Excel's Solver utility can also nd an optimum solution involving more than one variable. We will use Solver to nd the best t of a straight line yield curve for some bond prices (more information on Solver is available in the previous assignment, or in the online tutorial). You are to calibrate a straight line yield curve where W) = yo + mt by nding appropriate values for yo and m. The value y(t) is the semiannually compounding bond yield, or IRR, for a bond of maturity t. The yield curve is to be the best straight-line t for the following four bonds: Bond 1 has face value $100 and a term of 1 year with semiannual coupons of 5% and a price of $100.01. Bond 2 has face value $100 and a term of 1.5 year with semiannual coupons of 7% and a price of $101.31. Bond 3 has face value $100 and a term of 2 year with a semiannual coupon of 8% and a price of $103.03. Bond 4 has face value $100 and a term of 5 year with a semiannual coupon of 3% and a price of $82.36. Create a spreadsheet with headings similar to the following: A A B C D E F G H I J L L i y_0 m L at (rate change) i i Term Face Coupon FrequenLy vield Model price Market price Difference squared L 1 $100 ? 1 =y_o + m ' B7 .7 i 1 $100 ? 2 ? ? L 3 Objective 31 =sum(l7:l10) l 15 In column G, you are to price the bond based on the yield from column F. Column H contains the actual price of the bond. Column I is the difference between the two prices, squared. In cell 113, we have the sum of the differences squared, and it is this value that we seek to minimize. Use Excel's Solver add-in to simultaneously solve for yo and m to minimize the sum of the differences squared. (a) What is the value for yo found by Solver? (b) What is the value for m found by Solver