Code are provided for problem a-c as the attachments, XLL+ will be needed.
(Comparing binomial and trinomial option pricing) This problem asks you to create one
Excel add-in which has functions for the Black-Scholes formula, binomial option pricing
and trinomial option pricing. You can use the C code you created in the solutions to
previous homework assignments or the code provided in the solutions or lecture files.
The three Excel functions created in this problem should be placed in a single Excel add-
in called xllp bs.xll. Make sure that the .xll is compiled in Release mode. The XLL+
source code should be in a file named xllp [url removed, login to view] and the non-XLL+ source code (e.g.,
which contains the option pricing and supporting routines) should be in a separate file
bs [url removed, login to view] with corresponding header file bs routines.h. You might also want to
have a separate file containing NR routines nr [url removed, login to view] and corresponding header
file nr routines.h.
(a) Use XLL+ to create an Excel function bs call(S, σ, δ, r , T, K) which returns the
Black-Scholes price of a European call option. Test the function with the parameters:
S = 100, σ = 0.6, δ = 0.01, r = 0.05, T = 3, and K = 102.
(b) Use XLL+ to create an Excel function binomial call(S, σ, δ, r , T, K, n) which returns
the binomial approximation to the price of a European call option using n time steps
and the CPU time in seconds. The function binomial call is an Excel array function
because it returns two results in an array. The results should appear in a column.
Test the function with the same parameters as in (a) together with n = 100.
(c) Use XLL+ to create an Excel function trinomial call(S, σ, δ, r , T, K, λ, n) which
returns the trinomial approximation to the price of a European call option using the
trinomial stretch parameter λ and n time steps and also returns the CPU time in
seconds. The two results should be returned in a column array. Test the function
with the same parameters as in (a) together with λ =
√3/2 and n = 100.
(d) (Extra credit) This part asks you to create a graph in Excel of average absolute error
versus average CPU time. The graph should contain two series, one corresponding
to the binomial method and the other corresponding to the trinomial method. Each
series of results should correspond to n = 2000, 4000, 8000, and 16000 time steps.
For each value of n, price European call options using the binomial method, with
the same parameters as in (a), but for the eleven strikes 90, 92, . . . , 110. For each
strike compute the absolute value of the error (where the error is the binomial price
compared to the Black-Scholes price), and then average the results to compute the
average absolute error. Also average the CPU time over the eleven options priced.
Then repeat for each of the four values of n, giving four values of average absolute
error and average CPU time for the binomial method. Repeat the process for the
trinomial method. Plot the resulting two series of binomial and trinomial results of
the average absolute error versus average CPU time with the axes both in logarithmic
Hint: Set up the spreadsheet and graph for smaller values of n, e.g, n = 200, 400, 800
and 1600. When this is working properly, change the values of n to the larger set.