Finance homework-call options…i need this in excel

Problem 20-6 on Call Options based on Chapter 20

(Excel file included)

You own a call option on Intuit stock with a strike price of $40. The option will expire in exactly 3 months’ time.

a. If the stock is trading at $55 in 3 months, what will be the payoff of the call?

b. If the stock is trading at $35 in 3 months, what will be the payoff of the call?

c. Draw a payoff diagram showing the value of the call at expiration as a function of the stock price at expiration. 

Problem 20-8 on Put Options based on Chapter 20

(Excel file included)

You own a put option on Ford stock with a strike price of $10. The option will expire in exactly 6 months’ time.

a. If the stock is trading at $8 in 6 months, what will be the payoff of the put?

b. If the stock is trading at $23 in 6 months, what will be the payoff of the put?

c. Draw a payoff diagram showing the value of the put at expiration as a function of the stock price at expiration.

Problem 20-11 on Return on Options based on Chapter 20

Consider the September 2012 IBM call and put options in Problem 20-3. Ignoring any interest you might earn over the remaining few days’ life of the options, consider the following.

a. Compute the break-even IBM stock price for each option (i.e., the stock price at which your total profit from buying and then exercising the option would be 0).

b. Which call option is most likely to have a return of −100%?

c. If IBM’s stock price is $216 on the expiration day, which option will have the highest return?

Problem 21-12 on Option Valuation using the Black Scholes model based on Chapter 21

Rebecca is interested in purchasing a European call on a hot new stock—Up, Inc. The call has a strike price of $100 and expires in 90 days. The current price of Up stock is $120, and the stock has a standard deviation of 40% per year. The risk-free interest rate is 6.18% per year.

a. Using the Black-Scholes formula, compute the price of the call.

b. Use put-call parity to compute the price of the put with the same strike and expiration date.

Problem 30-14 on Swaps based on Chapter 30

Your firm needs to raise $100 million in funds. You can borrow short-term at a spread of 1% over LIBOR. Alternatively, you can issue 10-year, fixed-rate bonds at a spread of 2.50% over 10-year treasuries, which currently yield 7.60%. Current 10-year interest rate swaps are quoted at LIBOR versus the 8% fixed rate.

Management believes that the firm is currently “underrated” and that its credit rating is likely to improve in the next year or two. Nevertheless, the managers are not comfortable with the interest rate risk associated with using short-term debt.

a. Suggest a strategy for borrowing the $100 million. What is your effective borrowing rate?

b. Suppose the firm’s credit rating does improve 3 years later. It can now borrow at a spread of 0.50% over treasuries, which now yield 9.10% for a 7-year maturity. Also, 7-year interest rate swaps are quoted at LIBOR versus 9.50%. How would you lock in your new credit quality for the next 7 years? What is your effective borrowing rate now?

Problem 30-6 on Futures Contract based on Chapter 30

(Excel file included)

Your utility company will need to buy 100,000 barrels of oil in 10 days, and it is worried about fuel costs. Suppose you go long 100 oil futures contracts, each for 1,000 barrels of oil, at the current futures price of $60 per barrel. Suppose futures prices change each day as follows.

a. What is the mark-to-market profit or loss (in dollars) that you will have on each date?

b. What is your total profit or loss after 10 days? Have you been protected against a rise in oil prices?

c. What is the largest cumulative loss you will experience over the 10-day period? In what case might this be a problem?