MODELAMIENTO FINANCIERO EN EXCEL

1. INTRODUCCIÓN A MICROSOFT EXCEL
1.1 Qué es Microsoft Excel ........................................................................................................................ 25
1.2 Qué son las funciones ......................................................................................................................... 26
1.2.1 Asistente para funciones ............................................................................................................ 26
1.2.2 Errores frecuentes ...................................................................................................................... 27
1.3 Confi guración de argumentos ........................................................................................................... 29
1.4 Referencias relativas, absolutas y mixtas ...................................................................................... 29
1.4.1 Referencia relativa ...................................................................................................................... 29
1.4.2 Referencia absoluta .................................................................................................................... 30
1.4.3 Referencia mixta ......................................................................................................................... 31
1.5 Instalación de componentes .............................................................................................................. 32
1.6 Programación de funciones personalizadas en Visual Basic Application (VBA) .................... 32
1.6.1 Pasos para crear funciones personalizadas .............................................................................. 32
1.6.2 Funciones para calculo de valores presente y futuro de series ................................................. 36
1.6.3 Funciones de gradientes aritmetico y geometrico y amortizacion constante ........................... 38
1.6.4 Funciones para calcular los factores de equivalencia ............................................................... 39
2. TASAS DE INTERÉS
2.1 Concepto de interés ............................................................................................................................. 45
2.2 Principio de equivalencia ................................................................................................................... 47
2.3 Tasas de interés simple y compuesta .............................................................................................. 48
2.3.1 Tasa de interes simple ................................................................................................................ 49
2.3.2 Tasa de interes compuesta ......................................................................................................... 51
2.4 Plazo, periodo y frecuencia de capitalización ................................................................................ 53
2.5 Tasa de interés periódica ................................................................................................................... 55
2.5.1 Notacion de la tasa periodica ..................................................................................................... 55
2.6 Tasas de interés nominal y efectiva por periodo vencido ........................................................... 55
2.6.1 Tasa de interes nominal .............................................................................................................. 55
2.6.1.1 Notación de la tasa nominal...................................................................................................................... 57
2.6.2 Tasa de interes efectiva ............................................................................................................... 58
2.6.2.1 Notación de la tasa efectiva anual ............................................................................................................ 58
2.7 Función INT.EFECTIVO - EFFECT ..................................................................................................... 59
2.8 Tasas de interés anticipadas ............................................................................................................. 60
2.8.1 Notacion de las tasas de interes anticipadas ............................................................................. 60
2.8.2 Conversion de tasas anticipadas a vencidas y viceversa............................................................ 61
2.9 Función IP_IPA ..................................................................................................................................... 62
2.10 Función EA_JA ...................................................................................................................................... 64
2.11 Función IPA_EA .................................................................................................................................... 66
2.12 Función TASA.NOMINAL - NOMINAL ............................................................................................... 67
2.13 Capitalización continua ....................................................................................................................... 69
2.14 Conversión de tasas equivalentes en diferentes periodos de capitalización ......................... 71
2.15 Función IPM_IPN .................................................................................................................................. 74
2.16 Tasa de captación ................................................................................................................................. 76
2.17 Tasa de colocación ............................................................................................................................... 77
2.18 Margen de intermediación ................................................................................................................. 80
2.19 DTF (Depósito a término fi jo) ............................................................................................................. 80
2.19.1 Defi nicion y calculo ..................................................................................................................... 80
2.20 ¿Qué es la UVR? .................................................................................................................................... 83
2.20.1 .Quien determina el valor de la UVR? ........................................................................................ 84
2.20.2 .Cuando y como se establece la equivalencia entre UVR? ........................................................ 85
2.20.3 .Por que aumenta el saldo en pesos? ....................................................................................... 85
2.20.4 .Cuales son los sistemas de amortizacion en UVR? .................................................................. 85
2.20.5 .Como se determina el valor de la UVR? ................................................................................... 85
3. SISTEMAS DE AMORTIZACIÓN
3.1 ¿Qué es amortización? ........................................................................................................................ 89
3.2 Cuota única ............................................................................................................................................ 90
3.2.1 Funcion VF ................................................................................................................................... 93
3.3 Serie de pagos uniformes > Cuota constante ............................................................................. 94
3.3.1 Funcion PAGO .............................................................................................................................. 99
3.3.2 Funcion PA ................................................................................................................................ 100
3.3.3 Funcion PAGO.PRINC.ENTRE ................................................................................................... 104
3.3.4 Funcion PAGOINT ...................................................................................................................... 107
3.3.5 Funcion PAGOPRIN ................................................................................................................... 107
3.3.6 Funcion PAGO.INT.ENTRE ......................................................................................................... 108
3.4 Crédito con cuota extra pactada ..................................................................................................... 109
3.4.1 Funcion PF ................................................................................................................................ 111
3.5 Cuotas extras no pactadas ............................................................................................................... 114
3.5.1 Situacion A ................................................................................................................................ 114
3.5.2 Situacion B ................................................................................................................................ 117
3.6 Crédito con amortización constante .............................................................................................. 118
3.6.1 Funcion CUO_N_AMORT ........................................................................................................... 122
3.7 Crédito con gradiente geométrico .................................................................................................. 123
3.7.1 Funcion PRIM_CUO_GRAD_GEOM ........................................................................................... 127
3.7.2 Funcion VP_GRAD_GEOM ......................................................................................................... 130
3.7.3 Credito con gradiente igual a la tasa de interes ....................................................................... 131
3.8 Crédito con gradiente aritmético .................................................................................................... 137
3.8.1 Funcion PRIM_CUO_GRAD_ARIT ............................................................................................. 137
3.8.2 Funcion CUO_N_GRAD_ARIT.................................................................................................... 137
3.8.3 Funcion VP_GRAD_ARIT ........................................................................................................... 139
3.9 Crédito con DTF .................................................................................................................................. 140
3.10 Crédito en UVR .................................................................................................................................... 144
3.10.1 Credito con amortizacion constante en UVR ............................................................................ 145
3.10.2 Credito con cuota constante en UVR ........................................................................................ 149
4. CRITERIOS DE EVALUACIÓN
4.1 Valor presente neto (VPN) ................................................................................................................ 155
4.1.1 Funcion VA ................................................................................................................................. 158
4.1.2 Funcion VNA .............................................................................................................................. 164
4.2 Tasa interna de retorno (TIR) .......................................................................................................... 165
4.2.1 Funcion TIR ............................................................................................................................... 166
4.2.2 Funcion TASA ............................................................................................................................ 169
5. MODELACIÓN DE LA INCERTIDUMBRE Y EL RIESGO
EN PROYECTOS DE INVERSIÓN
5.1 Generalidades sobre evaluación fi nanciera de proyectos ........................................................ 173
5.2 Estructuras básicas del fl ujo de caja libre (FCL) ........................................................................ 175
5.2.1 Flujo de caja del proyecto ......................................................................................................... 177
5.2.2 Flujo de caja del inversor .......................................................................................................... 178
5.2.3 Flujo de caja incremental ......................................................................................................... 179
5.2.4 Desarrollo de modelos para la evaluacion fi nanciera .............................................................. 181
5.3 Riesgo e incertidumbre en el ciclo de vida del proyecto ........................................................... 181
5.4 Modelos para evaluación de proyectos bajo incertidumbre ..................................................... 183
5.4.1 Analisis de equilibrio................................................................................................................. 183
5.4.1.1 Análisis de equilibrio para un solo proyecto........................................................................... 186
5.4.2 Analisis de sensibilidad ............................................................................................................ 190
5.4.2.1 Gráfico de telaraña ............................................................................................................ 192
5.4.2.2 Gráficos tornado ................................................................................................................ 193
5.4.2.3 Herramienta Tabla ............................................................................................................. 194
5.4.3 Estimaciones optimistas vs. pesimistas ................................................................................... 195
5.4.4 Tasa minima de rendimiento (TMR) ajustada al riesgo ............................................................ 198
5.4.5 Metodo de equivalencia a certidumbre .................................................................................... 204
5.4.6 Reduccion de la vida util ........................................................................................................... 207
5.4.7 Criterios de decision bajo incertidumbre completa ................................................................. 207
5.4.7.1 Principio o regla de Hurwicz ...................................................................................................................208
5.4.7.2 Principio o regla de minimáximo arrepentimiento ...............................................................................209
5.5 Modelos para evaluación de proyectos bajo riesgo .................................................................... 210
5.5.1 Medidas estadisticas basicas.................................................................................................... 211
5.5.2 Evaluacion de proyectos con variables aleatorias .................................................................... 213
5.5.3 Simulacion Monte Carlo ............................................................................................................ 215
5.5.3.1 Generación de valores aleatorios ........................................................................................................... 217
5.5.3.2 Número de simulaciones ........................................................................................................................226
5.5.4 Modelacion de la correlacion y dependencias.......................................................................... 229
5.5.5 Procesos estocasticos ............................................................................................................... 231
5.5.6 Identifi cacion de variables ........................................................................................................ 234
5.5.6.1 Análisis basado en los coeficientes de correlación ..............................................................................234
5.5.6.2 Análisis basado en los coeficientes de regresión .................................................................................237
ANEXO 5.1 DISTRIBUCIONES DE PROBABILIDAD PARA
MODELAR: OPINIÓN DE EXPERTOS
Distribución discreta - Distribución general ........................................................................................ 241
Distribución uniforme ................................................................................................................................ 242
Distribución triangular .............................................................................................................................. 242
Distribución beta ......................................................................................................................................... 244
Distribuciones paramétricas .................................................................................................................... 245
REFERENCIAS 247

La gestión financiera de una empresa en la actualidad debe apoyarse tanto en unos sólidos conocimientos teóricos, como en herramientas que faciliten la toma de decisiones de forma ágil y eficiente. Por ello, el dominio de hojas de cálculo es una prioridad para cualquier profesional en el campo de las finanzas; este libro resulta de ayuda para iniciarse en este empeño. JOSÉ MARTÍ PELLÓN Catedrático de Economía Financiera / Universidad Complutense de Madrid Uno de los desafíos más interesantes en finanzas acerca de la efectividad de la modelación financiera es cerrar la brecha entre el ámbito académico y el práctico. Para lograr esto, el analista usa su 'caja de herramientas', en donde encuentra modelos, fórmulas, códigos, técnicas matemáticas y estadísticas; pero también intuición, referenciación, debate, creatividad, método, ensayo, error y persistencia. Dicha brecha no se cierra completamente, pero cada intento entrena al profesional para el siguiente problema. Este libro engrosa nuestra 'caja de herramientas' y acerca las técnicas a las soluciones prácticas; así, el proceso de análisis de problemas financieros tiene mayores posibilidades de alcanzar soluciones reales y efectivas. Este libro nos ayuda a entrenar y prepararnos mejor para el siguiente reto financiero. FELIPE HERRERA Vicepresidente de inversiones / Protección Este libro es una guía fundamental en el estudio de las Matemáticas Financieras y la Evaluación Financiera de Proyectos, pues expone la teoría de una manera muy clara y pedagógica, y permite aplicarla con una herramienta poderosa como Microsoft Excel. Pocos libros tienen esta conexión, la cual es vital para el desarrollo de las actividades de un analista financiero. SANTIAGO REVELO Coordinador de Modelaciones Financieras / SURA Este libro presenta Excel como herramienta fundamental en la transición de la teoría a la práctica que todo estudiante requiere para enfrentarse al campo laboral. Además de explicar con claridad los conceptos financieros, enseña técnicas en el uso de la hoja de cálculo que, sin duda, ahorran tiempo y facilitan el aprendizaje. MARIO OCHOA OCAMPO Coordinador de Planeación Financiera, profesor universitario / ARUS