Cómo calcular un préstamo en Excel

Cómo calcular un préstamo en Excel

7085

Cuando acudimos a una entidad financiera a pedir información sobre un préstamo, lo normal es no saber cuál va a ser la cuota que pagaremos porque no sabemos cómo se calcula cada término de un préstamo o no sabemos hacer su cuadro de amortización. Aunque pueda parecer complicado esto no es así.

Los cálculos realmente son sencillos y si los hacemos podremos comparar rápidamente entre diferentes préstamos o tener una idea en la cabeza de lo que vamos a tener que pagar en cada período de cuota.

En esta entrada intentaremos explicar fácilmente cómo calcular un préstamo en Excel de manera sencilla. El método de amortización del préstamo que usaremos será el francés. Usaremos este ya que las entidades financieras es el que más usan. La principal característica de este método es que siempre pagaremos lo mismo en cada período (mensualidad). Si quieres calcular un préstamo a través de diferentes sistemas de amortización aquí te ofrecemos un simulador de préstamos que hemos hecho y que admite más variables.

Para realizar el cuadro solo tendremos que crear una columna con los períodos que dure el préstamo y luego crear otras 5 columnas que son las que vamos a calcular y que explicaremos a continuación.

Cálculo de los intereses efectivos

Antes de nada tendremos que saber cómo calcular tipos de interés efectivos ya que las entidades suelen darnos tipos de interés nominales anuales. Estos tipos anuales tendremos que pasarlos a mensuales, trimestrales, cuatrimestrales, semestrales, etc. en función de la frecuencia de pago de nuestra cuota, es decir, si nosotros vamos a pagar la cuota mensualmente, tendremos que pasar a tipos de interés efectivos mensuales. Si vamos a pagar cada semestre, pasaremos a tipos de interés efectivos semestrales. Para cada período es lo mismo.

Para convertir el tipo de interés nominal a efectivo tendremos que usar la siguiente fórmula:

  • im=((1+I)(1/m))-1

Siendo:

  • m  la frecuencia de pago.
  • El tipo de interés efectivo, im
  • El tipo de interés nominal anual, I
Si m=2 (dividimos el año en 2) Semestral
Si m=3 (dividimos el año en 3) Cuatrimestral
Si m=4 (dividimos el año en 4) Trimestral
Si m=6 (dividimos el año en 6) Bimestral
Si m=12 (dividimos el año en 12) Mensual

Por ejemplo para una cuota mensual, tendríamos que usar la fórmula de la siguiente manera:

  • Im=((1+I)(1/12))-1

Con esto ya podemos empezar a montar nuestro préstamo en Excel.

Montando el préstamo en Excel

Aquí os dejo una captura de cómo quedaría y ahora vamos a explicarlo paso a paso. También podéis descargar de aquí el archivo del ejemplo del préstamo en Excel

prestamos excel amortizacion frances

En el cuadro de la izquierda están los datos de nuestro préstamo (se pueden cambiar ya que son inputs). En color azul están los datos que introducimos nosotros y en rojo los que calculamos a partir de ellos.

En rojo tenemos el tipo de interés efectivo que lo hemos calculado como dijimos anteriormente y también tenemos el número total de pagos que es la multiplicación de los años que dura el préstamo por 12 (porque los pagos son mensuales, si fueran por ejemplo semestrales, multiplicaremos por 2). En nuestro ejemplo escribiríamos en la celda B9, lo siguiente:

  • =B8*12.

Ahora vamos a definir de manera muy sencilla las partes del cuadro de amortización.

Cuadro de amortización del préstamo en Excel: Método de amortización francés

Cálculo de la mensualidad

Primero tenemos la mensualidad que en Excel se calcula a través de la función PAGO (se puede calcular de otras maneras). Esta función nos pedirá lo siguiente:

  • TASA: Será el tipo de interés efectivo que hemos calculado. Lo fijamos con dólares pulsando F4 para luego poder arrastrar hacia abajo sin que varíe.
  • Nper: Es el número de períodos que también hemos calculado. También lo fijamos con dólares como hicimos anteriormente.
  • VA: Será el capital que vayamos a solicitar en el préstamo. Le ponemos el signo “–“ delante para que Excel nos dé un valor positivo. Hacemos esto ya que si no lo hacemos Excel nos devolverá un valor negativo porque lo considera una salida de dinero.

En nuestro ejemplo nos situaríamos en la celda F4 e introduciríamos la función PAGO de la siguiente manera:

  • =PAGO($B$7;$B$9;-$B$4)

funcion pago prestamo excel

Hecho esto pulsamos en aceptar. Si solo queremos saber qué cantidad vamos a pagar al mes ya nos serviría con esto ya que nos calcula la mensualidad. Si queremos saber el resto de componentes del cuadro tendremos que seguir adelante, además,  a través de la confección del cuadro de amortización sabremos si está bien calculado o no el préstamo que hemos hecho en Excel.

Cálculo de los intereses

En la columna de intereses, lo que tendremos que multiplicar es el tipo de interés efectivo (ponemos dólares para fijar) por el capital vivo. Nos situamos en la celda G4 y realizamos la siguiente operación:

  • =I3*$B$7

Debemos fijarnos que en la fila del período “cero” no tenemos nada salvo el capital vivo (es decir el capital que nos queda por amortizar) como en el momento 0 todavía nos quedará todo el capital por devolver, en esa celda tendremos que poner el capital que hemos solicitado en el préstamo. Esto sería así en el período 0, el resto lo veremos más adelante.

Cálculo de la amortización

En la columna amortización se corresponde con la parte de capital que hemos amortizado en ese período. Es decir, tenemos que la mensualidad es la suma de los intereses y la parte de capital que amortizamos en ese período. Por lo tanto:

Amortización=Mensualidad-Intereses. En nuestro caso, nos situamos en H4 y calculamos:

  • =F4-G4

Cálculo del capital vivo

El capital vivo es aquel que nos queda por pagar del capital y que no son intereses, es decir, sería la diferencia del capital vivo en el período anterior y lo amortizado en el período actual. Por lo tanto, tendremos que hacer lo siguiente:

  1. En el período “cero” como no hemos amortizado nada todavía, tendremos el capital que hemos solicitado en el préstamo. Por lo tanto, nos situamos en la celda I3 y escribimos: =B4
  2. Después vamos a  I4 y escribimos lo siguiente =I3-H4

Cálculo de la amortización acumulada

Ya solo nos queda la columna de capital amortizado que se corresponde con la cantidad acumulada de la amortización de cada período. Por lo tanto el resultado será el resultado de la suma de la amortización del período anterior más la amortización del período actual.

En nuestro Excel nos situaríamos en J4 y escribiríamos lo siguiente:

  • =J3+H4

Una vez calculados todos los términos para el período 1 de nuestro préstamo, podemos seleccionar desde F4:J4 y arrastrar hacia abajo para ir completando el resto de períodos del cuadro de amortización que hemos hecho en Excel.

Con esto ya tendríamos hecho nuestro cuadro de amortización. Si está bien calculado, en el último período tendríamos que tener un capital vivo igual a cero y en la columna de capital amortizado tendríamos la cuantía de lo que hemos pedido prestado (10.000€).

Además cambiando el tipo de interés nominal anual (el que nos ofrece el banco) podremos ver como varía la cantidad a pagar en cada período.

Si tenéis alguna duda al crear vuestro cuadro o a la hora de calcular algún término, podéis dejarnos un comentario.

  • yasmin

    gracias muy bueno

  • Paula

    Hola, ¿me podrías decir cómo poner los colores (rojo y azul) de las columnas de “intereses” y “amortización”? Muchas gracias.

    • J. Garcia

      Se ponen con un formato condicional. Aparece en la pestaña de Datos ;)

  • Sergio

    Lo mismo digo, ¿cómo has puesto en colores las barraras de las columnas “intereses” y “amortización”?

    • J. Garcia

      Hola! se pone con formatos condicionales ;). En excel 201o sale en la pestaña “Datos” y en “Estilos” sale lo de formato condicional. No tiene mucha ciencia :)

  • http://la-patente.es/ La Patente

    ¡Hola! No soy especialista en excel y ando buscando un simulador de amortización anticipada que además contemple que el préstamo tiene un período de carencia. No soy capaz de unir los dos modelos que ofreces… ¿no lo tendrás hecho por ahí? :-D

    • http://ambito-financiero.com J. Garcia

      Que quieres unir el simulador que tenemos en Excel de prestamos con carencia y el de amortización anticipada? Es sencillo de hacer, pero no lo tenemos para descargar.

      Un saludo

      • http://la-patente.es/ La Patente

        Bueno, gracias, ya lo he intentado y no he sabido… :-(

        • http://ambito-financiero.com J. Garcia

          Luego lo miro y te digo ;)

          • http://la-patente.es/ La Patente

            ole! He intentado hacerlo por observación autodidacta, pero no domino tanto excel…

          • http://ambito-financiero.com J. Garcia

            A ver, con la carencia parcial es sencillo, las pruebas que hice creo que funcionan y con la carencia total han surgido algunos problemas. Seguramente se puedan resolver pero hay que dedicarle algo más de tiempo. Un saludo

          • http://la-patente.es/ La Patente

            Si publicaras el excel sería muy de agradecer… yo intento aplicar tu tutorial de amortización anticipada sobre tu plantilla de préstamo con carencia, pero como ésta no esa la función PAGO pues ya no sé ni por donde empezar…

          • http://ambito-financiero.com J. Garcia

            Pues escríbeme en la página de contacto y te lo envío. Lo que pasa que con la carencia total no te va a funcionar.

          • http://la-patente.es/ La Patente

            Hecho! MUCHAS GRACIAS!!!

  • El Brother

    Hola Brother J. Garcia, un apoyo, fijate que pedi un prestamo en mi trabajo, me dieron 59,673.30, me dicen que la Tasa Anual es de 12% que mis descuentos quincenales seran de 1,709.28 durante 40 quincenas, que el monto total de mi interes es de 8697.90 por lo que pagara al final un total de 68,371.20, ya utilice tus simuladores y no logro llegar a estas cuentas, hice mis calculos no logro llegar a los calculos, me podrias ayudar a como saber si fueron aplicados bien., Gracias.

    • http://ambito-financiero.com J. Garcia

      Hola! Pues sin saber todos los datos de como se aplican los pagos y demás (si es pospagable o prepagable) o si ese tipo de interés es nominal o efectivo.

      Haciendo los cálculos y redondeando me salen sobre los 1629 las quincenas.También puede ser que te estén metiendo otros costes o comisiones en el préstamo.

      Un saludo ;)

  • vicariote

    buenos dias, me pregunto, si seria facil tener un tabla excel que calcule amortización de préstamo por sistemas frances, con la especialidad que aplica Unicaja en el calculo diario de los intereses

    • http://ambito-financiero.com J. Garcia

      Si simplemente es que se calculan los intereses generados en cada día, es muy sencillo. En el blog tenemos un simulador de préstamos donde podemos elegir si el pago es mensual, trimestral, semestral, etc. sería adaptarlo o añadir la opción de calcular los intereses diarios