VBA Portfolio Management Tool Development

The VBA Program (15%):
You will have to develop on VBA a portfolio management tool for options and futures:
Where you will have the recap of your portfolio of options:
Start with these 3 options :
Call/Put Maturity Strike Price Long/Short
Call 1 85 20.093108 Short
Put 0.5 100 4.22943923 Long
Put 0.083 100 2.30297447 Long
The spot price is 100 while the dividend rate and the risk free rates are supposed to be
equal to 0% each.
Tasks:
1. You need to build a VBA interface (“a userform”) where you can rapidly enter a new
option or future position such as the following one:
You will add to your portfolio the following 3 options :
Call/Put Maturity Strike Price Long/Short
Call 1 85 20.093108 Short
Put 0.5 100 4.22943923 Long
Put 0.083 100 2.30297447 Long
The spot price is 100 while the dividend rate and the risk free rates are supposed to be
equal to 0% each.
2. You have to extract the implied volatility of each option using the newton Raphson
method described in the previous session (see the code in the file Calculate Implied
Volatility with VBA.pdf and the videos associated in the folder Implied Volatility Algo
on moodle) and build an option price monitor with all the greeks associated to them. Use
for the greeks the function provided in the file Function European Option.doc
3. You have to aggregate the greeks and the P&L of all these options and find the portfolio
greeks and P&L relative to the spot price in cell B2. You should use a mark-to-market
method for the futures positions and a mark-to-model method for the options positions.
The greeks and Black and Scholes pricer functions are available in the folder Implied
Volatility Algo