1 so far the excel calculation is not very so

Info iconThis preview shows page 1. Sign up to view the full content.

View Full Document Right Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: plied vol. options, As we can see, all options suggest that the As stock’s volatility ranges from 11% to 16% stock’s 15-11 VBA for implied vol. (1) So far the excel calculation is not very So convenient, so we wonder if we can write a code to compute implied vol. easily? code See “ImpliedVol_VBA” tab for the code See “ImpliedVol1” in VBA (“Alt+F11”) that uses iterating volatility to fit the option price iterating 15-12 VBA for implied vol. (2) We let sigma increase at 0.1% at each step and We run all the way to 200%: run sigma = 0.001 sigma For itCount = 1 To 2000 'We let the trial run from 1 to 2000 For priceEst = BSMOptPrice1(optType, S0, K, T, r, q, sigma) priceEst If priceEst >= optPrice Then If ImpliedVol1 = sigma ImpliedVol1 Exit For End If End sigma = sigma + 0.001 sigma Next “optPrice” is the given option price “BSMOptPrice1” is the implied option price BSMOptPrice1” starting from very low value (because sigma starting from 0.001) starting 15-13 VBA for implied vol. (3) When sigma increases, implied option price When (BSMOptPrice1) increases as well Two scenarios: Two • (1) When the implied option price (BSMOptPrice1) (1) increases beyond the option price in the market (optPrice), we stop, we let the implied volatility equals sigma and skip from the “for loop” equals • (2) When the implied option price (BSMOptPrice1) (2) remains below the option price in the market (optPrice), we increase the sigma by 0.1% 15-14 VBA for implied vol. (4) Check if our VBA code “ImpliedVol1” works fine First, we use the BS model (“BSMOptPrice1”) to First, compute the market price of European option given volatility 40%, it’s 17.4034 given Then, we import the 17.4034 as the market price Then, into the VBA function “ImpliedVol1” and get the implied volatility = 40.00%, which confirms that our code is good our 15-15 VBA for implied vol. (5) Another way to check if our code works fine is Another to compare the results based on Goal Seek function in “Vol2” tab. What...
View Full Document

This document was uploaded on 03/03/2014.

Ask a homework question - tutors are online