How to summarize repeated calculations using rand()

[roscoe]

I'm trying to compare two differential financial options, both based on the same (but randomly determined) market return rates. One option can't lose money but is capped on gains, whereas the other can make or lose as much as the market rates do. At the end of 10 years I'd like to compare the results. That in itself is not so hard.

Using the rand() function the market numbers recalculate constantly which subsequently changes the results. Here's the kicker. What I'd like to do is run 10, 100, 1000, whatever, iterations of random numbers and determine an average result for each scenario. Right now I would have to record one, then recalc, then record those results, etc...

Obviously there has to be an easier way...looking for ideas.

Thanks!

 

[Artik]

I don't see any solution other than recording intermediate results to calculate the average.

Assuming that a random number is entered in A1 and the result is obtained in D1, you can implement such a code:

Sub AAA()
    Dim v As Variant
    Dim Cnt As Long
    Dim i As Long
    Dim Result As Double

    Cnt = Application.InputBox(Prompt:="How many iterations?", Type:=1)

    If Cnt < 1 Then Exit Sub

    Application.ScreenUpdating = False

    ReDim v(1 To Cnt)

    Randomize

    For i = 1 To Cnt
        Range("A1").Value = Rnd
        v(i) = Range("D1").Value
    Next i

    Result = Application.Sum(v) / Cnt

    Range("A1").Value = Result

    Application.ScreenUpdating = True

    MsgBox "The average result for " & Cnt & " iterations is: " & Range("D1").Value

End Sub

Artik

 

[roscoe]

Thanks, that was kinda what I was thinking. But because I was rusty in VBA I ended up just creating 10,000 lines of identical (but randomized) data and built a table aggregating that data.

 

[roscoe]

I went back and used your code with some added modifications to handle multiple scenarios at once. Works great, thanks!