@d0rian It seems either way, BS or BT will require coding. I would instead recommend a commercial excel add-in: This one seems to use BS: http://www.everytick.com/trading/options-trading/options-greeks/excel-options-implied-volatility.php This one seems to include BS and BT: http://www.hoadley.net/options/develtoolsaddin.htm
Black Scholes and/or binomial model calculate an option price given IV, t, risk free rate and dividend rate. BS can approximate binomial using continuous rate for dividend. Calculating Options using BS with Excel is quite easy as all the functions are available. Doing the reverse, i.e., calculate IV giving option price, t, risk free and dividend rates is harder. I had to do it by an iterative process. Using Excel to do iteration is kind of cumbersome unless you program it in Fortran or C++ for example. If you could program in C++ that would be the way to go.
Yes, I just took a quick look at the paper you posted prior. Very interesting. I need to read through it more carefully. Thanks for the link.
Thank you for the link. I just finished reading the paper. I understand the concepts but the math is kind of difficult for me and the closed form equation is still quite messy, a 31 parameter equation. A Tayler series expansion is easier. I don't know how easy is to set up in Excel and calculate it real time. They also ended up needing some simple iterative "polishing" process to get the accuracy to match iterative BS. Since the paper started with a normalized BS, what do you think if I just set up a look up table of C/IV in Excel with normalized stock/strike, then convert to the correct stock/strike if I want quick realtime IV. Doing any iterative process is difficult in Excel but a look up table is very easy. Regards,
I will take a look at this one too. If I keep this up I might as well enroll and get my PhD in finance. You must be teaching finance or investment at some University and a professional trader.