Help to run multiple simulations in one cell - Monte Carlo

[calof1]

Hi Everyone,

I am looking to run simulations in excel and find the min, max and median of the results.

The formula used in each cell to run the simulation is =$B$4*(1+NORMINV(RAND(),$B$3,$B$2)).

Say there is 5000 simulations, how can i find the summary of min, max etc without using 5000 rows of data. Happy to use macros, VBA, formula etc.

Any help and suggestions are much appreciated.

I have attached an example which includes how the simulation and calc works, and the desired output summary.

[#2]

@calof1 

Excel has long had a little-known feature called "Data Tables" that may be used for Monte Carlo simulations. I used the a one-input Data Table in which the numbers 1 through 500 were successively input into Formula Example worksheet cell M1. Each time such a number was placed in M1, the formulas in Sim #1 through Sim #10 recalculated. The results from cells B7 to B11 were then reported back to the Data Table. In so doing, you tabulate the results of 5000 simulations in a couple of seconds.

The Data Table feature may be found in Data...What-If Analysis...Data Table menu item. The results appear in Formula Example columns O:S. Although I tabulated the dollars, you could just as easily report back percentages or any other values from what I like to call the "calculation engine" part of the worksheet.

To set up the Data Table, I listed the values 1 through 500 in column N, and put formulas in cells O2:S2 to return values from B7:B11. Those cells have a Custom number format ;;; so you don't see any values. I then selected N2:S502 and used the Data...What-If Analysis...Data Table menu item. The tricky part is to specify cell M1 as the Column input cell--I frequently put it in the Row input cell by mistake when learning how to set up Data Tables.

[#3]

Hi@Brad Yundt 

Thank you kindly for the assistance.

Its my first time using data tables so i will explore this over the weekend.

Thanks again for your help.

Kind regards,

[#4]

@calof1 I appreciate I'm very late to the party but i happened upon your problem while learning about Lambda and found it such a a neat solution. by making a recursive LAMBDA function to simulate each scenario, and MAKEARRAY to compile a set of scenarios, it was simple enough to get the entire simulation into an array. i then used LET to make that array sticky while computing the stats on it then a further MAKEARRAY just to spill the results onto the screen.

=LAMBDA(seed,sd,nominal,years,iter,
    LET(iter,iter+1,
        seed, seed*(1+NORMINV(RAND(),nominal,sd)),
        IF(iter=years,seed,simulatenorminv(seed,sd,nominal,years,iter)
        )
    )
)

=LET(
    sims,
    MAKEARRAY(P6,1,
        LAMBDA(row,column,
            simulatenorminv(P4,P2,P3,P5,0)
        )
    ),
    MAKEARRAY(5,1,
        LAMBDA(row,column,
        SWITCH(row,
            1,AVERAGE(sims),
            2,MEDIAN(sims),
            3,STDEV(sims),
            4,MIN(sims),
            5,MAX(sims)
            )
        )
    )
)