1. Write a VBA function to calculate implied volatility using the Black Scholes Model for a non-dividend paying stock. A numerical root finding algorithm is required to compute the implied volatility. Use the Newton Raphson method or the Bisection Method, information on which along with VBA example code is provided below. You can cross check your answer for the implied volatility calculated by your VBA function using the Goal Seek function in Excel. You can also cross check by computing the option price using the implied volatility you solved for and making sure you get back the same option price you input to compute the implied volatility. Please feel free to research on the internet on this topic, but refrain from blindly copying code found on the internet.
The pseudo code is as follows:
Function BS_impliedVol(CallorPut, Stock price, Strike, Risk free rate, Time to expiry, Option price, initial volatility guess)
Set maximum iterations to some number (e.g. 1000)
Set tolerance to some small number (e.g. 0.00001)
Set change in volatility to some small number (e.g. 0.00001)
Set first volatility value to initial volatility guess
Loop from one to max iterations
Compute first option price with the first volatility value (use the Black
Scholes function you wrote in one of the home works)
Compute second volatility value by reducing first volatility value by the change in volatility defined above
Compute second option price with the second volatility value (use the
Black Scholes function you wrote in one of the home works)
Compute dx as second option price minus first option price divided by change in volatility
If absolute value of dx is less than tolerance defined above then exit the loop. If not then compute first volatility value as the first
volatility – (Input Option price – first Option price)/dx
Continue Loop
Set BS_impliedVol to first volatility value. (This is the answer that will be output to the cell when you use the function in the spreadsheet)
End Function
The input/output spreadsheet to test the implied volatility function should look like this:
2. Write VBA functions for calculating the Option Greeks (Delta, Gamma, Theta and Vega) using the formulas found in your book.
3. Put together an Option Portfolio Profit and Loss and Risk Management spreadsheet using the VBA functions you developed for the Black Scholes Option Price, Implied Volatility and the Greeks, to measure the profit and loss and the risk of your option portfolio. A sample spreadsheet is provided to you as a guide. Get data for options on stocks that you are interested in, from Yahoo finance or any other source, to input into your spreadsheet and determine the profit and loss and risk of your option portfolio.
Attachment:- option P&L-2.xlsx