Exel financiero (página 2)

MONEDA.FRAC Convierte un precio en una moneda, expresado como

un numero decimal en un precio expresado como una

fracción.

NPER Devuelve él numero de periodos de una inversión

PAGO Devuelve el pago periódico de una anualidad

PAGO.INT.ENTRE Devuelve él interés acumulativo pagado entre dos

periodos

PAGO.PRINC.ENTRE Devuelve el pago acumulativo principal pagado en

un préstamo entre dos periodos

PAGOINT Devuelve el pago de intereses de una inversión

durante un periodo determinado

PAGOPRIN Devuelve el pago principal de una inversión durante un

periodo determinado

PRECIO Devuelve el precio por 100$ de un valor que genera

intereses periodicos

PRECIO.DESCUENTO Devuelve el valor nominal del precio por 100$ de una

obligación descontada

PRECIO.PER.IRREGULAR.1 Devuelve el precio por 100$ de un valor con un periodo

de inicio irregular

PRECIO.PER.IRREGULAR.2 Devuelve el precio por 100$ de un valor con un periodo

final irregular

PRECIO.VENCIMIENTO Devuelve el valor nominal del precio por 100$ de una

obligación que paga intereses en el vencimiento

RENDTO Devuelve el rendimiento de un valor que genera un

interés periódico

RENDTO.DESC Devuelve el rendimiento anual de un valor descontado.

Por ejemplo, una letra del tesoro

RENDTO.PER.IRREGULAR.1 Devuelve el rendimiento de un valor con un periodo

inicial irregular

RENDTO.PER.IRREGULAR.2 Devuelve el rendimiento de un valor con un periodo

final irregular

RENDTO.VENCTO Devuelve el interés anual de un valor que paga un

interés al vencer

SLN Devuelve la depreciación lineal de un activo durante

un periodo

SYD Devuelve la depreciación del numero de la suma de

años de un activo durante un tiempo especificado

TASA Devuelve el tipo de interés por periodo de una

anualidad

TASA.DESC Devuelve el tipo de descuento de un valor

TASA.INT Devuelve el tipo de interés de una inversión en

valores

TASA.NOMINAL Devuelve el tipo de interés anual nominal

TIR Devuelve el tipo interno de devolución de una serie de

flujos de efectivo

TIR.NO.PER Devuelve el tipo de interés interno de devolución de un

plan de flujos de efectivo que no sea necesariamente

periódico

TIRM Devuelve el tipo interno de una devolución en que los

flujos de efectivo positivo y negativo se financian con

diferentes tipos de interés

VA Devuelve el valor presente de una inversión.

VF Devuelve el valor futuro de una inversión.

VF.PLAN Devuelve el valor futuro de un pago inicial principal

después de aplicar una serie de tipos de interés

compuesto

VNA Devuelve el valor presente neto de una inversión

basándose en una serie de flujos de efectivo periódicos

y un tipo de descuento

VNA.NO.PER Devuelve el valor presente neto de un plan de flujos de

efectivo que no sea necesariamente periódico

Préstamos

COMBINACION DE FUNCIONES —— EXCEL FINANCIERO

Ejercicio desarrollado vinculando las Funciones Financieras, con Funciones de: Lógica, e Información respectivamente de Excel.

El ejercicio, referido al ejemplo tratado respecto a un Préstamo Hipotecario, tiene la particularidad de poder desarrollar todo el cuadro de Rentas hasta un plazo de 30 años, es decir 360 cuotas sin ingresar valores, e introducirlos, según interesen, los Montos, Intereses, Plazo etc. en su máxima extensión o cualquier plazo intermedio permitiendo analizar la variación de la cuota, interés, amortización, etc.

Su ejecución es posible combinando las Funciones Financieras conocidas con otras Funciones de Excel, que permiten condicionar los vacíos de celdas evitando que se produzcan errores, y realizar los cálculos correspondientes al producirse el ingreso de valores en la planilla de entrada de Datos respectiva.

Las Funciones complementarias que se emplearon fueron:

Función Lógica SI: de vuelve un único valor si una condición se evalúa como Verdadero y otro valor si se evalúa como Falso.

Función de Información ESERR: Devuelve Verdadero, cualquier valor de error de tipo #¡NUM!

Función de Información ESERROR: Devuelve Verdadero, cualquier valor de error de tipo #¡VALOR!

Indice

Tipo de Funciones empleadas

Teoría y practica

Préstamo Hipotecario

Diseño de Planilla

Salida de datos

Desarrollo

N° de Cuotas

Vencimiento de las cuotas

Calculo de Amortización

Calculo de Intereses

Calculo del valor de la cuota

Calculo pendiente de Amortización

Calculo de comisión por cancelamiento anticipado

Calculo del valor de recate del préstamo

Informe Adicional

Análisis de la amortización e intereses

Dentro de periodos Inicial- Intermedio-Final

Análisis con Datos / Tabla

Calculo del valor de la cuota, según la variación del monto prestado

Variación de la cuota para distintos importe y diferentes cantidades de cuotas

Variación de la cuota para diferente cantidad de cuotas y variaciones de la tasa de interés

Buscar Objetivo

Cantidad de Monto para una cuota determinada

Tipo y Funciones empleadas para realizar el trabajo

Funciones Financieras

PAGO

PAGOINT

PAGOPRIN

PAGO.PRINC.ENTRE

PAGO.INT.ENTRE

INT.EFECTIVO

TASA.NOMINAL

TASA

NPER

Funciones Lógicas

SI

Funciones Fecha y Hora

FECHA.MES

Funciones de Información

ESERR

ESERROR

Desarrollo de cada Función empleada

Función PAGO

Calcula el pago de un préstamo basándose en pagos constantes y una tasa de interés constante.

Sintaxis PAGO(tasa;nper;va;vf;tipo)

Tasa : es la tasa de interés del préstamo

Nper :es le numero total de pagos del préstamo

Va: es el valor actual

Vf : es el valor futuro. Si el argumento vf se omite, se asume que es 0 (o el valor futuro de un préstamo es cero)

Tipo : es un numero 0 o 1 e indica el vencimiento de pagos

Tipo :0 al final del periodo

Tipo :1 al inicio del periodo

Observaciones : El pago devuelto incluye el capital y el interés

Ejemplo

Celda B6=PAGO(B2/12;B3;B1;B4;B5)

En el caso de producirse el pago al inicio del periodo

Función PAGO para producir un ahorro en un tiempo determinado

La función PAGO, también puede determinar los pagos anuales que deberían efectuarse para producir un ahorro de $ 20.000 en 10 años a una tasa anual del 6,5% de interés en una cuenta de ahorro

Celda B6=PAGO(B2/12;B3*12;B1)

Si se depositan $ 227,10, cada mes en una caja de ahorro, que paga el 6,5% de interés, al final de 10 años se abra ahorrado $ 20.000

Función PAGOINT

Calcula el interés pagado en un periodo especificado por una inversión basándose en una tasa de interés constante y pagos en periodos constantes.

Sintaxis PAGOINT(tasa;periodo;nper;va;vf;tipo)

Tasa: es la tasa de interés del periodo

Periodo: es el periodo para el que se desea calcular el interés y deben estar entre 1 y el argumento nper

Nper: es numero total de pagos del préstamo

Va: es el valor actual de una serie de pagos futuros

Vf : es el valor futuro de una serie de pagos futuros. Si se omite se calcula como cero

Tipo : es un numero 0 o 1 e indica el vencimiento de pagos

Tipo :0 al final del periodo

Tipo :1 al inicio del periodo

Ejemplo

Celda B7= PAGOINT=(B2/12;B3;B4;B1;B5;B6)

En este caso se puede apreciar en la celda B7 que se calcula la parte correspondiente a intereses que incluye la cuota calculada anteriormente de $ 928, 57 en el primer pago es decir en el mes 1

En este caso se calcula en la celda B7 los intereses que integran la cuota de pago en él ultimo

mes es decir el mes 15

Función PAGOPRIN

Calcula el pago sobre el capital de una inversión durante un periodo determinado, basándose en una tasa de interés constante y pagos periódicos constantes

Sintaxis PAGOPRIN(tasa;periodo;nper;va;vf;tipo)

Tasa: es la tasa de interés del periodo

Periodo: es el periodo para el que se desea calcular la amortización y deben estar entre 1 y el argumento nper

Nper: es numero total de pagos del préstamo

Va: es el valor actual de una serie de pagos futuros

Vf : es el valor futuro de una serie de pagos futuros. Si se omite se calcula como cero

Tipo : es un numero 0 o 1 e indica el vencimiento de pagos

Tipo :0 al final del periodo

Tipo :1 al inicio del periodo

Ejemplo

Celda B7= PAGOPRIN=(B2/12;B3;B4;B1;B5;B6)

En este caso se puede apreciar en la celda B7 que se calcula la parte correspondiente a amortización que incluye la cuota calculada anteriormente de $ 928, 57 en el primer pago es decir en el mes 1

En este caso se calcula en la celda B7 la amortización que integra la cuota de pago en él ultimo mes es decir el mes 15

Como puede apreciarse produciendo la suma de amortización e interés en ambos casos se obtiene el valor de la cuota a pagar

$113,75+$814,82=$ 928,57

$8,06+$920,51= $ 928,57

El sistema desarrollado para calcular el préstamo es según el sistema francés donde el valor de la cuota es constante.

SISTEMA ALEMAN

Otro sistema es el alemán donde en este caso el valor constante es la amortización durante todo el periodo, siendo variable la cuota a pagar. A efectos de verlo someramente vamos a practicarlo sobre el mismo ejemplo , calculando el valor de la cuota n° 1 y la n° 15

En primer lugar se calcula la amortización que por ser constante resulta de dividir el monto del préstamo por el n° de cuotas

Celda B6 = B1/B3

A continuación calculamos el interés para la cuota n° 1 y n° 15

Celda B6=PAGOINT(B2/12;B4;B3;-B1)

Celda B7=PAGOINT(B2/12;B5;B3;-B1)

Produciendo la suma de la amortización constante con el valor obtenido del interés en la cuota 1 y 15 se obtiene el valor de las cuotas respectivas

Después de esta introducción se continua con el desarrollo de las funciones aplicadas en el cuadro de rentas

Función PAGO.PRINC.ENTRE

Calcula la cantidad acumulada de capital pagado de un préstamo entre dos periodos (per_inicial y per final)

Sintaxis PAGO.PRINC.ENTRE(tasa;nper;vp;per_inicial;per_final;tipo)

Tasa: es la tasa de interés

Nper: es él numero total de periodos de pago

Per_inicial: es el primer periodo en el calculo.

Per_final: es le ultimo periodo en el calculo

Tipo: es el tipo de pago (al comienzo o al final del periodo); el valor debe ser 0 o 1

Tipo :0 al final del periodo

Tipo :1 al inicio del periodo

Ejemplo

Celda B7= PAGO.PRINC.ENTRE(B2/12;B3;B1;B4;B5;B6)

La Celda B7, calcula la cantidad acumulada de capital pagado del préstamo entre los periodos

3 y 9

En este caso la Celda B7, calcula la cantidad acumulada de capital pagado del préstamo entre el periodo inicial 1 y el periodo final 1, el resultado coincide con el obtenido aplicando la función PAGOPRIN, correspondiente al mes 1

Función PAGO.INT.ENTRE

Calcula la cantidad de interés pagado de un préstamo entre dos periodos (per_inicial y per_final)

Sintaxis PAGO.INT.ENTRE(tasa;nper;vp;per_inicial;per_final;tipo)

Tasa: es la tasa de interés

Nper: es él numero total de periodos de pago

Per_inicial: es el primer periodo en el calculo.

Per_final: es le ultimo periodo en el calculo

Tipo: es el tipo de pago de intereses (al comienzo o al final del periodo); el valor debe ser 0 o 1

Tipo :0 al final del periodo

Tipo :1 al inicio del periodo

Ejemplo

Celda B7= PAGO.INT.ENTRE(B2/12;B3;B1;B4;B5;B6)

La Celda B7, calcula la cantidad acumulada de intereses pagado del préstamo entre los periodos 3 y 9

La suma de los intereses y amortización acumulados entre los periodos 3 y 9, equivalen a la

suma de las cuotas ejecutadas en los mismos periodos

En este caso la Celda B7, calcula la cantidad acumulada de interés pagado del préstamo entre el periodo inicial 1 y el periodo final 1, el resultado coincide con el obtenido aplicando la función PAGOINT, correspondiente al mes 1

Función INT.EFECTIVO

Calcula la tasa efectiva del interés anual, si se conocen la tasa de interés anual nominal y él numero de periodos de interés compuesto por año.

Sintaxis INT.EFECTIVO(Int_nominal;num_por_año)

Int_nominal: es la tasa de interés nominal

Num_por_año: es él numero de pagos de interés compuesto por año.

Celda B5= INT.EFECTIVO(B2;B4)

La celda B5 calcula el interés efectivo para una periodicidad de 12 pagos anuales

Función TASA. NOMINAL

Calcula la tasa de interés nominal anual, si se conocen la tasa efectiva y él numero de periodos de interés compuesto por año

Sintaxis TASA.NOMINAL(tasa_efectiva;num_per)

Tasa_efectiva es la tasa de interés efectiva anual

Num_per es él numero de pagos de interés por año

La celda B5 calcula la Tasa Nominal anual del préstamo, tomando el interés efectivo y la periodicidad de 12 pagos anuales

Celda B5=TASA.NOMINAL(B2;B4)

Función TASA

Calcula la tasa de interés por periodo de una anualidad

Sintaxis TASA(nper;pago;va;vf;tipo;estimar)

Nper es él numero total de periodos de pago en una anualidad

Pago es el pago que se efectúa en cada periodo y que no puede cambiar durante la vida de anualidad. Generalmente el argumento pago incluye el capital y el interés, pero no incluye ningún otro arancel o impuesto.

Va es el valor actual de la cantidad total de una serie de pagos futuros

Vf es el valor futuro o saldo en efectivo que desea lograr después de efectuar él ultimo pago. Si el argumento vf se omite, se asume que el valor es cero( por ejemplo el valor futuro de un préstamo es cero)

Tipo: es el valor debe ser 0 o 1 e indica el vencimiento de los pagos

Tipo :0 al final del periodo

Tipo :1 al inicio del periodo

Estimar es la estimación de la tasa de interés, si el argumento estimar se omite se supone que es 10%

Ejemplo

En la celda B7 la Función Tasa calcula la tasa de interés mensual, que en la celda B8 al multiplicarla por 12 periodicidad anual devuelve la tasa anual.

Celda B7=TASA(B1;B2;B3;B4;B5;B6)

Se trabajo sobre el mismo ejemplo anterior para verificar que los valores obtenidos son los mismos

Función NPER

Calcula el numero de pagos de un préstamo, basado en pagos constantes , periódicos y a una tasa de interés constante

Sintaxis NPER(tasa;pago;va;vf;tipo)

Tasa es la tasa de interés por periodo

Pago es el pago efectuado en cada periodo, debe permanecer constante durante la vida de la anualidad

Va es el valor actual o la suma total de una serie de futuros pagos

Vf es el valor futuro o saldo en efectivo que desea lograr después de efectuar él ultimo pago. Si el argumento vf se omite, se asume que el valor es cero( por ejemplo el valor futuro de un préstamo es cero)

Tipo: es el valor debe ser 0 o 1 e indica el vencimiento de los pagos

Tipo :0 al final del periodo

Tipo :1 al inicio del periodo

La celda B6 calcula la cantidad de cuotas necesarias para saldar el préstamo según las características del mismo ( Interés, cuota, monto). Se mantiene el mismo ejemplo para verificar los datos.

Celda B6= NPER(B1/12;B2;B3;B4;B5)

Funciones Complementarias

Las siguientes funciones a analizar, Lógica SI, de Fecha y hora, FECHA.MES, y de Información ESERR y ESERROR, actúan combinadas entre sí, y con las funciones financieras permitiendo tener la Planilla General, mediante las formulas incorporadas, preparada para ser utilizada en su totalidad 30 años o en forma parcial, 5, 10, 20 años etc.

Función SI

Devuelve un valor si la condición especificada es Verdadero y otro valor si dicho argumento es Falso.

Se utiliza para realizar pruebas condicionales en valores y formulas.

Sintaxis SI(prueba_logica;valor_si_verdadero;valor_si_falso)

Pruba_logica es cualquier valor o expresión que puede evaluarse como VERDADERO o FALSO

Valor_si_Verdadero es el valor que se devolverá si prueba_logica es VERDADERO

Valor_si_falso es el valor que se devolverá si prueba_logica es FALSO

Observaciones: Es posible anidar hasta siete funciones SI como argumento Valor_si_verdadero y Valor_si _ falso para construir formulas mas elaboradas

Ejemplo:

En el el ejemplo se determina que se pagaran comisiones para un determinado monto de ventas realizadas, y en algunos casos ninguna según la siguiente tabla

Ventas inferiores a $ 20.000 no se paga comisión

Ventas superiores a $20.000 pero inferiores a $ 30.000 un 1,8% de comisión

Ventas superiores a $30.000 un 4% de comisión

La formula a introducir en la celda B2 para ser trasladada al resto es la siguiente

Celda B2= SI(A2 <20000;" ";SI(A2 < 30000;A2*1,8%;SI(A2 > 30000;A2*4%)))

Función FECHA.MES

Devuelve el numero de serie que representa la fecha que indica el numero de meses anteriores o posteriores a la fecha especificada.

Usar FECHA.MES para calcular las fechas de vencimiento que caen en el mismo día del mes, que el día de emisión.

SintaxisFECHA.MES(fecha_inicial;meses)

Fecha_inicial es el numero de serie que representa la fecha inicial

Meses es el numero de meses antes o después del argumento fecha_inicial. Si meses es un valor positivo, corresponde a fecha futura, si el valor es negativo corresponde a fecha pasada

Observaciones: Si el argumento fecha_inicial no es una fecha valida, FECHA.MES devuelve el valor de error #¡NUM!

Ejemplo:

Celda B2= FECHA.MES(A2;1)

El formato que se adopto en las celdas corresponden a Formato-Celda-fecha

Adoptando el formato Formato-Celda-General, se obtiene el valor de serie correspondiente a cada fecha, como se puede apreciar a continuación

Función ESERR

Devuelve como VERDADERO si VALOR es cualquier valor de error excepto #N/A (valor no disponible)

SintaxisESERR(Valor)

Ejemplo

Caso común, un numero cualquiera dividido por cero, donde Excel devuelve error #¡DIV/0!

Celda C1= A1/B1

En este caso una de las soluciones posibles es:

En la celda C1 se aplica una función combinada en este caso la función que sé esta tratando con la función Lógica SI

Celda C1=SI(ESERR(A1/B1);" ";(A1/B1)

Se especifica con las funciones aplicadas que en caso de producirse el error mencionado reconozca como verdadero " Celda vacía" caso contrario el resultado que de la operación

Función ESERROR

Devuelve como VERDADERO si Valor es cualquier valor de error ( #N/A, #¡VALOR!, #¡REF!, #¡DIV/0!, #¡NUM!, #¿NOMBRE? o #NULO)

Ejemplo

Sencillo ejercicio, que consiste en obtener el costo total, multiplicando la cantidad columna B por el precio unitario columna I

Para resolverlo se aplica la función de búsqueda y referencia (Buscar en tabla) BUSCARV.

Celda C2= B2*(BUSCARV(A2;$H$2:$I$3;2;FALSO), obteniendo en la Celda C2 el costo total $ 84, al trasladar la formula a la celda C3 como consecuencia de no encontrar los datos de "Verde" por carecerse en ese momento de los mismos, se produce el error #N/A (valor no disponible)

Para solucionar el problema se recurre nuevamente a la combinación de funciones, empleando

en este caso tres funciones, Lógica SI, BUSCAV, y ESERROR

CeldaC2=SI(ESERROR(B2*(BUSCARV(A2;$H$2:$I$3;2;FALSO)));" " ;

(B2*(BUSCARV(A2;$H$2:$I$3;2;FALSO))))

La presente formula combinada devuelve como verdadero el error #N/A y coloca celda vacía, en este caso la celda C3

Préstamo hipotecario

El Ejercicio consiste en el desarrollo de un cuadro de Rentas referente a un préstamo hipotecario. En el se aplicaran las Funciones: PAGO, PAGOPRIN, PAGOINT.

El cuadro debe contener la siguiente información:

*Numero de cuotas, * Calendario de Pagos, *Cantidad a pagar(cuota o renta), *Cantidad de principal amortizada en cada cuota,* Interés devengado en cada cuota, * Principal pendiente de amortizar, * Comisión de cancelación de cada periodo, * Valor de rescate

Aplicando distintas funciones de las que cuenta Excel, en este caso como auxiliares de las funciones Financieras, se prepara la planilla para que pueda ser utilizada con un plazo máximo de 30 años, permitiendo analizar los resultados ante cualquier variación intermedia, de Plazo, Préstamo, Interés etc.

Diseño de Planilla

Celda C 20 Valor del inmueble

Celda C 21 Cantidad a integrar inicialmente

Celda C 22 Tasa de interés nominal

Celda C 23 Plazo en años

Celda C 24 Tasa en concepto de la formalización del préstamo

Celda C 25 Comisión por cancelación anticipada ( en porcentaje)

Celda C 26 Prepagable 1- Poapagable 0

Celda C 27 Valor futuro del inmueble

Celda C 28 Números de pago a realizar cada año ( 12, 6, 4, 1 )

Salida de Datos

( Se ocultaron filas para disminuir la extensión de la planilla)