Free Microsoft Excel 2013 Quick Reference

# Crystal Ball Monte Carlo

Hello, I am asking for help with the program Crystal Ball. Basically what I need is non repeating assumptions. I have a roughly 10 assumptions and they are all uniform discrete distributions between 1 and 10 but I need to make sure that if assumption 1 chooses say number 3 that no other assumptions chooses that number and thus all the numbers are used for each run (1000 runs in the simulation). Can anyone help me with this?

## Related Results

### Finding first cell from the left above threshold using Monte Carlo Simulation

Hi all,

I am using =MATCH(TRUE,INDEX(I105:AS105>100000,0),0) to locate the first cell in a row that is above 100000. This works just fine in a regular spreadsheet. However, my spreadsheet uses a Monte Carlo simulation (Crystal Ball) and when letting it run I always receive an error for the cell with this function.

"A calculation error has occurred in cell [my cell]. You may wish to check your formulas and assumptions for division by 0, etc."

I am not sure what the reason could be - I even hard-coded the last cell in the row to always be above 100000. Still the error appears, so it's not likely that it would be triggered by a scenario w/o any number above 100000...

Any ideas?

### Crystal Ball Users

Hi All,

I recently bought an academic license for Crystall Ball, which is like a probabilistic monte carlo "add-in" for excel. However, the guys at crystal ball do not support academic licence users.

All I would like to know, is how do I create a PERT distribution in crystal ball, so i can use in my simulation.

Any asistance would be appreciated.

Thank you

### Monte carlo

Hi, I am just trying to do a monte carlo simulation that will output like 3 calculations, using 250 rows of data
ie.

45.50 46.00 3/23/77
46.00 43.90 9/1/88
39.50 40.01 5/3/89
(I have about 250 rows like this)

I want to run the simulation based on the date. And find standard dev. etc.

Thank you.

### Monte Carlo simulation - lognormal distribution & jumps

Hi,

I'm trying to do a Monte Carlo simulation which involves randomly selecting a value from a lognormal distribution (with mean-reverting jumps) and performing a set of calculations from this value, then moving on to the next randomly selected value.

My current idea, albeit a rough one, is to first generate a normal distribution, then taking the log of the values to obtain the lognormal distribution. The mean-reverting jumps are then added randomly by replacing some of the generated values of the lognormal distribution. Next, using a loop, I'll take each value of the new distribution to make the necessary calculations.

However, I find the mentioned approach to be inefficient as I'll have at least four loops... first to generate the normal distribution, then to convert into lognormal, third is to add the jumps and lastly, to make the necessary calculations. Are there any better approach, where I can create the same outcome faster? Also, any ideas on creating mean-reverting jumps using VBA would be greatly appreciated.

Thanks.

### "Dirty" Monte Carlo VBA Code

I'm trying to run a macro that will essentially run a dirty version of a Monte Carlo simulation

My outcomes are set up as [probability1, outcome1, probability2, outcome2...etc.] that are arranged horizontally within a row.

I also have a column with 10,000 rand() variables set up.

I'm trying to use the following code

```
VB:

Private Sub monte()

Dim sumCell As Variant
Dim sum As Variant

For j = 1 To 100
'100 is the number of sims that I want to run
'Activecell will be the cell that I want the final sum to show up in
Set sumCell = Activecell.offset(j-1,0).Value
For i = 1 To 10000
'10000 is the number of iterations I want in the monte carlo
If Activecell.offset(i-1,-1) < Activecell.offset(i-1,17) Then
sum = sum + Activecell.offset(i-1,16)
Else
If Activecell.offset(i-1,1) < Activecell.offset(i-1,15) Then
'etc. for the number of outcomes I have

End If

Set sumcell = sum
Set Activecell.offset(j-1,0).Value = sumcell
Next j
End Sub

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

```
I then assigned the monte() macro to a shortkey of ctrl+k. Everytime I pressed ctrl+k, it erased the sheet I was working on. I have no idea why it was doing that, but I just shut everything down without saving to preserve my worksheet.

I basically just want to call the sum to a cell, then I can average it to find monte carlo expectation (as I said, pretty dirty as I don't want to run a distribution).

Anyone know what is going on?

### Generating a N x M matrix of variables in VBA for a monte carlo simulation

Hi,

Attached is a monte carlo that simulates the return on a portfolio given the constraints inputted by the user (starting portfolio value, expected return, standard deviation, and number of trials, time period). Currently, the simulation uses a geometric brownian motion and outputs the results on the second worksheet. However, I wish to not have any output, and have excel store the variables using VBA and then write code to perform the relevent calculations.

For example, if there are 5 years, and 5000 simulations, I would like a variable for year 1, year 2....year 5. So if X is the variable, t = years, i = simulation number, I would like Xt,i to be stored, and then perform calculations on this "range" or dataset rather than having the output on a sheet then performing the calculations on the sheet. Is this possible?

I know that currently the spreadsheet returns only the end value in 5 years and not the values at year 1 year 2 year 3 and year 4. But This is also what I want to change.

I know this is probably quite basic but I am new to this. Thanks

### Monte carlo Simulation with Skewness and Kurtosis

I am working on something which involves Monte Carlo Simulation with not only mean and standard deviation, but also the 3rd & 4th moment (Skewness & Kurtosis). I know how to do the simulation with 2 inputs (mean and S.D.), doesn anyone know how to do it with 4 inputs which will also include skewness and kurtosis? Or anyone knows the transformation formula? thanks

Where can I get a free Monte Carlo Simluation Add-In for excel.

I tried to search the internet and was not lucky enough to find....

### Monte Carlo Simulation

Has anyone ever created a spreadsheet that used Monte Carlo Simulation?

I've been able to create one that works using the random number
generator in conjunction with discrete probability distributions -
that's fairly easy, just assign a probability to each discrete value in
the distribution and then decide whether or not to select it using a
randomly generated number.

However, I want to perform a Monte Carlo simulation that uses a
-continuous- (not discrete) probability distribution, but have no idea
how I would use the random number generator to then select a value from
the distribution based on its probability of occurance.

I know it can be done, I just have no idea how!

Anyone done anything like this before?

Cheers
-Rob

--
TheRobsterUK

------------------------------------------------------------------------
TheRobsterUK's Profile: http://www.excelforum.com/member.php...fo&userid=9924

### Crystal ball question

Hi,

Suppose that I am using goal seek function to find out the volume of oil that i have to sell to achieve a 10% return on the investment.

How can i define it in crystal ball simulation to seek the range of volume that could possibly generate a 10% return on investment?

Thanks,

### Monte Carlo Stats Formula??????

Hi,
I'm having a problem with a Monte Carlo I am running in excel and I'm not sure it can be solved without VBA or a special add-in!

I have a number of line items with values of "Min Possible Score" ; "Most Likely Score" ; "Max Possible Score".

I then have 10,000 rows of iteration containing the formula:

=MinScore+RAND()*(Max Score-MinScore)

From which a Probability Density Function is derived. Now this is great if the original distribution is uniform i.e Min = 0, Likely = 10, Most = 20. However, if it is NOT uniform the answers come out skewed. EG, if in fact Min = 0, Likely = 10, Most = 100, then the above formula will skew the answers to be too high! Is there a formula / algorythm of generating numbers that reflect the original distribution - in the above example pick out 10 more often and only rarely pick out the higher number?

Cheers

Jay

### Monte Carlo in Excel

I need to make a monte carlo simulation in excel - a rather large on. Does any of you have any experience and can maybe recomment add-ins or site where this subject is explained?

### Monte Carlo Simulation Add-ins for the mac?

Anyone know of a add in for simulations, like @Risk or Crystal Ball, that work on the Mac?

Years ago, when I was last on a Mac platform, Crystal Ball had a Mac version, but they don't seem to now. I'm back with the Mac and would like to get an add in (doing it without one is possible, just cumbersome enough that I would be willing to pay for an add in).

Anyone know?

### Inputbox for password protection, monte carlo simulation complete code

Hello All,

Plz help me by providing a complete 1.Monte-Carlo simulation code.

2. code for disabling cut copy paste from ribbon and functionally

3. code for creating input box for password that has special characters enabled feature whenever password is entered and allows 3 attempts to enter password after that it closes workbook and it pop-ups at the time of opening workbook

Plz reply with specific solutions only. vague reply will make me lost. Thanks.

### Monte carlo

Can anyone plz help me with monte carlo simulation code that runs 1000 times, I

would appreciate to get a complete code

### How Do I Run A Monte Carlo On Stock Probabilities

can some one help me out with this ; I am really stumped and want to learn how to do this for a class am taking. been working on it for 8 hrs maybe someone can assist if they know how? thanks so much

A relative of yours is thinking of investing in one of the following stocks: ERTS , VZ,SONC. you have been requested to perform Monte Carlo simulation to estimate the probabilities of over 30% return for the above three stocks (or any other stocks of your choice) and determine the probabilities as requested.

how do I do this on excel? thanks - for anyone with the help!!

### What's a a good cheap Monte Carlo add-in for Excel?

I am looking for an inexpensive add-in to Excell that will allow me to do
simple Monte Carlo analysis. Commercial applications are too expensive and

Any ideas?

Thanks

### How can i set up a MONTE CARLO SIMULATION macro in Excel? thx

I'm looking for a simple macro for Monte carlo sim? I'd apprectiate any help.

many thanks

### monte carlo Method

Hey,

Im trying to do if statements to help me with the monte carlo method.

I have graphed a function of 1/50x^3 + 2 on a scatter graph and have randomly generated 100 x and y points onto the graph.

Now i need help writing an IF statement that can tell me if that x and y point is below the function or above.

For example

If my range is x between 0 and 5 and y is 0 and 4.5 then
x y
0.43 2.64

Then i want the statement to tell me if this point is below or above y = 1/50x^3 + 2

Thank You

### 2 Way Data Table issues when running monte carlo simulation

Hello Excel gurus,

I've spent the past 4 days running into an odd problem and despite searching all over the web and through various books, have not been able to determine the solution. I'm hoping one of you may be able to provide the answer to my problem.

The issue is that I've noticed that as I try to run monte carlo simulations using 2 way tables that sometimes the 2 way tables will work correctly by applying the formula throughout the data set of the table and then other times it will just copy the values in the row headers throughout the dataset.
This becomes extremely frustrating as I can never be sure when Excel will do it and when it will decide not to update the data set correctly.

I'm unaware whether there is a nuance to the use of the 2 way data table that I may be missing that is causing this issue or is there something I am blatantly doing wrong?

Typically I will set up my base equations and calculations; set the upper left corner of the data table to the equation, set up the left hand column under the equation to go from 1 to 1000 and then set up my row values in the row adjacent to the equation. Then I go to what if analysis in Excel 2007, select data tables, select the appropriate row and column and cross my fingers hoping it will work.

I've attached my examples to this email in the hopes that it may provide an idea of my issue.

Best regards,
Alpesh

### How can I create a monte carlo from a monte carlo?

I have created a Monte Carlo simulation from a data set of over 8000 variables. The monte carlo randomly generates 100,000 outputs using the probabilities from the sample. There are significant probabilities of small losses, and small probabilities of large gains. The long term expected return is a positive number

This all works just fine. I am able to create charts of the cumulative results of the first 1,000, first 10,000 and 100,000 randomly generated results.

The question I have, I would like to use this monte carlo data to determine the probability of dropping X points over Y runs. I would like two cells to contain inputs.

For example, In cell A1 I can input the starting points, say, 1000, and in cell B1 I can input the number of games, say, 10000. Using the monte carlo information, I'd like something to spit out the probability of reaching 0 given I start with 1000 points and play 10000 games.

Reminder: I already have a working Monte Carlo. Brute force I could click the Monte Carlo button I created and run through the scenario 100,000 times to get a decent estimate of the probability, but obviously this isn't feasible. It's almost as if I am asking for a Monte Carlo on a Monte Carlo. If you need more details I can share.

### Monte Carlo results capturing

Am running a Monte Carlo simulation in Office2003 Excel. I know the smart thing is to get all formulas, etc., in one row, have final output at last column, and copy that row for hundreds/thousands of further "simulation runs". But, program itself requires hundreds of rows; so not practical. I would like to have a macro that, in an unused column of spreadsheet, will "record/capture" as unique run-outputs, in sequence, these individual run-results, where then this fixed, final column, with all this data-output, can be interrogated for mean, std deviation, etc.

Can someone provide either a lead, to where such a macro can be found, or perhaps instruct or provide one? Thanks.

### Installing Crystal Ball 4.0 on Excel 2003?

I recently had my laptop stolen. Whilst setting up my new laptop I am trying to reinstall Crystal Ball 4.0 but Excel 2003 (with OS XP) wont recognise it. My previous laptop had the same software and OS and when I installed it previously the Crystal Ball web site had a help page that gave instructions. Now though it recommends that you upgrade and says XP wont support CB 4.0. I don't want to upgrade as CB 4.0 suits my needs. This seems a bit ridiculous given it had worked previously.

Does anyone know how to get XP and Excel 2003 to recognise the CB 4.0 add-in?