problems18
SIMULATING A LOGNORMAL PRICE PROCESSInitial stock price 12Mean return, m 12%Return sigma, s 35%Delta t 8% - =1/12Month Randomnumber Stockprice0 121 -1.4662 10.4517 - =C8*EXP($B$3*$B$5+$B$4*SQRT($B$5)*B9)2 1.9529 12.85953 -0.6471 12.16684 -0.3977 11.80515 0.6997 12.79726 -1.0366 11.64057 1.7332 14.00788 -1.3874 12.29809 -0.3265 12.018610 1.4052 13.991111 -0.1837 13.871812 0.3395 14.5002Cell B20 contains formula=NORMSINV(RAND()1 21 11 09876543210891 01 11 21 31 41 5A B C D12345678910111213141516171819202122232425261 21 11 09876543210891 01 11 21 31 41 5E F1234567891011121314151617181920212223242526模拟一个对数正态分布股票过程初始股票价格 12平均收益, m 12%收益标准差, s 35%Delta t 8% - =1/12月 随机数 股票价格0 121 0.4571 12.6935 - =C8*EXP($B$3*$B$5+$B$4*SQRT($B$5)*B9)2 -0.4280 12.27853 -0.1728 12.18734 -0.7064 11.46185 -0.6810 10.80726 0.5155 11.49947 -0.5861 10.94728 1.1831 12.46119 0.4219 13.134510 0.4137 13.832711 -0.4280 13.380412 1.3753 15.5295单元格B20 包含公式=NORMSINV(RAND()1 21 11 09876543210891 01 11 21 31 41 51 6A B C D12345678910111213141516171819202122232425261 21 11 09876543210891 01 11 21 31 41 51 6E F1234567891011121314151617181920212223242526SIMULATING A LOGNORMAL PRICE PROCESSInitial stock price 12Mean return, m 12%Return sigma, s 35%Delta t 0% - =1/250Day Randomnumber Stockprice0 121 -0.1012 11.9789 - =C8*EXP($B$3*$B$5+$B$4*SQRT($B$5)*B9)2 1.1082 12.28233 0.9296 12.54374 1.0490 12.84455 -0.6172 12.67636 -0.4999 12.54287 -0.8975 12.30208 1.0553 12.59889 -1.1255 12.294710 -0.9162 12.053611 1.1193 12.361912 1.0228 12.651013 0.4690 12.789214 -0.5475 12.641215 0.8537 12.888616 0.6044 13.068417 -0.5813 12.907518 0.5396 13.068919 0.4179 13.196720 0.5093 13.352721 -0.0935 13.331522 -1.2846 12.964023 1.7916 13.494924 1.5857 13.983725 -0.4707 13.845426 0.4002 13.975327 0.4513 14.122428 -0.6643 13.922929 -0.2846 13.842230 0.5330 14.013231 2.0925 14.684532 1.9297 15.332833 -0.2498 15.255534 0.8461 15.551435 1.2982 16.012536 1.7039 16.635937 1.0078 17.019438 0.3884 17.174639 0.8674 17.516040 -0.3302 17.396841 -1.1692 16.96042 5 02 2 52 0 01 7 51 5 01 2 51 0 07 55 02 50051 01 52 02 53 0A B C D1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484942 -0.0353 16.955343 -1.1736 16.528444 0.0799 16.565645 -1.6817 15.968046 -1.1063 15.589247 -1.1531 15.203648 0.1674 15.267349 1.0159 15.622050 -1.6486 15.069551 -0.4915 14.913652 1.0181 15.260853 0.7818 15.534754 -0.5689 15.347655 0.9086 15.667056 -0.6719 15.443157 0.3771 15.580058 -0.8913 15.283059 -0.5659 15.100060 -0.2791 15.014261 -0.5938 14.825262 0.4642 14.985663 0.5058 15.161664 -0.3330 15.057565 1.7702 15.666766 1.4163 16.173467 -1.0357 15.814468 -0.3250 15.708669 -0.7293 15.464570 -0.8452 15.185171 -0.1451 15.143772 -0.5584 14.964873 2.7248 15.902974 -1.2265 15.484375 0.4595 15.650276 -0.5364 15.472977 -2.5899 14.617878 -1.7529 14.068279 1.6090 14.585380 -1.0019 14.272281 -0.1326 14.237282 1.1589 14.614283 0.5265 14.792684 -0.4082 14.666585 0.3864 14.799686 0.5186 14.977787 -1.3946 14.529488 -0.0548 14.518789 0.8560 14.803690 -0.2552 14.727291 -0.9215 14.436892 1.3980 14.8977A B C D505152535455565758596061626364656667686970717273747576777879808182838485868788899091929394959697989910093 -1.3695 14.459894 0.3466 14.578295 1.1743 14.969396 1.9672 15.643097 -0.3863 15.517398 1.0015 15.872899 -0.2443 15.7948100 -0.3227 15.6899101 1.3375 16.1691102 0.7941 16.4637103 -0.4319 16.3149104 -0.6113 16.1033105 0.0025 16.1120106 -1.0460 15.7507107 0.2379 15.8415108 0.4922 16.0227109 -1.4042 15.5398110 2.4480 16.4130111 -0.1533 16.3653112 -0.2227 16.2926113 0.7696 16.5805114 -0.8387 16.2833115 0.2264 16.3730116 0.6793 16.6290117 1.2928 17.1200118 -0.9664 16.7657119 0.0265 16.7836120 -0.4881 16.6112121 0.1369 16.6696122 1.3219 17.1729123 -0.6657 16.9298124 0.9992 17.3168125 0.1490 17.3823126 2.4619 18.3647127 -0.1324 18.3198128 -1.3440 17.7913129 -1.5806 17.1878130 0.0077 17.1990131 -0.0972 17.1703132 0.9151 17.5300133 -0.9935 17.1569134 -0.2935 17.0540135 -0.5744 16.8466136 0.0061 16.8570137 1.9092 17.5931138 -0.5600 17.3847139 0.0590 17.4158140 -0.1868 17.3523141 -1.0710 16.9539142 -0.3362 16.8363143 -1.7948 16.1882A B C D101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151144 1.3328 16.6810145 0.6738 16.9398146 -0.0896 16.9143147 0.6494 17.1675148 0.1518 17.2335149 0.1731 17.3080150 -1.0943 16.9019151 -0.5531 16.7042152 -0.0519 16.6931153 2.1773 17.5257154 0.3698 17.6783155 0.4695 17.8716156 1.2194 18.3694157 -1.4109 17.8131158 -0.5960 17.5881159 -0.9259 17.2395160 0.4022 17.4020161 0.8279 17.7324162 -0.9191 17.3836163 -0.2032 17.3139164 0.3652 17.4628165 -1.3587 16.9535166 0.4245 17.1218167 0.3531 17.2645168 0.9216 17.6287169 -1.6636 16.9995170 -0.9155 16.6665171 0.4603 16.8452172 -0.1020 16.8153173 1.1824 17.2696174 -0.5923 17.0528175 -0.5869 16.8408176 2.1061 17.6530177 -0.1274 17.6117178 -0.0811 17.5886179 -0.9799 17.2194180 1.0558 17.6351181 -1.6820 16.9987182 1.8909 17.7338183 -0.3752 17.5956184 -1.1431 17.1642185 2.0286 17.9611186 -1.8518 17.2480187 0.7456 17.5434188 0.1111 17.5951189 -1.7737 16.9258190 -0.2568 16.8379191 -0.9849 16.4827192 0.8314 16.7969193 0.3209 16.9248194 0.2649 17.0325A B C D152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202195 -0.0764 17.0118196 2.0306 17.8025197 0.2177 17.8970198 0.7407 18.2016199 -0.0103 18.2062200 -0.0784 18.1834201 0.5365 18.4094202 -0.3884 18.2606203 0.2448 18.3687204 0.7952 18.7038205 1.1385 19.1904206 -0.7457 18.8853207 -1.2843 18.3648208 1.3672 18.9381209 -0.3103 18.8175210 -0.0768 18.7946211 -0.9654 18.4060212 2.4941 19.4601213 0.9671 19.8908214 -0.4076 19.7216215 -1.3657 19.1434216 0.8933 19.5351217 0.7547 19.8738218 -0.1047 19.8373219 0.0827 19.8832220 0.6216 20.1684221 1.5174 20.8673222 -0.3401 20.7208223 0.7781 21.0909224 -0.6316 20.8080225 0.8760 21.2256226 0.4841 21.4646227 -1.9315 20.5761228 -0.2620 20.4670229 0.1021 20.5231230 1.0702 21.0252231 -0.8628 20.6374232 0.6322 20.9382233 -0.7716 20.5935234 0.0269 20.6157235 0.7158 20.9550236 1.1659 21.5131237 -0.5918 21.2433238 0.2899 21.3904239 0.0522 21.4254240 2.1226 22.4669241 -1.9169 21.5438242 0.2562 21.6768243 0.8743 22.1110244 0.7375 22.4857245 1.6187 23.3172A B C D203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253246 0.5464 23.6123247 0.0226 23.6354248 0.8213 24.0807249 0.8778 24.5649250 -0.4827 24.3155A B C D2542552562572582 5 02 2 52 0 01 7 51 5 01 2 51 0 07 55 02 50051 01 52 02 53 0E F G H I J K12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849SIMULATING A LOGNORMAL PRICE PROCESS初始股票价格 12平均收益, m 12%收益的标准差, s 35%Delta t 0% - =1/250日 随机数 股票价格0 121 0.9219 12.2533 - =C8*EXP($B$3*$B$5+$B$4*SQRT($B$5)*B9)2 -0.0704 12.24013 -0.1387 12.20844 -0.4825 12.08455 -0.6165 11.92646 -1.5849 11.52087 -1.1789 11.22948 -0.4312 11.12819 -0.7401 10.952510 0.3228 11.036311 0.1414 11.076212 -1.2869 10.770313 2.7976 11.463914 -1.8503 11.009115 0.7703 11.203816 -0.8181 11.008017 -0.6906 10.846218 0.0366 10.860319 -0.6663 10.706420 0.3140 10.786321 1.3691 11.123522 -0.4979 11.006923 1.0496 11.271024 -0.2925 11.203625 1.3814 11.557126 -1.5443 11.174027 -0.1153 11.150928 0.7209 11.335729 -0.8498 11.129830 -0.9641 10.900031 -0.8353 10.705532 0.7826 10.897833 1.9603 11.386534 -0.2534 11.328335 -0.2522 11.270636 -0.2550 11.212537 -2.5261 10.607938 0.5895 10.752439 -0.9876 10.524940 0.4887 10.644541 -1.9216 10.206142 0.1156 10.23722 5 02 2 52 0 01 7 51 5 01 2 51 0 07 55 02 50024681 01 21 4A B C D123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495043 1.1921 10.516044 -0.9952 10.291845 -1.9486 9.862046 -1.3392 9.578547 1.2130 9.844048 0.3138 9.917349 0.1811 9.962050 1.8729 10.388651 -0.6233 10.251252 0.4732 10.364153 0.3281 10.444754 -0.5836 10.315655 -1.6918 9.941256 -0.3802 9.862657 -0.2479 9.813358 1.1284 10.066459 -0.7215 9.911660 -0.6462 9.775661 -0.0231 9.775262 -1.4366 9.473863 -0.2665 9.422664 1.3113 9.704865 2.2128 10.196966 -0.0564 10.189167 -0.5491 10.070868 1.6194 10.443469 -0.1386 10.416470 0.0245 10.427171 -0.1297 10.402272 0.6579 10.559873 -0.1020 10.541174 -0.7555 10.371275 2.0191 10.850576 -1.6634 10.463277 0.1556 10.504478 0.3683 10.595579 0.0329 10.608380 2.2464 11.154581 -0.4204 11.056582 1.2047 11.360783 0.3109 11.444784 -0.2232 11.393785 -1.3039 11.074986 1.6051 11.480987 -0.5073 11.358288 -0.2675 11.296689 1.5281 11.690890 0.6438 11.864391 0.6252 12.035492 -0.7818 11.834693 -0.6363 11.6747A B C D5152535455565758596061626364656667686970717273747576777879808182838485868788899091929394959697989910010194 1.9019 12.182595 1.0871 12.485396 0.5824 12.653397 -0.6071 12.490498 -1.2138 12.165199 -0.4755 12.0435100 -3.6310 11.1187101 1.0110 11.3758102 0.8585 11.5996103 0.9131 11.8421104 0.7113 12.0359105 -1.0777 11.7578106 0.5760 11.9143107 -1.3938 11.5579108 -0.2725 11.4939109 0.0720 11.5178110 -0.2404 11.4622111 -0.1910 11.4193112 0.5150 11.5558113 -1.6574 11.1448114 0.5674 11.2911115 0.4588 11.4118116 -2.0123 10.9199117 0.6987 11.0954118 0.6179 11.2536119 1.7373 11.7004120 0.8892 11.9387121 0.1665 11.9886122 0.5286 12.1355123 -0.0479 12.1285124 0.2204 12.1937125 -0.6460 12.0263126 -0.3321 11.9439127 1.1313 12.2527128 -1.8995 11.7538129 2.1818 12.3413130 0.5942 12.5107131 -1.6024 12.0805132 0.2145 12.1439133 -0.4908 12.0184134 -0.5503 11.8786135 -1.3189 11.5424136 -0.5601 11.4056137 -1.3600 11.0727138 -0.3788 10.9855139 0.0643 11.0064140 -0.3833 10.9187141 1.9781 11.4129142 0.7445 11.6081143 -0.3675 11.5196144 0.9189 11.7619A B C D102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152145 -2.1237 11.2272146 -3.0088 10.5088147 1.0003 10.7493148 -0.9406 10.5328149 -0.1053 10.5134150 -1.5808 10.1567151 0.7310 10.3274152 1.3706 10.6506153 0.2360 10.7115154 -0.3739 10.6283155 1.1880 10.9168156 -0.1743 10.8800157 -1.2691 10.5837158 0.6951 10.7529159 0.4508 10.8660160 -0.2257 10.8170161 -1.6352 10.4375162 0.3619 10.5265163 0.8791 10.7385164 -0.5340 10.6175165 1.1482 10.8960166 1.0503 11.1576167 -1.2518 10.8579168 0.5618 10.9991169 0.2430 11.0637170 0.3436 11.1535171 1.0099 11.4111172 -0.2999 11.3411173 -0.0610 11.3312174 -0.7450 11.1512175 1.5256 11.5398176 -0.9031 11.3168177 0.1345 11.3560178 -0.1520 11.3233179 -0.8247 11.1238180 0.0384 11.1386181 -0.9959 10.9009182 0.7770 11.0954183 0.6959 11.2730184 0.3714 11.3715185 -0.5703 11.2343186 -0.2939 11.1668187 -0.1775 11.1283188 0.5333 11.2659189 -1.2040 10.9749190 1.2030 11.2765191 1.2965 11.6104192 0.1160 11.6458193 0.0594 11.6667194 0.8685 11.8989195 0.0029 11.9054A B C D153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203196 0.3056 11.9919197 -0.3253 11.9116198 -2.1116 11.3731199 -0.4920 11.2553200 0.7893 11.4592201 -1.5431 11.0797202 -0.6472 10.9273203 -0.4556 10.8229204 0.9814 11.0659205 -1.6859 10.6656206 0.3171 10.7459207 0.8261 10.9495208 0.0435 10.9653209 1.8330 11.4248210 0.3920 11.5299211 -0.2498 11.4719212 1.6593 11.9068213 -0.5629 11.7650214 1.9830 12.2988215 -0.2725 12.2307216 -0.8863 11.9989217 -1.8825 11.5147218 0.2733 11.5901219 -0.3947 11.4948220 0.3529 11.5905221 -1.5945 11.1939222 1.3270 11.5331223 0.0824 11.5597224 -1.4530 11.1992225 0.7814 11.4001226 1.8613 11.8853227 0.2791 11.9647228 0.1159 12.0012229 0.7497 12.2079230 -0.6649 12.0353231 -0.7238 11.8497232 -1.5024 11.4676233 0.5086 11.6030234 0.3874 11.7086235 0.7573 11.9122236 0.1101 11.9470237 -0.4880 11.8243238 -0.1658 11.7867239 -2.0002 11.2816240 -0.8673 11.0724241 0.5442 11.2119242 0.6542 11.3809243 0.6179 11.5432244 -1.8587 11.0832245 1.8864 11.5614246 -0.0667 11.5499A B C D204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254247 0.0371 11.5649248 -0.8221 11.3618249 -1.5634 10.9806250 1.2719 11.2996A B C D2552562572582 5 02 2 52 0 01 7 51 5 01 2 51 0 07 55 02 50024681 01 21 4E F G H I J K1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950SIMULATING A LOGNORMAL PRICE PROCESSInitial stock price 20Mean return, m 12%Delta t 0.0833 - =1/12Stock price with sigma =Month Randomnumber 20% 40% 80%0 20.0000 20.0000 20.00001 -0.504 19.6217 19.0589 17.98142 1.483074 21.5906 22.8462 25.58083 0.511458 22.4612 24.4797 29.07734 -0.18428 22.4468 24.2051 28.14585 -2.10139 20.0820 19.1809 17.49836 0.164423 20.4773 19.7451 18.35827 0.768301 21.6212 21.7937 22.14278 -0.73958 20.9257 20.2109 18.85379 -0.71126 20.2856 18.8044 16.158510 0.192441 20.7184 19.4202 17.062611 0.052555 20.9902 19.7347 17.444612 0.050345 21.2629 20.0493 17.8259Cell B20 contains formula=NORMSINV(RAND()Legend titlesSigma = 20%Sigma = 40%Sigma = 80%A B C D E123456789101112131415161718192021222324252627282930313233343536373839404142434445- =E8*EXP($B$3*$B$4+$E$7*SQRT($B$4)*$B9)- =E9*EXP($B$3*$B$4+$E$7*SQRT($B$4)*$B10)- =E10*EXP($B$3*$B$4+$E$7*SQRT($B$4)*$B11)E ffe c t o f S ig m a o n L o g n o r m a l P r ic e P a th s1 21 086420051 01 52 02 53 03 5S ig m a = 2 0 %S ig m a = 4 0 %S ig m a = 8 0 %SIMULATING A LOGNORMAL PRICE PROCESSF G H I123456789101112131415161718192021222324252627282930313233343536373839404142434445模拟一个对数正态分布价格过程初始股票价格 20平均收益, m 12%Delta t 0.0833 - =1/12股票价格伴随的sigma =月 随机数 20% 40% 80%0 20.0000 20.0000 20.00001 -0.78139 19.3099 18.4581 16.86562 -0.46299 18.9895 17.6731 15.30773 0.5109 19.7546 18.9355 17.39784 0.25013 20.2433 19.6862 18.61765 -1.78022 18.4496 16.1894 12.46576 0.224527 18.8782 16.7816 13.26117 0.216701 19.3080 17.3797 14.08178 1.837563 21.6848 21.7038 21.74209 -0.32627 21.4940 21.1114 20.366610 1.369754 23.4966 24.9776 28.225611 1.880305 26.4542 31.3464 44.012212 2.171412 30.2889 40.6839 73.4006单元格B20包含公式=NORMSINV(RAND()Legend titlesSigma = 20%Sigma = 40%Sigma = 80%A B C D E123456789101112131415161718192021222324252627282930313233343536373839404142434445- =E8*EXP($B$3*$B$4+$E$7*SQRT($B$4)*$B9)- =E9*EXP($B$3*$B$4+$E$7*SQRT($B$4)*$B10)- =E10*EXP($B$3*$B$4+$E$7*SQRT($B$4)*$B11)Sigma 对 对 数 正 态 分 布 价 格 路 径 的 影 响1 21 08642001 02 03 04 05 06 07 08 0S ig m a = 2 0 %S ig m a = 4 0 %S ig m a = 8 0 %模拟一个对数正态分布价格过程F G H I123456789101112131415161718192021222324252627282930313233343536373839404142434445Exercise 4Page 23This is a repeat of the text, page 288-290.The relevant VBA programs and spreadsheets can be foundin the spreadsheet with the chapterExercise 4 中文Page 24这是对文章的重复,第288-290页.相关的VBA程序和电子表可以在本章的电子表中找到Exercise 5Page 25This is also covered in the spreadsheet for Chapter 18. I make students