Hello guys,
I struggle to code a function in Excel/VBA (Excel 2007). The intention of the function is to compute a price for an option (based on the simulated prices of 20 underlying stocks).
The simulation of the pricepaths for the stocks is based on a geometric Brownian motion.

Some other explanations:
  • chofactor is a 20x20 matrix, S0 is 1x20 matrix, weights is 1x20 Matrix
  • The complete loop has to be computed "numruns" times
  • each pricepath has "N" steps
  • UNRV is an array of 20 random numbers
  • CNRV is an array of 20 correlated random numbers (by multiplication of UNRV and the Matrix chofactor)
  • Pricepath is simulated price over "N" periods of each stock


The code:

Function BasketCallASX20(chofactor() As Double, S0() As Double, weights() As Double) As Double
'Declare variables and get values
rf = Range("B5")
T = Range("B6")
N = Range("B7")
numruns = Range("B9")
Assets = Range("B10")
'Declare S0 and weighst as vectors
ReDim S0(1 To Assets)
ReDim weights(1 To Assets)
'Declare cholesky matrix
ReDim chofactor(1 To Assets, 1 To Assets)
'Declare variable for uncorrelated normal random variables
Dim UNRV() As Double
ReDim UNRV(1 To Assets)
'Declare variable for correlated normal random variables
Dim CNRV() As Double
ReDim CNRV(1 To Assets)
'Declare variable to store price path
Dim PricePath() As Double
ReDim PricePath(1 To Assets)
'Declare other variables
dt = T / N
sdt = Sqr(dt)
OptPayoffSum = 0
'generate price paths
For i = 1 To numruns
    PricePath() = S0
    UNRV() = 0
    CNRV() = 0
    For j = 1 To N
        For k = 1 To Assets
            'generate uncorrelated and correlated norm.RVs
            Randomize
            UNRV(j, k) = Application.NormSInv(Rnd)
            CNRV(j, k) = Application.Transpose(Application.MMult(chofactor(), Application.Transpose(UNRV(j, k))))
            PricePath(j, k) = PricePath(j - 1, k) + rf * PricePath(j - 1, k) * dt + PricePath(j - 1, k) * sdt * CNRV(j, k)
        Next k
    Next j
    'Compute payoff
    OptPayoffPath = Application.Max(Application.SumProduct(weights, PricePath(Assets)) - Application.SumProduct(weights, S0), 0)
    'Add the payoffs up
    OptPayoffSum = OptPayoffSum + OptPayoffPath
Next i
'Compute expected payoff and discount
ExpOptPayoff = OptPayoffSum / numruns
BasketCallASX20 = Exp(-r * T) * ExpOptPayoff

End Function
Thank you in advance