Option Explicit

Sub mcSim()

    Dim mcSim(1 To 63, 1 To 30) As Double, mu As Double
    Dim sd As Double, i As Integer, j As Integer

    mu = Range("C11")
    sd = Range("C12")
    
    Randomize
    
    'fill 2x2 array
    
    For j = 1 To 30
        For i = 1 To 63
            mcSim(i, j) = mu + sd * WorksheetFunction.Norm_S_Inv(Rnd)
        Next i
    Next j

    Range("F4").Resize(63, 30) = mcSim

    'label 2x2 array
    
    For j = 1 To 30
        For i = 1 To 63
            Cells(i + 3, 5) = i
            Cells(3, j + 5) = j
        Next i
    Next j

    
End Sub

Sub valuecalcs()

Dim col As Integer, row As Integer
Dim myrange As Range

For col = 6 To 35
    For row = 4 To 66
        Cells(row, 37) = 1 + Cells(row, col)
    Next row
    
    Set myrange = Range(Cells(4, 37), Cells(66, 37))
    Cells(68, col) = WorksheetFunction.Product(myrange)
Next col
    
Range("E68").Value = "Future value of R1"

End Sub

Sub valuecalcs2()

Dim col As Integer, row As Integer
Dim myrange As Range

Range("E69").Value = "ST"
Range("E70").Value = "X"
Range("E71").Value = "Max(ST-X,0)"
Range("E72").Value = "DCF"
Range("E74").Value = "Average DCF"


For col = 6 To 35
    Cells(69, col) = WorksheetFunction.Product(Cells(68, col), Cells(3, 3))
    Cells(70, col) = Range("C7")
    If Cells(69, col) - Cells(70, col) <= 0 Then
        Cells(71, col) = 0
        Else
            Cells(71, col) = Cells(69, col) - Cells(70, col)
        End If
    Cells(72, col) = WorksheetFunction.Product(Cells(71, col), Cells(14, 3))
    Next col
    
Set myrange = Range(Cells(72, 6), Cells(72, 35))
Cells(74, 6) = WorksheetFunction.Average(myrange)
    
End Sub