Error #Value!, vba code trinomial model, Option Put American

[GLIMBO_BOX]

I am trying to calculate the PUT PRICE but I am getting error Value. This is the code :


Function Mon_AmericanPut_Trinomial(S, X, T, rf, sigma, n)
[/COLOR]delta_t = T / n
up = Exp(sigma * Sqr(3 * delta_t))
down = 1 / up
r = Exp(rf * delta_t)
Pu = (r - down) / (up - down)
Pd = (up - r) / (up - down)
Pm = 1 - Pu - Pd
Dim Prix_t_plus_1() As Double
Dim Prix_t() As Double
ReDim Prix_t_plus_1(n)
For State = 0 To n
Prix_t_plus_1(State) = Application.Max(X - S * _
up ^ State * down ^ (n - State), 0)
Next State
For Index = n - 1 To 0 Step -1
ReDim Prix_t(Index)
For State = 0 To Index
Prix_t(State) = Application.Max(X - S * up ^ State * down ^ (Index - State), _
(r) ^ (-1) * ((Pd) * Prix_t_plus_1(State) + (Pm) * Prix_t_plus_1(State + 1) + (Pu) * Prix_t_plus_1(State + 2)))
Next State
ReDim Prix_t_plus_1(Index)
For State = 0 To Index
Prix_t_plus_1(State) = Prix_t(State)
Next State
Next Index
Mon_AmericanPut_Trinomial = Prix_t(0)
End Function

[Paul_Hossler]

1. THERE'S NO NEED TO SHOUT IN YOUR TITLE

2. Please use CODE tags to format your macros. The [#] icon inserts them and paste the code between them. I fixed your macro this time

3. It's hard to test your macro without some input data that causes the error and the expected result. Please add this missing information

[GLIMBO_BOX]

Thank you, I did not mean to shout in the title sorry about that.
s= 30
X= 30
T= 1
Sigma= 25%
n = 30
rf=4%

[Paul_Hossler]

Is OK

?? rf = %

[GLIMBO_BOX]

I corrected it

[Paul_Hossler]

Can't help with math, but in
For State = 0 To Index I think the (state + 2) caused it to run off the edge

I added a - 2 to the For and it at least runs and doesn't give an error

30702



Option Explicit

Function Mon_AmericanPut_Trinomial(S As Double, X As Double, T As Double, rf As Double, sigma As Double, n As Long) As Double
Dim Prix_t_plus_1() As Double, delta_t As Double
Dim up As Double, down As Double, r As Double
Dim Prix_t() As Double
Dim Pu As Double, Pd As Double, Pm As Double
Dim State As Long, Index As Long
ReDim Prix_t_plus_1(n)
delta_t = T / n
up = Exp(sigma * Sqr(3 * delta_t))
down = 1 / up
r = Exp(rf * delta_t)
Pu = (r - down) / (up - down)
Pd = (up - r) / (up - down)
Pm = 1 - Pu - Pd
For State = 0 To n
Prix_t_plus_1(State) = Application.Max(X - S * _
up ^ State * down ^ (n - State), 0)
Next State
For Index = n - 1 To 0 Step -1
ReDim Prix_t(Index)
For State = 0 To Index - 2 ' <<<<<<<<<<<<<<<<<<<<<
Prix_t(State) = Application.Max(X - S * up ^ State * down ^ (Index - State), _
(r) ^ (-1) * ((Pd) * Prix_t_plus_1(State) + (Pm) * Prix_t_plus_1(State + 1) + (Pu) * Prix_t_plus_1(State + 2)))
Next State
ReDim Prix_t_plus_1(Index)
For State = 0 To Index
Prix_t_plus_1(State) = Prix_t(State)
Next State
Next Index
Mon_AmericanPut_Trinomial = Prix_t(0)
End Function