Being an academic, I am always wishing for free tools for my students and for myself to do my work and while searching for them I found a simple Monte Carlo add-on for Excel, built by colleagues from Wabash College. The tool with instructions for installation can be found here.

I wanted to test this tool and compare it (and Monte Carlo simulation in general) with the approach that the pay-off method uses in creating the pay-off distribution. Below I have included a full illustration of what I did with Excel screenshots of “everything”.

I “generated” the Excel sheet by just inventing numbers for a fictitious investment that has an uncertain discount rate, uncertain cost that happens in year zero and with revenues that accrue during five years. For the pay-off method I used three scenarios minimum possible, best guess, and maximum possible. The numbers… came out of the proverbial hat.

Costs and revenues are unrelated here – incidently this, i.e. unrelatedness, is also a requirement, when correctly using the Monte Carlo simulation that is based on randomness. This also means that minimum cost can occur with maximum revenue and vice versa. Screenshot from the Excel sheet is visible below; even more information about the calculation is included in the screenshot.

Below you will see the resulting pay-off distribution that has the top over 148 and min at -69 and max at +501.

Now with the Monte Carlo tool I used the same input information, but one major thing is different. The RAND() function that Excel uses and that is used by the MC simulator can only draw from an interval and hence will not consider the information about the best guess that we have available. Input parameters to the tool are 100000 (one hundred thousand) runs and seed 123 (in case you want to retry this illustration – the results should come out exactly the same, if you input the same seed).

With my oldish laptop it took about 30 seconds to run the simulation, this is quite acceptable for free software. I personally remember way back when running this kind of simulation took two hours. Without using the seed it runs faster.

The setup of the spreadsheet is a bit different as we are not calculating the values for the pay-off distribution for the three scenarios separately, but we are just giving the simulator “orders” to randomly generate the result for cells. For each cell of “PV cost” and “PV revenue” the Excel has been instructed to generate a random number from between the min and the max value. The “PV project” row is just the revenue minus the cost, and “NPV project” is just “PV project” cells summed up. The discount rates had also to be changed and given as integers.

[A related observation: there is also a function called RANDBETWEEN that allows for us to directly specify the interval – this however is not supported by the free simulator software].

Running this sheet (randomization) one hundred thousand times results in a pay-off distribution below – now observe that this is NOT a fuzzy number, but the Monte Carlo simulation hails from a different domain, that of the true randomness – so this is a probability distribution for the NPV.The top is above 307, min value is -8 and max is 598. We also get standard deviation information from the MC tool – standard deviation is 91.

In the two graphs below, I adjusted the scale of the MC simulation result and put the rescaled MC result simple scenario based fuzzy number result one on top of the other – now… these are NOT directly comparable, but if we look at them we can see that for 100000 trials the Monte Carlo seems to be “more to the right” than our other pay-off distribution. This is obviously caused by the difference in the information = the fact that we have the best guess scenario information available skews our triangular distribution to the left. I wonder if I should have compared a four scenario trapezoidal distribution with a “best guess core” on top… it could have been a better comparison. Right now I think I will not do it for this post…

Building the Monte Carlo model took me more time than using the simple way that is the pay-off method – I think as I am not very accustomed in using the add-on it took more than it would if I used it a lot. However, still building the spreadsheets without having to do any “programming” or using more complex formulas is faster. Once the spreadsheet is ready it is very easy to change parameters and run the simulator.

Here, in this case, with the not so sophisticated MC simulator I feel that having to give up the best guess information is such bad news that I would not use it over the simplistic triangular distribution. There are commercial of the shelf software packages available that allow us to input the best guess value and use a “triangular” input into our Monte Carlo simulator. These are most likely far better for the job, however, they cost some money and require learning to use them 😉

For a simple “quick and dirty” analysis I think that this simple experiment with the Monte Carlo simulation did not provide the kind of “bang” as the atom bomb for the construction of which it was originally built to be an aide. If we had not had the best guess scenario information the info provided by the MC would outweigh just “interval information” for sure!

Maybe you have noticed that I have tried to steer clear of any strong opinions on which of the two methods is better – IMO both are good and both have their own uses. The methods themselves are not an issue – the available information and the fit of the information to the method is the issue. Also available time to perform analysis and transparency are issues that one wants to think about.

If you want to try this yourself download here the spreadsheet file used in this illustration. Remember that you cannot run the simulations unless you have the software installed (link to the add-on here).