VB or VBA code to generate random distribution that takes into account the correlation ratio.

Hi everybody,

My question is part math, part stats, using VBA.  Although I only have an average grasp of mathematical concepts, I'm writing a spreadsheet based on Michael Edleson's Value Averaging book to run investment portfolio simulations.  What I would like some help with is how to calculate the effects of correlation between asset classes in generating my random distributions.

Using one fund is fairly easy to do following Mr. Edleson's instructions.  Here is some VB code that creates the normal random distributions:

    curPrice = Me.txtStartValue
    sglStandardDeviation = Val(Me.txtStandardDeviation) / 100
    sglReturnPerPeriod = Val(Me.txtReturnPerPeriod) / 100
    intNumberOfPeriods = Me.txtPeriods
   
    ReDim aRandomNumbers(1 To intNumberOfPeriods)
    Randomize
    For i = LBound(aRandomNumbers) To UBound(aRandomNumbers)
        'STEP 1: Generate normally distributed random numbers
        aRandomNumbers(i) = NormRand()
       
        'STEP 2: Convert numbers into monthly stock returns
        aRandomNumbers(i) = aRandomNumbers(i) * sglStandardDeviation + sglReturnPerPeriod
       
        'STEP 3: genrerate stock prices
        curPrice = curPrice * (1 + aRandomNumbers(i))
    Next

Here is the code for the NormRand function that I downloaded from a website:

Function NormRand() As Double
    ' NormRand returns a randomly distributed drawing from a
    ' standard normal distribution i.e. one with:
    ' Average = 0 and Standard Deviation = 1.0
    Dim fac As Double, rsq As Double, v1 As Double, v2 As Double
    Static flag As Boolean, gset As Double
   
    ' Each pass through the calculation of the routine produces
    '  two normally-distributed deviates, so we only need to do
    '  the calculations every other call. So we set the flag
    '  variable (to true) if gset contains a spare NormRand value.
    If flag Then
        NormRand = gset
        ' Force calculation next time.
        flag = False
    Else
        ' Don't have anything saved so need to find a pair of values
        ' First generate a co-ordinate pair within the unit circle:
        Do
            v1 = 2 * Rnd - 1#
            v2 = 2 * Rnd - 1#
            rsq = v1 * v1 + v2 * v2
        Loop Until rsq <= 1#
       
        ' Do the Math:
        fac = Sqr(-2# * Log(rsq) / rsq)
       
        ' Return one of the values and save the other (gset) for next time:
        NormRand = v2 * fac
        gset = v1 * fac
        flag = True
    End If    
End Function

Now I want to add more funds to the mix.  Presumably, these are not completely unrelated, as they all have some correlation to each other (IE:  their correlation coefficient is from -1 to + 1).  My question is:  

How would I calculate the random distributions for additional funds while taking into account the correlation coefficient between the funds?

For example, lets say that I add a second fund has a correlation ratio of +0.75.  I would now like to generate a second set of random distributions that take into account the correlation ratio so that it's movements follow the first fund to some extent.

Thanks a lot,

RobStart Free Trial