PDA

View Full Version : Problem with VBA code - simulating profit and loss from delta hedging.

arturb
12-31-2015, 09:58 AM
Hello,

Can somebody help me with the following problem?
The point is to calculate function - profit and loss from a delta hedging.
The code seems to be ok but there is a problem when calling it from the worksheet, there is a notification #ARG!

The code is:

Function PnL(N, T, S, mu, X, r, v, d)

Dim Stock() As Double
Dim delta() As Double
Dim Sigma As Double
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim dt As Double

dt = T / (N + 1)
Stock(0) = S

For i = 1 To N + 1
Stock(i) = Stock(i - 1) * Exp((mu - 0.5 * v ^ 2) * dt + Application.WorksheetFunction.NormSInv(Application.WorksheetFunction.RandBe tween(0, 1)) * v * Sqr(dt))
Next i
For j = 0 To N
delta(j) = OptionDelta("C", Stock(j), X, T - j * dt, r, v, d)
Next j

Sigma = delta(0) * Stock(0)

For k = 1 To N
Sigma = Sigma * Exp(r * dt) + (delta(k) - delta(k - 1)) * Stock(k)
Next k

PnL = Application.WorksheetFunction.Max(0, Stock(N + 1) - X) - delta(N) * Stock(N + 1) + Sigma * Exp(r * dt) - Exp(r * T) * OptionPrice("C", S, X, T, r, v, d)

End Function

Bob Phillips
12-31-2015, 10:41 AM
I think you need to add this line at the head of the code, at the least

ReDim Stock(0 To N + 1)
Redim delta(0 to N)