Árbol binomial generado con una macro

Puede descargar el archivo arbolBinomial.xlsm

Vamos a crear un árbol binomial usando una macro de Excel, con un poco de código VBA. Lo interesante del caso es que al variar el número de periodos n el árbol se recalcula y se redimensiona en tamaño.

Partimos de un precio de una acción de S=100 €. Este precio puede subir o bajar en cada periodo. Suponemos que si sube lo hará con incrementos del 25% (u=1,25) y si baja lo hace con disminución del 20% (d=0,80).

Se cumple que d=1/u

1 / 1,25 = 0,80

n=1

El árbol para un periodo sería el siguiente.

Donde

125 = 100 * 1,25

80 = 100 * 0,80

Partiendo del precio inicial de 100, transcurrido un periodo pueden suceder dos cosas, o bien, el precio se incrementa y 25% pasando a ser 125 €, o bien se reduce un 20% pasando a ser 80 €.

n=2

Si hacemos el árbol para dos periodos.

Donde

156,25 = 125 * 1,25

100 = 125 * 0,80  o bien  100 = 80 * 1,25

64 = 80 * 0,80

Para el periodo 2, el valor mayor  (156,25) se obtiene incrementando un 25% más el precio superior del periodo anterior. También se puede ver como que 156.25 = 100 * 1,25 * 1,25, ya que se parte de un precio inicial de 100 y se experimentan dos incrementos del 25%.

El valor de 100 € del periodo 2 se alcanza por uno de los dos siguientes caminos.

• 100 = 100*1,25*0,80 Partimos de 100, subimos a 125 y luego volvemos a bajar a 100.
• 100 = 100 *0.80*1,25. Partimos de 100, bajamos a 80 y luego volvemos a subir a 100.

El valor de 64 se puede entender que se alcanza partiendo del precio inicial de 100 y experimentando dos reducciones consecutivas del 20%. 64 = 100 * 0,80 * 0,80

n=3

Si hacemos el árbol para tres periodos.

Donde

195,3125 = 100 * 1,25³

125 = 100 * 1,25² * 0,80

80 =  100 * 1,25 * 0,80²

51,2 = 100 * 0,80³

Para n>3

La macro funciona hasta n=40, y no por la limitación de la propia macro sino porque hemos limitado hasta 40 el valor que se puede poner en la celda amarilla (C7) usando el control numérico que está a su lado.

Option Explicit
Public n As Integer 'número de etapas del árbol, hasta 40
Public A() As Double 'matriz que contiene el árbol

Sub arbol()
Dim i As Integer, j As Integer
Dim u As Double, d As Double
Worksheets("Hoja1").Activate
n = [C7]: u = [C4]: d = [C5]
Call borra
Call cabeceras
ReDim A(2 * n, n) 'el árbol tiene el doble de filas que de columnas
A(n, 0) = 100 'valor inicial en la columna cero
For j = 1 To n  'columnas de la matriz
  For i = 0 To 2 * n 'filas
    If j = n - i Then 'primero calculamos la diagonal superior
      A(n - j, j) = A(n - j + 1, j - 1) * u
    ElseIf j >= n - i + 2 And j <= i + n Then 'calculamos el resto
      A(i, j) = A(i - 1, j - 1) * d
    End If
  Next i
Next j
Call imprimeA
End Sub
Sub borra()
Range("C10:AR91").Clear ' borra hasta n=40
End Sub
Sub cabeceras()
Dim i As Integer
Range("A1").Copy ' copiamos A1 para luego pegar el formato
'pegamos el formato de A1 a la columna C
Range(Cells(10, "C"), Cells(2 * n + 11, "C")).PasteSpecial Paste:=xlPasteFormats
'pegamos el formato de A1 a la fila 10
Range(Cells(10, 4), Cells(10, n + 4)).PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False 'elimina la fila de hormigas
For i = 0 To n
  'generamos los números 0 a 2n de la columna C
  Cells(i + 11, 3) = i 'vertical, los n primeros
  Cells(i + n + 11, 3) = i + n 'vertical, los n últimos
  'generamos los números 0 a n de la fila 10
  Cells(10, i + 4) = i 'horizontal
Next i
Range("C10").Activate 'situamos el cursor en C10
End Sub
Sub imprimeA()
Dim i As Integer, j As Integer
For j = 0 To n
  For i = 0 To 2 * n 'recorremos toda la matriz A
    If A(i, j) <> 0 Then 'imprimimos solo los no vacios
      Cells(i + 11, j + 4) = A(i, j)
    End If
  Next i
Next j
End Sub

La idea básica para hacer el árbol es que la diagonal superior se obtiene como el precio anterior por u. Y el resto de los valores del árbol se obtienen como el precio superior del periodo anterior por d.

Ejemplo para n=4.

244,1 se obtiene como 195,3 * 1,25
El resto de valores se obtienen multiplicando por 0,80, así tenemos los siguientes.
156,3 = 195,6 * 0,80
100 = 125 * 0,80
64 = 80 * 0,80
40,96 = 51,2  * 0,80

Este es el método que usa la macro para obtener todos los valores del árbol binomial.

Lo que más esfuerzo ha costado es calcular bien las celdas donde se han de escribir los valores del árbol ya que al crecer n el árbol va aumentando de tamaño y es necesario ir bajando la celda inicial.