Excel financiero (página 2)

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/p
pfontLa incorporación en la columna A son datos fijos, en el resto de las columnas se irán incorporando las formulas que permitan realizar en forma automática los cálculos respectivos./font/p
pfontemDesarrollo/em/font/p
pfontCelda bD14= E9/b/font/p
pfontCelda bG14= E10/b/font/p
pfontCelda bJ14= E11/b/font/p
pfontCelda bB15= SI(D140;-B9;" ")/b/font/p
pfontEn la formula se establece que si la celda D14 donde se traslada el interés devengado, es mayor que cero (>0), es decir se aplica interés, a la celda B15 se traslada la inversión mensual con signo negativo, en caso contrario, si no se fija interés se anula la operación.

Celda B16= B15 trasladando hasta la celda B26

Celda B28= 1 ( indica que la inversión se realiza el primer día del mes que corresponde.

CeldaB29=SI(ESERROR(VF(D14/12;A26;B15;B27;B28));" ";

VF(D14/12;A26;B15;B27;B28))

En este caso se aplica la combinación de 3 funciones, lógica SI, de información ESERROR y financiera VF

Cómo la planilla calcula en forma automática tomando los datos de la planilla de entrada de datos, en caso de no incorporarse interés a devengar, la columna B de importes queda en blanco, produciéndose en la celda B29 el valor de error tipo #¡VALOR!

Empleándose para solucionar el problema la función ESERROR, que considera Verdadero este tipo de error.

La función SI, se aplica, teniendo en cuenta que si se produce el error, coloque celda vacía (""), caso contrario, se aplique la función VF.

Surgiendo por lo tanto una combinación de tres funciones.

Con referencia a la columna C, que desde la celda C15 hasta la celda C26, calcula el acumulado mensual, de no encontrare valores en la columna B, también produce el valor de error #¡VALOR!. En este caso se emplea la misma combinación de funciones pero por periodos.

Celda C15= SI(ESERROR(VF($D$14/12;A15;B15;$B$27;$B$28));" ";

VF($D$14/12;A15;B15;$B$27;$B$28))

Esta formula debe trasladarse hasta la celda C26, juntamente en esa celda el acumulado coincide con la celda B29

Idéntico criterio se produce con el resto de las columnas que integran el 2° y 3° año.

2° Año

Celda E15= SI(G14>0;B15;" ")

Celda E16= E15 trasladar hasta la celda E26

Celda E27= SI(G14>0;-B29;" ")

En este caso también se aplico la función SI, donde establece que si existe interés a devengar, traslade a esa celda el valor de la celda B29, (calculo de VF del 1° año) para ser incorporado como Va en el 2° año.

Celda E28= B28 En este caso se traslada el criterio adoptado de inversión a partir del primer día del mes correspondiente.

CeldaE29=SI(ESERROR(VF(G14/12;A26;E15;E27;E28));" ";

VF(G14/12;A26;E15;E27;E28))

Celda F15= SI(ESERROR(VF($G$14/12;A15;E15;$E$27;$E$28));" ";

VF($G$14/12;A15;E15;$E$27;$E$28))

Esta formula debe trasladarse hasta la celda F26, juntamente en esa celda el acumulado coincide con la celda E29

3° Año

Celda E15= SI(J14>0;E15;" ")

Celda H16=H15 trasladar hasta la celda H26

Celda H27= SI(J14>0;-E29;" ")

Celda H28= E28

CeldaH29=SI(ESERROR(VF(J14/12;A26;H15;H27;H28));" ";

VF(J14/12;A26;H15;H27;H28))

Celda I 15=SI(ESERROR(VF(J14/12;A15;H15;H27;H28));" ";

VF(J14/12;A15;H15;H27;H28))

Esta formula debe trasladarse hasta la celda I 26, juntamente en esa celda el acumulado coincide con la celda H29

Función VF.PLAN

Calcula el valor futuro de un capital inicial de aplicar una serie de tasas de interes compuesto.

Se emplea VF.PLAN para calcular el valor futuro de una inversion con tasa variable o ajustable.

Sintaxis VF.PLAN(capital;plan_serie_de_tasas)

Capital: es el valor presente

Plan_serie_de_tasas : es una matriz con las tasas de interes que se aplican

Ejemplo

Se realiza una inversion de $ 100.000. Durante el 1° año, la tasa sera de 7,50%, durante el 2° de 9,50%, durante el 3° de 10,50% y durante el 4° de 8,50%. La funcion en la celda B7 calcula el capital acumulado al cabo de los cuatro años.

Celda B7=VF.PLAN(B1;B3:B6)

Función VNA

Calcula el valor neto presente de una inversión a partir de una tasa de descuento y una serie de pagos futuros (valor negativo) e ingresos (valores positivos)

Sintaxis VNA(tasa;valor 1; valor 2;…….)

Tasa: es la tasa de descuento durante un periodo

Valor 1; valor 2….. son de 1 a 29 argumentos que representan los pagos e ingresos.

Valor 1; valor 2.. deben tener la misma duración y ocurrir al final de cada periodo.

VNA usa el valor 1; valor 2; …. para interpretar el orden de los flujos de caja. Deberá introducirse los valores de pagos y de los ingresos en el orden adecuado.

Los argumentos que consisten en números, celdas vacías, valores lógicos, se cuentan, los argumentos que consisten en valores de error o texto que no se pueden traducir a números se pasan por alto.

Observaciones

La inversión VNA comienza un periodo antes de la fecha del flujo de caja de valor 1 y termina con él ultimo flujo de caja de la lista. Él calculo VNA se basa en flujos de caja futuros. Si el primer flujo de caja ocurre al inicio del primer periodo, el primer valor se deberá agregar al resultado VNA, que no se incluye en los argumentos valores.

Ejemplo

En el primer caso, (columna B) se considera una inversión que comienza al principio del periodo.

La inversión se considera de $ 35.000 y se espera recibir ingresos durante los seis primeros años. La tasa de descuento anual es de 7,50%

En la celda B9 se obtiene el valor neto actual de la inversión.

Celda B9=VNA(B1;B3:B8)+B2

No se incluye el costo inicial de $ 35.000 como uno de los valores porque el pago ocurre al principio del primer periodo.

Segundo caso (columna D)

Se considera una inversión de $ 20.000 a pagar al final del primer periodo y se recibirá ingresos anuales durante los próximos cinco años

Suponiendo una tasa de descuento anual del 8,50%, en la celda D9 se calcula el valor actual de la inversión.

Celda D9= VNA(D1;D2:D7)

Función VNA.NO.PER

Calcula el valor neto actual para un flujo de caja que no es necesariamente periódico.

Sintaxis VNA.NO PER(tasa;valores;fechas)

Tasa: es la tasa de descuento que aplica a los flujos de caja.

Valores: es una serie de flujos de caja que corresponde a un calendario de pagos determinado por el argumento fechas.

El primer pago es opcional y corresponde al costo o pago en que se incurre al principio de la inversión. Todos los pagos sucesivos se descuentan basándose en un año calendario de 365 días.

Fechas: es un calendario de fechas de pago que corresponde a los pagos del flujo de caja. La primera fecha de pago indica el principio del calendario de pagos. El resto de las fechas deben ser posteriores a esta, pero pueden ocurrir en cualquier orden.

Ejemplo

1° caso rango A1:B10

Se considera una inversión que requiere 2 pagos en efectivo el 5/08/00 y el 5/09/00, y un retiro en 6 pagos con fechas irregulares.Suponiendo que los flujos de caja se descuentan al 6,5%, el valor actual se calcula en la celda B10

Celda B10=VNA.NO.PER(B1;B2:B9;A2:A9)

2° caso rango D1:E10

Se plantea una inversión que requiere un pago en efectivo el 1/01/00 y un retiro en cinco pagos con fechas irregulares.

Suponiendo que los flujos de caja se descuentan al 5,30% el valor neto actual se calcula en la celda E10

Celda E10=VNA.NO.PER(E1;E2:E7;D2:D7)

En este caso el resultado del valor actual neto es negativo. Teniendo en cuenta que el valor negativo representa dinero que se deposita y valor positivo dinero que se retira, un valor actual neto negativo, como en este caso, indica que la inversión no es conveniente a la tasa dada

EJERCICIO PRACTICO

FONDO DE INVERSION

Ejemplo practico de fondo de inversión, donde el ingreso y egreso de montos se produce en forma mensual.

El presente trabajo permite ingresar y extraer montos, desarrollando su comportamiento a través de una planilla con un máximo de 20 años.Se fijo 20 años, simplemente por problemas de extensión, pudiendo en caso de ampliación trasladar las formulas respectivas. En la planilla se aplico una combinación de funciones que permite en caso de optar el desarrollo de la misma a menos años, no produzca errores por celdas vacías.

El ejercicio en cuestión consiste en realizar un aporte de inversión en forma mensual de $ 60 a una tasa anual del 11,50%, durante 15 años y producir un retiro mensual del monto acumulado, durante 20 años a igual tasa de rendimiento.

Considero el ejercicio en su fas practica, interesante por cuanto permite desarrollar mediante una combinación de funciones, al incorporar las mismas, la planilla en blanco sin carga e ir testando en la misma dentro de los limites impuestos.

Desarrollo

Planilla de ingreso de datos y cálculos totales

Se desarrollo dentro del Rango A1:B7

Celda B1 se fija el aporte mensual en este caso $ 60,00

Celda B2 , tasa anual en este caso 11,50%

Celda B3 se fija la cantidad de años de aporte de capital en este caso 15 años

Celda B4= VF(B2/12;B3*12;B1;0)

Como se puede observar se aplico la función que calcula el valor futuro de una inversión. Se opto por efectuar los aportes al final del periodo es decir Tipo 0

Celda B5 se fija el periodo de retiro de fondos, en este caso 20 años

Celda B6 contiene el rendimiento del monto acumulado, se adopto el mismo que el de aporte es decir 11,50%

Celda B6= SI(ESERROR(PAGO(B6/12;B5*12;B4;0));"";PAGO(B6/12;B5*12;B4;0))

En este caso se aplico para calcular la cuota de retiro mensual la función PAGO, pero se la combino con las funciones lógica SI y de información ESERROR a efectos de no producirse el error tipo #¡DIV/0!, en caso de no completarse el dato correspondiente a la celda B5, cantidad de años de retiro. La función ESERROR reconoce el error de ese tipo como verdadero y en ese caso coloca celda vacía.

Cabe aclarar que la cuota calculada corresponde al retiro a fin de cada periodo es decir tipo 0

Planilla de desarrollo del monto acumulado por periodo

El desarrollo se efectúo para un periodo de 20 años y se encuentra en el rango A9:B249

En la columna A se incorpora en forma automática cada periodo y en la columna B el monto que se acumula en el periodo. Se mantuvo la totalidad de las filas sin ocultar ninguna, a efectos de que el interesado pueda observar la variación por periodos, como también verificar otros datos que se extraerán de la misma, que se verán en próximos pasos.

Celda A10= SI(Y($B$1>0;($B$3*12)>0);1;"")

Se a introducido dentro de la formula las funciones lógicas SI e Y. La función Y anidada dentro de la SI establece como condición que si las celdas B1 y B3 de la planilla de ingreso de datos no fueron cumplimentadas, coloque celda en blanco, caso contrario él numero 1 inicio del periodo.

Celda B10=SI(A10="";"";VF($B$2/12;A10;$B$1;0))

En este caso se introduce la función lógica SI para establecer que en el caso que la celda A10 este vacía, coloque la celda B10 en blanco, caso contrario, se aplica la función VF para el primer periodo solamente, que como se estableció al principio, corresponde al fin del periodo Tipo 0, por lo tanto no acumula interés.

Celda A11=SI(ESERROR(SI(Y($B$1>0;(A10+1)0;(A10+1)0;($B$5*12)>0);1;0)

Celda E10=SI(ESERROR((-VF($B$2/12;$B$3*12;$B$1;0)*(1+($B$6/12)))-($B$7*D10));"";

(-VF($B$2/12;$B$3*12;$B$1;0)*(1+($B$6/12)))-($B$7*D10))

Se introdujo la función VF con numero negativo para obtener montos de disminución por periodo positivo.

Celda F 10= SI( ESERROR(100%*(-B10-B1)/$B$1);""; 100%*(-B10-B1)/$B$1)

Celda G 10= SI(ESERROR(SI(E10="";"";((-$B$4-E10)/$B$4)));"";

SI(E10="";"";((-$B$4-E10)/$B$4)))

Celda D11= SI(Y($B$1>0;(D10+1)vencto, o si la fecha de vencto es posterior en mas de un año a la liquidación, la función devuelve el valor de error #¡NUM!

Ejemplo

El 31 de marzo de 2002 se hace una compra de un bono que vence el primero de junio del mismo año.

El bono se emite a una tasa de descuento del 9,10%.

La función en la celda B5 calcula la tasa de interés equivalente a una letra de tesorería.

Celda B5=LETRA.DE,TES.EQV.A.BONO(B1;B2;B3)

Función LETRA.DE.TES.PRECIO

Calcula el precio por 100$ de valor nominal de una letra de tesoreria.

Sintaxis LETRA.DE.TES.PRECIO(liq;vencto;descuento)

Liq es la fecha de liquidación del valor bursátil. La fecha de liquidación del valor bursátil es la fecha posterior a la fecha de emisión, cuando el comprador adquirió el valor bursátil.

Vencto es la fecha de vencimiento del valor bursátil. La fecha de vencimiento es cuando expira el valor bursátil.

Descuento es la tasa de descuento de la letra de tesorería.

Ejemplo

El 31 de marzo de 2002 se hace una compra de un bono que vence el primero de junio del mismo año.

El bono se emite a una tasa de descuento del 9,10%.

La función en la celda B5 calcula el precio equivalente a una letra de tesoreria por cada $ 100 de valor nominal.

Celda B5=LETRA.DE,TES.PRECIO(B1;B2;B3)

Función LETRA.DE.TES.RENDTO

Calcula el rendimiento de una letra de tesoreria

Sintaxis LETRA.DE.TES.RENDTO(liq;vencto;precio)

Liq es la fecha de liquidación del valor bursátil. La fecha de liquidación del valor bursátil es la fecha posterior a la fecha de emisión, cuando el comprador adquirió el valor bursátil.

Vencto es la fecha de vencimiento del valor bursátil. La fecha de vencimiento es cuando expira el valor bursátil

Precio es el precio de la letra de tesoreria por cada 100$ de valor nominal.

Ejemplo

El 31 de marzo de2002 se pagan $ 98,15 por $ 100 de valor nominal de un bono que vence el primero de Junio del mismo año.

La funcion en la celda B5 calcula el rendimiento de esta operación equivalente a una letra de tesoreria.

Celda B5=LETRA.DE,TES.RENDTO(B1;B2;B3)

Autor:

Prof. Felix Gutierrez Aguado