Please show excel formulas

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 $25 $35 2 4.5% 30% What is the value of the option? Suppose the Fed reduces Treasury rates to 4.0%, what is the new price of the option? Your company's share price falls to $23, what is the new price of the option? B. Your cousins grow corn in lowa and plan to harvest 500,000 bushels at the end of the season. They are unsure whether to sell the futures contracts and lock the price in at $4.05/bushel or take a gamble and sell it all at the spot price at season's end. They think they can get $4.10/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 $4.10? Mexico had a bumper harvest and spot prices fall to $3.86/bushel, what will the cousins' profit/loss be now? C. Because its financial position has strengthened considerably very recently, Apache Airlines is offered an interest rate swap - fixed to floating (LIBOR). The details are as follows: Current Apache Bond Maturity Bond Face Value Current Bond Rate Floating rate Projected LIBOR rates 10 years $200M 6% per year, fixed LIBOR + 100 basis points 4.5% (years 1-2) 5% (years 3-4) 5.5% (years 5-6) 5% (years 7-10). Show the cash flows for Apache and the present value gain/loss of doing the swap. D H I J K B a. Option Pricing Base Fed Price drop Mexico E F b. Futures Prices Base Bushels Bushels/contract # of contracts Contract price Spot price Profit/(Loss) to cousins c. Interest Rate Swap A 1 2 3 Exercise price 4 Maturity 5 Stock price 6 Risk free rate 7 Volatility 8 BS calculations: 9 di 10 N(dl) 11 d2 12 N(2) 13 Price of call 14 15 16 17 18 19 20 21 22 23 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! Cash Flows Swap Pmts. Original Net 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 Present value of net 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 $25 $35 2 4.5% 30% What is the value of the option? Suppose the Fed reduces Treasury rates to 4.0%, what is the new price of the option? Your company's share price falls to $23, what is the new price of the option? B. Your cousins grow corn in lowa and plan to harvest 500,000 bushels at the end of the season. They are unsure whether to sell the futures contracts and lock the price in at $4.05/bushel or take a gamble and sell it all at the spot price at season's end. They think they can get $4.10/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 $4.10? Mexico had a bumper harvest and spot prices fall to $3.86/bushel, what will the cousins' profit/loss be now? C. Because its financial position has strengthened considerably very recently, Apache Airlines is offered an interest rate swap - fixed to floating (LIBOR). The details are as follows: Current Apache Bond Maturity Bond Face Value Current Bond Rate Floating rate Projected LIBOR rates 10 years $200M 6% per year, fixed LIBOR + 100 basis points 4.5% (years 1-2) 5% (years 3-4) 5.5% (years 5-6) 5% (years 7-10). Show the cash flows for Apache and the present value gain/loss of doing the swap. D H I J K B a. Option Pricing Base Fed Price drop Mexico E F b. Futures Prices Base Bushels Bushels/contract # of contracts Contract price Spot price Profit/(Loss) to cousins c. Interest Rate Swap A 1 2 3 Exercise price 4 Maturity 5 Stock price 6 Risk free rate 7 Volatility 8 BS calculations: 9 di 10 N(dl) 11 d2 12 N(2) 13 Price of call 14 15 16 17 18 19 20 21 22 23 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! Cash Flows Swap Pmts. Original Net 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 Present value of net