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

$$omplete the steps below using cell references to given data or previous calculations. In some cases, a simple cell reference is all you need. To copy$/$paste a formula across a row or down a column, an absolute cell reference or a mixed cell reference may be preferred. If a specific Excel function is to be used, the directions will specify the use of that function. Do not type in numerical data into a cell or function. Instead, make a reference to the cell in which the data is found. Make your computations only in the blue cells highlighted below. In all cases, unless otherwise directed, use the earliest appearance of the data in your formulas, usually the Given Data section."

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

b$.$ If the stock is trading at $$35$ in three 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.

Strike Price $$40\mathrm{.}00$

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

Spot price in $3$ months $$55\mathrm{.}00$

Exercise value of call

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

Spot price in $3$ months $$35\mathrm{.}00$

Exercise value of call

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

Spot Price $($$$)$ Exercise Value $($$$)$

$0\mathrm{.}00$

$1.$ Start Excel $-$ completed.

$2.$ In cell E$15,$ by using the function IF and cell references, compare whether the stock price is greater than the strike price; if true, input the difference between the stock price and the strike price; if false, input $0(1$ pt$.).$

$3.$ In cell E$21,$ by using the function IF and cell references, compare whether the stock price is greater than the strike price; if true, input the difference between the stock price and the strike price; if false, input $0(1$ pt$.).$

$4.$ To graph the diagram showing the exercise value of the call as a function of the stock price at expiration, you need to generate a table of exercise values as a function of the stock prices at expiration. Start with a stock price of $0$ in cell C$26.$ In cell C$27,$ by using cell references, increase the previous stock price by $5(1$ pt$.).$ Copy and paste cell C$27$ onto cells C$28$:C$45(1$ pt$.).$

$5.$ In cell D$26,$ by using the function IF and absolute and relative cell references, compare whether the stock price is greater than the strike price; if true, input the difference between the stock price and the strike price; if false, input $0(1$ pt$.).$ Copy and paste cell D$26$ onto cells D$27$:D$45(1$ pt$.).$

$6."$In cells F$25$:M$35,$ insert a $2$D$-$line Chart to show the value of the call at expiration as a function of the stock price at expiration.

Inserting a Chart

On the Insert tab, in the Charts group, click the arrow next to Insert Line or Area Chart and choose $2$D$-$line Chart.

Selecting Data Series

Then in Select Data Source window, delete any series created automatically.

Add new series for the security market line using cells D$26$:D$45.$ Use cells E$26$:E$45$ as Horizontal $($Category$)$ Axis Labels. Do not name the series.

Edit Chart Elements

Add a chart title and choose the Above Chart option. Replace Chart Title with Exercise Value of a Call Option at Expiration. Add axis titles. Replace Axis Title for the horizontal axis with Stock Price at Expiration $($$$)$ and Axis Title for the vertical axis with Exercise Value $($$$)(4$ pt$.)."$

$7.$ Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed.