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
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