• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

XL VBA MONTE CARLO SIM CODE FOR COMPANY VALUATION

DAGUET

DAGUET

Member
Hi There,

I am trying (hard) to get out of the following situation and would need your help.

• I need to build from the attached spreadsheet an XLS User-Defined Function that is able to generate a stochastic-driven simulation (Monte Carlo process) to perform the valuation of Netscape.
• To do that, I would start from the « Simulation Model » worksheet input variable (those in green font) and build a macro out of each of those cells that would instruct XL to perform a loop on the stochastic generation process for those variable.
• Then obtain as an outcome a distribution of values of the company (cell "O4" is my output cell), the type of which I get in crystal ball .
I think the formulation I seek is more or less derived from the code used in VBA for MC sim for Value at Risk (see an illustration below unrelated to the case).
But I can't get to that for some unknown reason..... I flare that this is not that complicated at the end but I am not there and would appreciate if you could help me in some way.
Best regards
FOR ILLUSTRATION PURPOSE ONLY VBA CODE FOR VAR
Function ValueAtRiskMC(confidence, horizon, RiskFree, StDv, StockValue)
Dim i As Integer
Dim stockReturn(1 To 10000) As Double
For i = 1 To 10000
stockReturn(i) = Exp((RiskFree – 0.5 * StDv ^ 2) + StDv * Application.NormInv(Rnd(), 0, 1)) – 1
Next i
ValueAtRiskMC = -(horizon) ^ 0.5 * Application.Percentile(stockReturn, 1 – confidence)
ValueAtRiskMC = StockValue * ValueAtRiskMC
End Function
 

Attachments

  • netscape_revisited.xls
    245 KB · Views: 27
Hi Daguet,
For a non-statistician, can you explain a) what the inputs ranges/cells would be in your function, and what the actual math you need to be performed? I'm afraid I'm lost. :(
 
Hi Luke M,

my input cells are those that are marked green in my spreadsheet, worksheet named « Simulation Model ». In practice, these are my random variables to which I want to apply the MC Simulation. I want to randomly draw those variables to produce stochastic results in the form of data range. Once done, it shall also produce on the output cell "O4" a probability distribution function representative of the volatility of the company's value around the mean (its expected value). I suspect that it is achievable through variable declaration, then loop process in the VBA code . but again I am stuck on that so far. If you could help, that will be much appreciated.

Best Regards
 
I'm sorry, not trying to be difficult, but I really am not sure what that paragraph meant. :(
I probably sound like a little kid, but could you describe step-by step, which cells/numbers you want to take, what math operation is performed on these numbers (and examples), and how the loop would go?

Example:
Take B4 and change it's value from 1 to 100%. At each step, calculate average and sum of data table in B19:K26. Determine which B4 value generated best (max/min?) number and output that to O4.
 
No problem. I am grateful you try to help.
First open the spreadsheet, then go to worksheet named « Simulation Model ». Once there, look at variables one by one situated on B4, B7 and B15 (those in green font). Each of those variables have a specific distribution specified in the comment of the cell : as an example, B4 is normally distributed with a mean of 65% and a std of 5%. I would like the MC simulation to run for ex 1,000 draws at random on this cell so it creates a distribution of value out of it. Simultaneously, I would like this to be performed on the other two variables (in B7 consider a lower bound of 32% and an upper bound of 42%, on B15, consider a min of 5% and a max of 10%. Once the simulation is done, the output of the simulation is a range of values of the cell "O4", such range exhibiting, mean, standard dev etc in itself. That means that the output data (the 1,000 draws) shall be dumped somewhere in the spreadsheet so that we can perform analysis on it.

Hope this is clearer

best
 
Daguet
Have you read: http://chandoo.org/wp/2010/05/06/data-tables-monte-carlo-simulations-in-excel-a-comprehensive-guide/
That post walks you through the process

The use of Triangular and flat distributions is not a simple process in Excel without using complex addins
I'd be simply inclined to use a Normal Distribution for the triangular distribution and a Rand Between for the flat distribution

For the Normal Distribution in B4 I'd use:=NORMINV(RAND(),0.65,0.05)
For the Triangular Distribution B7 I'd use:=NORMINV(RAND(),0.37,0.01)
For the Flat Distribution in B15 I'd use:=RANDBETWEEN(5000,10000)/100000

Then setup a data table to collect the inputs and outputs, See attached

Excel seems to use a +/-6 SD in it's Functions which is probably good enough for most situations
So the two Normal functions

=NORMINV(RAND(),0.65,0.05)
will have values from 0.35 - 0.95
with 99.7% of values between 0.50 - 0.80

=NORMINV(RAND(),0.37,0.01)
will have values from 0.31 - 0.43
with 99.7% of values between 0.34 - 0.40

I should add that using formulas for this with a Data Table is so much faster than a UDF, which you originally asked about

I have also added a Histogram and Cummulative Count Distribution of the NPV Output as a chart

Enjoy
 

Attachments

  • netscape_revisited-1.xls
    436 KB · Views: 26
I shall defer to Hui on this one, as I was already stretching to understand.
 
Hui said:
Daguet
Have you read: http://chandoo.org/wp/2010/05/06/data-tables-monte-carlo-simulations-in-excel-a-comprehensive-guide/
That post walks you through the process

The use of Triangular and flat distributions is not a simple process in Excel without using complex addins
I'd be simply inclined to use a Normal Distribution for the triangular distribution and a Rand Between for the flat distribution

For the Normal Distribution in B4 I'd use:=NORMINV(RAND(),0.65,0.05)
For the Triangular Distribution B7 I'd use:=NORMINV(RAND(),0.37,0.01)
For the Flat Distribution in B15 I'd use:=RANDBETWEEN(5000,10000)/100000

Then setup a data table to collect the inputs and outputs, See attached

Excel seems to use a +/-6 SD in it's Functions which is probably good enough for most situations
So the two Normal functions

=NORMINV(RAND(),0.65,0.05)
will have values from 0.35 - 0.95
with 99.7% of values between 0.50 - 0.80

=NORMINV(RAND(),0.37,0.01)
will have values from 0.31 - 0.43
with 99.7% of values between 0.34 - 0.40

I should add that using formulas for this with a Data Table is so much faster than a UDF, which you originally asked about

I have also added a Histogram and Cummulative Count Distribution of the NPV Output as a chart

Enjoy
Click to expand...
Hui

Thank you so much for this detailed and grounded answer. I am now deeply indebted to you.

I appreciate a lot.

Thanks again, you solved my problem!!
 
@all
Hi!
Arriving at desset time but I'd have pointed out Hui's famous Monte Carlo simulation article. And if any question had arised, well, I'd have looked up :rolleyes: whistling :oops: as I waited (and prayed) that Hui read it. o_O
Regards!
 
I have been a bit distracted with other things in my life this past 2 weeks including travelling and becoming a grand father, so apologies for my tardiness ;)

Hui...
 
Hi, Hui!
Congratulations, old man. You've now entered in the same life age as b(ut)ob(ut)hc. :D
Regards!
 
You must log in or register to reply here.
Back
Top