Fórmulas para calcular un préstamo

Fórmulas para calcular un préstamo

3797

En anteriores entradas hemos visto como calcular un préstamo en Excel de manera sencilla y hemos puesto a vuestra disposición algunas plantillas ya hechas para calcularlo directamente sin tener que confeccionarlas desde cero. También incluimos la posibilidad de añadirle la opción de realizar una amortización anticipada, así como poder establecerle un período de carencia (total y parcial).

Ahora en esta entrada vamos a ver la equivalencia entre las fórmulas matemáticas, que están detrás del cálculo de un préstamo por el sistema francés o también conocido como sistema de termino amortizativo constante y que es el más extendido (existen otros sistemas), y las funciones de Excel.

Lo primero que tendremos que hacer es definir las variables que intervienen en el cálculo. Para realizar un cuadro de amortización tendremos que crear un cuadro con 6 columnas (período, cuota o término amortizativo, cuota de interés, cuota de amortización, capital vivo y capital amortizado).

Ahora veremos qué cálculos hay que seguir para calcular los valores de las variables en cada período.

Cuestiones previas

Antes de nada tenemos que tener en cuenta que tanto la cuota de intereses como los períodos deben ir en consonancia, es decir, si calculamos cuotas anuales el tipo de interés debe ser anual, si la cuota es mensual el tipo de interés debe ser mensual.

Para calcular el número de períodos es sencillo, solo tendremos que multiplicar:

  • Si las cuotas las tenemos que pagar mensualmente, multiplicaremos el número de años por 12
  • Si las cuotas las tenemos que pagar trimestralmente, multiplicaremos el número de años por 4.

Y así para cualquier período de pago, solo hay que subdividir el año en períodos iguales y multiplicarlo por el número de años que vaya a durar el préstamo.

[box style='warning']Ojo con el tipo de interés que usamos[/box]

Para poner en consonancia el tipo de interés que aplicamos con la frecuencia de pago tendremos que hacer algún cálculo más:

Si disponemos del tipo de interés anual efectivo, que denotaremos por “i”, la fórmula que usaremos para calcular el tipo de interés efectivo para el período en cuestión “im(suponemos capitalización compuesta de los intereses, la mayoría de entidades financieras lo calculan simplemente dividiendo el tipo de interés por “m”, el resultado puede resultar similar pero no es equivalente) de la siguiente manera.

  • Im = (1+i)(1/m)-1

Donde “m” se corresponderá con el número de partes en las que hemos subdividido el año o número de pagos que haremos cada año. Esto quedará más claro en la siguiente tabla:

m

Período

1

Anual

2

Semestral

3

Cuatrimestral

4

Trimestral

6

Bimestral

12

Mensual

Este será el tipo de interés que tendremos que utilizar si nuestro préstamo lo pagamos en cuotas mensuales, bimestrales, trimestrales, etc.

Una vez aclarado esto, calcularemos cada una de las columnas del cuadro de amortización de un préstamo.

Cuota o término amortizativo

El término amortizativo en un sistema de préstamo francés es constante, es decir, en cada período pagaremos lo mismo hasta el final del préstamo. La fórmula que emplearemos para calcular la cuota, que denotaremos por “a” es la siguiente:

  • a = C0/(1-(1/(1+i))n)/i)

Las variables de esta fórmula son:

  • C0: Capital inicial (el capital que pedimos prestado)
  • i: Tipo de interés efectivo (tendremos que acordarnos de ponerlo en consonancia a la frecuencia de pago como hemos explicado en las cuestiones previas).
  • n: Corresponde con el número de períodos de pago (tanto tipo de interés como períodos de pago deben ir en consonancia).

Capital vivo

El capital vivo es el capital que todavía nos queda por amortizar. Lo denotaremos por “Ck”.

  • Ck=a*(1-(1/(1+i))(n-k))/i)

Las variables que intervienen en esta fórmula ya las hemos visto salvo “k”. Esta variable indica el período en el que estamos calculando el capital vivo. Para verlo más claro podemos suponer que tenemos un préstamo a 10 años cuyos pagos son mensuales, en total tendremos 120 pagos o 120 períodos. Si queremos calcular el capital vivo después de realizar el 5º pago o de haber pagado la 5ª mensualidad la expresión (n-k) de la fórmula será, (120-5).

Cuota de interés

Este cálculo es bastante sencillo. Se corresponde con el producto del tipo de interés efectivo de cada período por el capital vivo a principio de dicho período. Denotaremos a la cuota de interés como “Ik”.

  • Ik = Ck-1*i

Cuota de amortización

Las cuotas de amortización se corresponden con la diferencia existente entre el capital vivo a principio de un período y el capital vivo al final del mismo. Se puede calcular de esta manera o podemos optar por otra fórmula más sencilla como:

  • Ak+1=Ak(1+i)
  • Ak=A1*(1+i)(k-1)
  • Donde A1 = a – C0*i
  • Ak: Cuota de amortización del período “k”.

Capital amortizado

El capital amortizado hasta un período “k” es la suma de todas las cuotas de amortización hasta ese mismo período “k”.

El capital amortizado en el último período tiene que ser igual al capital prestado al inicio del préstamo.

Denotamos al capital amortizado con “Mk”, entonces la fórmula será la siguiente:

  • Mk=A1*((1+i)k)* (1-(1/(1+i))k)/i)

Una aclaración es que “k” es el número de período para el cual queremos calcular el capital amortizado.

Fórmulas para Excel

Si somos de los que queremos hacer las cosas más fáciles en una hoja de cálculo las formulas a emplear podrían ser las mismas que hemos explicado anteriormente, solo tendríamos que introducirlas en las celdas y ya estaría. Por suerte, Excel dispone de funciones financieras que evitan que tengamos que introducir largas fórmulas a la hora de calcular un préstamo. Ahora explicaremos que funciones son equivalentes a las fórmulas anteriores en Excel.

Cuota de amortización en Excel

Para realizar este cálculo Excel dispone de la función PAGO, esta función admite una serie de parámetros. A continuación escribiremos su estructura:

  • PAGO(tasa;nper;va;[vf];[tipo])

Donde:

  • Tasa: Se corresponde con el tipo de interés efectivo de cada período (tendremos que calcularlo en una celda a parte como hemos explicado anteriormente)
  • Nper: Es el número de períodos o pagos a los que tenemos que hacer frente (podemos calcularlo en una celda aparte)
  • VA: Tendremos que introducir el capital inicial, es decir, el capital que nos han prestado. Le pondremos signo negativo para no tener números negativos.
  • [vf]: Lo dejaremos en blanco
  • [tipo]: Generalmente tampoco se especifica pero hace referencia a cuando se realiza el pago, a principio de período o al final del período. Si se omite o se le da un valor igual a 0 estaremos calculando los pagos a final de período y si se le da un valor igual 1 serán al comienzo.

[box style='help'] Como las cuotas son constantes en el sistema francés, podremos establecer dólares para fijar los valores y arrastrar hacia abajo. Esto nos permitirá agilizar los cálculos.[/box]

Lo habitual es crear un cuadro de variables al lado del cuadro de amortización en el cual pondremos: el capital, la frecuencia de pago, tipo de interés anual, tipo de interés efectivo (tendremos que calcularlo a partir del anual con las fórmulas que hemos explicado al principio), la duración en años y la duración en períodos según la frecuencia de pago (tendremos que multiplicar como hemos dicho anteriormente).

Cuota de interés en Excel

Aquí utilizaremos la misma fórmula que hemos visto antes.

  • Ik = Ck-1*i

Es decir, si queremos calcular los intereses que pagamos en el período 3, tendremos que coger el capital vivo del período 2 (3-1) y multiplicarlo por el tipo de interés del período. Una vez establecidos los dólares podremos arrastrar.

Cuota de amortización en Excel

Este cálculo es muy sencillo. Si en la cuota que pagamos mensualmente se recogen los intereses más el capital que amortizamos, si tenemos los intereses de un período y la cuota o pago mensual, sacar la cuota de amortización es una tarea fácil, solo tendremos que realizar una resta.

  • Ak= a – Ik

La variable “a” no lleva el subíndice “k” debido a que siempre tiene el mismo valor, es constante.

Si queremos calcular la cuota de amortización del período 5, tendremos que restar a la cuota mensual los intereses del período 5.

Capital Vivo en Excel

Al igual que el anterior, este valor se obtiene por diferencia. Para calcular el capital vivo de un período “k”, tendremos que echar mano del capital vivo del período “k-1” y restarle la cuota de amortización del período “k”.

  • Ck=Ck-1 – Ak

Capital amortizado en Excel

Si en las anteriores columnas de nuestro cuadro de amortización teníamos que restar, aquí tendremos que sumar.

Para calcular el capital amortizado en un periodo “k”, tendremos que sumarle al capital amortizado en “k-1” la cuota de amortización del período “k”.

  • Mk=Mk-1 +Ak

Con esto ya tendríamos confeccionado nuestro cuadro de amortización, lo único que podemos añadir es que hay que tener cuidado con los dólares a la hora de fijar ciertas cantidades.

Al realizar el cuadro observaremos que la cuota o pago mensual es constante, la cuota de interés es decreciente, la cuota de amortización es creciente, el capital vivo decreciente (en el último período debe ser 0) y el capital amortizado es creciente (en el último período el capital amortizado debe ser igual al capital que nos han préstamo).

  • Fernando

    Muchas gracias por aclarar todos estos cálculos, Tengo una pregunta:
    Si te calculan interés efectivo para el período como (i/m) en vez de (1+i)^1/12 -1. ¿Exite la posibilidad de reclamar?

    Muchas gracias por todo, por cierto muy buena pagina.

    • J. Garcia

      Pues yo diría que es posible reclamar (es mi opinión) aunque si ya has firmado no creo que puedas hacer gran cosa. Lo que sí puedes es exigir que te apliquen el tipo de interés correcto cuando solicitas un préstamo, peor claro, tendrías que darte cuenta antes. Seguramente te digan que es así como se calcula o que se hace así por cuestiones operativas, pero realmente el cálculo i/m es erróneo cuando hablamos de intereses compuestos. Aunque no sale en la entrada, Excel dispone de otras fórmulas para calcular cada parte de la tabla de amortización, por ejemplo PAGOINT, PAGOPRIN, etc. pero en la entrada creí que era mejor usar la lógica y hacer las operaciones sin funciones específicas. Un saludo