Un cronograma de préstamos en Excel te permite saber para cada período.
- La parte del préstamo que realmente pagas cada mes
- La cantidad de intereses que pagas también todos los meses
Construya un cronograma de endeudamiento en la siguiente situación
Cogeremos el caso en el que queremos pedir un préstamo de 2500€ a 1 año (12 meses) a un tipo del 10% anual. Como vimos en el artículo sobre calculo de pagos mensuales, podemos deducir la tasa de interés mensual de esta información.
Luego, gracias a la función VPM, es muy fácil saber el importe a devolver cada mes.
=VPM(B5;B3;B1)*-1 => 219,29
De los 219,29€ mensuales, ¿qué cantidad se gasta en devolver el préstamo en el primer mes, en el segundo mes, etc.?
Cálculo de intereses en un cronograma de préstamo
Para conocer la parte de interés pagada en cada fecha de vencimiento, Excel ofrece la función CUMUL.INTERSu sintaxis es la siguiente:
=CUMUL.INTER(taux; nombre_échéances; montant_emprunt; période_début; période_fin; type)
- taux : el tipo de interés, expresado mensual o anualmente según el contexto
- número_fechas_límite :el número total de períodos de reembolso
- monto del préstamo :el capital prestado
⚠️ Verifique cuidadosamente si la tarifa que indica es mensual o anual. Un error en este nivel distorsionará todo el cálculo.
Parámetros a monitorizar
Los parámetros período de inicio et fin_del_periodo Suelen ser una fuente de errores. Debe indicar el/los período(s) para los que desea calcular el interés acumulado.
💡 Para obtener la cantidad de interés pagada en un solo período (por ejemplo, el primero), utilice el mismo valor para ambos parámetros.
=CUMUL.INTER(0,797%; 12; 2500; 1; 1; 0) * -1
➡️ Esta fórmula da 19,94 €, o el monto de interés pagado en la primera fecha de vencimiento.
Para el segundo año, debes escribir la fórmula.
=CUMUL.INTER(0.797%;12;2500;2;2;0)*-1 => 18,35
Y ahora, si quieres saber la parte de los intereses que pagaste en el primer y segundo período, debes escribir
=CUMUL.INTER(0.797%;12;2500;1;2;0)*-1 => 38,28
Cálculo de la parte del capital reembolsado
Para conocer la parte del capital reembolsada en cada vencimiento, utilice la función ACUMULACIÓN DEL PRINCIPALCalcula la parte del préstamo (sin intereses) que se paga en uno o más períodos. Esta es la sintaxis:
=CUMUL.PRINCPER(taux; nombre_échéances; montant_emprunt; période_début; période_fin; type)
Esta fórmula utiliza exactamente los mismos parámetros que CUMUL.INTER, pero esta vez calcula el capital reembolsado.
💡 Para conocer la parte del capital reembolsada en un solo período (por ejemplo, la primera cuota), ingrese el mismo valor para el período inicial y final.
=CUMUL.PRINCPER(0,797%; 12; 2500; 1; 1; 0) * -1
➡️ Esta fórmula da 199,35 €, o el capital reembolsado durante el primer pago mensual.
Construcción del cronograma por período.
Y así, a partir de ahí, es fácil construir un cronograma de préstamos en Excel, mes a mes con:
- Por cada línea el total equivale a un pago mensual
- En la columna E, el monto total prestado
- Luego, en la columna F, el interés total pagado
- Columna G el monto total desembolsado del préstamo
También puedes utilizar el Función SECUENCIA de Excel para hacer lo mismo cronograma de préstamos dinámico. De esta manera, al cambiar el valor del período, se recalcula automáticamente toda la tabla.
Artículos relacionados
- Calcular el pago mensual de un préstamo con tasa fija
- Calcula los pagos mensuales de tu préstamo con Excel
- Determinar el número de pagos mensuales
- Pagos periódicos – Ganancias obtenidas
- Cree un cronograma de préstamos dinámico
Video explicativo
También puedes ver la construcción completa del cronograma en este vídeo
03/03/2023 a las 17:45
Hola,
Gracias por su video. Es muy claro e informativo... Sólo un dato: los préstamos inmobiliarios en Francia se rigen por el tipo de interés simple... Por lo tanto, conviene dividir el tipo anual entre 12 para obtener el tipo mensual... ya que es proporcional a la tasa anual...
Además, tienes toda la razón al insistir en que los préstamos al consumo se calculan mediante tasas actuariales ya que se rigen por la "mecánica" del interés compuesto... Conviene seguir la fórmula que detallas para encontrar el interés mensual. tasa periódica => (1+tA ^1/12) -1
Cdt,
04/03/2023 a las 05:15
Entonces ese es un gran comentario. Muchísimas gracias. Había aprendido esta técnica de calcular las tasas de interés mensuales cuando era estudiante, pero no sé en qué caso se aplicaba (ni que para préstamos inmobiliarios solo se necesita una división entre 12).
¿Podrías contactarme al contacto@excel-ejercicio.com enlaces sobre el tema, para que pueda escribir un artículo sobre este punto.
Es como el método deredondeo de banqueros. Si no te lo explicamos nunca sabrás qué es ni la importancia que tiene.
15/03/2021 a las 15:04
Hola Frédéric LE GUEN
Me gustaría saber la fórmula de cálculo de CUMUL.INTER (tasa; número de cuotas; monto del préstamo; período de inicio; período de finalización; tipo) teniendo en cuenta un pago aplazado (en meses).
Merci
12/12/2020 a las 14:18
Creo que hay un error en la explicación de los parámetros 4 y 5: el texto indica que deben establecerse en 1 para el año 1 y en 2 para el año 2. Pero en realidad año, estos son más bien números de período (en el ejemplo son por tanto meses).
10/05/2022 a las 10:54
la fórmula para calcular el coste durante un período no es buena....el pago mensual, por otro lado, tiene el área correcta
10/05/2022 a las 13:07
Como esto ? ¿Tienes un ejemplo para que pueda comprobarlo?