Macro needed to find Implied Volatility

[mlarson]

I am in need of finding the implied volatility (IV) for calls and puts on a minute-by-minute basis, and on a 5-minute moving average basis. Ideally there exists a macro so that I could avoid copying a formula over many cells. The table below shows the given data.

What I am seeking is...
(1) a macro that will show the appropriate value in cells I2:J391 of the IV for calls and puts, respectively,
(2) a macro that will show the appropriate value in cells K2:L391 of the 5-minute moving average for the IV just calculated in step 1 above, and
(3) a macro that outputs the standard deviation of the calculation done in step 2 above. This would be for both the Call 5-min moving average (output in cell N1) and the Put 5-min moving average (output in cell N2)

Any help would be MUCH appreciated, thank you!


<table border="0" cellpadding="0" cellspacing="0" width="1690"><col style="width: 48pt;" width="64"> <col style="width: 65pt;" width="86"> <col style="width: 60pt;" width="80"> <col style="width: 62pt;" width="83"> <col style="width: 58pt;" width="77"> <col style="width: 66pt;" width="88"> <col style="width: 68pt;" width="90"> <col style="width: 64pt;" width="85"> <col style="width: 48pt;" width="64"> <col style="width: 135pt;" width="180"> <col style="width: 130pt;" width="173"> <col style="width: 143pt;" width="190"> <col style="width: 137pt;" width="183" span="2"> <col style="width: 48pt;" width="64"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; width: 48pt;" width="64" height="20">
</td> <td class="xl67" style="width: 65pt;" width="86">A</td> <td class="xl67" style="width: 60pt;" width="80">B</td> <td class="xl67" style="width: 62pt;" width="83">C</td> <td class="xl67" style="width: 58pt;" width="77">D</td> <td class="xl67" style="width: 66pt;" width="88">E</td> <td class="xl67" style="width: 68pt;" width="90">F</td> <td class="xl67" style="width: 64pt;" width="85">G</td> <td class="xl67" style="width: 48pt;" width="64">H</td> <td class="xl67" style="width: 135pt;" width="180">I</td> <td class="xl67" style="width: 130pt;" width="173">J</td> <td class="xl67" style="width: 143pt;" width="190">K</td> <td class="xl67" style="width: 137pt;" width="183">L</td> <td class="xl67" style="width: 137pt;" width="183">M</td> <td class="xl67" style="width: 48pt;" width="64">N</td> </tr> <tr style="height: 30pt;" height="40"> <td class="xl72" style="height: 30pt;" height="40">1</td> <td class="xl73">Time of Day</td> <td class="xl73" style="border-left: medium none;">Call Price</td> <td class="xl73" style="border-left: medium none;">Put Price</td> <td class="xl73" style="border-left: medium none;">Stock Price</td> <td class="xl73" style="border-left: medium none;">Strike Price</td> <td class="xl73" style="border-left: medium none;">RiskFreeRate</td> <td class="xl73" style="border-left: medium none;">Term(years)</td> <td class="xl73" style="border-left: medium none;">Div Yield</td> <td class="xl73" style="border-left: medium none;">IV Implied Volatility (CALL)</td> <td class="xl73" style="border-left: medium none;">IV Implied Volatility (PUT)</td> <td class="xl73" style="border-left: medium none;">IV (CALL) 5-Min Moving Avg</td> <td class="xl73" style="border-left: medium none;">IV (PUT) 5-Min Moving Avg</td> <td class="xl73" style="border-left: medium none;">Standard Deviation (Call):</td> <td class="xl73" style="border-left: medium none;">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl72" style="height: 15pt;" height="20">2</td> <td class="xl68" style="border-top: medium none;">9:31</td> <td class="xl69" style="border-top: medium none; border-left: medium none;"> $ 5.95 </td> <td class="xl69" style="border-top: medium none; border-left: medium none;"> $ 2.95 </td> <td class="xl69" style="border-top: medium none; border-left: medium none;"> $ 568.06 </td> <td class="xl69" style="border-top: medium none; border-left: medium none;"> $ 565.00 </td> <td class="xl70" style="border-top: medium none; border-left: medium none;">1.50%</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">0.00397 </td> <td class="xl70" style="border-top: medium none; border-left: medium none;">0.00%</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">Standard Deviation (Put):</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl72" style="height: 15pt;" height="20">3</td> <td class="xl68" style="border-top: medium none;">9:32</td> <td class="xl69" style="border-top: medium none; border-left: medium none;"> $ 5.60 </td> <td class="xl69" style="border-top: medium none; border-left: medium none;"> $ 2.30 </td> <td class="xl69" style="border-top: medium none; border-left: medium none;"> $ 568.22 </td> <td class="xl69" style="border-top: medium none; border-left: medium none;"> $ 565.00 </td> <td class="xl70" style="border-top: medium none; border-left: medium none;">1.50%</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">0.00397 </td> <td class="xl70" style="border-top: medium none; border-left: medium none;">0.00%</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75">
</td> <td class="xl75">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl72" style="height: 15pt;" height="20">4</td> <td class="xl68" style="border-top: medium none;">9:33</td> <td class="xl69" style="border-top: medium none; border-left: medium none;"> $ 5.30 </td> <td class="xl69" style="border-top: medium none; border-left: medium none;"> $ 2.20 </td> <td class="xl69" style="border-top: medium none; border-left: medium none;"> $ 568.39 </td> <td class="xl69" style="border-top: medium none; border-left: medium none;"> $ 565.00 </td> <td class="xl70" style="border-top: medium none; border-left: medium none;">1.50%</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">0.00397 </td> <td class="xl70" style="border-top: medium none; border-left: medium none;">0.00%</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75">
</td> <td class="xl75">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl72" style="height: 15pt;" height="20">5</td> <td class="xl68" style="border-top: medium none;">9:34</td> <td class="xl69" style="border-top: medium none; border-left: medium none;"> $ 5.30 </td> <td class="xl69" style="border-top: medium none; border-left: medium none;"> $ 2.15 </td> <td class="xl69" style="border-top: medium none; border-left: medium none;"> $ 569.26 </td> <td class="xl69" style="border-top: medium none; border-left: medium none;"> $ 565.00 </td> <td class="xl70" style="border-top: medium none; border-left: medium none;">1.50%</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">0.00397 </td> <td class="xl70" style="border-top: medium none; border-left: medium none;">0.00%</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75">
</td> <td class="xl75">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl72" style="height: 15pt;" height="20">6</td> <td class="xl68" style="border-top: medium none;">9:35</td> <td class="xl69" style="border-top: medium none; border-left: medium none;"> $ 5.51 </td> <td class="xl69" style="border-top: medium none; border-left: medium none;"> $ 2.00 </td> <td class="xl69" style="border-top: medium none; border-left: medium none;"> $ 569.00 </td> <td class="xl69" style="border-top: medium none; border-left: medium none;"> $ 565.00 </td> <td class="xl70" style="border-top: medium none; border-left: medium none;">1.50%</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">0.00397 </td> <td class="xl70" style="border-top: medium none; border-left: medium none;">0.00%</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75">
</td> <td class="xl75">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl72" style="height: 15pt;" height="20">7</td> <td class="xl68" style="border-top: medium none;">9:36</td> <td class="xl69" style="border-top: medium none; border-left: medium none;"> $ 6.00 </td> <td class="xl69" style="border-top: medium none; border-left: medium none;"> $ 2.25 </td> <td class="xl69" style="border-top: medium none; border-left: medium none;"> $ 568.70 </td> <td class="xl69" style="border-top: medium none; border-left: medium none;"> $ 565.00 </td> <td class="xl70" style="border-top: medium none; border-left: medium none;">1.50%</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">0.00397 </td> <td class="xl70" style="border-top: medium none; border-left: medium none;">0.00%</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75">
</td> <td class="xl75">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl72" style="height: 15pt;" height="20">8</td> <td class="xl68" style="border-top: medium none;">9:37</td> <td class="xl69" style="border-top: medium none; border-left: medium none;"> $ 6.00 </td> <td class="xl69" style="border-top: medium none; border-left: medium none;"> $ 2.36 </td> <td class="xl69" style="border-top: medium none; border-left: medium none;"> $ 568.66 </td> <td class="xl69" style="border-top: medium none; border-left: medium none;"> $ 565.00 </td> <td class="xl70" style="border-top: medium none; border-left: medium none;">1.50%</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">0.00397 </td> <td class="xl70" style="border-top: medium none; border-left: medium none;">0.00%</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75">
</td> <td class="xl75">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl72" style="height: 15pt;" height="20">9</td> <td class="xl68" style="border-top: medium none;">9:38</td> <td class="xl69" style="border-top: medium none; border-left: medium none;"> $ 6.00 </td> <td class="xl69" style="border-top: medium none; border-left: medium none;"> $ 2.40 </td> <td class="xl69" style="border-top: medium none; border-left: medium none;"> $ 567.61 </td> <td class="xl69" style="border-top: medium none; border-left: medium none;"> $ 565.00 </td> <td class="xl70" style="border-top: medium none; border-left: medium none;">1.50%</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">0.00397 </td> <td class="xl70" style="border-top: medium none; border-left: medium none;">0.00%</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75">
</td> <td class="xl75">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl72" style="height: 15pt;" height="20">10</td> <td class="xl68" style="border-top: medium none;">9:39</td> <td class="xl69" style="border-top: medium none; border-left: medium none;"> $ 6.00 </td> <td class="xl69" style="border-top: medium none; border-left: medium none;"> $ 2.35 </td> <td class="xl69" style="border-top: medium none; border-left: medium none;"> $ 566.34 </td> <td class="xl69" style="border-top: medium none; border-left: medium none;"> $ 565.00 </td> <td class="xl70" style="border-top: medium none; border-left: medium none;">1.50%</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">0.00397 </td> <td class="xl70" style="border-top: medium none; border-left: medium none;">0.00%</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75">
</td> <td class="xl75">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl72" style="height: 15pt;" height="20">11</td> <td class="xl68" style="border-top: medium none;">9:40</td> <td class="xl69" style="border-top: medium none; border-left: medium none;"> $ 3.70 </td> <td class="xl69" style="border-top: medium none; border-left: medium none;"> $ 2.90 </td> <td class="xl69" style="border-top: medium none; border-left: medium none;"> $ 565.56 </td> <td class="xl69" style="border-top: medium none; border-left: medium none;"> $ 565.00 </td> <td class="xl70" style="border-top: medium none; border-left: medium none;">1.50%</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">0.00397 </td> <td class="xl70" style="border-top: medium none; border-left: medium none;">0.00%</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75">
</td> <td class="xl75">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl72" style="height: 15pt;" height="20">12</td> <td class="xl68" style="border-top: medium none;">9:41</td> <td class="xl69" style="border-top: medium none; border-left: medium none;"> $ 3.70 </td> <td class="xl69" style="border-top: medium none; border-left: medium none;"> $ 3.30 </td> <td class="xl69" style="border-top: medium none; border-left: medium none;"> $ 565.93 </td> <td class="xl69" style="border-top: medium none; border-left: medium none;"> $ 565.00 </td> <td class="xl70" style="border-top: medium none; border-left: medium none;">1.50%</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">0.00397 </td> <td class="xl70" style="border-top: medium none; border-left: medium none;">0.00%</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75">
</td> <td class="xl75">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl72" style="height: 15pt;" height="20">13</td> <td class="xl68" style="border-top: medium none;">9:42</td> <td class="xl69" style="border-top: medium none; border-left: medium none;"> $ 3.74 </td> <td class="xl69" style="border-top: medium none; border-left: medium none;"> $ 3.40 </td> <td class="xl69" style="border-top: medium none; border-left: medium none;"> $ 565.50 </td> <td class="xl69" style="border-top: medium none; border-left: medium none;"> $ 565.00 </td> <td class="xl70" style="border-top: medium none; border-left: medium none;">1.50%</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">0.00397 </td> <td class="xl70" style="border-top: medium none; border-left: medium none;">0.00%</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75">
</td> <td class="xl75">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl72" style="height: 15pt;" height="20">14</td> <td class="xl68" style="border-top: medium none;">9:43</td> <td class="xl69" style="border-top: medium none; border-left: medium none;"> $ 3.80 </td> <td class="xl69" style="border-top: medium none; border-left: medium none;"> $ 3.60 </td> <td class="xl69" style="border-top: medium none; border-left: medium none;"> $ 564.42 </td> <td class="xl69" style="border-top: medium none; border-left: medium none;"> $ 565.00 </td> <td class="xl70" style="border-top: medium none; border-left: medium none;">1.50%</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">0.00397 </td> <td class="xl70" style="border-top: medium none; border-left: medium none;">0.00%</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75">
</td> <td class="xl75">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl72" style="height: 15pt;" height="20">….</td> <td class="xl68" style="border-top: medium none;">…</td> <td class="xl69" style="border-top: medium none; border-left: medium none;"> … </td> <td class="xl69" style="border-top: medium none; border-left: medium none;"> … </td> <td class="xl69" style="border-top: medium none; border-left: medium none;"> … </td> <td class="xl69" style="border-top: medium none; border-left: medium none;"> … </td> <td class="xl70" style="border-top: medium none; border-left: medium none;">…</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">…</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">…</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75">
</td> <td class="xl75">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl72" style="height: 15pt;" height="20">391</td> <td class="xl68" style="border-top: medium none;">15:59</td> <td class="xl69" style="border-top: medium none; border-left: medium none;"> $ 1.05 </td> <td class="xl69" style="border-top: medium none; border-left: medium none;"> $ 0.05 </td> <td class="xl69" style="border-top: medium none; border-left: medium none;"> $ 566.00 </td> <td class="xl69" style="border-top: medium none; border-left: medium none;"> $ 565.00 </td> <td class="xl70" style="border-top: medium none; border-left: medium none;">1.50%</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">0.00397 </td> <td class="xl70" style="border-top: medium none; border-left: medium none;">0.00%</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75">
</td> <td class="xl75">
</td> </tr> </tbody></table>

 

[mlarson]

I should add that the desired output in cells I2:J4 is as follows. I just need to figure out a way to create a macro or formula to avoid doing this shorthand for a huge spreadsheet (the one I'm working with is much more than 390 rows). Here is the desired output for I2:J4...


20.26% (I2) 10.43% (J2)
19.05% (I3) 8.19% (J3)
18.01% (I4) 7.84% (J4)

 

[Sal Paradise]

What is the formula for calculating volatility?

 

[mlarson]

Formula for calculating volatility...
That's the problem, I'm not sure of the formula and was hoping someone on here would know.

Thank you for your quick response and I hope this slight clarification allows somebody to figure out this formula because I'm stuck.

 

[Sal Paradise]

All I can find on the net are paid add-ins:
http://www.hoadley.net/options/develtoolsimpliedvolcalc.htm
http://power4xl.com/black_scholes/functions/implied_volatility.html

I don't think the formula is going to pop out here any time soon. In the future, if you are looking for specific domain knowledge for your formula, you would be better off stating that up-front so that people know exactly what you're looking for.

 

[mlarson]

OK, let me try this again. I think this is a better way to explain what I'm trying to do, and the information below is is more informative than what I originally posted. Apologies for the confusion. The top part of the table below are the formulas that produce the bottom part of the table. I don't need both tables, they are just provided to show the calculations behind the values.

The calculations on the table below produce the Call Price if I know Volatility. However, I want to know how to find Volatility if I know the Call Price, Stock Price, Strike Price, Risk-free Rate, Term (yrs), and Div Yield. I don't need columns J through K but they might be necessary to develop a macro that will accomplish what I'm trying to figure out. Is there a macro or a formula to figure this out? There would be about 10,000 rows of data. Thanks so much for your help!

<table border="0" cellpadding="0" cellspacing="0" width="2666"><col style="width: 51pt;" width="68"> <col style="width: 299pt;" width="398"> <col style="width: 78pt;" width="104"> <col style="width: 87pt;" width="116"> <col style="width: 83pt;" width="110"> <col style="width: 92pt;" width="122"> <col style="width: 101pt;" width="134"> <col style="width: 98pt;" width="130"> <col style="width: 71pt;" width="94"> <col style="width: 288pt;" width="384"> <col style="width: 125pt;" width="166"> <col style="width: 110pt;" width="146"> <col style="width: 122pt;" width="162"> <col style="width: 66pt;" width="88"> <col style="width: 131pt;" width="174"> <col style="width: 74pt;" width="98"> <col style="width: 129pt;" width="172"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl72" style="height: 15pt; width: 51pt;" width="68" height="20">A</td> <td class="xl72" style="border-left: medium none; width: 299pt;" width="398">B</td> <td class="xl72" style="border-left: medium none; width: 78pt;" width="104">C</td> <td class="xl72" style="border-left: medium none; width: 87pt;" width="116">D</td> <td class="xl72" style="border-left: medium none; width: 83pt;" width="110">E</td> <td class="xl72" style="border-left: medium none; width: 92pt;" width="122">F</td> <td class="xl72" style="border-left: medium none; width: 101pt;" width="134">G</td> <td class="xl72" style="border-left: medium none; width: 98pt;" width="130">H</td> <td class="xl72" style="border-left: medium none; width: 71pt;" width="94">I</td> <td class="xl72" style="border-left: medium none; width: 288pt;" width="384">J</td> <td class="xl72" style="border-left: medium none; width: 125pt;" width="166">K</td> <td class="xl72" style="border-left: medium none; width: 110pt;" width="146">L</td> <td class="xl72" style="border-left: medium none; width: 122pt;" width="162">M</td> <td class="xl72" style="border-left: medium none; width: 66pt;" width="88">N</td> <td class="xl72" style="border-left: medium none; width: 131pt;" width="174">O</td> <td class="xl72" style="border-left: medium none; width: 74pt;" width="98">P</td> <td class="xl72" style="border-left: medium none; width: 129pt;" width="172">Q</td> </tr> <tr style="height: 37.5pt;" height="50"> <td class="xl72" style="height: 37.5pt; border-top: medium none;" height="50"> </td> <td class="xl73" style="border-top: medium none; border-left: medium none;">Call Price</td> <td class="xl74" style="border-top: medium none; border-left: medium none; width: 78pt;" width="104">Stock Price</td> <td class="xl74" style="border-top: medium none; border-left: medium none; width: 87pt;" width="116">Strike Price</td> <td class="xl73" style="border-top: medium none; border-left: medium none;">Volatility</td> <td class="xl73" style="border-top: medium none; border-left: medium none;">Variance</td> <td class="xl74" style="border-top: medium none; border-left: medium none; width: 101pt;" width="134">Riskfree Rate</td> <td class="xl73" style="border-top: medium none; border-left: medium none;">Term (yrs)</td> <td class="xl73" style="border-top: medium none; border-left: medium none;">Div Yield</td> <td class="xl73" style="border-top: medium none; border-left: medium none;">d1</td> <td class="xl73" style="border-top: medium none; border-left: medium none;">N(d1)Delta</td> <td class="xl73" style="border-top: medium none; border-left: medium none;">d2</td> <td class="xl73" style="border-top: medium none; border-left: medium none;">N(d2)</td> <td class="xl73" style="border-top: medium none; border-left: medium none;">d1-</td> <td class="xl73" style="border-top: medium none; border-left: medium none;">N(d1-)Delta</td> <td class="xl73" style="border-top: medium none; border-left: medium none;">d2-</td> <td class="xl73" style="border-top: medium none; border-left: medium none;">N(d2-)</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl72" style="height: 15pt; border-top: medium none;" height="20">3</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">=(EXP((0-I3)*H3)*C3*K3)-D3*(EXP((-G3)*H3))*M3</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">568.06</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">565</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">0.355</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">=E3^2</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">0.015</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">=7/365</td> <td class="xl79" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">=(LN(C3/D3)+(G3-I3+(F3/2))*H3)/(E3*SQRT(H3))</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">=NORMSDIST(J3)</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">=J3-E3*(H3^(0.5))</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">=NORMSDIST(L3)</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">=-J3</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">=NORMSDIST(N3)</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">=-L3</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">=NORMSDIST(P3)</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl72" style="height: 15pt; border-top: medium none;" height="20">4</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">=(EXP((0-I4)*H4)*C4*K4)-D4*(EXP((-G4)*H4))*M4</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">567.32</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">565</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">0.355</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">=E4^2</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">0.015</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">=7/365</td> <td class="xl79" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">=(LN(C4/D4)+(G4-I4+(F4/2))*H4)/(E4*SQRT(H4))</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">=NORMSDIST(J4)</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">=J4-E4*(H4^(0.5))</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">=NORMSDIST(L4)</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">=-J4</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">=NORMSDIST(N4)</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">=-L4</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">=NORMSDIST(P4)</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl72" style="height: 15pt; border-top: medium none;" height="20">5</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">=(EXP((0-I5)*H5)*C5*K5)-D5*(EXP((-G5)*H5))*M5</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">567.95</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">565</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">0.355</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">=E5^2</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">0.015</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">=7/365</td> <td class="xl79" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">=(LN(C5/D5)+(G5-I5+(F5/2))*H5)/(E5*SQRT(H5))</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">=NORMSDIST(J5)</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">=J5-E5*(H5^(0.5))</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">=NORMSDIST(L5)</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">=-J5</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">=NORMSDIST(N5)</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">=-L5</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">=NORMSDIST(P5)</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl72" style="height: 15pt; border-top: medium none;" height="20">6</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">=(EXP((0-I6)*H6)*C6*K6)-D6*(EXP((-G6)*H6))*M6</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">569.21</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">565</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">0.355</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">=E6^2</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">0.015</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">=7/365</td> <td class="xl79" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">=(LN(C6/D6)+(G6-I6+(F6/2))*H6)/(E6*SQRT(H6))</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">=NORMSDIST(J6)</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">=J6-E6*(H6^(0.5))</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">=NORMSDIST(L6)</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">=-J6</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">=NORMSDIST(N6)</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">=-L6</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">=NORMSDIST(P6)</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl72" style="height: 15pt; border-top: medium none;" height="20">7</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">=(EXP((0-I7)*H7)*C7*K7)-D7*(EXP((-G7)*H7))*M7</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">571.11</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">565</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">0.355</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">=E7^2</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">0.015</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">=7/365</td> <td class="xl79" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">=(LN(C7/D7)+(G7-I7+(F7/2))*H7)/(E7*SQRT(H7))</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">=NORMSDIST(J7)</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">=J7-E7*(H7^(0.5))</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">=NORMSDIST(L7)</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">=-J7</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">=NORMSDIST(N7)</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">=-L7</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">=NORMSDIST(P7)</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl72" style="height: 15pt; border-top: medium none;" height="20">8</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">=(EXP((0-I8)*H8)*C8*K8)-D8*(EXP((-G8)*H8))*M8</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">569.88</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">565</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">0.355</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">=E8^2</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">0.015</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">=7/365</td> <td class="xl79" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">=(LN(C8/D8)+(G8-I8+(F8/2))*H8)/(E8*SQRT(H8))</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">=NORMSDIST(J8)</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">=J8-E8*(H8^(0.5))</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">=NORMSDIST(L8)</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">=-J8</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">=NORMSDIST(N8)</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">=-L8</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">=NORMSDIST(P8)</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl72" style="height: 15pt; border-top: medium none;" height="20">9</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">=(EXP((0-I9)*H9)*C9*K9)-D9*(EXP((-G9)*H9))*M9</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">569.25</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">565</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">0.355</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">=E9^2</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">0.015</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">=7/365</td> <td class="xl79" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">=(LN(C9/D9)+(G9-I9+(F9/2))*H9)/(E9*SQRT(H9))</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">=NORMSDIST(J9)</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">=J9-E9*(H9^(0.5))</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">=NORMSDIST(L9)</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">=-J9</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">=NORMSDIST(N9)</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">=-L9</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">=NORMSDIST(P9)</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt;" height="20">
</td> <td class="xl67">
</td> <td class="xl67">
</td> <td class="xl67">
</td> <td class="xl68">
</td> <td class="xl69">
</td> <td class="xl69">
</td> <td class="xl70">
</td> <td class="xl71">
</td> <td class="xl70">
</td> <td class="xl70">
</td> <td class="xl70">
</td> <td class="xl70">
</td> <td class="xl70">
</td> <td class="xl70">
</td> <td class="xl70">
</td> <td class="xl70">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt;" height="20">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl72" style="height: 15pt;" height="20">A</td> <td class="xl72" style="border-left: medium none;">B</td> <td class="xl72" style="border-left: medium none;">C</td> <td class="xl72" style="border-left: medium none;">D</td> <td class="xl72" style="border-left: medium none;">E</td> <td class="xl72" style="border-left: medium none;">F</td> <td class="xl72" style="border-left: medium none;">G</td> <td class="xl72" style="border-left: medium none;">H</td> <td class="xl72" style="border-left: medium none;">I</td> <td class="xl72" style="border-left: medium none;">J</td> <td class="xl72" style="border-left: medium none;">K</td> <td class="xl72" style="border-left: medium none;">L</td> <td class="xl72" style="border-left: medium none;">M</td> <td class="xl72" style="border-left: medium none;">N</td> <td class="xl72" style="border-left: medium none;">O</td> <td class="xl72" style="border-left: medium none;">P</td> <td class="xl72" style="border-left: medium none;">Q</td> </tr> <tr style="height: 37.5pt;" height="50"> <td class="xl72" style="height: 37.5pt; border-top: medium none;" height="50"> </td> <td class="xl73" style="border-top: medium none; border-left: medium none;">Call Price</td> <td class="xl74" style="border-top: medium none; border-left: medium none; width: 78pt;" width="104">Stock Price</td> <td class="xl74" style="border-top: medium none; border-left: medium none; width: 87pt;" width="116">Strike Price</td> <td class="xl73" style="border-top: medium none; border-left: medium none;">Volatility</td> <td class="xl73" style="border-top: medium none; border-left: medium none;">Variance</td> <td class="xl74" style="border-top: medium none; border-left: medium none; width: 101pt;" width="134">Riskfree Rate</td> <td class="xl73" style="border-top: medium none; border-left: medium none;">Term (yrs)</td> <td class="xl73" style="border-top: medium none; border-left: medium none;">Div Yield</td> <td class="xl73" style="border-top: medium none; border-left: medium none;">d1</td> <td class="xl73" style="border-top: medium none; border-left: medium none;">N(d1)Delta</td> <td class="xl73" style="border-top: medium none; border-left: medium none;">d2</td> <td class="xl73" style="border-top: medium none; border-left: medium none;">N(d2)</td> <td class="xl73" style="border-top: medium none; border-left: medium none;">d1-</td> <td class="xl73" style="border-top: medium none; border-left: medium none;">N(d1-)Delta</td> <td class="xl73" style="border-top: medium none; border-left: medium none;">d2-</td> <td class="xl73" style="border-top: medium none; border-left: medium none;">N(d2-)</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl72" style="height: 15pt; border-top: medium none;" height="20">3</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">12.79</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">568.06</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">565</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">0.355</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">0.126</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">0.015</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">0.0192</td> <td class="xl79" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">0.1402</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">0.5557</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">0.091</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">0.5363</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">-0.1402</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">0.4443</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">-0.091</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">0.4637</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl72" style="height: 15pt; border-top: medium none;" height="20">4</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">12.39</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">567.32</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">565</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">0.355</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">0.126</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">0.015</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">0.0192</td> <td class="xl79" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">0.1137</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">0.5453</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">0.0645</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">0.5257</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">-0.1137</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">0.4547</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">-0.0645</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">0.4743</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl72" style="height: 15pt; border-top: medium none;" height="20">5</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">12.73</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">567.95</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">565</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">0.355</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">0.126</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">0.015</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">0.0192</td> <td class="xl79" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">0.1363</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">0.5542</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">0.0871</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">0.5347</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">-0.1363</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">0.4458</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">-0.0871</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">0.4653</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl72" style="height: 15pt; border-top: medium none;" height="20">6</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">13.44</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">569.21</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">565</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">0.355</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">0.126</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">0.015</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">0.0192</td> <td class="xl79" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">0.1814</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">0.572</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">0.1322</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">0.5526</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">-0.1814</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">0.428</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">-0.1322</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">0.4474</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl72" style="height: 15pt; border-top: medium none;" height="20">7</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">14.55</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">571.11</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">565</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">0.355</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">0.126</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">0.015</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">0.0192</td> <td class="xl79" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">0.2491</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">0.5984</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">0.1999</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">0.5792</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">-0.2491</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">0.4016</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">-0.1999</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">0.4208</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl72" style="height: 15pt; border-top: medium none;" height="20">8</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">13.83</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">569.88</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">565</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">0.355</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">0.126</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">0.015</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">0.0192</td> <td class="xl79" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">0.2053</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">0.5813</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">0.1561</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">0.562</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">-0.2053</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">0.4187</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">-0.1561</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">0.438</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl72" style="height: 15pt; border-top: medium none;" height="20">9</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">13.47</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">569.25</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">565</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">0.355</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">0.126</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">0.015</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">0.0192</td> <td class="xl79" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">0.1828</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">0.5725</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">0.1336</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">0.5531</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">-0.1828</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">0.4275</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">-0.1336</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">0.4469</td> </tr> </tbody></table>

 

[Sal Paradise]

Here is your simplified expression for Call Price:
(EXP((0-DivYield)*Term)*StockPrice*(NORMSDIST((LN(StockPrice/StrikePrice)+(RiskfreeRate-DivYield+(Variance/2))*Term)/(Volatility*SQRT(Term)))))-StrikePrice*(EXP((-RiskfreeRate)*Term))*(NORMSDIST(((LN(StockPrice/StrikePrice)+(RiskfreeRate-DivYield+(Variance/2))*Term)/(Volatility*SQRT(Term)))-Volatility*(Term^(0.5))))

Now you have to solve for Volatility! Easy as pie...

(or not)

Yeah, that's a pain. I suggest getting a big whiteboard and writing it all down and doing your best impression of a Junior High algebra student (sorry I can't be of more help -- the only really hard part looks to be the EXP() function, for which the inverse is LN(), but otherwise it doesn't seem like the Math is that rough).

So EXP(x) = y : LN = x
NORMSDIST(a) = b : NORMINV(b) = a

solve for Volatility and you have your answer. (Remember to substitute Variance for Volatility * Volatility)

 

[Sal Paradise]

Slightly Simplified:

a = EXP((0-DivYield)*Term)*StockPrice
b = LN(StockPrice/StrikePrice)
c = StrikePrice*(EXP((-RiskfreeRate)*Term))
=a*(NORMSDIST((b+(RiskfreeRate-DivYield+(Variance/2))*Term)/(Volatility*SQRT(Term))))
-c*(NORMSDIST((b+(RiskfreeRate-DivYield+(Volatility*Volatility/2))*Term)/(Volatility*SQRT(Term))-Volatility*(Term^(0.5))))

 

[Sal Paradise]

And with that, I'm spent. Just too many complications. It would take me weeks to sort through.

 

[mlarson]

Sal, thanks for your effort on that. But let's say the following were given (without any calculations needed): Call Price, Stock Price, Strike Price, Risk-free Rate, Term (yrs), and Div Yield. If those values are given, do you know how we could calculate volatility (possibly backing into it because we have the formulas mentioned above)? Perhaps something like GOALSEEK could help. What do you think?