Monte Carlo

Replies to This Discussion

Permalink Reply by Dipu James on December 3, 2008 at 6:29am

Check this out

http://www.finance30.com/forum/topics/1987892:Topic:1895

http://www.pdf-search-engine.com/monte-carlo-simulation-pdf.html

▶ Reply to This

Permalink Reply by Sachidanand (Sacha) Singh on December 3, 2008 at 1:47pm

Monte Carlo is the capital of the principality of Monaco in south of France. For over a hundred and fifty years it has been a favourite destination of the rich. They flock there to enjoy gaming sessions in one of the oldest functioning casinos of the world. Roullete is a very favourite game at the casino where large sums of money are made or lost over the turn of a spinning wheel. If the wheel is true and there are n different numbers, each appearing once, on which its needle can stop then the probability of getting any number after a spin is of course 1/n. Any model that attempts to create a life like scenario by generating random numbers is called, after the spinning wheel of the casino, Monte Carlo model.

I will give you a simple example. A restaurant has observed that teh number of diners on any weekday is given by teh following discrete distribution:
No of Diners Probability
100 10%
200 35%
400 30%
600 25%

Now suppose it sells one dinner for 4 and its variable costs per dinner is 1.5 and there is a disposal cost of 0.2 for each unsold dinner. How many dinners should be prepared?

Monte carlo way of solving this problem will be to prepare an excel model in which we cook each possible no of dinners (100,200,400,600) say 10000 times. And generate random numbers to get the no of diners as per the observed distribution each of teh 10,000 times. Then find out which quantity gives maximum average profit!

I am attaching an excel file of the model. In the file I have iterated only 1000 times and not 10,000 times as I would have liked to do because that would have slowed my ancient computer. I use Excel's RAND() function to generate random numbers, Table to get 1000 iterations (with calculation set at "Automatic except table") and standard statistical relationship to get 95% confidence interval of return. This is a very basic model - adapted from http://office.microsoft.com/en-us/excel/HA011118931033.aspx for class room discussions.
It makes one point very clear- any model can be at best as good as the assumptions that go into making it. In case of MC models statistical distributions play a crucial role. If we get the distribution incorrect we will get garbage for sure.

Finally Excel, though a very versatile package, is somewhat inadequate for stats.

Pl do let me know whether this has been helpful or not

Attachments:

• Monte Carlo.xls, 172 KB

▶ Reply to This

Permalink Reply by Penelope Lynch on December 9, 2008 at 11:53am

Sacha,

Thanks for that very clear explanation, complete with geographical and cultural context!

I would add that the Rand() function can be combined with a number of other excel stats functions to simulate a range of possible distrubutions. There are specialist software packages avaialable as well, although with good excel skills they may not offer very much that you can't achieve independently.

The important thing to bear in mind when using Monte Carlo with financial models, is that the answers are only meaningful if there is a good basis for choosing the probability distributions applied to the variables.

There has been a trend in recent years to use Monte Carlo simulation with project finance models to test the likelihood of default under a range of possible outcomes for several different input assumptions. The trouble is that in project finance there are often no useful data from which to derive assumptions about the distribution of the selected variables, so the process effectively applies an additional layer of more-or-less arbitrary assumptions to the analysis. Taken together with a tendency to assume that these 'more scientific' results are somehow closer to the truth than a simpler base case and sensitivity analysis, this becomes quite a dangerous process.

I ran an illustrative monte carlo analysis with one of my models, using the same set of random data with two slightly different distribution assumptions (the 'shape' parameter for the commonly used 'PERT' distribution). In one case the results indicated that the project would be in default in less than 5% of cases, in the other case, that default would arise in more than 20% of cases. I varied only two inputs, used 10,000 iterations, and the only difference between the two analyses was the value selected for a statistical parameter which I suspect few people really understand, and which is generally just left at the default value when using standard software packages. So for project finance I'd like to raise a big warning flag overthe reckless use of Monte Carlo!

All best,

Penny

▶ Reply to This

Permalink Reply by Johnni on July 10, 2009 at 5:57pm

Dear Penelope, I realize this is an old tread, but I hope this reply will find your way anyway.

You touch upon something really important, and I would like to ask you to dance with me a little here?
I understand your warning of not“inducing”more uncertainty into the model, by defining shapes and sizes of probability distributions, that one essentially does not yet know in a new project. Fair point!

But if our motivation is to create transparency through a sensitivity analysis, one can only simultaneously manipulate two variables in a Excel table. We need MC if we have more that two variables.

Regarding your point on the shape of the assumptions probability distribution. If you have discovered that the impact of going from fx. a triangular distribution to a Gaussian, or say change the width of the distribution has a huge effect on the end result, I guess that you as an analyst has to make an educated transparency/risk induction judgement. Let me ask you..how do you deal with that?

Are there some relative “safe shapes”among the relevant probability distributions one can start out with, until reality has given a clue? And would it make sense to then create a tornado chart and apply the rule of thump, only to leave the distributions for the top 3-5 most sensitive variables? I realize the implications of GIGO on this, but one need a framework.

Br

Johnni

▶ Reply to This

Permalink Reply by Penelope Lynch on September 9, 2009 at 5:51am

Johnni,

I have not been checking in to Finance 3.0 very much for a while, as I had a rather excessive workload earlier in the year, seamlessly followed by family holiday, so apologies for this very delayed response.

I think the question of combining the effects of variation in more than one variable is a separate one from the choice to use Monte Carlo. The limitations of Excel sensitivity tables are manyfold, and I would recommend a range of alternative approaches to generate single specific sensitivity cases, or sets of cases running through series of values for specific inputs. The first thing to define in all instances, however, being what question you are asking the model to answer.

Monte Carlo is not simply about generating outcomes for multiple sensitised variables, it is about weighting the probabilities of different combinations of variable values, and then using the statstics generated by this process to inform a decision making process.

Monte Carlo is clearly an excellent and useful statistical tool, provided it is properly understood and used approriately. My concern about it's use with project finance models is that it is hard to understand exactly what the results mean in relation to the assumptions used. I don't think it adds transparency, I think it obscures the relationship between assumptions and results, whilst adding a layer of apperantly sophisticated analysis which seduces people into seeing the results as more 'accurate' than simple sensitivity analysis.

For a project finance 'base case' anyone making credit or investment decisions should have access to the assumptions used for the base case, should understand them, and should have an idea of the reliability attributable to each assumption. The effects of a defined downside variation in any single assumption, or in any combination of assumptions can then be quantified by running sensitivity cases. Again, the assumptions used can be known and understood and clearly related to specific outcomes, which should facilitate rational decision making.

Applying Monte Carlo as an alternative to making judjements based on a set of clearly defined base and sensitivty cases undermines this process because, for project finance models, many variables do not have an obvious, established distribution. For example, when running sensitivities for a thermal power plant, what is the probability distribtion for the achieved vs predicted labour costs, chemical costs, plant operating efficiency? On what knowledge, documentation, history can this new set of assumptions be based, and how thoroughly can they be understood? Surely, if the Monte Carlo analysis is going to give results which can be relied upon to assist in decision making, the distribution assumptions are just as important, should be just as well understood and as well justified as any other base case assumption. They should also be subject to sensitivity testing themslves to see how critical they are to the results. Rather than providing transparency, for this specific application (project finance), I would contend that Monte Carlo simply introduces a whole set of poorly understood, poorly justified and generally opaque additional assumptions, produces results which in some cases are essentially arbitrary, but which have a veneer of technical credibility which can lead to them being seen as more informative and reliable than the simple results produced from the relatively well understood and justified basic model assumptions.

I guess it's clear that I have a bit of a thing about this, so I should add that I am well aware that for other fields of financial modelling these issues do not apply, and Monte Carlo is a wholly appropriate tool. I just think it's dangerous and nasty when combined with a project finance model!

My approach would be the usual project finance simple, unexcitng method. Look hard at the base case. Test the assumptions to see which varaibles really matter. If they matter too much, fix them or hedge them. See how bad each remaining risk item can make things. See how bad a combination of moderate downsides for all the remaining risk items makes things. For investors, maybe run one 'blue sky' case which looks at how good things could possibly be. Ensure lenders and investors have access to a set of results they understand, based on which they can use their knowledge and judgement (I appreciate recent events may throw both into question, but..) to make an informed decsision. The quality of the underlying assumptions remains key, with or without Monte-Carlo.

Hope that addresses some of your questions. I certainly feel better for sharing it all!

With all best regards,

Penny

▶ Reply to This

Permalink Reply by kumarravijayanath on February 2, 2010 at 7:04am

Hi Penny,

Your detailed reply was enlightening. For that matter the replies by all had made me think. I am working in a firm which invests in real estate development projects in the developing countries like India, China, Korea, etc. When I do an feasibility analysis of a specific project we look at IRRs, NPVs, cash on cash etc. I do all this in excel. the summary sheet includes a sensitivity table lets us say showing how the IRRs vary with different cap rates and LTV & cap rate vs interest rate & inflation vs cap rate so on. So you have a bunch of tables each dealing with 2 variables.

First, I would like to know if there is there a way in excel where I can see how my returns are affected my changes in 4 - 5 variables simultaneously? say in a single table.

Second, How use ful is montecarlo analysis in real estate development projects or acquisition of developed projects useful.

Third, recently there ahave been questions as to why I am not doing a monte carlo analysis? So it would be great if anyone would guide me as to how I can create one in excel, that will flow along with the feasibility analysis.

▶ Reply to This

Permalink Reply by James McGough on March 10, 2009 at 9:26pm

Sacha,

In the model you have the upper and lower calculated as
= SD +- (1.96 * Average / SQRT(1000)

Shouldn't it be the other way around?
= Average +- (1.96 * SD / SQRT(1000))

It's been awhile so I'm not 100% sure

▶ Reply to This

Permalink Reply by Sachidanand (Sacha) Singh on May 15, 2009 at 5:23am

I say, James, I'm really very sorry. I do not know how I missed it when you posted.

I have given = SD +- 1.96 * Average / SQRT(1000); which is the same as what you propose i.e.
= SD +- (1.96 * Average / SQRT(1000))

Trust it clarifies. (The way you have written; = SD +- (1.96 * Average / SQRT(1000); the parentheses do not match (two open but only one closes) and Excel will not accept it.)

Sorry, again

Sacha

▶ Reply to This

Permalink Reply by Philip Young on August 24, 2009 at 12:52pm

Sacha
Just reviewing this thread for the first time
Unless I have misunderstood, I think what James asked is, should not the calculation for the upper and lower confidence interval level (cells I7 I8) be
= av +/- 1.96 * sd/(sqrt n) ?
PY

▶ Reply to This

Permalink Reply by Sachidanand (Sacha) Singh on August 25, 2009 at 2:16am

Yes, of course; stand corrected; Thanks

▶ Reply to This

Permalink Reply by Mukund Bhide on March 12, 2009 at 2:14am

Hi sacha,
You have given comprehensive inputs,very good!
mukund

▶ Reply to This

Permalink Reply by Stephen Janes on December 7, 2008 at 1:55pm

Ali,
I'd recommend checking out www.palisade.com and their Monte Carlo simulation software. We use it at Vair Training, (www.vaircompanies.com) and I'd be happy to discuss.
Steve

▶ Reply to This

• ‹ Previous
• 1
• 2
• Next ›
• Page