Business and Financial Statistics Using Minitab 12 and Microsoft Excel 97

USING MICROSOFT EXCEL TO ESTIMATE ALTERNATIVE OPTION PRICING MODELS 349

The put option formula for the currency can be defined as

P = Xe-"T'N(-d,)

-~

e-'i(~)~(-d~)

(22.6)

where

P = the price of the put option. The other notations have been defined in equation (22.5).

Following example 19A.2 of Lee et. al. (2000, pp. 876-77), assume that S = 130, X = 125, r = 0.06, rf= 0.02, CT =0.15, and T=4/12. The following shows how to set up Microsoft Excel to solve the problem.

The following shows the answer to the problem in Microsoft Excel.

APPENDIX A REFERENCES

A.l

Microsoft Excel References

I feel that one of the best book on Microsoft Excel programming is by John Walkenbach. Microsoft Excel 2000 Power Programming with VBA, John Walkenbach, IDG Books Worldwide, New York, 1999. The Internet has made access to information very easy. They following are excellent Internet Sites about Microsoft Excel Development. Microsoft Excel Home Page http ://www.microsoft.com/excel John Walkenbach’s Excel Page http://www.j-walk.com/ss

Stephen Bullen’s Excel Page http://www.bmsltd.co.uk/excel

Chip Pearson’s Excel Page http://www.cpearson.com/excel

A.2 Statisical References Statistics for Business and Financial Economics, Cheng-Few Lee, John C. Lee, Alice C. Lee, Second Edition, World Scientific, Singapore and New Jersey, 2000. Statistics for Managers using Microsoft Excel, David M. Levine, Mark L. Berenson, David Stephan, Prentice Hall, New Jersey, 1998.

A.3

MINITAB References

http://www.minitab.com/resources/ctl/Business.htm 35 1

INDEX #, 153 add-in, 6 adjpint, 33 1 adjzint, 329 alt-F11,34 analysis tools pack, 189 anova, 190 aovoneway, 187 arithmetic mean, 39 autocorrelation, 262 average, 41 Bayesian, 342 binomdi st,79 binomial, 121,79 binomial distribution, 121 binomial random variable, 78 bintreg, 224 box-and-wisker plot, 30 boxplot, 30,26 1 cdf, 72 chisquare, 132 center, 49 central limit theorem, 107,136 chi-square distribution, 131 coefficient of skewness, 5 1 comb, 65 combin, 66 combination,64 confidence, 153 continuous random variable, 85 correl, 214 correlation,266 counts, 19, 24 culmuniformpdf, 90 cumpercents, 25 describe, 43,150,172 discrete, 73 discrete random variable, 70 dotplot, 27, 177, 260 352

drawboxplot, 32 durbin-Watson, 262 exponential, 136 exponential distribution, 134 expsmth, 290 F distribution, 133, 139 fisheridealpriceindex, 3 17 formula bar, 4 1 heteroscedasticity, 26 1 histrogram, 28 holtreg, 298 holtzero, 297 hypothesis testing, 168 increment, 28 invcdf, 239 kurskal-wallis, 274 laspeyrespriceindex, 3 11 laspeyresquantityindex, 320 lognormal, 116, 101 lognormal distribution, 100, I 16 ma.mac, 285 mann-whitney, 27 1 maximum, 277,285 mean, 39 median, 42 noecho, 153 normal, 113 normal distribution, 9 1, 113 normalpdf, 96 paaschepriceindex, 3 14 paaschequantityindex,322 pdf, 79,82 percents, 25 perm, 59 permut, 63 permutation, 59 PhYP2,179 pint, 161 Poisson, 124

INDEX

Poisson random variable, 82 pooled, 177 population, 56 predict, 226, 245 priceindex, 307 print, 200 probability, 57 professional graphs, 28 p-value, 171 random 100 random, 108, 121, 113, 116, 124, 136, 324 rank, 281 regress, 222,226,245,252 regression, 207, 236 same, 177 sample, 56 sample size, 333 set, 54 skewness, 53 stack, 281 standard deviation, 44 standard graphs, 28 standard normal distribution, 102 start, 28 stdev, 44 stem and leaf, 29 stepwise, 268 stepwise regression, 248 sumproduct, 74 sumxmy2,263 t distribution, 129, 145 table, 58 tally, 25 template, 154 tint, 159 trend, 288 tukey, 194 twosample, 177 uniform distribution, 85, 109 uniformpdf, 88 var, 46 variance, 45 visual basic editor, 34

wilcoxon, 277 West, 278 xint, 166 z score, 48 zint, 151 zinterval, 150 ztest, 169

353