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