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 $33 $38 1 2.5% 20% What is the value of the option? Suppose the Fed reduces Treasury rates to 2.0%, what is the new price of the option? After the rate reduction, your company's share price falls to $28, what is the new price of the option? B. Your cousins grow corn in Wisconsin and plan to harvest 5,000,000 bushels at the end of the season. They are unsure whether to sell the futures contracts and lock the price in at $4.55/bushel or take a gamble and sell it all at the spot price at season's end. They think they can get $4.70/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.80 at maturity? Ukraine had a bumper harvest and spot prices fall to $3.95/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 15 years $250M 6.5% per year, fixed LIBOR + 150 basis points 4.0% (years 1-2) 4.3% (years 3-4) 4.5% (years 5-6) 4.7% (years 7-15) Show the cash flows for Argo and the present value gain/loss of doing the swap. B K L a. Option Pricing Base Fed #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! Net Exercise price Maturity - Stock price Risk free rate Volatility = BS calculations: . di N(1) 1 d2 2 N(02) 3 Price of call 4 5 6 7 8 9 D 1 2 3 4 s DE F H I J b. Futures Prices Price drop Base Ukraine Bushels Bushels/contract # of contracts Contract price Spot price Profit/(Loss) to cousins #DIV/0! #DIV/0! c. Interest Rate Swap #DIV/0! Cash Flows #DIV/0! Bond outstanding Original Swap Pmts. #DIV/0! Maturity (yrs.) Year 1 Fixed rate Year 2 Spread over LIBOR Year 3 LIBOR: Year 4 Years 1-2 Year 5 Years 3-4 Year 6 Years 5-6 Year 7 Years 7-15 Year 8 Year 9 Year 10 Year 11 Year 12 Year 13 Year 14 Year 15 Present value of net 6 7 8 9 0 1 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 $33 $38 1 2.5% 20% What is the value of the option? Suppose the Fed reduces Treasury rates to 2.0%, what is the new price of the option? After the rate reduction, your company's share price falls to $28, what is the new price of the option? B. Your cousins grow corn in Wisconsin and plan to harvest 5,000,000 bushels at the end of the season. They are unsure whether to sell the futures contracts and lock the price in at $4.55/bushel or take a gamble and sell it all at the spot price at season's end. They think they can get $4.70/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.80 at maturity? Ukraine had a bumper harvest and spot prices fall to $3.95/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 15 years $250M 6.5% per year, fixed LIBOR + 150 basis points 4.0% (years 1-2) 4.3% (years 3-4) 4.5% (years 5-6) 4.7% (years 7-15) Show the cash flows for Argo and the present value gain/loss of doing the swap. B K L a. Option Pricing Base Fed #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! Net Exercise price Maturity - Stock price Risk free rate Volatility = BS calculations: . di N(1) 1 d2 2 N(02) 3 Price of call 4 5 6 7 8 9 D 1 2 3 4 s DE F H I J b. Futures Prices Price drop Base Ukraine Bushels Bushels/contract # of contracts Contract price Spot price Profit/(Loss) to cousins #DIV/0! #DIV/0! c. Interest Rate Swap #DIV/0! Cash Flows #DIV/0! Bond outstanding Original Swap Pmts. #DIV/0! Maturity (yrs.) Year 1 Fixed rate Year 2 Spread over LIBOR Year 3 LIBOR: Year 4 Years 1-2 Year 5 Years 3-4 Year 6 Years 5-6 Year 7 Years 7-15 Year 8 Year 9 Year 10 Year 11 Year 12 Year 13 Year 14 Year 15 Present value of net 6 7 8 9 0 1