Can you answer these questions in EXCEL and I should be able to click the EXCEL tab and the see the formula

3- June Klein, CFA, manages a $100 million (market value) U.S government bond portfolio for an institution. She anticipates a small parallel shift in the yield curve and wants to fully hedge the portfolio against any such change. Conversion Security Modified Basis point factor for to cheapest Portfolio value/ Duration value Deliver bond Future Contract price Portfolio 10 years $100,000.00 Not applicable $100, 000, 000 U.S Treasury bond 8years $75.32 1 94-05 a. Discuss two reasons for using futures rather than selling bonds to hedge a bond portfolio. No calculations required. b. Determine how each of the following would change in value if interest rates increase by 10 basis points as anticipated. Show all calculations. c. Determine how each of the following would change in value if interest rates increase by 10 basis points. Show all calculations. (1) The original portfolio (2) The treasury bond futures position (3) The newly hedged portfolio d. State three reasons why Klein's hedging strategy might not fully protect the portfolio against interest rate risk. e. Describe a zero-duration hedging strategy using only the government bond portfolio and options on U.S Treasury bond futures contracts. No calculations required. 4. A bond speculator currently has positions in two separate corporate bond portfolio: a long holding in portfolio 1 and a short holding in portfolio 2. All the bonds have the same credit quality. Other relevant information on these positions includes: Market coupon Compounding Yield to Portfolio Bond value (Mil.) rate Frequency Maturity maturity 1 A $6.0 0.0% Annual 3yrs 7.31% B 4.0 0.0 Annual 14yrs 7.31 2 C 11.5 4.6 Annual 9 yrs 7.31 Treasury bond futures based on $100,000 face of 20 year T-bonds having an 8 percent (semiannual coupon) with a maturity exactly six months from now are currently priced at 109-24 with a corresponding yield to maturity of 7.081 percent. The yield beta between the futures contract and bonds A, B, and C are 1.13, 1.03, and 1.01, respectively. Finally, the modified duration for T-bond underlying the futures contract is 10.355 years. a. Calculate the modified duration (expressed in years) for each of two bond portfolios. What will be the approximate percentage change in the value of each if all yields increase by 60 basis points on an annual basis? b. Without performing the calculations, explain which of the portfolio will actually have its value impacted to the greatest extent (in absolute terms) by the shift yields. (Hint: this explanation requires knowledge of the concept of bond convexity) c. Assuming the bond speculator wants to hedge her net bond position, what is the optimal number of futures contracts that must be bought or sold? Start by calculating the optimal hedge ratio between the futures contract and the two bond portfolio separately and then combine them. 6. As a relationship officer for a money-center commercial bank, one of your corporate accounts has just approached you about a one year loan for $1,000,000. The customer would pay a quarterly interest expense based on the prevailing level of LIBOR at the beginning of each three month period. As is the bank's convention on all such loans, the amount of the interest payment would then be paid at the end of the quarterly cycle when the new rate for the next cycle is determined. You observe the following LIBOR yield curve in the cash market: 90-day LIBOR 4.60% 180-day LIBOR 4.75 270-day LIBOR 5.00 360-day LIBOR 5.30 a. If 90-day LIBOR rises to the levels \"predicted\" by the implied forward rates, what will the dollar level of the bank's interest receipt be at the end of each quarter during the one year loan period? b. If the bank wanted to hedge its exposure to failing LIBOR on this loan commitment, describe the sequence of transactions in the futures markets it could undertake. c. Assuming the yields inferred from the Eurodollar futures contract prices for the next three settlement periods are equal to the implied forward rates, calculate the annuity value that would leave the bank indifferent between making the floating-rate loan and hedging it in the futures market and making a one year fixed rate loan. Express this annuity value in both dollar and annual (360-day) percentage terms. 9. Alex Andrew, who manages a $95 million large capitalization U.S equity portfolio, currently forecasts that equity market will decline soon. Andrew prefers to avoid the transaction costs of making sales but wants to hedge $15 million of the portfolio's current value using S&P 500 futures. Because Andrew realizes that his portfolio will not track the S&P 500 index exactly, he performs a regression analysis on his actual portfolio return versus the S&P futuresreturns over the past year. The regression analysis indicates a risk minimizing beta of 0.88 with an R^2 of 0.92. Futures Contract Data S&P 500 futures price 1,000 S&P 500 index 999 S&P 500 index multiplier 250 a. Calculate the number of futures contracts required to hedge $15 million of Andrew's portfolio, using the data shown. State, whether the hedge is long or short. Show all calculations. b. Identify two alternatives methods (other than selling securities from the portfolio or using futures) that replicate the strategy in part a. Contract each of these methods with the futures strategy. 10. The treasurer of a middle market, import-export company has approached you for advice on how to best invest some of the firm's short-term cash balances. The company, which has been a client of the bank that employs you for a few years, has $250,000 that is able to commit for a one year holding period. The treasurer is currently considering two alternatives: (1) invest all the funds in a one year U.S treasury bill offering a bond equivalent yield of 4.25%, and (2) invest all the funds in a Swiss government security over the same horizon, locking in the spot and forward currency exchanges in the FX market. A quick call to the bank's FX desk gives you the following two currency exchange quotes. Swiss francs U.S Dollar per per U.S Dollar Swiss Franc (CHF) Spot 1.5035 0.6651 1-year CHF futures - 0.6586 a. calculate the one year bond equivalent yield for the Swiss government security that would support the interest rate parity condition. b. Assuming the actual yield on a year Swiss government bond is 5.50%, which strategy would leave the treasurer with the greatest return after one year? c. Describe the transaction that an arbitrageur could use to take advantage of this apparent mispricing and calculate what the profit would be for a $250,000 transaction. 11. Bonita Singer is a hedge fund manager specializing in futures arbitrage involving stock index contracts. She is investigating potential trading opportunities in S&P 500 stock index futures to see if there are any inefficiencies that she can exploit. She knows that the S&P 500 stock index is currently trading at 1,100. a. Assuming that the treasury yield curve is flat at 3.2% and the annualized dividend yield on the S&P index is 1.8%. Using the cost of carry model, demonstrate what the theoretical contract price should be for a futures position expiring six months from now. b. Describe the set of transactions that Bonita would have to undertake to take advantage of an actual futures contract price that was (1) substantially higher or (2) substantially lower than the theoretical value you established in part a. c. Assuming that total round-trip arbitrage transaction costs are $20 for the set trades described in part b, calculate the upper and lower bounds for the theoretical contract price such that arbitrage trading would not be profitable