Calcular el Valor Actual y el Valor Final de una renta de cuantía constante es sencillo con las funciones de Excel VA y VF. Calcular el valor actual o final de una renta en la que el tipo de interés varía ya no es tan obvio. Para calcular el Valor Actual tendremos que capitalizar cada cuantía hasta el final de la renta, aplicando el tipo de interés vigente en cada momento a medida que la cuantía atraviesa los diferentes periodos hasta llegar al final (t=n).
Hoja E11
Nos vamos a la Hoja E11 del fichero Rentas.xls.Vamos a calcular el VA y el VF de una renta de 60 términos mensuales pospagables valorada a diferentes tipos de interés cada uno de los 5 años de duración. La cuantía de los términos de la renta también son variables siendo las mensualidades del primer año de 1.000 €, las del segundo año 1.200 €, y así con incrementos de 200 € cada año, hasta llegar al quinto año donde la cuantía mensual es de 1.800 €.
Utilizaremos dos métodos. El método 1 trabaja en meses, por lo que necesitamos una tabla de 60 meses. El método 2 trabaja en años, debido a que durante cada año la cuantía mensual es constante y el tipo dentro del año no varía. Esto nos permite valorar la renta de cada año al final de ese año, y así con los valores anualizados podemos trabajar como si se tratara de una renta anual.
Método 1 (trabajando en meses)
Columna B: Mes
Rellenamos los meses de 0 a 60. Comenzamos en cero, pero al tratarse de una renta pospagable la primera cuantía vence al final del primer mes, que corresponde al instante 1.
Columna C: Año
Se calcula con fórmula el año. Así la celda C8 contiene la siguiente fórmula:
=SI(RESIDUO(B8;12)=1;C7+1;C7)
Observar que en Excel siempre que deseamos tratar valores periódicos es muy frecuente utilizar la función RESIDUO que calcula el resto. Si estuviéramos programando en VBA usaríamos MOD (módulo) que en español se denomina resto.
Otra forma de conseguir la columna de años sin necesidad de fórmulas muy elaboradas es la siguiente. Durante los primeros 12 meses escribimos un 1. En la celda que se corresponde con el primer mes del segundo año (celda C20) pondríamos esta fórmula: =C8+1. Finalmente copiaríamos esta fórmula hacia abajo.
Columna D: Tipo anual
En nuestro caso el tipo de interés varía cada año comenzando en el 5%, e incrementándose un punto cada año, hasta llegar al 9% en el quinto año. Estos datos se encuentran en una tabla a la derecha. Tabla que posteriormente completaremos para obtener el método 2 de resolución. Creamos un nombre de rango denominado tabla que es: K7:S11.
La fórmula utilizada en la celda D8, que luego copiaremos hacia abajo, es la siguiente.
=BUSCARV(C8;tabla;2;0)
Esto nos proporciona el tipo de interés efectivo anual.
Columna E: Tipo mensual
Tomamos el tipo mensual de la tabla de la derecha como se ve en la fórmula de la celda E8:
=BUSCARV(C8;tabla;3;0)
Columna F: Renta
La celda F8 nos proporciona el importe de los términos de la renta, y para ello consulta la tabla de la derecha con la siguiente fórmula.
=BUSCARV(C8;tabla;5;0)
Columna G: Factor
El factor en finanzas es igual a uno más el tanto. En este caso lo que hacemos es sumar 1 al tipo mensual de la columna anterior. En finanzas es muy típico trabajar con el famoso (1+i) que es el factor que elevado a exponente negativo y multiplicado por la cuantía, la capitaliza. Por el contrario, elevado a exponente negativo y multiplicado por la cuantía, lo que hace es descontarla, tantos periodos como indique el exponente.
La celda G8 es simplemente: =1+E8.
Columna H: VA
Calculamos el Valor Actual de cada término de la renta. Lo que hacemos es llevar financieramente hasta el origen de la renta (t=0) cada una de las cuantía que la componen. La peculiaridad en este caso es que el tanto es variable por lo que se ha de dividir (o multiplicar con exponente negativo) entre el producto de todos los factores que son necesarios para llegar hasta su valor actual.
En Excel existe la función PRODUCTO que multiplica todos los elementos que se indiquen en su rango. Así la celda H8 tiene la siguiente expresión:
=+F8/PRODUCTO($G$8:G8)
Esta fórmula se copia hacia abajo. Para analizar mejor la fórmula tomemos como ejemplo la de la celda H12:
=+F12/PRODUCTO($G$8:G12)
En esta fórmula se toma la cuantía de la celda F12 que son los 1.000 € que vencen en t=5 y se han de descontar 5 meses. Este es el motivo de que el argumento de la función PRODUCTO sea G8:G12.
Observar que en el argumento de la función PRODUCTO la celda G8 se fija con dólares. Esto es así para que al copiar la fórmula hacia abajo siempre se descuenten las cuantías de la renta hasta el momento inicial (t=0).
El Valor Actual de la renta se calcula en la celda P14 sumando todos los valores actuales individuales de todos los términos de la renta. Así P14 es:
=SUMA(H8:H67)
Columna I: VF
Para calcular el Valor Final de la renta capitalizamos cada cuantía hasta el final de la renta (t=60). Comencemos el razonamiento por el final. La celda I67 es simplemente una fórmula que se vincula con la cuantía que vence en ese momento (=+F67) sin necesidad de capitalizar, ya que la cuantía vence justo al final, en t=60.
La celda I66 tiene la siguiente fórmula.
=+F66*PRODUCTO(G67:$G$67)
En esta expresión lo que hacemos es capitalizar la cuantía hasta su valor final. Para ello utilizamos la función PRODUCTO y en su argumento debemos incluir el rango de los factores necesarios. Para la cuantía que vence en t=59, que está en la celda F66, se ha de multiplicar únicamente por un factor que es el que está en la celda G67. Pero como esta fórmula la copiaremos hacia arriba, hemos de poner el rango de factores como G67:$G$67 dentro de la función PRODUCTO. Esto hace que al fijar con dólares la celda G67 los factores implicados vayan siempre hasta el último.
Observar el desfase de filas en la fórmula. Cuando estoy capitalizando la celda F66 llego hasta el factor G67. Para entender esto, siempre es conveniente recordar que una cosa es un instante, por ejemplo el instante en el que vence una cuantía, y otra cosa es un intervalo de tiempo, por ejemplo el que va asociado a un factor mensual es todo un mes.
El Valor Final de la renta se calcula en la celda Q14 sumando todos los valores finales individuales de todos los términos de la renta. Así Q14 es:
=SUMA(I8:I67)
Método 2 (trabajando en años)
En este método podemos trabajar en años gracias a que la cuantía únicamente varía una ver al año, al igual que sucede con la variación del tipo de interés. Vamos a calcular el valor a final de cada año de los términos de la renta que vencen dentro de ese año. Así obtendremos unas cuantías anualizadas equivalentes financieramente a las 12 mensualidades de cada año. Con las anualidades calculadas podremos trabajar como si de una renta anual se tratara.
Las columnas K y L son datos.
Columna M: Tipo mensual
La celda M7 es:
=+(1+L7)^(1/12)-1
Lo que hacemos es considerar que el tipo anual es un efectivo anual y lo que nosotros pretendemos es calcular el efectivo mensual equivalente. Para ello se aplica la expresión: im=(1+i)^(1/m)-1.
Columna N: Factor Anual
Puesto que trabajaremos con la renta anualizada necesitaremos el factor anual que es (1+i).
Columna O: Renta
Los términos de la renta son datos.
Columna P: Renta Anual
La celda P7 tiene la siguiente expresión:
=+VF(M7;12;-O7)
Lo que hacemos es calcular el valor final con VF, ya que se trata de una renta de 12 términos mensuales constantes. El valor obtenido es el de la renta anualizada para el primer año. Luego lo que hacemos es copiar la fórmula hacia abajo y así obtenemos los 5 términos de la renta anualizada.
Columna Q: Renta Anual
La celda Q7 es:
=+P7/PRODUCTO($N$7:N7)
Aquí calculamos el valor actual del primer término de la renta anualizada. Ese término vence al final del primer año y para descontarlo utilizamos el factor anual, esto es, descontamos con el tanto efectivo anual. Esto fórmula se copia hacia abajo y así obtenemos el valor actual de cada una de las cuantías previamente anualizadas.
Columna R: Factor Capitaliz
En esta columna calculamos el Factor de Capitalización. La celda R7 es:
=+PRODUCTO($N8:N$11)
Esta fórmula se copia hacia abajo, con la excepción de la última celda. En la celda R11 debemos poner un 1 de forma manual, ya que si se copia la fórmula anterior hasta esta celda el proceso no funcionará bien. De hecho el motivo de la existencia de esta columna es precisamente este pequeño e importante detalle. Observar que en el caso del cálculo del VA no fue necesaria una columna calculando previamente el Factor de Descuento.
El Valor Actual de la renta se calcula en la celda P15 sumando todos los valores actuales individuales de todos los términos de la renta. Así P15 es:
=SUMA(Q7:Q11)
Columna S: VF
La celda S7 es:
=+R7*P7
Esta fórmula se copia hacia abajo y nos da el valor final de cada término de la renta anualizada.
El Valor Final de la renta se calcula en la celda Q15 sumando todos los valores finales individuales de todos los términos de la renta. Así Q15 es:
=SUMA(S7:S11)
Comprobación
La celda R14 es:
=+P14*PRODUCTO($N$7:$N$11)-Q14
Esta fórmula se copia a la R15 y si obtenemos cero indica que ambos método coinciden.
En la fórmula R14 lo que hacemos es tomar el valor actual calculado por el método 1 y capitalizarlo con el PRODUCTO de los factores anuales. Esto nos debería dar el valor final de la renta, por lo que al restar éste el resultado debe ser cero.
Puede realizar esta comprobación pero utilizando el producto de los factores mensuales. ¿Cuál es el resultado que debiera obtener con la siguiente expresión?
=+P14*PRODUCTO($G$8:$G$67)-Q14
Ejercicio propuesto
Proponemos al lector que realice este mismo caso pero suponiendo la renta prepagable, esto es, con 60 mensualidades pero venciendo la primera al inicio del primer mes (en t=0). Podrá comprobar que las ideas son las mismas pero hay que tener especial cuidado con los factores que se han de utilizar para descontar o capitalizar cada cuantía.
No hay comentarios:
Publicar un comentario