Cálculo de la rentabilidad de un título en Excel

Cálculo de la rentabilidad de un título en Excel

4512

La rentabilidad es una medida fundamental cuando estamos realizando un análisis sobre cualquier inversión financiera. Existen diferentes medidas para calcular la rentabilidad de un título y que vamos a exponer a continuación.

Lo primero que haremos será definir algunas variables y posteriormente aplicar los cálculos en Excel con un pequeño ejemplo. Aquí os dejo el archivo de ejemplo para calcular la rentabilidad de un título en Excel.

 calcular rentabilidad en excel

Para calcular la rentabilidad de un título, debemos conocer su precio (normalmente de cierre) Ct para la fecha t, el precio del título en el período anterior Ct-1 y además, cualquier tipo de renta que nos haya proporcionado dicho título en ese lapso de tiempo que denotaremos por Dt (dividendos, por ejemplo). Ya tenemos las variables definidas, ahora veremos cuáles son las fórmulas de su cálculo.

Diferenciaremos entre rentabilidad simple, rentabilidad compuesta y rentabilidad continua (también se le puede conocer por instantánea o logarítmica). Se usará un método u otro en función a la equivalencia financiera derivada de la capitalización continua, compuesta o simple.

Para realizar el cálculo de la rentabilidad a través de sus fórmulas, partiremos de las ecuaciones de equivalencia financiera de capitalización:

Capitalización simple

Cn=C0(1+nr)

Donde n es el número de períodos.

Capitalización compuesta

Cn=C0(1+i)n

Capitalización continúa

Cn=C0enk

A continuación obtendremos las fórmulas de las rentabilidades.

Rentabilidad simple

Ct+Dt=Ct-1(1+rt)

Rt=Ct-Ct-1+Dt/Ct-1

Rentabilidad Compuesta

Ct+Dt=Ct-1(1+it)n

Si n=1 tendremos la misma ecuación que para la rentabilidad simple

it=((Ct+Dt/Ct-1)1/n-1

Rentabilidad continúa

Ct+Dt=Ct-1ekt

Kt=ln(Ct+Dt/Ct-1)

De aquí podemos extraer que kt=ln(1+it)n

Cuando calculamos las rentabilidades de varios períodos podremos calcular su rentabilidad media, dividiendo por el número de datos que tengamos.

Calcular rentabilidad de un título en Excel

Una vez visto la parte matemática podremos aplicar esto en Excel. Vamos a explicarlo con el ejemplo que hemos dicho al principio de la entrada.

Introduciremos una columna con los días y otra con los datos de los precios.

Vamos a coger los datos de los precios de cierre diarios de cualquier acción. Lo primero que haremos será crear una columna para calcular la rentabilidad simple. Nos situamos en la celda C4 (en la C3 no podremos porque no tenemos período Ct-1 en la muestra seleccionada). A continuación aplicaremos la fórmula que hemos visto matemáticamente, e introduciremos lo siguiente:

  • =(B4/B3)-1

Como vemos no hemos introducido los dividendos porque no los hay en ese período, si los hubiera habría que tenerlos en cuenta o sino recurriremos a los precios ajustados por dividendo.

Una vez calculada la primera celda podremos arrastrar para abajo y se irán completando todas las celdas.

Nos vamos a la columna D y calculamos la rentabilidad instantánea. Nos situamos en D4 y aplicamos la fórmula.

  • =Ln(B4/B3)

Arrastramos y calcularemos las rentabilidades continuas de los días siguientes.

Por último calcularemos la rentabilidad compuesta. Vamos a la celda E4 y ponemos:

  • =(B4/$B$3)^(1/(A4-$A$3))-1

Esta fórmula lo que hace es comparar el Ct del período en el que estemos con el primer precio de cierre, por eso ponemos los dólares, para fijar dicha cantidad. También pondremos dólares en el período.

No calcula la rentabilidad con respecto al dato anterior como las otras rentabilidades que hemos calculado. Calcula la rentabilidad desde el inicio hasta el período en el que estemos. Si queremos calcular la rentabilidad compuesta de todo el período, será igual al último dato (color rojo).

calcular rentabilidad media en excel

Con estas rentabilidades que hemos calculado podremos calcular la rentabilidad media diaria de la muestra. Tanto para la rentabilidad simple como para la instantánea, usaremos la fórmula PROMEDIO en Excel, por ejemplo:

  • =Promedio(C4:C68)

Para la rentabilidad compuesta, no tendremos que hacer nada, la rentabilidad media diaria será igual al último dato. Es decir que desde el primer período tuvo que crecer a esa tasa cada día para llegar al valor del último dato.

Rentabilidades anualizadas

Lo habitual es que nos interese conocer las rentabilidades anualizadas. Hay que señalar que las sesiones de cotización diarias a lo largo del año son 252. Si tenemos la rentabilidad media diaria, multiplicaremos ésta por 252. Si tenemos las semanales multiplicaremos por 52 y si son mensuales por 12. Esto solo es así con las rentabilidades simples e instantáneas.

Para la rentabilidad compuesta tendremos que ver el horizonte temporal de los datos (si son diarios, semanales, mensuales, trimestrales, etc.). La fórmula es la siguiente:

  • =((1+i)^n)-1

Donde i es la rentabilidad media del período y n es el número de períodos en el que hemos subdividido el año ( si tenemos datos diarios habremos subdividido el año en días, si tenemos datos trimestrales habremos subdividido el año en trimestres. En días tendríamos 252 períodos que son los días de cotización anuales, en trimestres tendríamos 4 trimestres al año.)

Por ejemplo:

Con datos diarios=> =((1+i)^252)-1

Con datos trimestrales=> =((1+i)^4)-1

Con datos mensuales=> =((1+i)^12)-1