I need help constructing a spreadsheet to answer the question below with step by step instructions, and explanations of what$$s being done, and why. Use market data taken from the cmegroup, WSJ and$/$or WSJ online that reflects closing prices from Tuesday, May $14.($Please be sure to show ALL CELLS in your solution. Someone keeps posting similar solutions that reference column J$,$ but J isn$$t included in the screenshot $)$

$2.$ Hedging a Bond Portfolio:

Assume you are a fixed income portfolio manager holding Apple notes that have $$40$ million in total current market value $($not face amount$).$ The notes have an annual coupon rate of $3\mathrm{.}00\%,$ and mature November $13,2027$ or in about $3\mathrm{.}5$ years. The notes make semi$-$annual coupon payments on May $13$ and Nov $13$ of each year; assume it just paid a coupon and thus the next coupon is to be received in $6$ months and so on every $6$ months, and the note matures in $3\mathrm{.}5$ years.

Find the last trade price for AAPL$4562446$ or the cusip $037833$DK$3,$ Note that corporate bond prices are quoted as a $\%$ of par $($e$.$g$.,$ a last trade price of $$94\mathrm{.}95$ would mean $94\mathrm{.}95\%$ of par or $$949\mathrm{.}50$ per $$1000$ par amount$).$

$($a$)$ The next month or so is of concern, as you believe that rates may unexpectedly rise. What impact would a $50$ basis point jump in interest rates have on the value of your bond portfolio $($approximated by using mod duration$)?$ In other words, estimate the predicted change in value based on modified duration.

$($b$)$ Describe carefully what action you will take to hedge your interest rate exposure using the June $20242-$year Treasury$-$Note futures. Show all calculations and be judicious in your selection of a hedging model and appropriate futures maturity. $($As discussed in class, assume that the underlying instrument for the $2-$year T$-$note futures is a $6\%,2-$year T$-$note with a $$200,000$ face value.$)$

$($c$)$ Repeat part $($b$),$ but assume instead you decide to hedge with the June $20245$year T$-$Note futures. $($Assume that the underlying instrument for the $5-$year T$-$note futures is a $6\%,5-$year T$-$note with a $$100,000$ face value.$)$