- Details
- Parent Category: Programming Assignments' Solutions

# We Helped With This Excel (VBA) Programming Homework: Have A Similar One?

SOLVED

Category | Programming |
---|---|

Subject | Excel (VBA) |

Difficulty | Undergraduate |

Status | Solved |

More Info | Excel Vba Assignment Help |

## Assignment Image

2. This question is about American Option pricing.
(a) In Excel, write a VBA function called AmericanOption that values an American
option using a binomial tree. The function inputs must include the option strike K,
spot price of the stock S, risk-free rate r, the stock price drift μ, volatility o and years
to expiry T.
The other input must be the number of time steps N per year. You should also include
two extra inputs. The first is called "OptionType" and can be "C" or "P" for call or
put. The other input is "ExerciseType". This can be "E" or "A" for European or
American. You should use these inputs in your valuation code.
(b) Show that when S = 100, K = 100, r = 0.01, q = 0.06, o = 0.12 and T = 1.0 you
recover the same American call option results as those in the course slides where we
used N = 4. You must provide a clear and easy-to-understand listing of your
code in the answer.
(c) For a European call option with S= 100, K = 100, r = 0.01, q = 0.06, 0 = 0.12 and
T = 1.0 show that as N increases from N = 10 to N = 120 in steps of 10 the error
(tree price V(N) minus Black-Scholes price) converges towards zero. Provide a table
of values and a plot of the error versus N.
(d) Using the tree, calculate the delta, gamma and theta of the option. Explain how
you do this. Calculate the delta, gamma and theta of the European call option and
compare them to the American option values.
(e) Now consider an American call option with the same characteristics but set the
dividend yield q = 0. Set N = 100 and calculate the value of the option for
S 80, 90, 100, 110, 120 and calculate the price difference to the value the Eu-
ropean call option with the same features. Show this in a table. What does the results
tell you ?