assistance with execl sheet formulas for C

4. Options and Futures a. Your employer is offering you stock options on the firm as part of your pay package. You know the following about this offer: Current Stock Price Exercise Price Maturity (yrs) Risk-free Rate Stock Volatility $13 $19 3 2.1% 30% What is the value of the option? Suppose the Fed raises Treasury rates to 2.3%, what is the new price of the option? After this Fed action, your company's share price falls to $12, what is the new price of the option? b. Your cousins grow corn in Wisconsin and plan to harvest 10,000,000 bushels at the end of the season. They are unsure whether to sell the futures contracts and lock the price in at $5.40/bushel or take a gamble and sell it all at the spot price at season's end. They think they can get $4.80/bushel based on historical prices and their own analysis. Assuming no transaction costs and each contract covers 5,000 bushels, what will the cousins' profit/loss be if they sell the contracts and the spot price is $5.44 at maturity? Ukraine had a bumper harvest and spot prices fall to $5.33/bushel, what will the cousins' profit/loss be now? c. Because its financial position has strengthened considerably very recently, Argo Airlines is offered an interest rate swap - fixed to floating (LIBOR). The details are as follows: Current Argo Bond Maturity Bond Face Value Current Bond Rate Floating rate Projected LIBOR rates 10 years $333M 4.5% per year, fixed LIBOR + 100 basis points 3.3% (years 1-2) 3.4% (years 3-4) 3.5% (years 5-6) 3.6% (years 7-10) Show the cash flows for Argo and the present value gain/loss of doing the swap. D E G H K 1 2 3 Price drop 3 Ukraine 4 F b. Futures Prices Base Bushels Bushels/contract # of contracts Contract price Spot price Profit/(Loss) to cousins 5 5 6 7 7 8 9 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! c. Interest Rate Swap Original Cash Flows Swap Pmts. Net 10 11 12 13 14 15 16 17 18 Bond outstanding Maturity (yrs.) Fixed rate Spread over LIBOR LIBOR: Years 1-2 Years 3-4 Years 5-6 Years 7-10 Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Year 8 Year 9 Year 10 19 20 21 22 23 24 25 26 27 28 + Present value of net