Please show all 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 $25 Exercise Price $35 Maturity (yrs) Risk-free Rate 4.5% Stock Volatility 30% 2 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. Prob. 1 Prob. 2 Prob. 3 Prob. 4 a. Option Pricing Base Fed Price drop Mexico Exercise price Maturity Stock price Risk free rate Volatility BS calculations: di N(dl) d2 Nd2) Price of call b. Futures Prices Base Bushels Bushels contract # of contracts Contract price Spot price Profit/(Loss) to cousins Net Bond outstanding Maturity (yrs.) Fixed rate Spread over LIBOR LIBOR: Years 1-2 Years 3-4 Years 5-6 Years 7-10 c. Interest Rate Swap Cash Flows Original Swap Pmts. Year! 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 $25 Exercise Price $35 Maturity (yrs) Risk-free Rate 4.5% Stock Volatility 30% 2 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. Prob. 1 Prob. 2 Prob. 3 Prob. 4 a. Option Pricing Base Fed Price drop Mexico Exercise price Maturity Stock price Risk free rate Volatility BS calculations: di N(dl) d2 Nd2) Price of call b. Futures Prices Base Bushels Bushels contract # of contracts Contract price Spot price Profit/(Loss) to cousins Net Bond outstanding Maturity (yrs.) Fixed rate Spread over LIBOR LIBOR: Years 1-2 Years 3-4 Years 5-6 Years 7-10 c. Interest Rate Swap Cash Flows Original Swap Pmts. Year! Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Year 8 Year 9 Year 10 Present value of net