Help to run multiple simulations in one cell - Monte Carlo

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.