Pricing European Options with a Trinomial Tree in VBA

# Trinomial tree VBA code


# Trinomial tree VBA code












I am studying binomial trees and I'm implementing them in VBA to see their convergence to the BS model. I searched 3-4 hours in the web; the only good site I know is Volopta.

Very simply question by a beginner: where can I find the VBA function code for the trinomial tree?

EDIT: I found the VBA function code for the trinomial tree in the Rouah-Veinberg book; I reported it on VBA as follows

```
Function Trinomial(Spot, K, T, r, v, n, PutCall As String, EuroAmer As String)

dt = T / n: u = Exp(v * Sqr(2 * dt)): d = 1 / u
pu = (Exp(r * dt / 2) - Exp(-v * Sqr(dt / 2))) ^ 2 / (Exp(v * Sqr(dt / 2)) - Exp(-v * Sqr(dt / 2))) ^ 2

pd = (Exp(v * Sqr(dt / 2)) - Exp(r * dt / 2)) ^ 2 / (Exp(v * Sqr(dt / 2)) - Exp(-v * Sqr(dt / 2))) ^ 2
pm = 1 - pu - pd
S(1, 1) = Spot
For j = 2 To (n + 1)
For i = 1 To (2 * j - 1)
S(i, j) = S(1, 1) * u ^ j * d ^ i
Next i
Next j

For i = 1 To (2 * n + 1)
Select Case PutCall
Case "Call"
Op(i, n + 1) = Application.Max(S(i, n + 1) - K, 0)
Case "Put"
Op(i, n + 1) = Application.Max(K - S(i, n + 1), 0)
End Select
Next i

For j = n To 1 Step -1
For i = 1 To (2 * j - 1)
Select Case EuroAmer
Case "Amer":
If PutCall = "Call" Then
Op(i, j) = Application.Max(S(i, j) - K, Exp(-r * dt) * (pu * Op(i, j + 1) + pm * Op(i + 1, j + 1) + pd * Op(i + 2, j + 1)))
ElseIf PutCall = "Put" Then
Op(i, j) = Application.Max(K - S(i, j), Exp(-r * dt) * (pu * Op(i, j + 1) + pm * Op(i + 1, j + 1) + pd * Op(i + 2, j + 1)))
End If
Case "Euro":
Op(i, j) = Exp(-r * dt) * (pu * Op(i, j + 1) + pm * Op(i + 1, j + 1) + pd * Op(i + 2, j + 1))
End Select
Next i
Next j

End Function
```

but when I come back to Excel and insert the parameters into the function and I press Enter, the following window appears

Compiling error Sub or Function not defined

What's wrong with this code? I doubt the error is into the data I submit, because I've already implemented two different binomial trees with success with the same datas. However, I repeat, I'm a beginner, so any help would be appreciated. Maybe somewhere on the web in which I can copy-paste the code.

Many thanks

EDIT2 I changed the beginning of my code as follows:

```
Function Trinomial(Spot As Double, K As Double, T As Double, r As Double, v As Double, n As Integer, PutCall As String, EuroAmer As String)
```

and then, before the line

```
dt = T / n: u = Exp(v * Sqr(2 * dt)): d = 1 / u
```

I added

```
Dim S() As Double
ReDim S(i, j) As Double

Dim Op() As Double
ReDim Op(n + 1, n + 1) As Double

Dim dt As Double
Dim Pu As Double
Dim Pd As Double
Dim Pm As Double
```

without touching anything else. In this way I have no more problem on the VBA side (in particular, the message "Compiling error Sub or Function not defined" doesn't appear anymore); and this is good.

The problem now is on EXCEL side: when I use this function, something goes wrong; in the cell, instead of the number resulting from this function, it appears

"#VALUE!"

but I have no idea at all, of what is going wrong.

## Answer by amdopt (score 3, accepted)

https://quant.stackexchange.com/a/33275

Joe,

I wrote this a while ago and it could be cleaned up a little. It is for European Calls and Puts. I have a couple of lines commented out. I was probably going to add American pricing in but never did so. I just tested it out and it still works for me. Note that my input on the spreadsheet is in days and not years. I convert to years in the VBA. See below.

```
Function TrinomialTree(OptionType As String, S As Double, K As Double, _
                sigma As Double, rfr As Double, T As Double, _
                div As Double, n As Integer)

Dim Val() As Double
Dim up As Double
Dim dn As Double
Dim pUp As Double
Dim pDn As Double
Dim pStat As Double
Dim cst As Double
Dim dt As Double
Dim df As Double
Dim T1 As Double
Dim i As Integer
Dim j As Integer
Dim x As Integer
Dim OpType As Integer

ReDim Val(n * 2 + 1)

T1 = T / 365
dt = T1 / n
df = Exp(-rfr * dt)
cst = rfr - div

Select Case OptionType
    Case "EC"
        OpType = 1
    Case "EP"
        OpType = -1
'        Case "AC"
'            OpType = 1
'        Case "AP"
'            OpType = -1
End Select

up = Exp(sigma * Sqr(2 * dt))
dn = Exp(-sigma * Sqr(2 * dt))

pUp = ((Exp(cst * dt / 2) - Exp(-sigma * Sqr(dt / 2))) / _
        (Exp(sigma * Sqr(dt / 2)) - Exp(-sigma * Sqr(dt / 2)))) ^ 2
pDn = ((Exp(sigma * Sqr(dt / 2)) - Exp(cst * dt / 2)) / _
        (Exp(sigma * Sqr(dt / 2)) - Exp(-sigma * Sqr(dt / 2)))) ^ 2
pStat = 1 - (pUp + pDn)

For i = 0 To (n * 2)
    Val(i) = Application.WorksheetFunction.Max(0, OpType * (S * up ^ _
                Application.WorksheetFunction.Max(i - n, 0) * dn ^ _
                Application.WorksheetFunction.Max(n * 2 - n - i, 0) - K))
Next i

For x = n - 1 To 0 Step -1
    For i = 0 To (x * 2)
        Val(i) = (pUp * Val(i + 2) + pStat * Val(i + 1) + pDn * Val(i)) * df
    Next i
Next x

TrinomialTree = Val(0)

End Function
```

Being that this has drifted off topic, feel free to contact me off the forum if you are stuck and I will try to help you out.

## Answer by nbbo2 (score 2)

https://quant.stackexchange.com/a/33269

You need to declare the size of the matrix S(.,.).

For example Dim S(50, 50) As Double

This is an elementary VBA question not suitable for QuantStackExchange.

Also you shouold ask new questions separately, not trash an existing question by changing it.