Introducción Excel Financiero

INTRODUCCIÓN A LAS FUNCIONES FINANCIERAS

Objetivo: Conocer la sintaxis de las funciones financieras y construcción de funciones personalizadas a través del código visual basic de las ecuaciones matemáticas como con el contenidos de celdas.

Abrir Excel
Guardarlo con el nombre de intro funciones financieras. tipo de archivo: Libro de excel habilitado para macros, como se muestra en la siguiente imagen:

INT.EFECTIVO (Nombre de la Hoja)

Devuelve la tasa efectiva del interés anual si conocemos la tasa de interés anual nominal y el número de períodos de interés compuesto por año. De aplicación cuando los períodos de pago son exactos.

Sintaxis INT.EFECTIVO(int_nominal;núm_per_año)

Si alguno de los argumentos es menor o igual a cero o si el argumento núm_per_año es menor a uno, la función devuelve el valor de error #¡NUM!.

La respuesta obtenida viene enunciada en términos decimales y debe expresarse en formato de porcentaje. Nunca divida ni multiplique por cien el resultado de estas funciones. Esta función proporciona la tasa efectiva de interés del pago de intereses vencidos. Para intereses anticipados debe calcularse la tasa efectiva aplicando la fórmula.

El argumento núm_per_año se trunca a entero cuando los períodos son irregulares, hay que tener especial cuidado con esta función, sólo produce resultados confiables cuando la cantidad de períodos de pago en el año (núm_per_año) tiene valores exactos; por ejemplo:

mensual(12), trimestral(4), semestral(2) o anual (1).

El resultado proporcionado por esta función lo obtenemos también con la siguiente fórmula:

EJERCICIO 2 (Aplicación de la función INT.EFECTIVO)

(A) Cuando los períodos de pago son exactos y el resultado es confiable:

FECHA INICIAL : 15-03-2004

FECHA FINAL : 15-06-2004

TASA NOMINAL : 32,93% anual, compuesto trimestralmente

Solución: n = (15/03/2004 - 15/06/2004) = 90/30 = 3, m = (12/3) = 4 Aplicando ambos métodos:

Si excel no tiene la función el ejercicio se podría hallar de la siguiente forma:

Construya la siguiente información:

En la celda B7 digitar la función =SIFECHA(B4;B5;"m") para hallar el valor en meses de la fecha inicial y final del periodo de la tasa interés efectiva anual (EA) periódica. como parte de la tasa efectiva anual 12 meses, para hallar el número de periodos por año que corresponde a la variable (m), se construye la fórmula en la celda B8= 12/B6 dando como resultado 4 periodos que corresponde a la tasa periódica trimestralmente.
Ya tenemos los valores ahora podemos obtener el interés efectivo de la tasa periódica trimestral: aplicando la siguiente fórmula en la celda D1=(1+B6/B8)^B8-1 que corresponde a la ecuación matemática del interés efectivo.

También podemos construir funciones personalizadas para hallar de forma diferente el resultado de una ecuación matemática, a través de la creación de macros con la herramienta de programación Visual Basic, para ello seguir los siguientes pasos:

Identificar el número de incógnitas o variables de la ecuación matemática, en este caso son dos la tasa nominal (j) y el número de periodos por año (m).

Una vez identificadas las variables realizamos los siguientes pasos de acuerdo a lo indicados en las siguientes imágenes:

Clic Derecho del Mouse sobre el nombre de la hoja y dar clic en ver código

Se Mostrará la ventana de programación de macros Microsoft Visual Basic para aplicaciones, cerrar la ventana que se muestra de fondo de color blanco.

Dar clic en el menú insertar módulo, se mostrará la siguiente imagen

Dar clic al lado izquierdo en el texto Módulo1, para que nos muestre las propiedades y en el campo (Name): cambiemos el nombre de Módulo1 por intefectivo (importante tener en cuenta que los nombres de los módulos, como de las funciones y variables no deben ser palabras reservadas de excel o nombres de funciones, tampoco nombres de hojas, esto generará error de ejecución de la macro), como se muestra en la siguiente imagen:

Digitamos el código que se muestra en la siguiente imagen:

: Donde Function es la sintaxis con la que se declaran funciones en la programación de Visual Basic para aplicaciones de Office.
inefe: Es el nombre de la función personalizada creada por el usuario.
(j,m): Una de las formas para declarar variables dentro de una función, en este caso tenemos las dos variables que intervienen en la ecuación matemática del cálculo del interés efectivo.
: Sintaxis de la ecuación matemática para obtener el resultado del interés efectivo.
End Function: Es la sintaxis con la que se cierra la codificación de una función.

Es importante agregar a la función programada ayuda de la función por lo que se debe crear una macro para la descripción general de la función y de las dos variables j y m, para tal fin se programa el siguiente código debajo de la programación de la función:

: La sintaxis Sub es la estructura con la que se escribe una macro, ayuda_inefe es el nombre de la macro que tendrá la descripción general de la función y de las variables.
Luego se declaran las variables para el paso de los argumentos de la función inefe y de los textos de la ayuda; NomF es la variable que asociará el nombre de la función y pasará los argumentos para cada descripción de ayuda.
DesF: Es la variable que almacenará la descripción general de la función.
Cat: Es la variable que almacena el nombre de la categoría a crear en el grupo de funciones como “NelsonRG”.
DesA: Es la variable que almacenará las ayudas de los diferentes argumentos dentro de la función creada.
Application.MacroOptions: Este método se corresponde con las opciones del cuadro de diálogo Opciones de la macro. Puede utilizarlo para mostrar una función definida por el usuario en una categoría integrada o nueva dentro del cuadro de diálogo Insertar función.
Macro:=NomF: Asigna el nombre de la función personalizada creada a la macro y asociar la ayuda de la descripción a través del argumento Description:=DesF.
Category:=Cat: Incluye en el grupo de categorías de funciones la categoría personalizada creada “NelsonRG”.
ArgumentDescriptions:=DesA: Asigna las descripciones de cada uno de los argumentos a cada variable dentro de la función personalizada inefe.
Por último se debe ejecutar el módulo de la programación de la función y de la macro, este proceso se conoce comúnmente como compilar en programación, para esto debemos ubicarnos en el código de la función inefe y de la macro ayuda_inefe y pulsar la tecla F5 para verificar que no existan errores de sintaxis o programación, si el código esta bien mostrará la siguiente ventana, damos clic en ejecutar para validar la programación de la macro ayuda_inefe. como se muestra en la siguiente imagen:

Si al ejecutar la macro, no se muestran errores la función a quedado bien y esta queda incorporada en el grupo de funciones de excel y se podrá utilizar como una función más, el resultado será el siguiente:

Ubicarse en la celda D2, dar clic en el icono de funciones fx, se mostrará la ventana de insertar función, buscar en la categoría de funciones el grupo llamado “NelsonRG”, se mostrará la función personalizada creada con la programación de Visual Basic de la Macro llamada inefe, clic en aceptar; como se indica en la siguiente imagen a través de las flechas.

Ahora se mostrará la ventana del asistente de la función con los dos argumentos creados de la función inefe programada j que corresponde a la celda B6 y m el número de periodos al año, que corresponde a la celda B8, como se muestra en la siguiente imagen:

El resultado será el mismo de la celda D1.

Excel ya tiene incorporada en su grupo de funciones la función INT.EFECTIVO() que tiene la misma estructura del ejercicio creado para los resultados de las celdas D1 y D2, nos ubicamos en la celda D3 para incorporar la función INT.EFECTIVO(). Como se observa en la siguiente imagen:

El objetivo de este taller es comprobar que con los tres métodos llegamos al mismo resultando, aprendiendo a utilizar Excel como una herramienta para la solución de ecuaciones matemáticas a través del uso de referencias de celdas y funciones personalizadas, en el caso de que excel no tenga la función incorporada en el grupo de funciones

En la siguiente imagen se observa el mismo resultado en las celdas D1, D2 y D3:

Ejercicio Propuesto

En el mismo libro construya una macro para el cálculo de la TASA.NOMINAL (función TASA.NOMINAL)

Descripción

Devuelve la tasa de interés nominal anual si se conocen la tasa efectiva y el número de períodos de interés compuesto por año.

Sintaxis

TASA.NOMINAL(tasa_efectiva;núm_per_año)

La sintaxis de la función TASA.NOMINAL tiene los siguientes argumentos:

Tasa_efectiva Obligatorio. La tasa de interés efectiva.
Núm_per_año Obligatorio. El número de períodos de interés compuesto por año.

Observaciones

El argumento núm_per_año se trunca a entero.
Si alguno de los argumentos no es numérico, TASA.NOMINAL devuelve el valor de error #¡VALOR!
Si tasa_efectiva ≤ 0 o si núm_per_año < 1, TASA.NOMINAL devuelve el valor de error #¡NUM!
TASA.NOMINAL (tasa_efectiva,núm_per_año) está relacionado con INT.EFECTIVO(tasa_efectiva,núm_per_año) a través de tasa_efectiva=(1+(tasa_nominal/núm_per_año))^núm_per_año -1.
TASA.NOMINAL está relacionado con INT.EFECTIVO como se indica a continuación:
El argumento núm_per trunca a entero, hay que tener especial cuidado con esta función, sólo produce resultados confiables cuando la cantidad de períodos de pago en el año (núm_per) tiene valores exactos; por ejemplo: mensual (12), trimestral (4), semestral (2) o anual (1). Si alguno de los argumentos es menor o igual a cero o si el argumento núm_per es menor a uno, la función devuelve el valor de error #¡NUM!. La respuesta obtenida viene enunciada en términos decimales y debe expresarse en formato de porcentaje. Nunca divida ni multiplique por cien el resultado de estas funciones.

CASO

Se desea conocer la TASA NOMINAL para los siguientes periodos Mensual, Trimestral, semestral y anual. donde la tasa efectiva es de 0.3449 %, cree una solución donde se pueda comprobar el mismo valor obtenido con referencia de celdas, macro y la función de excel.

Cree una solución eficiente, es necesario utilizar los conceptos de validación de datos por lista donde al seleccionar el periodo se calculen los valores de acuerdo al periodo, funciones lógicas y de búsqueda.

Es el resultado esperado, como se muestra en la siguiente imagen: