¡Descarga interés simple en matemática y más Apuntes en PDF de Matemáticas Orientadas a las Enseñanzas Aplicadas solo en Docsity!
Cómo calcular un préstamo en Excel
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.
Tabla de contenido [ocultar] 1 Cálculo de los intereses efectivos 2 Montando el préstamo en Excel 3 Cuadro de amortización del préstamo en Excel: Método de amortización francés 3.1 Cálculo de la mensualidad 3.2 Cálculo de los intereses 3.3 Cálculo de la amortización 3.4 Cálculo del capital vivo 3.5 Cálculo de la amortización acumulada
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:
=((1+I) )-
Siendo:
m la frecuencia de pago. El tipo de interés efectivo, i 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
Por J.Garcia - Mar 7, 2013
im (1/m)
m
Utilizamos cookies para mejorar tu experiencia de navegación. Si sigues navegando, entendemos que las aceptas. Disfruta de tu paso por ambito- nanciero.com. Política de cookies
Cerrar y aceptar
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:
I =((1+I) )-
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
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:
m (1/12)
Utilizamos cookies para mejorar tu experiencia de navegación. Si sigues navegando, entendemos que las aceptas. Disfruta de tu paso por ambito- nanciero.com. Política de cookies
Cerrar y aceptar
=F4-G
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:
- 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: =B
- Después vamos a I4 y escribimos lo siguiente =I3-H
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+H
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.
J.Garcia https://ambito-financiero.com Consultor de Business Intelligence y apasionado de la analítica avanzada. Licenciado en Economía, Máster en Banca y Finanzas con licencia de operador en bolsa emitido por Bolsas y Mercados Españoles (BME), Máster SAP...y alguna cosa más ;)
Utilizamos cookies para mejorar tu experiencia de navegación. Si sigues navegando, entendemos que las aceptas. Disfruta de tu paso por ambito- nanciero.com. Política de cookies
Cerrar y aceptar
