Una operación de Leasing se contrata para financiar unas máquinas cuyo precio es de 500.000 €, a 15 años, con cuotas anuales prepagables y el valor residual es tal que se pagaría una cuota más un año más tarde para liquidar la operación. El tanto anual es del 8,0% y el IVA del 18%. Calcular la cuota de amortización asociada al valor residual, esto es, la última cuota de amortización.
Nos encontramos ante otro tipo de de operaciones de arrendamiento financiero.
En este caso la operación es prepagable y el VR (valor residual) se abona un año después de pagada la última anualidad, y es del mismo importe que una anualidad normal.
La forma de comenzar el cuadro difiere de la habitual, ya que es prepagable. Lo primero que se hace es destinar a amortización la primera anualidad ya que no ha dado tiempo a que se devenguen intereses. La cuota de intereses del año cero es cero, y por tanto todo lo que se paga se destina a amortizar el capital. Esto hace que el capital vivo en cero, no sean los 500.000 €, sino que a ese importe se ha de minorar con la primera anualidad, ya que todo los que se paga inicialmente se destina a amortizar.
Sea una operación de Leasing de principal 100.000 €, y duración 10 años, con términos anuales pospagables. El valor residual es del mismo importe que una anualidad y se pagaría justo al final del año 10. Valorar al 8,0% anual. Calcular la anualidad del primer año.
Nos encontramos ante una operación de Leasing, o arrendamiento financiero. Vea lo que dice sobre este tema la Wikipedia.
El Leasing se puede ofertar de diferentes formas. En este caso se trata de una operación anual pospagable donde el valor residual (VR) es del mismo importe que una anualidad, y se paga junto con la anualidad última. Esto hace que el último pago en t=10 años sea de importe 2a.
La clave está en la celda F11, que es:
=C11/VA(C13;C12;-1;-1)
Hemos de tener cuidado con los signos menos. Una alternativa la podemos ver en la celda F12.
Sea un préstamo a tipo variable con las siguientes características:
* Principal 900.000 euros
* Duración 12 años
* Tipo variable: Euribor + 0,75%, con revisión anual.
* El Euribor el primer año ha sido del 2% y luego ha crecido 0,1% cada año.
Al final del año 8 se entregan 100.000 euros, y al final del mes 122 se entregan 50.000 euros, en concepto de amortización anticipada.
Calcular la mensualidad del mes 144.
Método 1
Hemos creado un nombre de rango. Se denomina tabla y es el rango: L23:O34.
La celda C30 se copia hacia abajo y es:
=C18+1
La celda D18 es:
=BUSCARV(C18;tabla;4)
siendo el rango tabla el L23:O34. Si no nos gusta nombrar rango debemos cambiar la fórmula anterior por la siguiente:
=BUSCARV(C18;$L$23:$O$34;4)
sin olvidar los dólares, ya que esta expresión se ha de copiar hacia abajo.
La mensualidad es E18:
=PAGO(D18;144-B17;-H17)+J18
observar que se copia hacia abajo y va sin dólares.
Los intereses también van sin dólares. La celda F18 es:
=H17*D18
Método 2
El método 2 no necesita utilizar BUSCARV para obtener la columna del tipo de interés mensual.
BuscarV
La función BUSCARV se utiliza para hacer una búsqueda vertical en una tabla. Revisa este Post:
En nuestro caso la utilizamos para obtener la columna del tipo de interés mensual correspondiente a cada año. Para ello se hace una tabla con el Euribor de cada año, el Euribor más el diferencial, y el Tanto mensual equivalente. Esta tabla del Euribor hemos de nombrarla, con un nombre de rango.
Si ves que te da error, posiblemente sea porque no has nombrado el rango, que en este caso se llama: tabla
El rango tabla es: L23:O34
Así la fórmula de la celda D139 te funcionará bien:
=BUSCARV(C139;tabla;4)
Si no te gusta nombrar rangos puedes cambiar la fórmula anterior por esta:
=BUSCARV(C139;$L$23:$O$34;4)
No olvides los dólares, ya que tendrás que copiar esta fórmula a toda su columna, para obtener la tasa mensual de todos los periodos.
Pregunta
Una pregunta operativa,
Si le doy un nombre a un conjunto de celdas (tabla), igual que hacemos con la tabla de
intereses, y veo que me he equivocado, ¿cómo puedo quitar el nombre a esa tabla?.
Supongamos que ya he guardado el fichero y no puedo deshacer la operación.
Respuesta
Si tienes la versión 2007, para gestionar los nombres de rango debes ir a:
Fórmulas
Administrador de nombres
Donde tienes las opciones: Nuevo, Editar y Eliminar
Si tienes la versión de Excel 2003 o anteriores, puedes llegar a una ventana similar a ésta, donde podrás gestionar los nombres de rango. Está en: Insertar, Nombre, Definir.
Sea un préstamo a tipo fijo con las siguientes características:
* Principal 700.000 euros
* Duración 8 años
* TIN 6%
Al final del mes 30 se entregan 100.000 euros, en concepto de amortización anticipada que permite reducir la duración del préstamo. Calcular la última mensualidad.
Se elabora el cuadro de amortización con la mensualidad inicialmente calculada. Al final del mes 30 se añaden los 100.000 euros en concepto de amortización anticipada. Para considerar este aporte adicional hemos creado una columna adicional y la fórmula de la mensualidad suma el importe de esta columna. Así, la celda D17 es:
=PAGO($L$19;$L$17;-$L$15)+I17
Esta fórmula se copia hasta el final de su columna.
Al añadir los 100.000 euros de amortización anticipada observamos que al final comienzan a producirse capitales vivos negativos. Eliminamos todas las filas donde se encuentran los capitales vivos negativos, salvo la primera de ellas. En este caso resulta ser la fila del mes 82.
Ahora debemos ajustar los valores del mes 82 para lo cual hacemos coincidir la cuota de amortización A82 con el capital vivo del periodo anterior. Por tanto, la celda F98 es:
=+G97
Finalmente hacemos que D98 sea:
=+F98+E98
ya que la última mensualidad debe cumplir, al igual que todas las demás, que es igual a la Cuota de Intereses más la Cuota de Amortización.
Observar como al efectuarse el pago de los 100.000 € en concepto de Amortización Anticipada no se modifica la mensualidad posteriormente. Sigue siendo de 9.199 €.
Al no modificarse la mensualidad lo que sucede es que se acorta la duración del préstamo. Ya no es necesario ir hasta los 96 meses para amortizar completamente el préstamo. Observamos que en el mes 82 conseguimos obtener un capital vivo incluso negativo. Por eso es necesario ajustar el último mes. Para ello hacemos que la última cuota de amortización coincida con el capital vivo del mes 81. Esto es, hacemos iguales las dos celdas verdes.
Finalmente ajustamos la celda de color naranja que es la última mensualidad y hacemos que sea igual a la suma de la cuota de intereses más la cuota de amortización.
Veamos las fórmulas del cuadro de amortización. La clave está en la fórmula del término amortizativo.
Método 2
En la celda F116 calculamos la mensualidad constante a, con la función PAGO.
En F117 calculamos el Capital Vivo C30 por el método prospectivo, con la función VA.
Si quisiéramos utilizar el método retrospectivo la expresión empleada debiera ser la de la celda G127:
=+C116*(1+C120)^30-VF(C120;30;-F116)
En F119 calculamos n con la expresión:
=+NPER(C120;F116;-F118)
Tomamos la parte entera que es 51.
Calculamos el Capital Vivo en t=51, que es C51, por el método retrospectivo. Eso se calcula en la celda F21 con la siguiente expresión:
=+F118*(1+C120)^F120-VF(C120;F120;-F116)
La última mensualidad a52, en F122, se obtiene capitalizando el capital vivo anterior.
Vídeo
.
Para calcular n hemos de despejar tomando logaritmos. Veamos cómo se consigue despejar.
Sea un préstamo variable en progresión geométrica con las siguientes características:
* Duración 20 años
* Términos anuales
* Principal: 500.000 euros
* Tipo fijo: 10% anual
* Los términos se incrementan un 4% anual acumulado
Calcular la última cuota de amortización.
Se trata de un préstamo en progresión geométrica anual de 20 años de duración. Nos piden la última cuota de amortización, que por ser la última coincide con el capital vivo del periodo n-1. Esto siempre sucede, y en nuestro caso es:
C19 = A20
Eso es así, por tratarse del último periodo. Ver el gráfico siguiente.
Por ser un préstamo de 20 años, el capital vivo en t=19, que es C19, se puede calcular descontando un año al 10%, el importe del último término amortizativo que es: aq^19. Por tanto será:
C19=aq^19/1,1
La fórmula anterior se obtiene observando el gráfico previo.
Observar en la imagen cómo se obtiene la primera anualidad a:
=F22/vageo(1;C19;C18;C17)
La anualidad segunda y siguientes se obtienen multiplicando reiteradamente por la razón q. La celda C24 es:
=C23*$C$19
Y luego se copia hacia abajo.
En un préstamo variable en progresión geométrica puede suceder que el capital vivo al principio aumente, como en este caso. Esto se debe a que inicialmente los primeros términos amortizativos es posible que no cubran siquiera los intereses devengados.
Experimento
Si en la razón (celda C19) indicamos el valor 1, estaríamos ante un préstamo en el que los términos amortizativos no se incrementan, permaneciendo constantes durante toda la vida del préstamo. Esto es lo que conocemos como préstamo francés. Por tanto, un préstamo geométrico, a tipo fijo, cuya razón fuera uno sería un préstamo francés, ya que tendría los términos amortizativos constantes.
Método 2
Pasos:
Calculamos la primera anualidad a: =C47/vageo(1;C49;C46;C48)
Calculamos la última mensualidad: aq^19
Calculamos el capital vivo C19: =F47/(1+C48)
Calculamos la última cuota de intereses I20: =F48*C48
Por diferencias, obtenemos la última Cuota de Amortización A20: =F47-F49
Método 3
Es un método curioso que calcula la primera anualidad a sin necesidad de utilizar la función programada VAgeo. Utilizaremos Solver.
Pasos:
En la celda verde (F60) nos inventamos un valor de a, por ejemplo, 50.000
Creamos una tabla con los Flujos de Caja de la operación. La celda C56 es esta: =$F$60*$F$57^(B56-1). Y luego se copia hacia abajo.
En F66 calculamos la TIR. =TIR(C55:C75)
Pedimos a Solver que calcule la celda verde, haciendo que la celda azul sea del 10%.
Si todo ha ido bien ya tenemos calculado el valor de la primera anualidad en la celda verde (F60).
Ahora se han de seguir los pasos del método anterior hasta llegar a lo que nos piden.
Añadimos un par de ceros más a la precisión.
Método 4
Podemos resolver este problema de una forma similar a la utilizada en el método 3 pero en este caso sin la necesidad de utilizar Solver, ni Buscar Objetivo, y sin utilizar la función VAgeo.
Se trata de añadir una columna auxiliar a la tabla en la que partiendo de un euro montemos una renta geométrica que dura lo mismo que la que estamos estudiando y que varia según la misma razón q.
La clave de este método está en la celda G85.
=500000/VNA(G81;D81:D100)
Lo que hacemos es dividir el principal entre el valor actual de una renta geométrica de primera cuantía 1 euro, duración n, y tipo de interés i.
Sea un préstamo variable en progresión geométrica y fraccionado con las siguientes características:
* Duración 15 años
* Términos mensuales
* Principal: 980.000 euros
* Tipo fijo: TIN 6%
* Los términos se incrementan un 5% anual acumulado
Calcular la última cuota de amortización.
D27 nos da la primera mensualidad, y se copia a todas las del primer año:
=$G$26/vageo(VF($C$21;12;-1);$C$23;$C$18;$C$22)
D39 nos da la primera mensualidad del segundo año, y se copia al resto de la columna:
=D27*$C$23
Método 2
La celda F210 calcula la primera anualidad:
=C212/vageo(1;C216;C210;C215)
Esto es suponiendo que el préstamo fuera de periodicidad anual.
En F211 calculamos la primera mensualidad. Para ello tomamos la anualidad y la fraccionamos en 12 meses. Para ello NO dividimos entre 12, ya que hemos de considerar el efecto financiero, y la anualidad esta valorada al final del primer año, y nosotros deseamos calcular la mensualidad constante durante los 12 meses del primer año. Para ello, utilizamos la función PAGO, dejando vacío el lugar correspondiente a va y usando el lugar de vf para poner en él la anualidad:
=PAGO(tasa;nper;va;vf;tipo)
=PAGO(C214;12;;-F210)
En F212 calculamos aq^14.
En F213 calculamos la última Cuota de Amortización A180. Para ello, simplemente descontamos un mes la última mensualidad:
=F212/(1+C214)
Método 3
Este método calcula la primera mensualidad sin utilizar la función VAgeo, utilizando Solver.
Pasos:
En la celda verde (C228) nos inventamos un valor para la primera mensualidad, por ejemplo 9.000
Aunque sabemos que el préstamo es mensual, podemos trabajar suponiendo que fuera anual. Creamos una tabla con las anualidades del préstamo. La celda F222, que luego se copiará hacia abajo, es: =VF($C$224;12;-$C$228)*$C$226^E221
En la celda azul (C230) calculamos el VAN: =VNA(C225;F222:F236)
Pedimos a Solver que calcule la celda verde y que para ello logre que la celda azul sea el principal del préstamo
Una vez calculada la primera mensualidad se calcula la última: aq^14
Y finalmente, descontando la última mensualidad durante un mes se obtiene C179 que coincide con A180.
Método 4
Este método no utiliza Solver, ni Buscar Objetivo, ni utiliza la función VAgeo.
La clave de este método está en la celda C249, donde calculamos la primera mensualidad dividiendo el principal entre el valor actual de una renta variable en progresión geométrica de 15 años de duración, razón q=1,05, y primera cuantía 1 euro.
