VBA how to speed up the code

Question

Hey I have the following code written in VBA. It simulates trajectory of asset price (BS_trajektoria_2 function) and calculate payoff from option, This procedure is repeated num_of_sim times to get better ressult (because asset price is random so payoff is random).I use it like this: barrier_MC(100;100;1;0,05;0,02;0,2;120;365;10000;"call";"UO"). How can I speed up this code? I would like to use bigger number of N and num_of_sim but it takes a long time to compile.

Function RandNorm(Optional mean As Double = 0, Optional sd As Double = 1) As Double
Dim r1, r2, s As Double
r1 = Rnd()
If r1 = 0 Then r1 = Rnd()
r2 = Rnd()
s = Sqr(-2 * Log(r1)) * Cos(6.283185307 * r2)
RandNorm = mean + sd * s
End Function
 Function payoff(S_T As Double, K As Double, CallPut As String)
 If CallPut = "call" Then
 omega = 1
 Else: omega = -1
 End If
 
 payoff = WorksheetFunction.Max(omega * (S_T - K), 0)
 
 End Function
 
 Function BS_trajektoria2(S_0 As Double, T As Double, r As Double, q As Double, sigma As Double, N As Long) As Double()
 
 Randomize
 Dim s() As Double
 Dim delta_t As Double
 Dim i As Long
 ReDim s(N)
 Dim rand As Double
 s(0) = S_0
 delta_t = T / N
 
 For i = 1 To N
 s(i) = s(i - 1) * Exp((r - q - 0.5 * sigma ^ 2) * delta_t + sigma * delta_t ^ 0.5 * RandNorm())
 Next i
 
 BS_trajektoria2 = s
 End Function
 
 
 
 Function barrier_MC(S_0 As Double, K As Double, T As Double, r As Double, q As Double, sigma As Double, _
 B As Double, N As Long, num_of_sim As Long, CallPut As String, BarType As String) As Double
 
 Dim max_value As Double
 Dim suma_wyplat As Double
 Dim wyplata As Double
 Dim i As Long
 Dim s() As Double
 suma_wyplat = 0
 
 If (BarType = "DO" Or BarType = "DI") And B > S_0 Then
 MsgBox "Too high barrier!"
 Exit Function
 ElseIf (BarType = "UO" Or BarType = "UI") And B < S_0 Then
 MsgBox "Too low barrier"
 Exit Function
 End If
 
 With WorksheetFunction
 For i = 1 To num_of_sim
 Randomize
 s = BS_trajektoria2(S_0, T, r, q, sigma, N)
 max_value = .Max(s)
 If max_value >= B Then
 wyplata = 0
 Else
 wyplata = payoff(s(N), K, CallPut)
 End If
 suma_wyplat = suma_wyplat + wyplata
 Next i
 End With
 barrier_MC = Exp(-r * T) * suma_wyplat / num_of_sim
 End Function
 
 Sub test3()
 MsgBox barrier_MC(100, 100, 1, 0.05, 0.02, 0.2, 120, 1000, 10000, "call", "UO")
 End Sub

• BCdotWEB: The current question title, which states your concerns about the code, applies to too many questions on this site to be useful. The site standard is for the title to simply state the task accomplished by the code. Please see How do I ask a good question?.
• Freeflow: Please use Option Explit at the start of each module. Also take a look at the fantastic and free Rubberduck addin for VBA. The indenter and code inspections will help your code a lot. Given the above my inclination would be to move from pure VBA to a library in C#. pragmateek.com/extend-your-vba-code-with-c-vb-net-or-ccli
• pacmaninbw: What does the code do?
• Kerni: @pacmaninbw it simulates trajectory of asset price and calculate payoff from option, This procedure is repeated num_of_sim times to get better ressult
• pacmaninbw: Please state that in the question.

Answer 1

The question is how much speed improvement you expect. What I would do:

I'd analyze RandNorm for this is a user function that you call 10M times that really influences runtime. Considering that rnd provides pseudo-random numbers it might even be unappropriate but it's your choice.

Minor improvements can be achieved by replacing

 For i = 1 To N
 s(i) = s(i - 1) * Exp((r - q - 0.5 * sigma ^ 2) * delta_t + sigma * delta_t ^ 0.5 * RandNorm())
 Next i

with

 fact1 = Exp((r - q - 0.5 * sigma ^ 2) * delta_t
 fact2 = sigma * delta_t ^ 0.5
 For i = 1 To N
 s(i) = s(i - 1) * fact1 + fact2 * RandNorm())
 Next i

for most factors are constant within the loop so you don't need to calculate them so many times.

Also you can check max value in this loop like

If s(i) > sMax Then sMax = s(i)

and return sMax somehow to barrier_MC so you don't need to call WorksheetFunction.Max on an array of 1K doubles 10K times.

I'd also rework payoff to eliminate the need of WorksheetFunction.Max (10K times) like

payoff = omega * (S_T - K)
If CallPut = "call" Then
 If payoff > 0 Then payoff = 0
Else
 If payoff < 0 Then payoff = 0
End If

I'd also consider replacing "Call" string parameter with a Long constant so you can replace 10K string comparisons with Long comparisons.

You Randomize before calling BS_trajektoria2 and within BS_trajektoria2.