Question

A stock price is currently $100. A call option on this stock with a strike price of $100 and one year to maturity costs $13.61. The continuous-time interest rate is 5%. By using a one-step binomial tree, estimate the expected volatility level (σ) for the stock. Assume that u and d are modeled as below. 

( Excel's Goal Seek will help solving this and will be much appreciated to be posted to see how it has been used to solve this problem, thanks)



Answer 1

Design the template like this

fox D16 N O E F G Н K L M Results Calculations 2 Parameters 3 Stock Price S Time Interval Binomial 100 1 100 0.05 Call Put 8.732940748 Down movement 0.979379353 13.6099983 Up movement 4 Exercise Price X 5 Interest Rate r 1.021054811 d 1/u 6 Volatility 7 Time to Maturity Up probability Discount Factor 0.020836221 1.725037895 1 0.951229425 8 Number of Steps 9 Dividend Yield Step 0 0 1 2 3 5 Time 10 0 1 2 3 4 5 Sn u 11 100 102.1054811 104.2552926 106.4503681 108.6916604 10.9801427 97.93793533 Stock Price 12 L06.4503681 13 100 102.1054811 104.2552926 95.91839176 7.93793533 100 L02.1054811 93.94049249 95.91839176 7.93793533 14 SN 15 d So 92.00337877 3.94049249 16 d2 So 90.1062096 17 18 13.609 983 Option Price 10.9801427 21.10181943 16.76974799 12.60257039 9.1323501666.450368052 19 17.1633427 17.50803792 15.96662625 13.56871793 20 max(SN-X, 0) 9.302572971 5.669165945 3.454898189 .105481056 21 Call 22 0 0 Price 0 23 24 25 26 ד

Feed the formula like I filled up.

D16 F Е G Calculations Results 3 Binomial Time Interval -TimeToMaturity/nSteps 4 H19 -EXP(B6*SQRT(H3)) Up movement 5 (H19-B3)+B4 EXP(-B5*B8)Down movement -1/H4 d 1/u Up probability Discount Factor (EXP(B5*H3)-H5)/(H4-H5) -EXP(-B5*H3) 8 Step 9 0 1 2 4 5 9*SH$3 Snu = K9*$H$3 Time 19*$H$3 =19*$H$3 =L9*$H$3 M9*$H$3 10 11 -L12*SH$4 -L12* $H$5 Stock Price H12*$H$4 112*$H$4 =J12* $H$4 =K12* $H$4 12 B3 H 2 SH$5 12*$H$5 -113 $H$5 K12 SH$5 =K13 $H$5 =J12 $H$5 1 SH$5 J14*$H$5 13 L13*SH$5 14 =K14 $H$5 =K15*$H$5 L14*$H$5 L15*SH$5 15 d So 16 d2 So L16 $H$5 17 18 $H$6*I19+(1-$H$6) *120) *: =($H$6*J 19+(1-$H$6) *J 20) * -($H$6* K19+(1-$H$6) *K20) -($H$6 *L19+(1-$H$6) * L20) * =($H$6 *M 19+( 1- $H$6) * M2C =NAX(0,(M12-$B$4)) ($H$6*J20+(1-$H$6) *J21) * - ($H$6*K20+(1-$H$6) * K21) =($H$6*L20+( 1-$H$6) *L21) = ($H$6*M20+(1-$H$6) *M21 =NAX(0,(M13-$B$4)) =($H$6*K21+(1-$H$6) * K22) -($H$6*L21+(1- $H$6) * L22) * -($H$6*M21+(1-$H$6) *M22 =NAX(0, ( M14-$B$4)) ($H$6*L22+(1-$H$6) *L23) * ($H$6*M22+(1-$H$6 *M23 =NAX(0,(M15-$B$4)) ($H$6*M23+(1-$H$6) *M24 =NAX(0,(M16-$B$4)) =MAX(0,(M17-$B$4)) Option Price 19 20 Call Price 24

Enable solver add-in in the excel

you can feed any volatility during the start of the problem.

To find the volatility, open the solver in data tab and enter the data as i feed, Volatility changes as

Group Flash Fill O Consolidate 2 Solver Show Queries Connections Clear EProperties From Table Solver Parameters Get External Data New Refresh Sort Filter Recent Sources Edit Links Query All Sort & Filte Get & Transform Connections Analyze Set Objective: SHS19 X sigma O Min O Value Of: 13.61 To: O Max Ic D Q R A В Е G H P By Changing Variable Cells: 1 SB$6 2 Parameters Results Calculations Time Interval 3 Stock Price So 100 Binomial Subject to the Constraints: Call 78.07318374 Up movement 2.718281 4 Exercise Price X 100 Add Put 73.19612619 Down movement 0.367879 0.290755 0.951229 5 Interest Rate r 0.05 6 Volatility 7 Time to Maturity 8 Number of Steps Up probability Discount Factor Change 1 Delete 1 9 Dividend Yield Step 0 Time 10 Reset All Snu 11 Load/Save Stock Price 12 Make Unconstrained Variables Non-Negative 13 14 Select a Solving Method: GRG Nonlinear Options 15 16 Solving Method 17 Select the GRG Nonlinear engine for Solver Problems that are smooth nonlinear. Select the LP Simplex engine for linear Solver Problems, and select the Evolutionary engine for Solver problems that are non-smooth. 18 Option Price 78.0731 19 20 21 Call Help Solve Close 22 Price