-1
$\begingroup$

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.

$\endgroup$

closed as off-topic by LocalVolatility, Bob Jansen Mar 27 '17 at 14:58

  • This question does not appear to be about quantitative finance within the scope defined in the help center.
If this question can be reworded to fit the rules in the help center, please edit the question.

  • 2
    $\begingroup$ Jackson & Staunton: Advanced Modelling in Finance using Excel and VBA $\endgroup$ – noob2 Mar 24 '17 at 20:34
  • $\begingroup$ not what you asked but there are better trees. See the chapter in my book "more mathematical finance" for a comprehensive survey, $\endgroup$ – Mark Joshi Mar 26 '17 at 0:49
  • $\begingroup$ @MarkJoshi: Is there also the VBA function code of the trinomial tree? $\endgroup$ – Joe Mar 26 '17 at 10:39
  • $\begingroup$ @Joe I may have trinomial VBA codes at my office. I will post Monday if no one else has answered by then. $\endgroup$ – amdopt Mar 26 '17 at 12:57
  • 1
    $\begingroup$ My comment stands. Your last edit introduced yet again another problem. This has nothing to do with the original question any more. $\endgroup$ – LocalVolatility Mar 27 '17 at 14:38
2
$\begingroup$

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.

$\endgroup$
  • $\begingroup$ You are very very kind. Thanks again. I've already searched your private contact; not to bore you with tons of questions; but I saw what you wrote in your profile and it seemed interesting to me; nevertheless I didn't found any kind of contact (nor mail addresses, neither similar). How can I write you privately? $\endgroup$ – Joe Mar 27 '17 at 14:58
  • $\begingroup$ my un at gmail will work. $\endgroup$ – amdopt Mar 27 '17 at 15:04
2
$\begingroup$

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.

$\endgroup$
  • $\begingroup$ Look in the code you copied and find out WHERE that Dim statement is. It is somewhere in there. $\endgroup$ – noob2 Mar 27 '17 at 11:50
  • $\begingroup$ first of all thanks. Then, when you are a beginner it's difficult to get a good awareness of own competencies. If I have elementary VBA question, where can I go to ask? Can you suggest me a properly forum? Many thanks $\endgroup$ – Joe Mar 27 '17 at 12:03
  • $\begingroup$ You are welcome. I can sympathize, I am a noob also. $\endgroup$ – noob2 Mar 27 '17 at 12:23

Not the answer you're looking for? Browse other questions tagged or ask your own question.