A small working assignment to create one Excel add-in (Should be very basic)

1. (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 assign- ments or the code provided in the solutions or lecture les. 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 le named xllp [login to view URL] and the non-XLL+ source code (e.g., which contains the option pricing and supporting routines) should be in a separate le bs [login to view URL] with corresponding header le bs routines.h. You might also want to have a separate le containing NR routines nr [login to view URL] and cor- responding header le nr routines.h. 1 B9122: Homework Assignment #8 (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  = p 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 scale. 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.