Monte Carlo simulation in VBA

[diogene]

Hi everyone,

I am running a brief simulation with VBA where one variable (call it x) affects the output (call it y). The variables are two time series (x1,x2,...,x10 and y1,y2,...,y10).
I just implemeted the code to run 200 simulations at once and get the average values for x and y for each year.
What I would like to achieve in another tab is basically copying the worst case among these 200 simulations and just paste it there.
I am not sure how to proceed since the code would need to compare the results for each simulation and establish the lowest, then when finding a new lowest substituing with that, and so on.
Would anyone be able to help me?

Many thanks!

 

[RSpin]

You could make another variable, lets say Worstxy, then set it equal to the first simulation. Then during each simulation, simply compare the results of that simulation to what is currently stored in Worstxy. If the current simulation is worse that what is stored in Worstxy, then set Worstxy to the current simulation and continue on to the next simulation. After all of the simulations are done, Worstxy will hold the worst case for all of the simulations run. Something like this:

Dim Worstxy As Double 'dim Worstxy as whatever your regular output would be instead of double
Worstxy = 0 ' or some other value that is unreachable as the output of the simulation


'run your simulation as normal


If Worstxy = 0 Then ' so Worstxy will be set to the output of the first simulation
    Worstxy = OutputOfCurrentSimulation
Else
    If OutputOfCurrentSimulation < Worstxy Then
        Worstxy = OutputOfCurrentSimulation
    End If
End If


' Continue on to the next simulation as normal

 

[diogene]

You could make another variable, lets say Worstxy, then set it equal to the first simulation. Then during each simulation, simply compare the results of that simulation to what is currently stored in Worstxy. If the current simulation is worse that what is stored in Worstxy, then set Worstxy to the current simulation and continue on to the next simulation. After all of the simulations are done, Worstxy will hold the worst case for all of the simulations run. Something like this:

That makes sense, thank you. I also used a similar code to find the second worst observation, since I am trying to find the 99% VaR out of 100 observation (with 100 simulations, it is then the 99th worst result). How can I improve this so that when I change the number of simulations (let's say 200 instead of 100) it yields the proper 99% VaR (which would then be the 198th observation)?

Many thanks!