Effectively, I would like you to replicate the Black$-$Scholes problem from Assignment #$1,$ where we created a chart with two series. The first series depicted the payoff at maturity for a European call or put option as a function of the underlying stock price and the second series showed the Black$-$Scholes value of the option at some point prior to maturity as a function of the underlying stock price. So$,$ you will need the $5$ functions you wrote for Assignment #$1.$ I would like you to write yet another function that returns an array of N rows by $3$ columns. The $$N$$ refers to the number of stock prices. The first column should have a range of stock prices from $0$ to $2*$X$.$ 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 $($i$.$e$.$ the Black$-$Scholes price$).$ So$,$ this new function should create, internally in VBA, the data that was on the worksheet in assignment #$1.$ Your new function should call the $$Payoff$$ and $$BlackScholes$$ functions inside some sort of For Loop and store the results in the array. Once you have your function working, you can create a named formula to call your new function in Excel. Then create another $3$ named formulas that return each of the individual columns. Use the INDEX function to accomplish this task. For example:

Price$\_$Array $=$ NewFunction$(.....)$

Stock$\_$prices $=$ index$($Price$\_$Array,,$1)$

Option$\_$payoff $=$ index$($Price$\_$Array,,$2)$

Option$\_$Price $=$ index$($Price$\_$Array,,$3)$

Then used the named formulas to create the chart. See the $$Damped Sin Wave$$ video for the technique. In the end, the only thing on that should be on the worksheet are input parameters and the chart.

The inputs to the new function are the same as in assignment #$1.$

Parameter Name Parameter Value Description Data Type

Security Call or Put Option Type String

S $$20$ Stock Price Double

X $$20$ Exercise Price Double

r $2\%$ Risk Free Rate $*$ Double

q $0\%$ Dividend Yield $*$ Double

sigma $30\%$ Volatility of Asset Return $*$ Double

T $3$ Time to Maturity $($years$)$ Double

$*\%$ per year $($continuously compounded$)$

Actually, you don$$t need the current stock price, S$,$ as an input to this new function, since you will create a range of stock prices from $0$ to $2*$X inside this function. These will become the first column of the array.