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
Preview:
downloadDownload PNG
downloadDownload JPEG
downloadDownload SVG
Tip: You can change the style, width & colours of the snippet with the inspect tool before clicking Download!
Click to optimize width for Twitter