I just need questions 30-6 and 30-14 placed on excel and everything must be calculated USING EXCEL. Thank you very much.

FIN516 WEEK 3 - HOMEWORK Andrew Plaza 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. Solution: a. The payoff of the call will be: $55 - $40 = $15 b. The payoff of the call will be $0 c. Draw graph: 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. Solution: FIN516 WEEK 3 - HOMEWORK a) The payoff of the put will be: $10 - $8 = $2 b) payoff of the put will be $0 c) 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? Solution: a) BE Stock Price for calls = Strike + Ask and BE Stock Price for puts = Strike - Ask Calls Strike Ask Break even stock price 12 sep 200 6.65 206.65 12 sep 205 2.49 207.49 12 sep 210 .42 210.42 12 sep 215 .08 215.08 Puts Strike Ask Break even stock price 12 sep 200 .022 199.78 12 sep 205 1.10 203.90 12 sep 210 4.15 205.85 FIN516 WEEK 3 - HOMEWORK 12 sep 215 8.95 206.05 b. The 215 call option is most likely to have a return of -100%. c. The 215 call option will have the highest return of ($216 - $215)/$0.08 -1 = 1,150% 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 stockUp, 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. Solution: a) b) FIN516 WEEK 3 - HOMEWORK 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? Solution: a) Borrow $100m short term and paying LIBOR + 1.0%. Then enter a $100m notional swap to receive LIBOR and pay 8.0% fixed. Effective borrowing rate is: (LIBOR + 1.0%) - LIBOR + 8.0% = 9.0%. b) Refinance $100m short-term loan with long-term loan at 9.10% + 0.50% = 9.60%. Unwind swap by entering new swap to pay LIBOR and receive 9.50%. Ef- FIN516 WEEK 3 - HOMEWORK fective borrowing cost now: 9.60% + (-LIBOR + 8.0%) + (LIBOR - 9.50%) = 8.10%. 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? Solution: a) Mark-to-market profit or loss = 100,000 x price change Day Price 0 Price change $ 60.00 Profit/Loss FIN516 WEEK 3 - HOMEWORK 1 2 3 4 5 6 7 8 9 10 $ 59.50 $57.50 $57.75 $58.00 $59.50 $60.50 $60.75 $59.75 $61.75 $62.50 ($0.50) ($2.00) $0.25 $0.25 $1.50 $1.00 $0.25 ($1.00) $2.00 $0.75 ($50,000) ($200,000) $25,000 $25,000 $150,000 $100,000 $25,000 ($100,000) $200,000 $75,000 b) The total gain or the sum of the daily profit is $250,000. Yes, I have been protected against a rise in oil prices because the gain exactly offsets the overall increase in cost. c) The largest cumulative loss I will experience over the 10-day period will be $250,000 which happens at the end of the second day. This would be a problem if I encountered a margin call and I did not have enough funds to cover the loss. My position would automatically be closed so that I would realize the $250,000 loss, leaving me unprotected against a rise in oil prices and so requiring me to pay higher prices at the end of 10 days