Exel financiero (página 3)

Desarrollo

Columna A – N° de Cuotas- Celda A 33= SI(1<=(C 23*C 28);1;0). Limita que la primer cuota se establezca al introducir el plazo respectivo en el ingreso de datos, en caso contrario devuelve valor cero.

Celda A 34 = SI(A 33=0;0;SI(1+A 33<=($A 23*$C$28);1+A 33;0) En ambos casos se aplico la función lógica SI, en este ultimo caso limita el numero de cuotas, al total que surge de multiplicar los años de plazo con la periodicidad

Función para ser trasladada al resto de la columna

Columna B –Fecha de vencimiento de las cuotas. En la primer celda en este caso B33, se introduce la fecha de vencimiento de la primer cuota. En la celda B34 se emplea la función SI donde se considera la celda anterior y la cuota correspondiente, combinándose con la función Fecha.Mes que determina el mes siguiente.

Celda B34= SI(B33=0;" ";FECHA.MES(B33;1)))

En la celda siguiente, para trasladar al resto de la planilla varia solamente ,cero por " ".

Celda B35= SI(B34=" ";" ";SI(A35=0;" ";FECHA.MES(B34;1)))

Esto permite que al colocar la fecha de la primer cuota, el resto de las fechas se ubiquen automáticamente, hasta la cuota correspondiente

Columna C –Principal. Función a colocar en la celda C424, para ser trasladada al resto de la columna

CeldaC33=SI(ESERR(PAGOPRIN($C$22/$C$28;A33;$C$23*$C$28;$C$20-$C$21;$C$27;$C$26));0;(PAGOPRIN($C$22/$C$28;A33;$C$23*$C$28;$C$20-$C$21;$C$27;$C$26)))

Calcula la cantidad de cuota correspondiente a amortización en cada periodo. En este caso se combinan tres funciones, SI, ESERR, y PAGOPRIN, condicionando la función SI que a través de la función ESERR coloque cero en caso de no existir numero de cuota.

Columna D – Intereses : Función a colocar en la celda D33, para ser trasladada al resto de la columna

CeldaD33=SI(ESERR(PAGOINT($C$22/$C$28;A33;$C$23*$C$28;$C$20-$C$21;$C$27;$C$26);0;(PAGOINT($C$22/$C$28;A33;$C$23*$C$28;$C$20-$C$21;$C$27;$C$26)))

Calcula que cantidad de cuota corresponde a intereses en cada periodo. El mismo criterio del caso anterior combinando las funciones SI, ESERR y PAGOINT

Columna E –Cuota: Función a colocar en la celda E33 para ser trasladada al resto de la columna

Celda E33 = SI(A33=0;0;PAGO($C$22/$C$28;$C$23*$C$28;$C$20-$C$21;C$27;$C$26))

Calcula la cuota por periodo, siendo el mismo monto para todos. Idéntico resultado se obtiene, sumando los montos obtenidos en las dos columnas anteriores. (PAGOPRIN+PAGOINT). En este caso se utilizo otra variante, empleando la función lógica SI, condiciona que el resultado en cero a igual valor de la cuota

Columna F –P.pal pdte: Función a colocar en la celda F33 para ser trasladada al resto de la columna

Celda F 33 = SI($C$20-$C$21+SUMA($C$33:C33)<=0;" ";$C$20-$C$21+SUMA($C$33:C33)

Consiste en la cantidad del préstamo pendiente de amortización. Mediante la función SI, se condiciona que si la cantidad de préstamo pendiente de amortización es <=0, de cómo resultado, " " ( celda en blanco)

Columna G – Comis cancel : Función a colocar en la celda G33 para ser trasladada al resto de la columna

Celda F 33 = SI(F33 =" ";" ";F33*$C$25). Comisión a cobrar por la entidad financiera en caso de querer cancelar anticipadamente el préstamo concedido. La función SI condiciona a valor de celda en blanco en caso que la celda de prestamos pendientes de amortizar se encuentre en blanco

Columna H – Coste cancel: Función a colocar en la celda H33 para ser trasladada al resto de la columna

Celda H 33 = SI(ESERROR(F33+G33); " ";(F33+G33)

Valor de rescate del préstamo para los periodos de la vida del mismo. En caso de que se produzca la suma de dos celdas en blanco, el valor resultante seria #¡VALOR!, ese es el motivo de optar con la combinación de funciones SI y ESERROR, esta ultima considera ese error como verdadero, condicionando con la función SI que en ese caso deje la celda en blanco ;" ";

Informe Adicional

Análisis de la amortización e intereses dentro de periodos establecidos Inicial- Intermedio- Final

Amortización Acumulada entre dos periodos

Intereses Acumulados entre dos periodos a determinar

Importe de la comisión de apertura

Interés real del préstamo

Amortización: Calculo del total amortizado entre dos periodos

Celda C406= PAGO.PRINC.ENTRE(C22/C28;C23*C28;(C20-C21);C400;C401;C26)

Celda D406= PAGO.PRINC.ENTRE(C22/C28;C23*C28;(C20-C21);D400;D401;C26)

Celda E406= PAGO.PRINC.ENTRE(C22/C28;C23*C28;(C20-C21);E400;E401;C26)

Intereses: Calculo del total de intereses entre dos periodos

Celda C407= PAGO.INT.ENTRE(C22/C28;C23*C28;(C20-C21);C400;C401;C26)

Celda D407= PAGO.INT.ENTRE(C22/C28;C23*C28;(C20-C21);D400;D401;C26)

Celda E407= PAGO.INT.ENTRE(C22/C28;C23*C28;(C20-C21);E400;E401;C26)

Comisión ap: Tasa en concepto de la formalización del préstamo

Celda C408= (C20-C21).C24

Interés real: Calculo del interés efectivo que cobra la entidad financiera

Celda C409= INT.EFECTIVO(C22;C28)

DATOS / TABLA

Análisis del Préstamo Hipotecario empleando la opción TABLA

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

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

Se crea una tabla de montos que varían con respecto al original en un incremento máximo del

20% y una disminución del 15%, con variación en 5%

Colocar en la celda B9=B7

Ejecución

• 1) Seleccionar la columna de montos incluyendo la celda donde esta la formula, en este caso, A9:B17

• 2) Tomar la opción Datos / Tabla

• 3) En la Celda entrada (columna) indicar B1 ( saldo a financiar)

• 4) Aceptar

b) Variación de la cuota para distintos importes y diferentes cantidades de cuotas

Se crea la siguiente tabla, donde en la celda D19 se calcula la cantidad de cuotas, tomando del cuadro inicial (cuadro origen) la cantidad de años y la periodicidad.

Celda D19= B3*B6

En la celda D20 se calcula el valor de la cuota, partiendo de los datos de la planilla inicial (cuadro origen) y de la cantidad de cuotas de la celda D19.

Celda D20 = PAGO(B2/12;D19;B1;B4;B5)

En la celda A23 se escribe una referencia a la formula que calcula la cuota

Celda A23= D20

La variable fila es la cantidad de cuotas, que en la formula aparece como D19.

La variable columna es el importe del préstamo, que en la formula aparece como B1 (cuadro origen)

Ejecución

• 1) Seleccionar el rango A22:G31

• 2) Opcion-Menu-Datos / Tabla

• 3) Donde dice Celda de entrada (fila), indicar D19 donde calcula la cantidad de cuotas.

• 4) Donde dice Celda de entrada (columna) indicar B1, celda donde esta el importe (cuadro origen)

• 5) Aceptar

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

Se confecciono una tabla donde en columna se introduce la cantidad de cuotas que oscila entre 60 y 360.

En la primer fila de la tabla se colocan los interesen que varían entre 9,50% a 7,50% anual. Dentro de esas variables se obtendra el valor de las cuotas.

En la celda A33, se escribe una referencia a la formula que calcula la cuota en la celda D20 de la tabla anterior

Celda A33= D20

La variable fila es la tasa de interés, tomando el valor B2 que corresponde a la planilla inicial (cuadro de origen)

La variable columna es la cantidad de cuotas que en la formula aparece como D19 en la tabla anterior

Ejecución

• 1) Seleccionar el Rango A23:G31

• 2) Menu-Datos / tabla

• 3) Celda de entrada (fila), indicar B2, celda donde esta la tasa de interés en la planilla inicial (cuadro origen)

• 4) Celda de entrada (columna), indicar D19, celda donde calcula la cantidad de cuotas en la tabla anterior

• 5) Aceptar

La celda D19, calculo de la cantidad de cuotas, y celda D20, calculo del valor de la cuota, se realiza por cuanto para ejecutar las dos planillas ultimas, requieren la cantidad de cuotas y él calculo del valor de la cuota, ejecutada directamente sobre la cantidad de cuotas

Buscar Objetivo

Siguiendo con el planteo del préstamo inmobiliario, y partiendo de su pantalla principal

El planteo que se presenta es inverso a lo tratado hasta el momento, es decir la persona solamente puede pagar una cuota de $ 290,50, manteniendo las mismas condiciones del préstamo, se debe resolver entonces cuanto dinero puede pedir prestado.

Excel en este caso dispone de una herramienta Buscar Objetivo

Ejecución

• 1) Tomar la opción Herramientas / Buscar objetivo

• 2) Donde dice Definir la celda, indicar B7, la celda donde esta la cuota calculada

• 3) Donde dice con el valor, colocar –290,50 que es el valor que debe tomar la cuota

• 4) Donde dice para cambiar la celda, indicar B1 que es la celda del importe del préstamo.

• 5) Aceptar

Finalizada la operación se obtendra:

Excel encontró un valor de monto del préstamo que cumple las condiciones

Un préstamo de $ 34.548,20, se puede devolver en 30 años (360 cuotas) de $ 290,50

Amortización de bienes o rentas

Excel dispone de Funciones que permiten calcular las amortizaciones de bienes o rentas que no incluyen calculo de interés.

Las amortizaciones pueden ser iguales en todos los periodos de vida útil del bien, en este caso corresponde Amortización Lineal

Las amortizaciones pueden ser superiores en los primeros años e ir decreciendo en los posteriores a medida que decrece la vida útil del bien, en este caso corresponde Amortización Progresiva

AMORTIZACION LINEAL

Función AMORTIZ.LIN

AMORTIZACION PROGRESIVA

Función AMORTIZ.PROGRE

Función DB

Función DDB

Función DVS

Función SLN

Función SYD

AMORTIZACION LINEAL

Función AMORTIZ.LIN

Calcula la amortización lineal de un bien al final de un ejercicio fiscal determinado, utilizando el método francés de amortización lineal completa.

Sintaxis AMORTIZ.LIN(costo;compra;primer_periodo;valor residual; periodo; tasa;

base_anual)

Costo: es el costo o valor de compra del bien

Compra: es la fecha de compra del bien

Primer_periodo: es la fecha del final del primer periodo

Valor residual: es el valor residual o valor del bien al final del periodo de la amortizacion

Periodo: es el periodo de la amortización

Tasa: es la tasa de amortización

Base: es la base anual utilizada

Base Base para contar días

• 0 360 días (Método NASD)

• 1 Actual

3 365 al año

4 360 al año (Sistema europeo)

Ejemplo

Celda B7=AMORTIZ.LIN(B1;B2;B3;B4;B5;B6;3)

Base anual se toma =3, correspondiente a 365 días al año

Desarrollo de amortizaciones lineales en donde se proporciona el Primer Ejercicio y él ultimo ya que no son regulares.

Función en la celda G10, para ser trasladada al resto de la columna

Celda G10= AMORTIZ.LIN(A10;B10;C10;D10;E10;F10;3)

Partiendo del mismo ejemplo anterior pero considerando un valor residual del 10% del capital es decir $ 6.000

Función en la celda G10, para ser trasladada al resto de la columna

Celda G10= AMORTIZ.LIN(A10;B10;C10;D10;E10;F10;3)

El ejercicio presente la variante 1° caso valor residual es igual a cero

2° Caso Valor residual igual al 10% del capital, en este caso el valor residual se resta de las ultimas cuotas de la amortización.

El total del importe sumado de las amortizaciones totaliza $54.000, que corresponde al valor original del capital menos $ 6.000 del valor residual

*El periodo de origen se denomina periodo 0 y no 1, por cuanto el desarrollo contempla 10 años pero dentro de 11 periodos anuales.

AMORTIZACION PROGRESIVA

Función AMORTIZ.PROGRE

Calcula la depreciación de un activo en el periodo contable especificado, utilizando el método francés de amortización progresiva completa.

Esta función permite calcular cualquier periodo dentro de una amortización progresiva decreciente.

La función es similar a AMORTZ.LIN, excepto que el coeficiente de amortización se aplica al calculo de acuerdo a la vida esperada del bien.

Sintaxis AMORTIZ.PROGRE(costo;compra;primer_periodo;valor residual; periodo; tasa;

base_anual)

Costo: es el costo o valor de compra del bien

Compra: es la fecha de compra del bien

Primer_periodo: es la fecha del final del primer periodo

Valor residual: es el valor residual o valor del bien al final del periodo de la amortización

Periodo: es el periodo de la amortización

Tasa: es la tasa de amortización

Base: es la base anual utilizada

Observaciones: Esta función calcula la amortización hasta él ultimo periodo de vida del bien o hasta que el valor acumulado de dicha amortización sea mayor que el valor inicial del bien menos el valor residual.

Ejercicio

Celda B7=AMORTIZ.PROGRE(B1;B2;B3;B4;B5;B6;3)

Base anual se toma =3, correspondiente a 365 días al año

En el presente ejercicio la variante 1° caso :valor residual es igual a cero

AMORTIZACION PROGRESIVA

Función en la celda G10, para ser trasladada al resto de la columna

Celda G10=AMORTIZ.PROGRE(A10;B10;C10;D10;E10;F10;3)

Partiendo del mismo ejemplo anterior pero considerando un valor residual del 10% del capital es decir $ 6.000

Función en la celda G10, para ser trasladada al resto de la columna

Celda G10=AMORTIZ.PROGRE(A10;B10;C10;D10;E10;F10;3)

El total del importe anual sumado de las amortizaciones totaliza $ 56.570, y no $ 54.000 como en el caso de la amortización lineal, ya que el valor residual también decrece de $ 6.000 a $3.430

Función DB

Calcula la depreciación de un bien durante un periodo especifico usando el método de depreciación de saldo fijo

El método de depreciación de saldo fijo calcula la depreciación a tasa fija.

Sintaxis DB(costo;valor_residual;vida;periodo;mes)

Costo: es el valor inicial del bien

Valor_residual: es el valor al final de la depreciación

Vida: es él numero de periodos durante el cual se deprecia el bien (también conocido como vida útil)

Periodo: es el periodo para el que se desea calcular la depreciación.

Mes: es él numero de meses del primer año, si no se especifica, se asume que es 12

En esta formula no se toma 0(cero). El primer año es el 1

Ejemplo

Celda B5= DB(B1;B2;B3;B4)

Desarrollo de los 10 periodos

Función en la celda E8, para ser trasladada al resto de la columna

Celda E8 = DB(A8;B8;C8;E8)

El total de la amortización corresponde al valor de origen menos el valor residual

Función DDB

Calcula la depreciación de un bien en un periodo especifico mediante el método de depreciación por doble disminución de saldo u otro método que se especifique.

El método de depreciación por doble disminución del saldo calcula la depreciación a una tasa acelerada. La depreciación es mas alta durante el primer periodo y disminuye en los periodos sucesivos.

Sintaxis. DDB(costo;valor_residual;vida;periodo;factor)

Costo:es el valor inicial del bien

Valor_residual: es el valor al final de la depreciciacion

Periodo: es el periodo para el que se desea calcular la depreciación

Factor: es la tasa de declinación del saldo. Si factor es omite, se supondrá que es 2 ( el método de depreciación por doble disminución del saldo)

Celda B5= DDB(B1;B2;B3;B4)

Si se omite Factor, Excel considera =2 (doble depreciación)

Función en la celda F8, para ser trasladada al resto de la columna

Celda F8= DDB(A8;B8;C8;D8)

Como ultima acotación, de la misma manera que obtuvimos la amortización en el primer año podemos obtener al primer día, y primer mes

El mismo Ejemplo anterior pero desarrollando la amortización con Factor=3 triple

Celda B6=DDB(B1;B2;B3;B4;B5)

Función en la celda F9, para ser trasladada al resto de la columna

Celda F9= DDB(A8;B8;C8;D8;E8)

Función DVS

Calcula la amortización de un bien durante un periodo especificado, inclusive un periodo parcial, usando el método de amortización acelerada, con una tasa doble y según el coeficiente que especifique.

Las iniciales DVS corresponden a disminución variable del saldo.

Sintaxis

DVS(costo;valor_residual;vida;periodo_inicial;periodo_final;factor;sin_cambios)

Costo: es el costo inicial de bien

Valor _residual: es el valor residual del bien

Vida: vida útil del bien

Periodo_inicial: es periodo inicial para el que se desea calcular la amortización

Periodo_final: es el periodo final para el que se desea calcular la amortización

Factor: es la tasa a la que disminuye el saldo. Si el argumento factor se omite, se calculara como 2 ( el método de amortización con una tasa doble de disminución del saldo)

Sin_cambios: es un valor lógico que especifica si deberá cambiar el método directo de depreciación cuando la depreciación sea mayor que él calculo del saldo.

Si el argumento sin_cambios se omite Excel cambia el método de depreciación cuando la depreciación es mayor que él calculo del saldo en disminución

Ejemplo 1

En la celda B6 se calcula la amortización desde el periodo 2 al 6

Celda B6= DVS(B1;B2;B3;B4;B5)

El calculo de la amortización por periodo se realizo con la función DDB, en la

Celda F8, para trasladar al resto de la columna.

Celda F8= DDB(A8;B8;C8;D8)

El calculo realizado para el periodo 2 al 6 de $ 2.078,21 en la celda B6, empleando la función DVS, se obtiene en la planilla general por periodos mediante la sumatoria del periodo 3 al 6.

Celda F14= SUMA(F10:F13)

Como la función DVS empieza a sumar desde el año posterior que se indica como "desde", se debe informar desde 2 al 6

La amortización se calcula al omitirse Factor, según el método de doble depreciación, considerando en este caso valor residual igual cero

Ejemplo 2

Partiendo de los datos del ejemplo1, se considera un valor residual de $ 500, suma de periodos 1 a 5, y un factor de depreciación =3

El importe obtenido en la celda B7 de $ 4.575,62 se obtiene aplicando:

Celda B6= DVS(B1;B2;B3;B4;B5;B6)

Corresponde a la suma de los periodos 1 al 5. Como la función empieza a sumar desde el año posterior al periodo que se indica como desde se debe indicar de 0 a 5

Comprobando en la planilla de desarrollo, donde él calculo de la amortización por periodo se realiza con la función DDB, se verifica que:

Como se verifica la celda F13 contiene el valor de $ 4.575,62 como consecuencia de efectuar la suma

Celda F13= SUMA(F8:F12)

Celda F8=DDB(A8;B8;C8;D8;E8) para trasladar al resto de las celdas

Función SLN

Calcula la depreciación por método directo de un bien

Sintaxis SLN(costo; valor_residual;vida útil)

Costo : es el costo inicial del bien

Valor _residual: es el valor al final de la depreciciacion

Vida útil: es él numero de periodos durante el cual se produce la depreciación del bien

Función SYD

Calcula la depreciación por método de anualidades de un bien durante un periodo especifico.(amortización por suma de dígitos de los años aplicados a un valor constante)

Sintaxis SYD(costo;valor_residual;vida útil; periodo)

Costo : es el costo inicial del bien

Valor_residual: es el valor al final de la depreciación

Vida Util: es él numero de periodos durante el cual se produce la depreciación del bien

Periodo: es el periodo al que se quiere calcular

La Celda B5 calcula la amortización para el periodo 3 de 10 años con un valor residual de $ 6.000

Celda B5=SYD(B1;B2;B3;B4)

La celda E8 calcula la amortización para el periodo 1 mediante la formula:

Celda E8= SYD(A8;B8;C8;D8) trasladándose al resto de las celdas de la columna

Realizando él calculo con la función SYD, no calcula para el periodo cero a diferencia de la función AMORTIZ.PROGRE,

Bonos

Funciones aplicables a bonos amortizables con cupones

Sirven para realizar todos los cálculos relativos a rentas, valuación de cupones, plazos etc.

De acuerdo a la metodología de emisión, los bonos pagan un rendimiento y además un cupón de amortización de capital mas los intereses. Estos cupones suelen ser anuales, en ese caso la frecuencia a indicar será 1 ya que es un cupón por año ( semestrales se debe indicar frecuencia =2 o cuatrimestral cuya frecuencia es = 4)

a) Calculo de días de vigencia de un cupón de un bono amortizable

1–Función CUPON.DIAS

2–Función CUPON.DIAS.L1

3–Función CUPON.DIAS.L2

b) Calculo de fechas de vencimiento de cupones

1–Función CUPON.FECHA.L1

2–Función CUPON.FECHA.L2

c) Calculo de la cantidad de cupones pendientes

1–Función CUPON.NUM

d)Funciones referidas a la rentabilidad del bono

1–Función DURACION

2–Función DURACION.MODIF

3–Función INT.ACUM

4–Función INT.ACUM.V

5–Función PRECIO

6–Función PRECIO.DESCUENTO

7–Función PRECIO.PER.IRREGULAR.1

8–Función PRECIO.PER.IRREGULAR.2

9–Función PRECIO.VENCIMIENTO

10–Función RENDTO

11–Función RENDTO.DESC

12–Función RENDTO.PER.IRREGULAR.1

13–Función RENDTO.PER.IRREGULAR.2

14–Función RENDTO.VENCTO

Calculo de días de vigencia de un cupón de un bono amortizable

Función CUPON.DIAS

Calcula él numero de días del periodo (entre dos cupones) donde se encuentra la fecha de liquidación.

Sintaxis CUPON.DIAS(liq; vencimiento;frec;base)

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. Fecha de compra.

Vencimiento: es la fecha de vencimiento del valor bursátil.

Frec: es él numero de pagos de cupones que se pagan por año. Para pagos anuales frec=1

Para pagos semestrales frec= 2, para pagos trimestrales, frec= 4

Base= determina en que tipo de base debe contarse los días.

1 Actual/ actual

• 2 Actual / 360

• 3 Actual / 365

• 4 Europea 20/360

Normalmente se toma base =3 correspondiente a 365 días al año

Observaciones