Excel Spreadsheet Monte Carlo - Excel

							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
e spreadsheet