compile error: expected array ; binomial asset pricer

[fruitloop44]

hello,

i am trying to create a binomial asset Pricer and am expecting an output of ~$6.37 from my code.

i am getting the below error:
error.png

please help, thank you

full code:
BAPM_v1.xlsm

[WideBoyDixon]

Well, variable S is passed as a Double. The line of code that's failing is trying to reference S as if it were a two-dimensional array. VBA is expecting an array when you reference array indices but it's not an array; it's a Double.

WBD

[fruitloop44]

Well, variable S is passed as a Double. The line of code that's failing is trying to reference S as if it were a two-dimensional array. VBA is expecting an array when you reference array indices but it's not an array; it's a Double.

WBD

Gotcha!
What Can I change it to? I don?t believe it needs to be an array. That line should output just 1 # per line

[Marc L]

( removed )

[WideBoyDixon]

Based on this page:

https://en.wikipedia.org/wiki/Binomi..._pricing_model

And adapting the algorithm on the right into VBA, this is what I came up with:

Public Function BAPM(S As Double, q As Double, r As Double, sigma As Double, T As Double, N As Double, K As Double) As Variant

' S - stock price
' q - dividend yield
' r - ?
' sigma - ?
' T - expiration time
' N - height of binomial tree
' K - strike price

Dim deltaT, up, p0, p1

deltaT = T / N
up = Exp(sigma * deltaT ^ 0.5)
p0 = (up * Exp(-q * deltaT) - Exp(-r * deltaT)) / (up ^ 2 - 1)
p1 = Exp(-r * deltaT) - p0

Dim i As Long, j As Long, p, exercise

ReDim p(0 To N)

For i = 0 To N
    p(i) = K - S * up ^ (2 * i - N)
    If p(i) < 0 Then p(i) = 0
Next i

For j = N - 1 To 0 Step -1
    For i = 0 To j
        p(i) = p0 * p(i + 1) + p1 * p(i)
        exercise = K = S * up ^ (2 * i - j)
        If p(i) < exercise Then p(i) = exercise
    Next i
Next j

BAPM = p(0)

End Function

But it returns 5.251026 as the answer so perhaps it's not right?

WBD