MODELO 4. CALCULO DEL VALOR PRESENTE NETO Y LA TASA INTERNA DE RETORNO PARA UN PROYECTO DE INVERSIÓN. PROPÓSITO: Diseñar un modelo en hoja de cálculo que permita calcular el Valor Presente Neto (VPN) de un proyecto de inversión. RESTRICCIONES: ? El modelo considerará proyectos hasta por 36 periodos. ? Los periodos a considerar podrán ser años, semestres, cuatrimestres, trimestres, bimestres y meses. ? Para todos los casos, la Tasa de interés de oportunidad (TIO), estará dada para el mismo período que se considera en el proyecto. Es decir, si los periodos del proyecto son trimestres, entonces una TIO del 9.8% se considerará trimestral. Esto con el ánimo de no incluir, dentro de este modelo, un módulo de conversión de tasas. ACTIVIDAD 1: Para iniciar el diseño, analice y responda las siguientes preguntas: DATOS DE SALIDA: según el propósito de este modelo, cuál o cuáles son sus datos de salida.

DATOS DE ENTRADA: Escriba a continuación cuáles son los datos de entrada del modelo (es decir aquellos que son necesarios para obtener el resultado pedido)

PROCESO: describa brevemente el proceso que usted supone debe realizarse para que con los datos de entrada se obtengan los datos de salida.

Modelos Financieros en Excel – Modelo III Miguel Angel Rojas – Pág. 1

ACTIVIDAD 2: Diseñe y desarrolle su modelo, considerando: Considerando los datos de entrada diseñe, la distribución de la pantalla de manera que sea fácil ingresar estos datos. El modelo debe permitir el ingreso de los flujos para cada uno de los periodos (máximo 36), según sea el caso. El número del periodo debe ser dinámico (similar al modelo de la tabla de amortización). Debe mostrarse una columna en la cual se calcule el Valor presente de cada flujo. Utilice la expresión:

VP ? VF (1 ? TIO ) ? n Donde VP: valor presente; VF: valor Futuro; n: número de periodos y TIO: Tasa de interés de oportunidad. En el cálculo del VPN() debe utilizarse la función Valor Neto Actual, VNA() (También podría hacerse de otras maneras pero optaremos esta para lograr nuestros propósitos). Usted debe crear una Macro que borre los flujos de un proyecto y asignarla aun botón. Mejore su modelo añadiendo una hoja de presentación y otra de ayuda (en esta explique el funcionamiento del modelo y la teoría financiera que se aplica) Automatice su modelo, utilizando botones que mediante Macros permitan navegar por todo él. Ejecute todas las actividades de “maquillaje” y seguridad. Pruebe exhaustivamente su modelo.

Modelos Financieros en Excel – Modelo III Miguel Angel Rojas – Pág. 2

ACTIVIDAD 3: OPTIMIZANDO EL MODELO Considere las siguientes situaciones: 1- Las celdas de entrada no pueden ser identificadas con color “verde vivo” pues podría suceder que algunas celdas de este color no sean de entrada (es decir el color de la celda no lo podemos controlar) Entonces, en la columna siguiente a la de los flujos de los periodos Usted debería generar un aviso para el usuario indicándole si debe “digitar el valor del flujo en tal periodo” (cuando el periodo efectivamente pertenece al proyecto y no ha escrito el valor del flujo) o si ha cometido el error de escribir un valor de flujo en un periodo fuera del rango del proyecto (cuando escribe un valor para el flujo en un periodo que no pertenece al proyecto)

SUGERENCIAS: Debe utilizar las funciones SI(), Y(), CONCATENAR() 2- El cálculo de la función VNA() considera: la tasa de interés (TIO) y el rango en el cual se encuentran los flujos para cada periodo, excepto el flujo del periodo cero. Este rango cambia para cada caso, es decir si escogemos un proyecto de 5 periodos el rango de los flujos es diferente a si el proyecto es de 6 (u otros) periodos. Por esta razón este rango de flujos debe ser dinámico de manera que se excluyan de él los valores BLANCO de los periodos que no pertenecen al proyecto.

Modelos Financieros en Excel – Modelo III Miguel Angel Rojas – Pág. 3

El cálculo del valor presente neto para un proyecto de 6 periodos se obtendrá calculando el VNA() para los periodos 1, 2, 3, 4, 5 y 6 y sumándole a este resultado el valor del flujo del periodo CERO. Considere los siguientes ejemplos: Un proyecto a 6 periodos, con TIO del 9%, con los flujos de caja mostrados para cada uno de los seis periodos. El VPN es: 183.81. La función VNA() considera el rango D9:D14.

Otro proyecto: a 3 periodos, con TIO del 4% y los flujos abajo mostrados. El VPN es –170.93. La función VNA() considera el rango D9:D11.

La función DESREF() de Ms-Excel permite definir rangos dinámicos, a partir de una celda de referencia. Modelos Financieros en Excel – Modelo III Miguel Angel Rojas – Pág. 4

Antes de iniciar su estudio, hablemos de direcciones referenciadas. Para referenciar un rango debemos establecer con respecto a cual celda se referencia, luego indicar el número de filas y columnas que se deberán considerar para llegar al comienzo del rango y por último el alto (filas) y el ancho (columnas) que tiene el rango. Es algo análogo a cuando dentro de una ciudad damos una dirección por señas: “Usted debe encuestar 8 manzanas y para llegar a ellas, desde el parque central (referencia) baje 5 cuadras (filas) y luego a la derecha 3 cuadras (columnas), a partir de ahí encueste 4 manzanas hacia abajo (alto) y dos manzanas a la derecha (ancho)” La función DESREF() requiere de los siguientes parámetros: Referencia, No. de filas, No. de columnas, ancho y alto del rango.

Cada uno de estos parámetros pueden ser variables, almacenadas en una celda. En nuestro caso, el rango tendrá un alto definido por el número de periodos del proyecto, el ancho será siempre de una columna (que contiene los flujos), la celda referencia será la celda donde se almacena el flujo del periodo CERO, el número de filas a “bajar” será cero, igual que el número de columnas a avanzar a la derecha.

Modelos Financieros en Excel – Modelo III Miguel Angel Rojas – Pág. 5

Para definir un rango dinámico con un nombre Usted debe escoger la opción definir..., de Nombre, en el menú de Insertar de la barra de menú. (No interesa en qué celda se encuentre ubicado).

En la ventana “definir nombres” defina el nombre flujos, e indicando que se refiere a : =DESREF($D$8;1;0;$D$4;1) Y luego ACEPTAR. De esta manera se ha definido un rango con el nombre flujos, que empieza una fila debajo ( CERO COLUMNAS) de donde se encuentra el flujo del periodo CERO y que tiene tantas filas de alto como lo indique la celda D4 (número de periodos), con un ancho de una columna. Es importante fijar las direcciones indicadas. Modelos Financieros en Excel – Modelo III Miguel Angel Rojas – Pág. 6

ACTIVIDAD 4: REDEFINIENDO EL CALCULO DEL VPN(). Ahora USTED podrá recalcular el valor del Valor presente Neto utilizando la función VNA(). En la celda D5, del siguiente gráfico, se encuentra la expresión: =VNA(flujos)+D8

Analícela!!!!. ACTIVIDAD 5: TERMINANDO EL MODELO. Para concluir el modelo, pruébelo exhaustivamente, protéjalo, esconda la información, las filas, las columnas y las hojas no necesarias. APLICACIÓN Y EVALUACIÓN. Practique el manejo de la función DESREF() mediante la siguiente extensión del modelo. 1- Definir un nuevo rango dinámico llamado complemento, el cual debe iniciar una celda debajo de aquella en la cual se encuentra el flujo del último periodo del proyecto y va hasta la celda que corresponde al flujo del periodo 36. 2- Diseñe y cree una MACRO, asociada a un botón, mediante la cual el usuario puede borrar todos los flujos no necesarios en el proyecto. 3- Complemente el modelo con el cálculo de la tasa interna de rentabilidad (TIR) del proyecto. Modelos Financieros en Excel – Modelo III Miguel Angel Rojas – Pág. 7

4- Opcionalmente, cómo lograría que las celdas de entrada del modelo se pudieran identificar mediante el color “verde vivo” dependiendo del número de periodos que tenga el proyecto. 5- Grabe su proyecto terminado y entréguelo a su profesor, debidamente marcado en la fecha que sea convenida.

OBSERVACIONES: Antes de iniciar, repase los conceptos relacionados con Valor presente, valor futuro. Consulte sobre la funciones lógicas Y(), O(),sobre la función VNA() y sobre la función DESREF()

Modelos Financieros en Excel – Modelo III Miguel Angel Rojas – Pág. 8