Sub MCEurCallPrice()
Dim S As Double
Dim K As Double
Dim r As Double
Dim q As Double
Dim sigma As Double
Dim timeToExp As Double
Dim numberOfTrajectories As Integer
Dim u As Double
Dim epsilon As Double
Dim ST As Double
Dim payoff As Double
Dim ans As Double

S = Range("B" & 4).Value
K = Range("B" & 5).Value
r = Range("B" & 6).Value
q = Range("B" & 7).Value
sigma = Range("B" & 8).Value
timeToExp = Range("B" & 9).Value
numberOfTrajectories = Range("B" & 12).Value

ans = 0



For i = 1 To numberOfTrajectories
u = Rnd()
epsilon = Application.WorksheetFunction.NormSInv(u)
ST = S * Exp((r - q - 0.5 * sigma ^ 2) * timeToExp + sigma * epsilon * timeToExp ^ 0.5)
If ST - K >= 0 Then payoff = ST - K Else payoff = 0
ans = ans + payoff
Range("B" & 19 + i).Value = u
Range("C" & 19 + i).Value = epsilon
Range("D" & 19 + i).Value = ST
Range("F" & 19 + i).Value = payoff
Next i

Range("F" & 6).Value = ans / numberOfTrajectories
Range("F" & 6).Value = Exp(-r * timeToExp) * ans / numberOfTrajectories

End Sub






























----------------------------------------------------------------------
Sub MCAsianCallPrice()
Dim S As Double
Dim K As Double
Dim r As Double
Dim q As Double
Dim sigma As Double
Dim timeToExp As Double
Dim numberOfTrajectories As Integer
Dim numberOfPartitions As Integer
Dim u As Double
Dim epsilon As Double
Dim ST As Double
Dim trajectoryAverage As Double
Dim payoff As Double
Dim ans As Double

S = Range("B" & 4).Value
K = Range("B" & 5).Value
r = Range("B" & 6).Value
q = Range("B" & 7).Value
sigma = Range("B" & 8).Value
timeToExp = Range("B" & 9).Value
numberOfTrajectories = Range("B" & 12).Value
numberOfPartitions = Range("B" & 11).Value

ans = 0

Range("E" & 19).Select

For i = 1 To numberOfTrajectories
trajectoryAverage = 0
For j = 0 To numberOfPartitions - 1
u = Rnd()
epsilon = Application.WorksheetFunction.NormSInv(u)
ST = S * Exp((r - q - 0.5 * sigma ^ 2) * (timeToExp / numberOfPartitions) + sigma * epsilon * (timeToExp / numberOfPartitions) ^ 0.5)
trajectoryAverage = trajectoryAverage + ST
ActiveCell.Offset(i, j).Value = ST
Next j
trajectoryAverage = trajectoryAverage / numberOfPartitions
Range("A" & 19 + i).Value = trajectoryAverage
If trajectoryAverage >= K Then payoff = trajectoryAverage - K Else payoff = 0
Range("B" & 19 + i).Value = payoff
ans = ans + payoff
Next i

Range("F" & 6).Value = ans / numberOfTrajectories
Range("F" & 6).Value = Exp(-r * timeToExp) * ans / numberOfTrajectories

End Sub


----------------------------------------------------------------------

Sub clear()

Range("A20:IA65000").ClearContents

End Sub















----------------------------------------------------------------------

Function myFactorial(N As Integer)

Dim K As Integer
Dim ans As Double
ans = 1

For K = 2 To N
ans = ans * K
Next K

myFactorial = ans


End Function
----------------------------------------------------------------------


Function myNx(x As Double)

Dim K As Integer
Dim M As Integer
Dim Pi As Double
Dim ans As Double
Dim bk As Double

Pi = 3.14159265


If ((10 - 2 * x ^ 2) ^ 2 - 24 * (6 - x ^ 2) < 0) Then M = 0 Else M = Int((-(10 - 2 * x ^ 2) + ((10 - 2 * x ^ 2) ^ 2 - 24 * (6 - x ^ 2)) ^ 0.5) / 8)


ans = 0.5


For K = 0 To M + 1
bk = (x ^ (2 * K + 1)) / (((2 * Pi) ^ 0.5) * (2 ^ K) * myFactorial(K) * (2 * K + 1))
ans = ans + ((-1) ^ K) * bk
Next K

Do While Abs(bk) > 1e-05
bk = (1 / (2 * Pi) ^ 0.5) * ((0.5) ^ K) * (x ^ (2 * K + 1)) / (myFactorial(K) * (2 * K + 1))
ans = ans + ((-1) ^ K) * bk
K = K + 1
Loop


myNx = ans

End Function

----------------------------------------------------------------------

Function BSCall(S As Double, K As Double, r As Double, q As Double, sigma As Double, timeToExp As Double)

Dim d1 As Double
Dim d2 As Double

d1 = (Log(S / K) + (r - q + 0.5 * sigma ^ 2) * (timeToExp)) / (sigma * (timeToExp) ^ 0.5)
d2 = d1 - sigma * (timeToExp) ^ 0.5


BSCall = S * Exp(-q * timeToExp) * myNx(d1) - K * Exp(-r * timeToExp) * myNx(d2)



End Function