Workshops UCEMA 2016 Excel/VBA Excel/VBA orientado a las finanzas Herramientas, Herramientas, funciones y programación programación Clase 1
Julián R. Siri Lic. en Economía y Mg. en Finanzas – UCEMA MSc. in Financial Engineering – Engineering – Columbia Columbia University
Excel/ Ex cel/VB VBA A - Tema emario rio Tópicos a tratar
Reembolso de Préstamos Préstamos
Funciones Funciones estándarizadas estándarizadas de Excel UDFs Macros y controles de Excel
Valor actual neto y tasa interna de retorno Supuesto de reinversión y TIR modificada Testeo de TIR múltiple VAN y TIR no periódicos
Funciones estandarizadas de Excel Macros
Activos de Renta Fija
Sistemas Francés, Alemán, y Americano Sistema Directo Descontado, Cargado, y Promediado Costo Financiero Total
Evaluación de Proyectos de Inversión
Herramientas de Excel a utilizar…
Precio y retorno de un bono Duration Duration y duration duration modificada modificada Yield Yield curve curve Intereses corridos, valor técnico, y paridad
Funciones de Excel Gráficos Macros
Optimización
Frontera eficiente
Solver Macros
Excel/VBA – Dinámica del Workshop La lógica aplicada para este workshop será la siguiente: 1. Haremos un repaso de características propias de cada tópico y tema específico tratado, incluyendo el formulario matemático involucrado. 2. Desarrollaremos ejemplos numéricos 3. Finalmente avanzaremos sobre implementaciones programación, ejecución y eficientización de procesos.
prácticas,
temas
de
Excel/VBA – Reembolso de Préstamos SISTEMA ALEMÁN Principales características: •
•
Amortización de capital periódica constante. Intereses abonados decrecientes. Dado los dos puntos anteriores, podemos darnos cuenta que la cuota total es decreciente.
Cálculos básicos: 1. Amortización periódica: Valor nominal del préstamo repartido entre la cantidad de períodos a pagar,
2. Intereses: En este caso serán sobre el saldo de capital aún no cancelado,
− =
×
3. Cuota resultante: Ni más ni menos que la sumatoria de los dos términos anteriores.
Excel/VBA – Reembolso de Préstamos SISTEMA ALEMÁN Ejemplo: Un préstamo a 5 meses, con una TNA de 20%, un capital prestado de 1.000 y aplica el •
sistema alemán. Datos
Capital Nro. Períodos T.N.A.
1.000,00 5 20,00% Período 1 2 3 4 5
Deuda 1.000,00 800,00 600,00 400,00 200,00
Intereses Amortización 16,44 200,00 13,15 200,00 9,86 200,00 6,58 200,00 3,29 200,00
Cuota 216,44 213,15 209,86 206,58 203,29
Amortizado 200,00 400,00 600,00 800,00 1.000,00
Excel/VBA – Reembolso de Préstamos SISTEMA FRANCÉS Principales características: •
•
Amortización de capital periódica creciente. Intereses abonados decrecientes. La cuota total resulta constante.
Cálculos básicos: 1. Calcular el valor de la cuota: resulta de la siguiente fórmula,
× 1 + × 1 + 1
2. Intereses: sobre el saldo del capital no cancelado,
− =
×
3. Capital amortizado en primera cuota: Restarle a la cuota el primer pago de intereses. 4. Amortizaciones restantes: a partir de entonces se puede calcular el capital amortizado en cualquier período restante,
1 + −
Excel/VBA – Reembolso de Préstamos SISTEMA FRANCÉS Ejemplo: Mismos parámetros que antes. •
Datos
Capital Nro. Períodos T.N.A.
1.000,00 5 20,00%
Tasa periodica
1,64% Período 1 2 3 4 5
Deuda 1.000,00 806,47 609,75 409,81 206,57
Intereses Amortización 16,44 193,53 13,26 196,71 10,02 199,95 6,74 203,23 3,40 206,57
Cuota 209,97 209,97 209,97 209,97 209,97
Amortizado 193,53 390,25 590,19 793,43 1.000,00
Excel/VBA – Reembolso de Préstamos FUNCIONES ESTANDARIZADAS DE EXCEL •
Podemos hacer los mismos cálculos mediante funciones de Excel estándar PAGO PAGOINT PAGOPRIN PAGO.INT.ENTRE PAGO.PRINC.ENTRE VA VF NPER TASA INT.EFECTIVO TASA.NOMINAL
Excel/VBA – Reembolso de Préstamos FUNCIONES ESTANDARIZADAS DE EXCEL
PAGO Calcula el flujo de una renta constante. En nuestro caso es la cuota de un sistema francés.
En donde la Tasa será la que aplique para cada período, Nper será el número de períodos y Va será el capital prestado.
Excel/VBA – Reembolso de Préstamos FUNCIONES ESTANDARIZADAS DE EXCEL
PAGOINT Determina la parte correspondiente a intereses en una cuota dentro del sistema francés.
En donde, Tasa será la que aplique para cada período, Período es el número de cuota, Nper será el número de períodos, Va será el capital prestado, Vf es el valor futuro (o final) que consideramos 0 usualmente, Tipo es un indicador de si la cuota es vencida o adelantada.
Excel/VBA – Reembolso de Préstamos FUNCIONES ESTANDARIZADAS DE EXCEL
PAGOPRIN Determina la parte de la cuota que corresponde a la amortización de capital, en un sistema francés.
En donde, Tasa será la que aplique para cada período, Período es el número de cuota, Nper será el número de períodos, Va será el capital prestado, Vf es el valor futuro (o final) que consideramos 0 usualmente, Tipo es un indicador de si la cuota es vencida o adelantada.
Excel/VBA – Reembolso de Préstamos FUNCIONES ESTANDARIZADAS DE EXCEL
PAGO.INT.ENTRE En el sistema francés, devuelve los intereses acumulados entre dos períodos.
En donde, Tasa será la que aplique para cada período, Nper será el número de períodos, Vp será el capital prestado, Per_inicial indica el período a partir del cual se desea acumular los intereses, Per_final es el período hasta el cual se acumularán los intereses, Tipo es un indicador de si la cuota es vencida o adelantada.
Excel/VBA – Reembolso de Préstamos FUNCIONES ESTANDARIZADAS DE EXCEL
PAGO.PRINC.ENTRE En el sistema francés, devuelve las amortizaciones de capital acumuladas entre dos períodos determinados.
En donde, Tasa será la que aplique para cada período, Nper será el número de períodos, Vp será el capital prestado, Per_inicial indica el período a partir del cual se desea acumular las amortizaciones, Per_final es el período hasta el cual se acumularán las amortizaciones, Tipo es un indicador de si la cuota es vencida o adelantada.
Excel/VBA – Reembolso de Préstamos FUNCIONES ESTANDARIZADAS DE EXCEL
VA Calcula el valor actual de una renta constante.
En donde, Tasa será la que aplique para cada período, Nper será el número de períodos, Pago es el flujo de fondos (constante) a lo largo de toda la renta. Si omitimos esto, tendremos que completar el input Vf , Vf será el valor final (o futuro). Si lo omitimos asume que es 0, Tipo es un indicador de si la cuota es vencida (0) o adelantada (1).
Excel/VBA – Reembolso de Préstamos FUNCIONES ESTANDARIZADAS DE EXCEL
TASA Dado los inputs podemos verlo de dos maneras: si se completa el argumento Pago, esta función permite hallar la tasa de interés empleada en una renta constante. En cambio, si se completan los argumentos Va y Vf también se puede utilizar para calcular la tasa implícita en una operación de renta variable.
En donde, Estimar permite solucionar los problemas de soluciones múltiples (donde más de un resultado puede ser el correcto). Si lo omitimos, supone un valor de output de 10%.
Excel/VBA – Reembolso de Préstamos FUNCIONES ESTANDARIZADAS DE EXCEL
INT.EFECTIVO y TASA.NOMINAL Permite encontrar la tasa opuesta (nominal si nuestro input es efectiva y viceversa), detallando como dato adicional la frecuencia de capitalización (el número de períodos de interés compuesto por año)
Excel/VBA – Reembolso de Préstamos SISTEMA FRANCÉS Caso real: Simulador de préstamos del Banco Ciudad. •
Excel/VBA – Reembolso de Préstamos SISTEMA FRANCÉS Caso real: Simulador de préstamos del Banco Ciudad. ¡Repliquela con funciones! •
Excel/VBA – Reembolso de Préstamos CONTROLES DE EXCEL (PARA FORMULARIOS Y ActiveX) Están para brindar más flexibilidad a las planillas. Permite agregar funcionalidad de formularios a nuestros libros de Excel. Se encuentran dentro de la ficha de Programador/Desarrollador. La diferencia entre los controles de formulario y los controles ActiveX es que los primeros solo responde luego de interactuar con ellos, mientras que los últimos aumentan el nivel de interacción. En ambos casos podemos visualizar sus propiedades.
Cuadro de grupo
Botón
Control de números
Cuadro de lista Cuadro combinado
Casilla de verificación
Botón de opción
Barra de desplazamiento
Excel/VBA – Reembolso de Préstamos CONTROLES DE EXCEL (PARA FORMULARIOS Y ActiveX) El acceso a dichas propiedades permite personalizar los controles utilizador (siempre y cuando esté en modo diseño la aplicación) y asociarlo con código específico:
Excel/VBA – Reembolso de Préstamos CONTROLES DE EXCEL (PARA FORMULARIOS Y ActiveX) • •
•
• •
•
•
•
Botón: Es probablemente el control más conocido (porque se lo utiliza habitualmente para ejecutar macros). Casilla de verificación: Es un botón que activa o desactiva una opción. Puede seleccionarse más de una casilla de verificación a la vez. El control puede asignar estos valores a la celda vinculada: VERDADERO, FALSO, #N/A. Botón de opción: Es un botón que activa o desactiva una opción. Para realizar una selección múltiple hay que emplear el Cuadro de grupo. La celda vinculada toma números que se encuentran correlacionados con el número de botón activado. Cuadro de grupo: Agrupa los controles que se encuentran relacionados. Cuadro de lista: Permite seleccionar uno (opción: simple) o varios elementos de una lista (opción: múltiple o extendida). El resultado de la selección se indica en la celda vinculada mediante un número que representa el orden de ubicación del elemento dentro de la lista. Cuadro combinado: Lista despegable que permite seleccionar un elemento a la vez. El resultado de la selección representa la posición del elemento en cuestión dentro de la lista. Barra de desplazamiento: Intervalo numérico prefijado por el usuario. Se pueden establecer los límites y la magnitud de la variación (dos opciones). Control de números: Permite trabajar con un intervalo numérico prefijado por el usuario. Se pueden establecer los límites y la magnitud de la variación (una opción).
Excel/VBA – Reembolso de Préstamos GENERACIÓN DE UNA PLANILLA INTERACTIVA Botón de opción Casilla de verificación Barra de desplazamiento
Control de números
Excel/VBA – Reembolso de Préstamos GENERACIÓN DE UNA UDF (FUNCIÓN PERSONALIZADA)
Excel/VBA – Reembolso de Préstamos SISTEMA AMERICANO Principales características: •
•
Amortización de capital es íntegramente al vto. Intereses abonados son constantes. La cuota no es constante.
Cálculos básicos: 1. Calcular el valor de la cuota: resulta de la siguiente fórmula,
2. Intereses: sobre el saldo del capital no cancelado,
− =
×
Excel/VBA – Reembolso de Préstamos SISTEMA AMERICANO Principales características: •
•
Amortización de capital es íntegramente al vto. Intereses abonados son constantes. La cuota no es constante.
Cálculos básicos: 1. Calcular el valor de la cuota: resulta de la siguiente fórmula,
2. Intereses: sobre el saldo del capital no cancelado,
− =
×
Excel/VBA – Reembolso de Préstamos SISTEMA DIRECTO CARGADO Principales características: •
Los intereses se calculan siempre sobre el monto original del préstamo. La amortización no es más que la división del monto del préstamo por la cantidad de períodos. Tanto la cuota como los intereses y la amortización de capital son constantes. Período
Deuda
Intereses
Amortización
Cuota
Amortizado
1
1.000,00
16,44
200,00
216,44
200,00
2
800,00
16,44
200,00
216,44
400,00
3
600,00
16,44
200,00
216,44
600,00
4
400,00
16,44
200,00
216,44
800,00
5
200,00
16,44
200,00
216,44
1.000,00
Excel/VBA – Reembolso de Préstamos SISTEMA DIRECTO DESCONTADO Principales características: •
Los intereses se descuentan del monto del préstamo original, por ende, para obtener 100 de préstamo, debería pedir un monto que incluya los intereses (ejemplo, a tasa del 10% y 4 períodos, si pido 100 obtengo 100 – 100 x 10% x 4 = 60, entonces pido 166,67). Las cuotas las obtengo de dividir el monto de la operación por la cantidad de períodos (cuota constante).
SISTEMA DIRECTO PROMEDIADO Principales características: •
La amortización del capital es constante y los intereses inicialmente se calculan sobre saldo adeudado. La suma de intereses se promedia por el número de períodos y finalmente se determina la cuota sumando capital más intereses. Tanto la cuota como los intereses y la amortización de capital son constantes.
Excel/VBA – Bibliografía •
Ross, Westerfield & Jaffe: “Finanzas Corporativas”. 7ma Edición. McGraw-Hill (2009)
•
López Dumrauf: “Finanzas Corporativas”. Grupo Guía (2003)
•
López Dumrauf: “Cálculo Financiero Aplicado”. 2da Edición. La Ley (2009)
•
Kofler: “Definitive Guide to Excel VBA”. 2da Edición. Apress (2003)
•
Roman: “Writing Excel Macros with VBA”. 2da Edición. O’Reilly (2002)
•
Fernández: “Excel Aplicado: Soluciones para el profesional en Ciencias Económicas”. Errepar (2010)
