Q1b

PHOTO EMBED

Mon Nov 15 2021 10:48:27 GMT+0000 (UTC)

Saved by @IsabellaLM #vba

Option Explicit
Sub mcSim()

    range("C16").Clear
    range("E3", "AL100").Clear

    Dim mcSim() As Double, mu As Double, iters As Integer, time As Integer
    Dim sd As Double, row As Integer, col As Integer

    
    time = InputBox("On which day would you like to exercise your option? (select a number between 1 and 63)")
    
    ReDim mcSim(1 To time, 1 To 30)
    iters = range("C17")
    range("B16").Value = "Selected exercise day"
    range("C16").Value = time

' Here you want to set the mean and stddev
' to whichever cell contains your inputs

    mu = range("C11")
    sd = range("C12")
    
    Randomize
    
   'i is rows, j is cols
   
    For col = 1 To iters
        For row = 1 To time
            mcSim(row, col) = mu + sd * WorksheetFunction.Norm_S_Inv(Rnd)
        Next row
    Next col

    range("F4").Resize(time, iters) = mcSim

    'label 2x2 array
    
    For col = 1 To 30
        For row = 1 To time
            Cells(row + 3, 5) = row
            Cells(3, col + 5) = col
        Next row
    Next col
    
    range("E3").Value = "Day"
    range(Cells(3, 5), Cells(time + 3, 5)).Interior.ColorIndex = 24
    range(Cells(3, 5), Cells(time + 3, 5)).HorizontalAlignment = xlCenter
    
End Sub
Sub FV1rand()

Dim col As Integer, row As Integer
Dim myrange As range
Dim time As Integer

time = range("C16")

For col = 6 To 35
    For row = 4 To time + 3
        Cells(row, 37) = 1 + Cells(row, col)
    Next row
    
    Set myrange = range(Cells(4, 37), Cells(time + 3, 37))
    Cells(time + 5, col) = WorksheetFunction.Product(myrange)
Next col
    
Cells(time + 5, 5) = "Future value of R1"
Cells(time + 5, 5).Interior.ColorIndex = 24

End Sub

Sub valuecalcs()

Dim col As Integer, row As Integer
Dim myrange As range
Dim time As Integer

time = range("C16")

Cells(time + 6, 5) = "ST"
Cells(time + 7, 5) = "X"
Cells(time + 8, 5) = "Max(ST-X,0)"
Cells(time + 9, 5) = "DCF"
Cells(time + 11, 5) = "Average DCF"


For col = 6 To 35
    Cells(time + 6, col) = WorksheetFunction.Product(Cells(time + 5, col), Cells(3, 3))
    Cells(time + 7, col) = range("C7")
    If Cells(time + 6, col) - Cells(time + 7, col) <= 0 Then
        Cells(time + 8, col) = 0
        Else
            Cells(time + 8, col) = Cells(time + 6, col) - Cells(time + 7, col)
        End If
    Cells(time + 9, col) = WorksheetFunction.Product(Cells(time + 8, col), Cells(14, 3))
    Next col
    
Set myrange = range(Cells(time + 9, 6), Cells(time + 9, 35))
Cells(time + 11, 6) = WorksheetFunction.Average(myrange)

Cells(time + 6, 5).Interior.ColorIndex = 24
Cells(time + 7, 5).Interior.ColorIndex = 24
Cells(time + 8, 5).Interior.ColorIndex = 24
Cells(time + 9, 5).Interior.ColorIndex = 24
Cells(time + 11, 5).Interior.ColorIndex = 17
Cells(time + 6, 5).HorizontalAlignment = xlCenter
Cells(time + 7, 5).HorizontalAlignment = xlCenter
Cells(time + 8, 5).HorizontalAlignment = xlCenter
Cells(time + 9, 5).HorizontalAlignment = xlCenter
Cells(time + 11, 5).HorizontalAlignment = xlCenter
    
End Sub
content_copyCOPY