The goal of this project is to explore the topic of financial options. The project requires you to work in Excel with the provided spreadsheet. Be sure to fill in the yellow boxes in the Excel file for full credit. In addition, type up a report in Word with an introduction (description of the mini project), findings (answer assignment questions, plots, etc.), and conclusion (summary). Your grade will depend on both quantity and quality. Upon completion, please submit both your Word report and Excel file to blackboard. Questions A, B, C, D, E, and F are worth 10 points each. The report is worth 10 points and the excel file is worth 5 points. If you employ external references, please cite them in a bibliography section.

Financial Options

Suppose you have a call option on a stock with a strike price of $20.

A) Fill in the stock price and strike price in the table and calculate the exercise value (10 points)

B) Plot the Stock price on the x-axis and the Exercise value on the y-axis. Be sure to label the axes with titles and include a chart title (10 Points)

Now assume you have the following data for a call option:

Current stock price

Strike price

Time to expiration

Risk-free rate

Stock return standard deviation

$65.00

$70.00

0.50

5.00%

50.00%

C) Fill in the components of the Black-Scholes model and calculate d1 and d2 (10 points)

D) Calculate the value of N(d1) and N(d2) using the Excel function and find the value of VC (10 Points)

Now use the binomial option pricing model in conjunction with the following data to value a call option:

Current stock price, P =

$27.00

Risk-free rate, rRF =

6%

Strike price, X =

$25.00

Up factor for stock price, u =

1.41

Down factor for stock price, d =

0.71

Years to expiration, t =

0.50

E) Calculate the stock price using the binomial model and find the option payoff in each case, in addition to the value of NS (10 points)

F) Calculate the portfolio payoff in each case and find the present value of the payoff, in addition to the value of the call option (10 Points)

NOTE: Make sure you reference cells or numbers in your Excel file. Do not simply type in the final answers. Points will be deducted if work (i.e., referencing cells) is not shown.

Formula Sheet:

[]Exercise value MAXCurrent price of stockStrike price, 0=?

2RF1P?lnrtX2d=?t??????++??????????????

21dd?t=?

Use the Excel function ?=NORM.S.DIST(X,1)? to find N(X)

()()RFrtC12VPNdXeNd?=?????????

Ending "up" stock Price = (P)(u)

Ending "down" stock Price = (P)(d)

()()C uMAXPuStrike price, 0=?????

()()C dMAXPdStrike price, 0=?????

()()()()N=udSCCPuPd??

Portfolio payoff ?up? position = ()()()NCSuPu?

Portfolio payoff ?down position = ()()()NCSdPd?

1nmRFPortfolio payoffPresent value of riskless portfoliorm=??+????

()CSV=NPPresent value of riskless portfolio

FINC 4352 - Project 2 - Financial Options Stock Price $0.00 $5.00 $10.00 $15.00 $20.00 $25.00 $30.00 $35.00 $40.00 $45.00 A) Stock Price B) Strike Price Exercise Value Current stock price Strike price $65.00 $70.00 Time to expiration 0.50 Risk-free rate 5.00% C) P= t= s= X= rRF = d1= d2= D) N(d1) = N(d2) = V C= Current stock price, P = Risk-free rate, rRF = Strike price, X = Up factor for stock price, u = Down factor for stock price, d = Years to expiration, t = $27.00 6% $25.00 1.41 0.71 0.50 E) Option payoff = Cu = Ending "up" stock Price= P= Ending "down" stock price= Option payoff = Cd = Stock return standard deviation 50.00% NS = F) Portfolio payof P= Portfolio payof Present value of riskless payof = VC = turn standard deviation FINC 4352 - Intermediate Finance Project 2 The goal of this project is to explore the topic of financial options. The project requires you to work in Excel with the provided spreadsheet. Be sure to fill in the yellow boxes in the Excel file for full credit. In addition, type up a report in Word with an introduction (description of the mini project), findings (answer assignment questions, plots, etc.), and conclusion (summary). Your grade will depend on both quantity and quality. Upon completion, please submit both your Word report and Excel file to blackboard. Questions A, B, C, D, E, and F are worth 10 points each. The report is worth 10 points and the excel file is worth 5 points. If you employ external references, please cite them in a bibliography section. Financial Options Suppose you have a call option on a stock with a strike price of $20. A) Fill in the stock price and strike price in the table and calculate the exercise value (10 points) B) Plot the Stock price on the x-axis and the Exercise value on the y-axis. Be sure to label the axes with titles and include a chart title (10 Points) Now assume you have the following data for a call option: Current stock price $65.00 Strike price $70.00 Time to expiration 0.50 Risk-free rate 5.00% Stock return standard deviation 50.00% C) Fill in the components of the Black-Scholes model and calculate d1 and d2 (10 points) D) Calculate the value of N(d1) and N(d2) using the Excel function and find the value of VC (10 Points) Now use the binomial option pricing model in conjunction with the following data to value a call option: Current stock price, P = Risk-free rate, rRF = Strike price, X = Up factor for stock price, u = Down factor for stock price, d = Years to expiration, t = $27.00 6% $25.00 1.41 0.71 0.50 E) Calculate the stock price using the binomial model and find the option payoff in each case, in addition to the value of NS (10 points) F) Calculate the portfolio payoff in each case and find the present value of the payoff, in addition to the value of the call option (10 Points) NOTE: Make sure you reference cells or numbers in your Excel file. Do not simply type in the final answers. Points will be deducted if work (i.e., referencing cells) is not shown. Formula Sheet: = Exercise value MAX [Current price of stock Strike price, 0] d1 = 2 P ln + rRF + t X 2 t d= 2 d1 t Use the Excel function \"=NORM.S.DIST(X,1)\" to find N(X) VC = P N ( d1 ) XerRF t N ( d2 ) Ending "up" stock Price = (P)(u) Ending "down" stock Price = (P)(d) Cu =MAX (P )(u) Strike price, 0 Cd =MAX (P )( d) Strike price, 0 NS = Cu C d (P )(u) (P )( d) Portfolio payoff \"up\" position = (P )(u)( N S ) Cu Portfolio payoff \"down position = (P )( d)( N S ) Cd Present value of riskless portfolio = Portfolio payoff nm rRF 1 + m VC = NS (P ) Present value of riskless portfolio FINC 4352 - Project 2 - Financial Options Stock Price $0.00 $5.00 $10.00 $15.00 $20.00 $25.00 $30.00 $35.00 $40.00 $45.00 A) Stock Price B) Strike Price Exercise Value Current stock price Strike price $65.00 $70.00 Time to expiration 0.50 Risk-free rate 5.00% C) P= t= s= X= rRF = d1= d2= D) N(d1) = N(d2) = V C= Current stock price, P = Risk-free rate, rRF = Strike price, X = Up factor for stock price, u = Down factor for stock price, d = Years to expiration, t = $27.00 6% $25.00 1.41 0.71 0.50 E) Option payoff = Cu = Ending "up" stock Price= P= Ending "down" stock price= Option payoff = Cd = Stock return standard deviation 50.00% NS = F) Portfolio payof P= Portfolio payof Present value of riskless payof = VC = turn standard deviation