Question $2(25$ points$)$

A small pension fund has the following liabilities $($in million dollars$)$:

$\backslash$table$[[$Year $1,$Year $2,$Year $3,$Year $4,$Year $5,$Year $6,$Year $7,$Year $8,$Year $9],[24,26,28,28,26,29,32,33,34]]$

It would like to construct a dedicated bond portfolio. The bonds $($price and the coupon payments$)$ available for purchase are the following:

$\backslash$table$[[,$Bond$1,$Bond$2,$Bond$3,$Bond$4,$Bond$5,$Bond$6,$Bond$7,$Bond$8],[$Price$,102\mathrm{.}44,99\mathrm{.}95,100\mathrm{.}02,102\mathrm{.}66,87\mathrm{.}90,85\mathrm{.}43,83\mathrm{.}42,103\mathrm{.}82],[$Coupon$,5\mathrm{.}625,4\mathrm{.}75,4\mathrm{.}25,5\mathrm{.}25,3\mathrm{.}75,3\mathrm{.}25,3\mathrm{.}00,5\mathrm{.}75],[$Maturity$,$Year $1,$Year $2,$Year $2,$Year $3,$Year $3,$Year $4,$Year $5,$Year $5]]$

$\backslash$table$[[,$Bond$9,$Bond$10,$Bond$11,$Bond$12,$Bond$13,$Bond$14,$Bond$15,$Bond$16],[$Price$,110\mathrm{.}29,108\mathrm{.}85,109\mathrm{.}95,107\mathrm{.}36,104\mathrm{.}62,99\mathrm{.}07,103\mathrm{.}78,64\mathrm{.}66],[$Coupon$,6\mathrm{.}875,6\mathrm{.}5,6\mathrm{.}625,6\mathrm{.}125,5\mathrm{.}625,4\mathrm{.}75,5\mathrm{.}5,3\mathrm{.}00],[$Maturity$,$Year $6,$Year $6,$Year $7,$Year $7,$Year $8,$Year $8,$Year $9,$Year $9]]$

All the bonds have a face value of $$100($i$.$e$.,$ sold for $$100$ at the end of maturity year$).$ Coupon payments are annual. All bonds can be purchased in any quantity. If you purchase one unit of Bond$1,$ you have to pay $$102\mathrm{.}44$ today, and you will receive $$5\mathrm{.}625$ coupon payment and $$100$ face value at the end of year $1.$

$($a$)(15$ points$)$ Formulate an LP that minimizes the cost of the dedicated portfolio, assuming a $2\%$ per year reinvestment rate for the excess funds. Define the decision variables clearly.

$($b$)(10$ points$)$ Solve the above formulation using OpenSolver and report the solution. Submit your excel file as well. $($Hint: Optimal objective function value is $$187,479,127.)$

Question $3(20$ points$)$

Assume that the stock of an Auto manufacturer is currently priced at $$45.$ At the end of the next