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