# Excel Spreadsheet Monte Carlo - Excel by kxx13921

VIEWS: 42 PAGES: 9

• pg 1
```									Monte Carlo Simulation in Excel
Generating Random Numbers

First determine sample size and model to be generated
1 a Uniform Distribution between 0 and 1      Xi  U(0,1)
2 a Uniform Distribution between 0 and 100    Xi  U(0,100)
3 a Standard Normal distribution              Xi  N(0,1)
4 a Normal Distribution with large variance   Xi  N(0,100)
5 a Normal Distribution with mean of 100      Xi  N(100,20)
and moderate variance

Set sample size to 100

To generate random numbers use Tools > Data Analysis > Random Number Generation
(If you do not see the Data Analysis Option, go to Tools > Add-Ins in Windows XP )

See examples on next page

Go to the Uniform Sheet to see examples
om Number Generation
ns in Windows XP )
Xi  U(0,1)   Xi  U(0,100)   Xi  N(0,1)   Xi  N(0,100)

0.382     74.41633          -0.0556     78.10604
0.100681      36.86026        1.219316      1.786361
0.596484      44.23353        -1.63121      -50.8326
0.899106      27.39647         1.58253        -75.212
0.88461      43.85815        1.472322      -46.3133
0.958464      1.275674        -1.41278      13.06091
0.014496      44.68825        0.066182      11.82741
0.407422      9.189123        -0.42254      -55.3155
0.863247       56.2212        -0.17364      -214.386
0.138585       69.2526        -1.13914      -161.804
0.245033      90.60945        1.509511      15.03099
0.045473       26.7861        0.890191       130.176
0.03238      22.72408        0.888826      33.16745
0.164129      55.81835        0.824456      31.66781
0.219611      40.84902        0.627471      -58.2911
0.01709      41.74932        -0.61809        -20.792
0.285043      94.26252        -1.42535        -114.06
0.343089      51.50304        0.061123      91.86328
0.553636      44.19691        0.700236      -81.8666
0.357372      96.63991        -0.70837      -38.8409
0.371838      26.41072        -2.06739      -19.7379
0.355602      89.81597        -0.85968      -235.803
0.910306       49.3881          -1.5535     -22.7503
0.466018      47.43187        1.904646        -6.6027
0.42616      94.63179        -0.96525      57.78406
0.303903      64.15601        1.542094      -6.07383
0.975707      66.89657        0.269956      97.12357
0.806665      40.70559        2.292945      -163.439
0.991241      75.28001        -0.88894         -7.876
0.256264      47.26402        0.098652      46.10899
0.951689       23.0842        -0.86913      -95.6991
0.053438      13.62041        1.733133      -44.5239
0.705039      58.07062        -0.20425      -4.74847
0.816523       16.3213        -0.11173      -173.142
0.972503      97.37846        0.017021      -141.967
0.466323      44.93851        -1.15196      215.9341
0.300211      5.618458        0.511811        -80.463
0.750206      36.86941        2.323686      -77.3816
0.351482      83.07749        0.654148      -157.853
0.775658      35.08713        -1.28133      45.11583
0.074343      25.65996        -1.29271      71.08338
0.198431       8.59096        0.365249      -83.1244
0.064058      62.40425        0.435629      57.18846
0.358348      43.49803        -0.55869      -47.8345
0.487045      71.60863        0.879893      34.50407
0.511216      26.19404        -0.92989      -81.5141
0.373455      36.46046        0.120971      67.63867
0.9859      61.2537        0.595285      -53.1004
0.040712      37.03116        0.596565      68.00462
0.23072      58.40327        -0.18538      61.24515
0.004975   0.668355    1.017515    -26.5356
0.926145   47.25486     0.28315    -71.4482
0.100314   59.74914    1.672197    77.52624
0.256691   98.28486    -0.28897    48.17787
0.775689   6.360057    -1.34264    35.49633
0.679647   5.954161    -0.41812      -6.8635
0.809107   1.058992    1.317449    -50.3718
0.724326   87.94519    0.248674    -188.349
0.085055   99.12107    0.937343    196.6364
0.132267   34.82772    -0.67044    141.4653
0.756157   15.84521      -1.3222   111.4884
0.626514   70.81515    -0.29623    77.06217
0.17365   74.50484      -1.6376   213.0346
0.404798   19.70885    1.969547    -5.19276
0.552324   79.08261    -0.54098    -28.6814
0.711509   74.54451    1.566673     92.5304
0.555162   41.78289    -1.71654    0.937121
0.181158   6.079287    0.125133     26.5278
0.970275   84.44166    -0.60766    -166.207
0.686941    77.7398     1.02719    -14.8531
0.528794   36.29872    2.295074    53.78865
0.796686      24.485   -0.95156    -67.5426
0.805658   80.58412    0.430504    -116.906
0.262215   63.56395    2.210072    156.7196
0.177953   71.09897    -0.47706    88.78055
0.866756   98.15058    0.912817    82.15579
0.114841   60.77151    -0.69672    80.50529
0.059511   13.42204    -0.14536    -51.0416
0.761559   68.42555    -1.08753     80.7064
0.738395   10.96225    0.136861    -109.447
0.986297   71.41636      -1.1085    0.30218
0.925596   43.78185    -0.28124    -64.6776
0.903867    61.7481    0.564334    64.91348
0.544969   63.94848    0.683134    25.45949
0.500778   55.22324    -0.69147    115.1072
0.674978   39.28343    0.010289    47.02133
0.489822   8.893094    -0.42899    -43.7396
0.145787   98.30317    -1.42029    87.56183
0.037965   94.49446    -0.07024    51.64384
0.796258   85.28703    1.517446    63.51274
0.67156   6.970428    0.095192    219.9631
0.731681   13.19315    -0.10403    -86.8124
0.584521   70.28718    1.240337    -182.101
0.152226    52.9313    -0.35358    42.79877
0.892178   49.64141    -1.52499    -91.1077
0.377819   56.40431    1.077224    -61.0239
0.200476   2.993866    0.199797    311.6438
0.205786   39.86938    -0.95893    26.60704
0.333964   17.41386      -0.1442   17.08452
0.325144   15.92456    -1.16921    -29.9672
Descriptive Statistics
Column1                Column1                 Column1                Column1           Column1

Mean        0.485457   Mean        48.81222   Mean           0.0476   Mean        -0.18641
0.030924
Standard Error                     2.834972
Standard Error                     0.106247
Standard Error                      9.928938
Standard Error
Median       0.46617   Median      47.25944   Median      -0.02266    Median      -2.22315
Mode          #N/A     Mode          #N/A     Mode          #N/A      Mode          #N/A
0.309236
Standard Deviation                 28.34972
Standard Deviation                 1.062467
Standard Deviation                  99.28938
Standard Deviation
0.095627
Sample Variance                    803.7064
Sample Variance                    1.128835
Sample Variance                      9858.38
Sample Variance
Kurtosis    -1.33092   Kurtosis    -0.99496   Kurtosis    -0.67826    Kurtosis    0.514209
Skewness 0.099757      Skewness 0.051839      Skewness 0.262939       Skewness 0.182492
Range       0.986267   Range       98.45271   Range       4.391077    Range       547.4467
Minimum     0.004975   Minimum     0.668355   Minimum     -2.06739    Minimum     -235.803
Maximum 0.991241       Maximum 99.12107       Maximum 2.323686        Maximum 311.6438
Sum          48.5457   Sum         4881.222   Sum         4.759958    Sum         -18.6412
Count            100   Count            100   Count             100   Count            100
Xi  N(100,20)

126.2908        See descriptive stats & frequency distributions below
146.6578
72.8002        Note that if you use the Rand fuction,
91.26724        the number changes every time everytime there is any change in the spreadsheet
112.7588
93.18843         0.113292              Change this value
106.3963         0.930901
119.7335         0.809701                       1
98.47084          0.27616
102.9969         0.863059
97.9347         0.067159
94.15363         0.973131
119.6764         0.597483
140.5635         0.784563
144.3077         0.811328
94.18555         0.181177
87.46921         0.188059
121.5691         0.139721
99.16822         0.383244
103.6796         0.384395
94.06896         0.054655
91.06483         0.243516
62.52664         0.342937
119.1277         0.840126
108.1457         0.366988
77.3398         0.378112
97.43718         0.982175
57.98098          0.65119
65.66239          0.54959
89.92564         0.779303
94.48337         0.040528
90.69132
67.29466
105.7251
86.31411
80.06383
79.86924
127.4488
58.46902
81.90092
130.7517
95.58643
118.9184
94.07214
121.0376
126.9892
83.03992
121.8309
71.65687
134.4382
115.7565
88.07052
88.69719
74.96043
110.1282
99.60601
116.6833
74.50159
68.61425
99.98395
154.3354
87.39838
58.50849
72.10898
95.71646
120.4017
114.8543
89.69047
95.19364
107.6973
119.2076
91.45534
116.2304
109.9356
81.92625
107.3917
96.00407
81.46809
97.57752
73.22607
50.74832
105.0698
115.0343
132.2929
81.87327
83.20156
100.4047
94.39751
80.22658
105.4721
97.49734
98.95992
128.107
128.6044
110.8303
80.66091
89.52762
63.66787
123.2695
92.731
Column1

Mean        98.77037
2.143868
Standard Error
Median      96.72062
Mode          #N/A
21.43868
Standard Deviation
459.6171
Sample Variance
Kurtosis    -0.26043
Skewness 0.158325
Range       103.5871
Minimum     50.74832
Maximum 154.3354
Sum         9877.037
Count            100