Hi all,

I am currently trying to develop a code for calculating the value of
an asian option using a binomial tree... In running the macro I get a
run-time error 6 - overflow message. I have put the code below. Please
note that the code is still under construction - in order to check it
I have put in some hard-coded figures. Of course, these should be
calculated in the model eventually. The bug is in calculating the
"InterO1" value ...

thanks in advance,

regards,

paul

Sub bereken_asian_call()

'input parameters
sig = Sheets("Sheet1").Range("B1").Value
T = Sheets("Sheet1").Range("B2").Value
N = Sheets("Sheet1").Range("B3").Value
r = Sheets("Sheet1").Range("B7").Value
div = Sheets("Sheet1").Range("B8").Value
S = Sheets("Sheet1").Range("B12").Value
K = Sheets("sheet1").Range("b13").Value
alpha = Sheets("Sheet1").Range("B14").Value

Dim St() As Double
Dim F() As Double
Dim O() As Double
Dim NewAv1() As Double
Dim NewAv2() As Double
Dim Ffut1() As Double
Dim Ffut2() As Double
Dim Ffut3() As Double
Dim Ffut4() As Double
Dim den1() As Double
Dim den2() As Double
Dim InterO1() As Double
Dim InterO2() As Double

'initialise parameters
dt = T / N
u = Exp(sig * Sqr(dt))
d = 1 / u
pu = (Exp(dt * r) - d) / (u - d)
pd = 1 - pu
edx = u / d
disc = Exp(-r * dt)

'initialise asset prices
ReDim St(N, 0 To N)
St(0, 0) = S

For index = 1 To N Step 1
St(index, 0) = St(0, 0) * d ^ (index - 0)
For state = 1 To index
St(index, state) = St(index, state - 1) * edx
Next state
Next index

'find range of maximum average for each node
ReDim F(N, 0 To N, 1 To alpha)

For index = 0 To N

If index = 1 Then
For state = 0 To index
F(index, state, 1) = Application.Average(St(0, 0), St(index,
state))
Next state
End If

If index 1 Then
For state = 0 To index
If index = state Then F(index, state, 1) =
Application.Average(index * F(index - 1, state - 1, 1) + St(index,
state)) / (index + 1)
If index < state Then F(index, state, 1) =
Application.Average(index * F(index - 1, state, 1) + St(index, state))
/ (index + 1)
Next state
End If

Next index

'find range of minimum average for each node
For index = 0 To N

If index = 1 Then
For state = 0 To index
F(index, state, alpha) = Application.Average(St(0, 0),
St(index, state))
Next state
End If

If index 1 Then
For state = 0 To index
If state = 0 Then F(index, state, alpha) =
Application.Average(index * F(index - 1, state, alpha) + St(index,
state)) / (index + 1)
If state 0 Then F(index, state, alpha) =
Application.Average(index * F(index - 1, state - 1, alpha) + St(index,
state)) / (index + 1)
Next state
End If

Next index

'find range of intermediate averages for each node
For index = 0 To N

For state = 0 To index
For a = alpha - 1 To 2 Step -1
F(index, state, a) = F(index, state, a + 1) + (F(index,
state, 1) - F(index, state, alpha)) / (alpha - 1)
Next a
Next state

Next index

'initialise option values at maturity
ReDim O(N, 0 To N, 1 To alpha)
For state = 0 To N
For a = 1 To alpha
O(N, state, a) = Application.Max(F(N, state, a) - K, 0)
Next a
Next state

'step back trough the tree
ReDim NewAv1(N, 0 To N, 1 To alpha)
ReDim NewAv2(N, 0 To N, 1 To alpha)
ReDim Ffut1(N, 0 To N, 1 To alpha)
ReDim Ffut2(N, 0 To N, 1 To alpha)
ReDim Ffut3(N, 0 To N, 1 To alpha)
ReDim Ffut4(N, 0 To N, 1 To alpha)
ReDim den1(N, 0 To N, 1 To alpha)
ReDim den2(N, 0 To N, 1 To alpha)
ReDim nom1(N, 0 To N, 1 To alpha)
ReDim nom2(N, 0 To N, 1 To alpha)
ReDim InterO1(N, 0 To N, 1 To alpha)
ReDim InterO2(N, 0 To N, 1 To alpha)
For index = N - 1 To 0 Step -1
For state = 0 To index
For a = 2 To alpha - 1
NewAv1(index, state, a) = ((index + 1) * F(index, state,
a) + St(index + 1, state + 1)) / (index + 2)
NewAv2(index, state, a) = ((index + 1) * F(index, state,
a) + St(index + 1, state)) / (index + 2)
Ffut1(index, state, a) = F(index + 1, state + 1, a + 1)
Ffut2(index, state, a) = F(index + 1, state + 1, a)
Ffut3(index, state, a) = F(index + 1, state, a + 1)
Ffut4(index, state, a) = F(index + 1, state, a)
den1(index, state, a) = Ffut2(index, state, a) -
Ffut1(index, state, a)
nom1(index, state, a) = ((NewAv1(index, state, a) -
Ffut1(index, state, a)) * 8.635 + (Ffut2(index, state, a) -
NewAv1(index, state, a)) * 8.101)
InterO1(index, state, a) = nom1(index, state, a) /
den1(index, state, a)

Next a
Next state
Next index

'Output
Sheets("sheet1").Range("F25").Value = NewAv1(4, 2, 2)
Sheets("sheet1").Range("F26").Value = NewAv2(4, 2, 2)
Sheets("sheet1").Range("F27").Value = InterO1(4, 2, 2)
Sheets("sheet1").Range("F28").Value = den1(4, 2, 2)

End Sub

Paul,
I have not looked at your code, but a start would be DIM your input
parameters (and those used in the "initialise parameters" section) in the
expected type and see what Excel/VBA is trying to fill them with, using the
Cxxx functions as necessary.

That may you track down

"Paul" wrote in message
om...
Hi all,

I am currently trying to develop a code for calculating the value of
an asian option using a binomial tree... In running the macro I get a
run-time error 6 - overflow message. I have put the code below. Please
note that the code is still under construction - in order to check it
I have put in some hard-coded figures. Of course, these should be
calculated in the model eventually. The bug is in calculating the
"InterO1" value ...

thanks in advance,

regards,

paul

Sub bereken_asian_call()

'input parameters
sig = Sheets("Sheet1").Range("B1").Value
T = Sheets("Sheet1").Range("B2").Value
N = Sheets("Sheet1").Range("B3").Value
r = Sheets("Sheet1").Range("B7").Value
div = Sheets("Sheet1").Range("B8").Value
S = Sheets("Sheet1").Range("B12").Value
K = Sheets("sheet1").Range("b13").Value
alpha = Sheets("Sheet1").Range("B14").Value

Dim St() As Double
Dim F() As Double
Dim O() As Double
Dim NewAv1() As Double
Dim NewAv2() As Double
Dim Ffut1() As Double
Dim Ffut2() As Double
Dim Ffut3() As Double
Dim Ffut4() As Double
Dim den1() As Double
Dim den2() As Double
Dim InterO1() As Double
Dim InterO2() As Double

'initialise parameters
dt = T / N
u = Exp(sig * Sqr(dt))
d = 1 / u
pu = (Exp(dt * r) - d) / (u - d)
pd = 1 - pu
edx = u / d
disc = Exp(-r * dt)

'initialise asset prices
ReDim St(N, 0 To N)
St(0, 0) = S

For index = 1 To N Step 1
St(index, 0) = St(0, 0) * d ^ (index - 0)
For state = 1 To index
St(index, state) = St(index, state - 1) * edx
Next state
Next index

'find range of maximum average for each node
ReDim F(N, 0 To N, 1 To alpha)

For index = 0 To N

If index = 1 Then
For state = 0 To index
F(index, state, 1) = Application.Average(St(0, 0), St(index,
state))
Next state
End If

If index 1 Then
For state = 0 To index
If index = state Then F(index, state, 1) =
Application.Average(index * F(index - 1, state - 1, 1) + St(index,
state)) / (index + 1)
If index < state Then F(index, state, 1) =
Application.Average(index * F(index - 1, state, 1) + St(index, state))
/ (index + 1)
Next state
End If

Next index

'find range of minimum average for each node
For index = 0 To N

If index = 1 Then
For state = 0 To index
F(index, state, alpha) = Application.Average(St(0, 0),
St(index, state))
Next state
End If

If index 1 Then
For state = 0 To index
If state = 0 Then F(index, state, alpha) =
Application.Average(index * F(index - 1, state, alpha) + St(index,
state)) / (index + 1)
If state 0 Then F(index, state, alpha) =
Application.Average(index * F(index - 1, state - 1, alpha) + St(index,
state)) / (index + 1)
Next state
End If

Next index

'find range of intermediate averages for each node
For index = 0 To N

For state = 0 To index
For a = alpha - 1 To 2 Step -1
F(index, state, a) = F(index, state, a + 1) + (F(index,
state, 1) - F(index, state, alpha)) / (alpha - 1)
Next a
Next state

Next index

'initialise option values at maturity
ReDim O(N, 0 To N, 1 To alpha)
For state = 0 To N
For a = 1 To alpha
O(N, state, a) = Application.Max(F(N, state, a) - K, 0)
Next a
Next state

'step back trough the tree
ReDim NewAv1(N, 0 To N, 1 To alpha)
ReDim NewAv2(N, 0 To N, 1 To alpha)
ReDim Ffut1(N, 0 To N, 1 To alpha)
ReDim Ffut2(N, 0 To N, 1 To alpha)
ReDim Ffut3(N, 0 To N, 1 To alpha)
ReDim Ffut4(N, 0 To N, 1 To alpha)
ReDim den1(N, 0 To N, 1 To alpha)
ReDim den2(N, 0 To N, 1 To alpha)
ReDim nom1(N, 0 To N, 1 To alpha)
ReDim nom2(N, 0 To N, 1 To alpha)
ReDim InterO1(N, 0 To N, 1 To alpha)
ReDim InterO2(N, 0 To N, 1 To alpha)
For index = N - 1 To 0 Step -1
For state = 0 To index
For a = 2 To alpha - 1
NewAv1(index, state, a) = ((index + 1) * F(index, state,
a) + St(index + 1, state + 1)) / (index + 2)
NewAv2(index, state, a) = ((index + 1) * F(index, state,
a) + St(index + 1, state)) / (index + 2)
Ffut1(index, state, a) = F(index + 1, state + 1, a + 1)
Ffut2(index, state, a) = F(index + 1, state + 1, a)
Ffut3(index, state, a) = F(index + 1, state, a + 1)
Ffut4(index, state, a) = F(index + 1, state, a)
den1(index, state, a) = Ffut2(index, state, a) -
Ffut1(index, state, a)
nom1(index, state, a) = ((NewAv1(index, state, a) -
Ffut1(index, state, a)) * 8.635 + (Ffut2(index, state, a) -
NewAv1(index, state, a)) * 8.101)
InterO1(index, state, a) = nom1(index, state, a) /
den1(index, state, a)

Next a
Next state
Next index

'Output
Sheets("sheet1").Range("F25").Value = NewAv1(4, 2, 2)
Sheets("sheet1").Range("F26").Value = NewAv2(4, 2, 2)
Sheets("sheet1").Range("F27").Value = InterO1(4, 2, 2)
Sheets("sheet1").Range("F28").Value = den1(4, 2, 2)

End Sub