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.
Ver el ejemplo 12 del fichero prestamos.xlsm
Método 1
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.
Vídeo
Una vez que sabemos que a=6068,7164 , que a14= 12015,6505 , ¿ Cómo llegamos a c179?
ResponderEliminarHola Javier.
EliminarLa mensualidad del último año podemos llamarla a15 y es igual a a*q^14. Se eleva a n-1 por el desfase que siempre se observa en las geométricas.
a15 = a*q^14 = 12.015,64645
Para calcular C179 nos basamos en el gráfico que se muestra en el post y vemos que, por ser el último periodo se ve que descontando a15 durante un mes, al tipo i12, llegamos a obtener C179.
C179 = a15/(1+i12)
Un saludo.
Entiendo como se calcula la anualidad, pero ¿como se calcula a?
ResponderEliminarHola Reiixel.
EliminarSi ya tenemos calculada la anualidad y deseamos calcular la mensualidad equivalente, lo que hemos de plantear es que las doce mensualidades valoradas a final de año han de ser igual a la anualidad que vence justo al final de ese año.
a = mensualidad
Anualidad = a * VF(i12;12;-1)
despejando
a= Anualidad / VF de una renta unitaria POS de 12 meses valorada al tanto i12
Un saludo.
Hola, ¿cómo sería el procedimiento para hacerlo a mano?
ResponderEliminarHola Sara. Ya está.
EliminarHola Adolfo, es este ejercicio todas las mensualidades son distintas no? No entiendo por qué una vez sabemos la cantidad a que multiplicamos por q^14 se descuenta un mes; cómo sabemos que "axq^14" es el ultimo mes y no la cifra mensual que se paga el ultimo año?
ResponderEliminarHola Javier.
EliminarEn el gráfico se puede ver que durante los 12 meses de cada año la mensualidad es constante y que se incrementa un 5% acumulado al pasar al siguiente año.
Cada una de las mensualidades del año 1 son de importe: a
Cada una de las mensualidades del año 2 son de importe: a*q
Cada una de las mensualidades del año 3 son de importe: a*q^2
Cada una de las mensualidades del año 4 son de importe: a*q^3
... .... .... ...
Cada una de las mensualidades del año 15 son de importe: a*q^14
Observa el desfase entre el año y el exponente de la razón q.
La última mensualidad es de importe a*q^14. Si se descuenta un mes al tanto i12 correspondiente se obtiene el capital vivo C179.
C179 = a*q^14 / (1+i12)
Y en el gráfico que se muestra en el método 2 puedes ver que C179 = A180.
Un saludo.
Buenas Adolfo, me preguntaba porque elevas a 14 en la celda C53 del método 4
ResponderEliminarUn saludo
Hola.
EliminarEn las rentas variables en progresión geométrica pospagables existe un desfase entre el exponente al que se ha de elevar la razón y el número de periodos n.
En t=1 la cuantía es C
En t=2 la cuantía es C*q
En t=3 la cuantía es C*q^2
En t=4 la cuantía es C*q^3
En t=5 la cuantía es C*q^4
.... / ....
En t=15 la cuantía es C*q^14
Un saludo.