Problem # $2$: 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$).$

You are to calibrate a straight line yield curve where

$y(t)=y_0+mt$

by finding appropriate values for $y_0$ 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 fit for the following four bonds:

Bond $1$ has face value $$100$ and a term of $1$ year with semiannual coupons of $10\%$ and a price of $$105\mathrm{.}27.$

Bond $2$ has face value $$100$ and a term of $1\mathrm{.}5$ year with semiannual coupons of $9\%$ and a price of $$104\mathrm{.}60.$

Bond $3$ has face value $$100$ and a term of $2$ year with a semiannual coupon of $5\%$ and a price of $$97\mathrm{.}85.$

Bond $4$ has face value $$100$ and a term of $5$ year with a semiannual coupon of $6\%$ and a price of $$92\mathrm{.}70.$

Create a spreadsheet with headings similar to the following:

$\backslash$table$[[4,$A$,$B$,$C$,$D$,$E$,$F$,$G$,$H$,1,$J$],[1],[2],[3,,$y$\_$o$,m,,,,,,,],[4,,$$$100?=y_{-}0+m**B7=(G7-H7{)}^2$$$100=y_{-}0+m**B8?$$$100$$$100={\displaystyle \underset{?}{\overset{?}{}}}(17$:$110)y_{0}m{y}_{0}m22$