martes, 22 de octubre de 2019

Creación del Libro Personal en el Excel de un Mac

Previamente hemos conseguido la pestaña Programador siguiendo los pasos de este post:

Vamos a crear una macro con grabadora. La macro será enormemente sencilla, simplemente situaremos el cursor en alguna celda y con ello finalizaremos la grabación de la macro. Lo interesante no será la macro que hemos creado en sí misma, sino que hemos dicho que se grabe en el libro Personal de forma que si previamente no estaba creado en este ordenador conseguiremos que se cree.

Los pasos a seguir son los siguientes.

Paso 1

Ir a la ficha Programador


Paso 2

Crear macro

Paso 3

No cambiaremos el nombre de la macro que vamos a crear. El nombre Macro1 nos vale perfectamente.
Lo que sí haremos es elegir que se grabe en el "Libro de macros personal". Esto se hace así para que obliguemos a Excel a que cree el libro en nuestro ordenador por si previamente no existía.
Aceptar.

Paso 4

Comprobemos que se ha creado el Libro de macros Personal. Para ello en la pestaña Programador elegimos Visual Basic, que es el icono que hay a la izquierda.
Al hacer esto veremos el Editor de Visual Basic que es el de la siguiente imagen.

Paso 5

A la izquierda podremos ver que pone VBAproject(PERSONAL.XLSB) eso quiere decir que se ha creado bien el libro personal.
Pulsaremos sobre el signo + que aparece a la izquierda del libro personal para que se abra ese desplegable.
También pulsaremos sobre el signo +  que aparece a la izquierda de la palabra Módulos, y así podremos ver el Módulo1 que aparece en la imagen.



Paso 6

Hacemos doble click sobre el Módulo1. No basta con hacer un solo click, debe ser doble click. De esta forma llegaremos a ver las macros que contiene este módulo. En la imagen siguiente únicamente se muestra una macro que contiene este Módulo1. Se trata de la macro que se creó con la grabadora de macros.

Paso 7

La macro que se creó con la grabadora de macros denominada Macro1 no nos sirve para nada ya que únicamente se creó en el libro personal para forzar a Excel a que creara el libro Personal. Ahora, si así lo queremos podemos borrar la macro. Como es texto, simplemente la seleccionamos y la borramos con la tecla suprimir del teclado.

Paso 8

Es ese Módulo1 escribiremos nuestro código VBA (Visual Basic for Applications) que en este caso en lugar de ser una macro será unas funciones que luego podremos usar.
En nuestro caso crearemos dos funciones una para calcular el valor actual de una renta geométrica y otra para calcular el valor final de este tipo de rentas. El código es el siguiente.


Function VAgeo(C, q, n, i)
If q = 1 + i Then
    VAgeo = C * n / (1 + i)
Else
    VAgeo = C * (1 - (q / (1 + i)) ^ n) / (1 + i - q)
End If
End Function

Function VFgeo(C, q, n, i)
VFgeo = VAgeo(C, q, n, i) * (1 + i) ^ n
End Function


Este código se ha de incluir en nuestro módulo quedando así.

Paso 9

Grabamos el código que hemos creado pulsando arriba el icono que representa un disquete.

Paso 10

Comprobemos que todo funciona bien. En la hoja de cálculo en una celda escribimos:

=personal.xlsb!vageo(1000;1,1;8;5%)

Con ello estamos pidiendo a Excel que calcule el valor actual de una renta geométrica pospagable de 8 términos anuales, donde el primero de ellos es de 1.000 €, y el resto se incrementan anualmente un 10%, por lo que la razón de la renta geométrica es 1,1 y donde todo ello se valora al 5% anual.

Los datos son:

  • C=1000
  • q=1,1
  • n=8
  • i=0,05


Paso 11

Si todo va bien el resultado obtenido, usando los datos anteriores, es 9017,305652.

Paso 12

Comprobación. Veamos qué resultado se obtiene al calcular el valor actual de esta renta utilizando el VAN con la función de Excel VNA. La fórmula sería

=VNA(5%;D3:D10)

En la imagen se puede ver que el resultado obtenido es de 9.017,31 € que redondeado a dos decimales y con formato de euros, coincide con el anteriormente obtenido con el VAgeo programado.


Notas


  • Los pasos iniciales se deben realizar solo una vez para conseguir crear el libro PERSONAL.XLSB en nuestro ordenador. Posteriormente, si deseamos incluir más fórmulas en el libro Personal simplemente las iremos añadiendo como texto unas debajo de otras en el Módulo1.
  • El libro Personal tiene la ventaja de que todas las fórmulas y macros que incluyamos en él estarán disponibles para los nuevos libros de Excel que creemos en el futuro. Esto supone que, en ese ordenador cualquier libro nuevo que se cree permitirá utilizar las funciones programadas, y evitaremos tener que estar incluyendo estas macros en todos los libros que las necesiten.
  • En principio las macros y funciones creadas en el libro Personal estarán disponibles únicamente para los libros nuevos que se creen en el futuro. Pero para los libros antiguos, los que ya teníamos grabados antes de crear el libro Personal es posible que no tengamos operativas las fórmulas programadas, del tipo VAgeo. Esto lo podemos comprobar y si realmente no nos funciona VAgeo en los libros antiguos existen dos soluciones. La primera de ellas es que si en el libro antiguo ya teníamos creada la macro asociada a ese libro, ésta seguirá funcionado y no necesitaremos hacer nada. La segunda solución, en caso de que no tengamos las funciones ya programadas en el libro antiguo consiste en hacer un duplicado del libro antiguo y grabarlo con otro nombre. De esta forma al grabar el duplicado con un nuevo nombre ya se tratará de un libro nuevo donde funcionará perfectamente toda fórmula que tengamos creada en el libro Personal.
  • No olvidemos que al grabar los libros que usan macros deben ser grabados con la extensión .xlsm en lugar de la habitual para hojas de cálculo sin macros que es .xlsx. La extensión en ocasiones no está visible en nuestro ordenador en cuyo caso nos tenemos que fijar en que al grabar ponga "Libro de Excel habilitado para macros".


viernes, 11 de octubre de 2019

Obtener la ficha Programador en el Excel de un Mac

Queremos instalar o habilitar la pestaña o ficha PROGRAMADOR en el Excel de nuestro Mac. Existen algunas diferencias entre el Excel de Windows y el Excel que utilizamos en Apple.

Para obtener la ficha Programador en el Excel de MacOS sigue estos pasos:

Paso 1

Excel / Preferencias






Paso 2


Barra de herramientas y cinta de opciones.




Paso 3


Marcar la casilla correspondiente a Programador que estará inicialmente desmarcada.



Paso 4

Comprobar que ahora ya dispondremos de la pestaña Programador.



Versión utilizada

Las imágenes anteriores corresponden a la versión 16.29.1 del Microsoft Office a fecha octubre 2019.


jueves, 2 de mayo de 2019

VAN a tipo variable

Puede descargar el archivo VanTir.xlsm

El Valor Actual Neto (VAN) habitualmente se calcula utilizando un tipo de interés fijo. En esta ocasión vamos a calcular el VAN utilizando una tasa de descuento variable. Lo haremos montando en Excel una tabla con los factores de descuento y usando la función SUMAPRODUCTO para calcular el valor actual. También afrontaremos la resolución utilizando una función programada por el usuario denominada VANvariable. Veamos los dos métodos.

Método 1 (resolución manual)

Como datos manejamos la columna C con los flujos de caja y la columna D con la tasa de descuento k. Se ha de cumplir que si los flujos de caja comienzan en t=0 con el desembolso inicial y terminan en t=n con el último flujo de caja, podamos disponer de n tasas de descuento, uno por cada periodo, salvo el instante t=0 donde no se requiere ninguna tasa de descuento. 


La columna D contiene la tasa de descuento k.



La función SUMAPRODUCTO multiplica por parejas los flujos de caja por el factor de descuento.

Método 2 (programando una función)

La función programada VANvariable no incluye el desembolso inicial que se ha de sumar fuera de la función. En nuestro ejemplo sumamos los -4.000 € que al ser negativo minoran el valor obtenido.


Veamos el código.

Function VANvariable(flujos As Range, tasas As Range) As Double
'tasas es el rango donde se encuentran las tasas de descuento
'Los flujos de caja son todos menos el desembolso inicial
Dim n As Long
Dim fDto As Double 'factor de capitalización
Dim VA As Double 'Valor Actual
'n = nº de celdas que contienen los flujos de caja, NO incluido el desembolso inicial
n = flujos.Rows.Count
VA = 0 'inicializamos el VA a cero
If n = tasas.Rows.Count Then
  fDto = 1 'inicialmente el factor de descuento es 1
  For i = 1 To n
    fDto = fDto / (1 + tasas(i)) 'el factor de descuento es acumulativo
    VA = VA + flujos(i) * fDto
  Next i
Else
  'mensaje que sale si el nº de filas de los rangos tasas y flujos no coincide
  VANvariable = "rangos no coinciden"
End If
VANvariable = VA
End Function

martes, 16 de abril de 2019

TIR modificada a tipo variable

Puede descargar el archivo VanTir.xlsm

Disponemos de una inversión a 20 años con flujos de caja unos positivos y otros negativos. En este caso si calculamos la TIR obtenemos un caso de TIR múltiple con tres puntos de corte en el eje de abcisas. Vea la hoja denominada 'TIR MULTIPLE'.



Podemos resolver la incoherencia que haber obtenido tres valores para la TIR utilizando la denominada TIR modificada o corregida. Consiste en descontar los flujos de caja negativos hasta el instante t=0 a cierta tasa de descuento y capitalizar los flujos de caja positivos hasta su valor final en t=n a cierta tasa de capitalización también conocida. De esta forma habremos conseguido tener únicamente dos capitales, uno inicial Co y otro final Cn. Para calcular la TIR modificada únicamente tendremos que aplicar la ley de la capitalización compuesta a esos capitales durane el número de peridos n y despejar el tipo de interés i necesario para que la ecuación se cumpla.

Esto lo podemos ver en la hoja denominada 'TIRM'.



Si deseamos calcular la TIR modificada o corregida a tipo variable tanto para la tasa de descuento como para la de capitalización seguiremos los siguientes pasos que se pueden ver en la hoja denominada 'TIRM variable'.


Pasos a seguir.
  1. En las columnas D y E separamos los flujos de caja positivos y negativos
  2. En las columnas F y G escribimos la tasa de descuento k1 y la tasa de capitalización k2 que son datos que debemos obtener.
  3. En las columnas H e I calculamos los factores 1/(1+k1) para el descuento de un periodo y (1+k2) para la capitalización de un periodo.
  4. En las columnas J y K calculamos el factor de descuento y el factor de capitalización para multiples periodos. Por ejemplo, para la cuantía que vence el sexto año, en t=6, que es de -200 € tendremos que descontarla multiplicando por el producto siguiente  =PRODUCTO($H$7:H13)
  5. Calculamos la TIRM variable en la celda K2 con la fórmula TASA siguiente   =TASA(20;;SUMAPRODUCTO(D7:D27;J7:J27);SUMAPRODUCTO(E7:E27;K7:K27))

Programación de una función

También disponemos de una función programada en código de Excel VBA que nos permite calcular la TIR modificada  usando tipos de interés variables.

Podemos verla trabajando en la celda K3.

=TIRMvariable(C7:C27;F8:F27;G8:G27)


Los argumentos son los siguientes.

=TIRMvariable(flujos de caja;rango de tasas de descuento;rango de tasas de capitalización)

Los flujos de caja son un rango que incluye el desembolso inicial. En nuestro ejemplo, es el rango C7:C27 que está compuesto por 21 celdas. La macros de la fórmula usa la variable n para calcular el número de esas celdas. En este ejemplo n será igual a 21.

n=21

Los rangos k1 y k2 son los que contienen las tasas de descuento y de capitalización respectivamente y en el ejemplo son los siguientes.


  • k1 → Rango F8:F27 → número de celdas = 20
  • k2 → Rango G8:G27 → número de celdas = 20

Observe que el número de tasas de capitalización o descuento son 20 cuando n hemos visto que es 21. Por tanto, siempre se ha de cumplir que el número de tasas de descuento o capitalización han de ser iguales a n-1.

Existe un caso de error, cuando al  indicar los argumentos de la función no se cumple que las filas del rango de k1 han de ser las mismas que las filas del rango k2 y ambas han de ser iguales al número de filas del rango que indica los flujos de caja menos uno. Cuando se produce el error la función responde con el texto "rangos no coinciden".

El código de la macro que contiene la función es la siguiente.

Function TIRMvariable(flujos As Range, k1 As Range, k2 As Range)
'k1 es el rango donde se encuentran las tasas de descuento
'k2 es el rango donde se encuentran las tasas de capitalización
Dim n As Long
Dim fDto As Double 'factor de capitalización
Dim fCap As Double 'factor de descuento
Dim VA As Double 'Valor Actual
Dim VF As Double 'Valor Final
'n = nº de celdas que contienen los flujos de caja incluido el desembolso inicial
n = flujos.Rows.Count
VA = flujos(1) 'inicializamos el VA con el desembolso inicial
VF = 0 'inicializamos el VF a cero
'pedimos que el rango de los flujos de caja tenga una celda más que
'los rangos de las tasas de descuento y capitalización
If n = k1.Rows.Count + 1 And n = k2.Rows.Count + 1 Then
    fDto = 1 'inicialmente el factor de descuento es 1
    For i = 2 To n 'comienza en 2 porque i=1 ya se recogió con el desembolso inicial
        fDto = fDto / (1 + k1(i - 1)) 'el factor de descuento es acumulativo
        'Si el flujo de caja es negativo se calcula el VA acumulando
        If flujos(i) < 0 Then VA = VA + flujos(i) * fDto
    Next i
    'Al finalizar del bucle anterior ya tenemos calculado el VA de los flujos negativos
    fCap = 1 'inicialmente el factor de capitalización es 1. Se comienza por el final
    For j = n To 1 Step -1 'Los calculos comienzan por el final
        fCap = fCap * (1 + k2(j)) 'el factor de capitalización es acumulativo
        'Si el flujo de caja es positivo se calcula el VF acumulando
        If flujos(j) > 0 Then VF = VF + flujos(j) * fCap
    Next j
    'Al finalizar del bucle anterior ya tenemos calculado el VF de los flujos positivos
    'Llamamos a la función TASA que en inglés es RATE
    TIRMvariable = WorksheetFunction.Rate(n - 1, 0, VA, VF)
    'Nper es n-1 ya que los flujos de caja comienzan en 0 con el desembolso inicial
Else
   'mensaje que sale si los rangos de k1 y k2 no son de longitud n-1
    TIRMvariable = "rangos no coinciden"
End If
End Function

También disponemos de una macro de prueba que lanza la instrucción Debug.Print.

Sub prueba()
Debug.Print TIRMvariable(Range("C7:C27"), Range("F8:F27"), Range("G8:G27"))
End Sub

Esto permite ejecutar la función que mostrará en la ventana Inmediato el resultado de la TIR.

lunes, 15 de abril de 2019

Valor Actual en Excel y en Python

Puede descargar el archivo ValorActual.xlsx

Veamos cómo calcular el valor actual de un capital y de una renta en varios casos.
  1. un capital
  2. una renta pospagable
  3. una renta pospagable con un valor final adicional
  4. una renta prepagable con un valor final adicional
Se calculará usando los recursos siguientes.
  1. La función VA de Excel
  2. La función VNA de Excel que calcula el VAN
  3. Programando en Python usando la librería NumPy
  4. Programando en Python una función def


El código en Python se encuentra en los siguientes enlaces.

lunes, 11 de febrero de 2019

La diversificación reduce el riesgo financiero

Puede descargar el archivo de Excel: reaseguro.xlsx

En finanzas representamos el riesgo por la varianza σ2 o su raíz cuadrada, la desviación estándar σ.

Supongamos dos carteras A y B que proporcionan una cierta rentabilidad en términos esperados del 10% cada una de ellas, y donde ambas tienen el mismo riesgo expresado por una desviación estándar de 0,4 o lo que es lo mismo expresado en porcentaje, del 40%. Vamos a simular mediante una distribución normal inversa las posibles rentabilidades. En principio vamos a generar 5.000 valores para cada cartera. Lo ideal sería generar muchos más si lo soportan la memoria RAM y la capacidad de cálculo de nuestro computador.


La celda B5 tiene la siguiente fórmula que usa la distribución normal inversa.

=INV.NORM(ALEATORIO();$H$7;$H$9)

Suponemos que las rentabilidades de las carteras A y B no están correlacionadas por pertenecer a activos y mercados muy diferentes. Observe que en la celda H11 se obtiene una correlación cercana a cero, ya que la función usada en Excel para generar las rentabilidades esta arrojando valores aleatorios que siguen una distribución normal, siendo la campana de Gauss de la cartera A y B independientes entre si.

Vamos a realizar un intercambio de carteras de forma que la cartera A pasará a convertirse en la cartera A', y la cartera B se convertirá en la cartera B'.

Tanto la cartera A' como B' se forman realizando una mezcla al 50% de las carteras anteriores A y B. De esta forma, la cartera A' estará formada por un 50% de sus propios activos A, más otro 50% de los activos de la cartera B. Sucediendo lo mismo en la formación de la cartera B' que también estará formada por 0,5A+0,5B.

Veamos que la esperanza matemática o media de rentabilidades esperadas μ se comporta de forma lineal ya que la media es una magnitud lineal, proporcional. De esta forma podemos expresar la esperanza de las nuevas carteras A' y B' con las siguientes ecuaciones.

El valor 1/2 hace referencia al 50%, que en tanto por uno es 0,5, ya que estamos suponiendo que las nuevas carteras A' y B' participan en un 50% de las viejas carteras A y B.

Otra forma de interpretarlo sería suponer carteras de dos compañías de seguros con la misma esperanza de rentabilidad (10%), la misma desviación estándar (40%) y totalmente incorrelacionadas. Estas compañías de seguros deciden efectuar un reaseguro mutuo intercambiando el 50% de sus carteras con la otra compañía. De forma que las nuevas carteras que se alcanzan son A' y B'.

Veamos ahora que el riesgo expresado por la desviación estándar no se comporta de forma lineal ya que la varianza es una medida cuadrática. Justo esta propiedad es la que justifica que el reaseguro o la diversificación hacen que el riesgo disminuya.

En el caso de la varianza:


Supongamos que hacemos que coincidan las dos desviaciones. En nuestro caso ambas σ serían 0,4.


De aquí, operando, se siguen estas expresiones:



Esta es la justificación formal de que es posible reducir el riesgo de una cartera mediante la diversificación o mediante el reaseguro con otra cartera con la que mantenga una reducida correlación.

Veamos el ejemplo numérico que hemos creado en Excel donde se obtiene precisamente que el nuevo riesgo de la cartera diversificada es igual al anterior dividido entre raíz de dos.