Problem #1: Need to use VBA (Excel)

Effectively, I would like you to replicate assignment #1, where we created a chart with two series. The first series depicted the payoff at maturity for a European call option as a function of the underlying stock price and the second series showed the Black-Scholes value of a European call option as a function of the underlying stock price. In assignment #1, you used named formulas to drive the chart. In this assignment, I want you to create a user defined function to create the numbers for the x-axis and the two series (i.e. payoff and value). I would like your function to work for both European puts and calls on an asset that pay a continuous dividend yield. The appropriate equations are:

where:

The only thing on the workheet should be the input parameters and the chart. The input parameters are:

Option_Type as string Call or Put

Stock_Price as double, S $20

Exercise_Price as double, X $20

Time_to_Maturity as double, T 1 year

Risk_Free_Rate as double, r 2%

Volitility as double, [\sigma] 30%

Optional Div-Yield as double = 0, q 0%

Feel free to shorten the parameter names. You will need to create a function that accepts the above input parameters and returns an array of N rows by 3 columns. The first column should have a range of stock prices from 0 to 2*Exercise Price. Note: The N refers to the number of stock prices. The second column should have the payoff at maturity of the option for each stock price and the third column should have the value of the option at some time prior to maturity for each stock price. You might want to consider creating a private function that just returns the value of the option for a specified set of input parameters and then create a second function that calls the first function, to generate the N by 3 array. Note your functions should be able to deal with stock price, volatility and time to maturity equal to zero.

Once you have your function working, you can create a named formula to call your function in Excel and then used the named formula to create the chart.

C = Se-9(T) N(dj) - Xe-r(T) N(dz) C = Se-9(T) N(dj) - Xe-r(T) N(dz)