Hi all,

Below I have provided the code (Sub bereken_asian_call) for a macro
which finally calculates two arrays: O(index,state,a) and
F(index,state,a). The range for the a values in this case is 1 to
alpha (which is 4 now).
Additionally, a new value is calculated called NewAv1.

I should now interpolate NewAv1 given the array O(index,state,1 to
alpha) and F(index,state,1 to alpha). For that I want to use a
function written in VBA called VlinearInterpolation (see below).
Therefore, I must therefore to that function within the sub
bereken_asian_call. The function is defined as:
VlinearInterpolation(value to be interpolated, range 1, range 2) ...

In my case range 1 is: O(index,state,1 to alpha) and range 2 is
F(index,state,1 to alpha) ...

Question is: how should i refer to the VLinearInterpolation function
and how do I assign the O(index,state,1 to alpha) and F(index,state,1
to alpha) ranges to range 1, respectively range 2 in the
VLinearInterpolation function.

thanks in advance,

thei


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 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 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
InterO1(index,state,a)=VlinearInterpolation(NewAv1 ,range O
values, range F values)

Next state
Next index

'Output
Sheets("sheet1").Range("F25").Value = O(18, 0, 1)
Sheets("sheet1").Range("F26").Value = O(18, 0, 2)
Sheets("sheet1").Range("F27").Value = O(18, 0, 3)
Sheets("sheet1").Range("F28").Value = O(18, 0, 4)

End Sub

Function VLinearInterpolation(T As Double, TRange As Range, _
LRange As Range) As Double
Dim nRow As Integer
Dim TLow As Double
Dim THigh As Double
Dim LLow As Double
Dim LHigh As Double

'If at top or bottom, use two points at the end to extrapolate
If T < TRange.Cells(1, 1) Then
nRow = 1
Else
nRow = WorksheetFunction.Match(T, TRange)
If nRow = TRange.Rows.Count Then
nRow = nRow - 1
End If
End If

TLow = TRange.Cells(nRow, 1)
THigh = TRange.Cells(nRow + 1, 1)
LLow = LRange.Cells(nRow, 1)
LHigh = LRange.Cells(nRow + 1, 1)
VLinearInterpolation = (T - TLow) * (LHigh - LLow) / (THigh -
TLow) + LLow

End Function