¿Cómo Crear un Plan de Amortización en Excel? Guía Paso a Paso con Ejemplos
Tanto si es prestamista como prestatario, seguro que necesitará cuadros de amortización para comprender sus compromisos financieros o gestionar sus préstamos. Así pues, aquí tiene una guía básica sobre cómo crear un plan de amortización en Excel con una plantilla ya preparada o desde cero.
Tabla de contenido
1. ¿Qué es un plan de amortización?
2. ¿Cómo calcular la amortización de un préstamo con la plantilla Someka?
3. ¿Cómo crear un plan de amortización en Excel desde cero?
4. ¿Por qué utilizar Excel para los planes de amortización?
5. ¿Quién utilizará los cuadros de amortización de préstamos en Excel?
6. Conclusión
1. ¿Qué es un plan de amortización?
Básicamente, el cuadro de amortización es una tabla que enumera detalladamente cada periodo de pago del préstamo.
Aquí, los pagos se dividen en componentes de capital e intereses. Así, este calendario ilustra cómo el saldo del préstamo irá disminuyendo con el tiempo hasta llegar a cero. Por lo tanto, es un recurso útil para aprender cómo se efectúan los pagos del préstamo a lo largo del tiempo.
¿Cuáles son las variables de un cuadro de amortización?
Las variables clave en un plan de amortización incluyen el importe del préstamo, el tipo de interés, el plazo y la frecuencia de pago.
- Importe del préstamo: Suma inicial prestada
- Tipo de interés: Coste del préstamo, normalmente un tipo anual
- Plazo del préstamo: Duración del plazo para devolver el préstamo.
- Frecuencia de pago: Medida de la frecuencia con la que se efectúan los pagos. Puede ser anual, trimestral o mensual.
Además, también podemos añadir el pago global, que es una cláusula opcional de los contratos de préstamo.
¿Qué es un pago global?
Un pago global es un pago global de gran cuantía que vence al final del plazo de un préstamo, tras una serie de pagos regulares más pequeños. Además, este tipo de estructura de pago es común en ciertos tipos de préstamos, particularmente en los acuerdos de préstamos hipotecarios y comerciales.
2. ¿Cómo calcular el plan de amortización de un préstamo con la plantilla Someka?
En primer lugar, no tiene que molestarse en preparar tablas de amortización y hacer cálculos exhaustivos. Le proporcionamos una plantilla lista para usar con la que podrá calcular sus calendarios de pago en cuestión de segundos.
Esta plantilla consta de tres áreas principales: 1. Supuestos, 2. Resumen del préstamo, 3. Tabla de amortización.
– Esta es una captura de pantalla de la plantilla Excel de amortización de préstamos de Someka –
Es muy fácil utilizar esta plantilla:
Paso 1: Rellene sus supuestos
Verá una tabla de supuestos en la parte superior izquierda de su hoja de cálculo. Podrá rellenar esta zona con:
- Importe del préstamo
- Tipo de interés
- Fecha del primer pago
- Frecuencia de pago
- Plazo del préstamo
- Pago global (opcional)
Paso 2: Analice el resumen de su préstamo
La plantilla calculará su resumen automáticamente. Por lo tanto, todo lo que tiene que hacer es analizar sus resultados:
- Importe de la cuota
- Número de pagos
- Interés total pagado
- Pago total
Así, esta tabla le permitirá comprender sus compromisos financieros de forma limpia.
Paso 3: Imprima su cuadro de amortización
Por último, la plantilla prepara automáticamente su cuadro de amortización. Además, está lista para imprimir de forma dinámica. Por lo tanto, sólo tiene que hacer clic en CTRL+P y obtener su plan de amortización impreso.
3. ¿Cómo crear un plan de amortización en Excel desde cero?
Si aún así prefiere preparar su propio plan de amortización, vamos a crear juntos un plan de amortización en Excel.
Paso 1: Prepare los supuestos
En primer lugar, debe establecer los supuestos de su préstamo.
Introduzca la fecha de inicio, el plazo, el tipo de interés y el importe del préstamo en diferentes celdas. Dado que estas cifras son la base de su programa, asegúrese de que son correctas.
Paso 2: Crear la tabla de amortización
Cree una tabla con columnas para el capital, los intereses y el saldo restante.
Así, ésta será la sección principal de su plantilla. Así, los pagos efectuados sobre este préstamo se controlarán en esta tabla.
Paso 3: Cálculo de pagos y cuotas
A continuación, calcularemos los pagos mensuales y la división del capital y los intereses utilizando funciones de Excel. Obviamente, cada fila de la tabla representará un periodo de pago.
¿Qué son las fórmulas de amortización en Excel?
Excel dispone de funciones específicas para los cálculos de amortización:
- Función PMT: En primer lugar, la fórmula PMT calcula el pago de un préstamo basándose en pagos constantes y un tipo de interés constante.
- Función IPMT: A continuación, la fórmula IPMT calcula la parte de interés de cada pago.
- Función PPMT: Por último, la función PPMT calcula la parte de capital de cada pago.
Antes de sumergirnos en la sintaxis de cada fórmula queremos dar una explicación de cada elemento de estas fórmulas:
- Tipo: El tipo de interés para cada periodo. Si tiene un tipo anual y realiza pagos mensuales, divida este tipo por 12.
- Nper: Número total de pagos del préstamo.
- Pv: Valor actual o importe total del préstamo
- Per: El periodo para el que desea hallar el interés y debe estar en el rango de 1 a nper.
Pagos mensuales:
El cálculo de los pagos mensuales de un préstamo en Excel puede realizarse utilizando la función PMT. La función PMT se utiliza para calcular el pago de un préstamo basándose en pagos constantes y un tipo de interés constante.
Por ejemplo
Suponga que pide prestados 100.000 $ con un tipo de interés anual del 5% y un plazo de préstamo de 20 años. Entonces su fórmula será la siguiente
El resultado se mostrará en la celda seleccionada. Es importante tener en cuenta que la función PMT devuelve un número negativo, que representa un pago efectuado.
Esto le dará el importe del pago mensual. Recuerde que esto incluye tanto el capital como los intereses, pero no incluye otros costes como el seguro o los impuestos, si éstos forman parte de su acuerdo de préstamo.
Interés de cada pago
Así pues, calcularemos el interés para cada pago con la función IPMT de Excel:
Continuemos con nuestro ejemplo:
Del mismo modo, la fórmula IPMT también da un número negativo como pago.
Pago del principal
Utilizaremos la función PPMT incorporada en Excel para calcular los pagos del principal de cada periodo.
Esta función es muy similar a la IPMT en cuanto a sintaxis.
Tenga en cuenta que también podemos calcular el pago del principal restando el pago mensual de intereses del pago mensual total.
Saldo restante en el plan de amortización en Excel
No se trata de una función estándar de Excel, pero se puede calcular restando el principal pagado hasta la fecha del importe original del préstamo. Al utilizar el signo menos, también debemos convertir los pagos negativos del principal en positivos multiplicándolos por el coeficiente -1.
Por último, copie todas las fórmulas de la primera fila hasta el final de su tabla.
Eso es todo. Su tabla de amortización está lista.
Paso 4: Calcular el resumen del préstamo en el cuadro de amortización de Excel
Por último, resumiremos el importe total pagado a lo largo del periodo del préstamo al final de la tabla.
- Importe de las cuotas = El total de los pagos mensuales. Toma la suma de los pagos de Interés y Principal de cualquier periodo.
- Númerode pagos = Número total de pagos. Multiplique los plazos del préstamo en años por 12 para una frecuencia mensual.
- Interés Total Pagado = Tome la suma de la columna de intereses.
- Pago Total = Obtenga la suma de todos los pagos de Interés y Principal.
Este es el final de nuestra guía paso a paso. Así pues, ahora ya sabe cómo crear un plan de amortización en Excel.
4. ¿Por qué utilizar Excel para los planes de amortización?
Excel es una potente herramienta para crear planes de amortización. Resumamos en una lista las ventajas de Excel para crear planes de amortización:
- Flexibilidad
- Precisión
- Funciones incorporadas para simplificar cálculos complejos
- Ampliamente accesible
- Fácil de usar
- Editable y personalizable
- Acceso sin conexión
- Y mucho más…
Así pues, estos factores hacen que Microsoft Excel sea ideal para la planificación financiera tanto personal como profesional, incluidos los cálculos de amortización.
5. ¿Quién utilizará las Plantillas de Amortización de Préstamos en Excel?
Todas las partes implicadas en un préstamo, empréstito o cualquier otro contrato de crédito necesitarán un cuadro de amortización para comprender los compromisos financieros.
Entre los usuarios comunes, podemos decir:
- Prestamistas
- Pagadores de créditos
- Pagadores de hipotecas
- Prestatarios de préstamos estudiantiles
- Prestamistas y prestatarios de créditos para automóviles u otros productos básicos
Si desea calcular específicamente el plan de amortización de su hipoteca, también puede utilizar nuestra Plantilla de calculadora de hipotecas:
– Esta es la sección principal de la Plantilla Excel de Calculadora Hipotecaria de Someka –
Además, puede haber otros calendarios que desee construir en Excel, como el calendario de rotaciones, el calendario de ligas, el calendario de tareas, etc.
6. Conclusión: ¿Cómo crear un plan de amortización en Excel?
En este artículo, hemos explicado cómo crear un plan de amortización en Excel con una guía paso a paso. Encontrará dos opciones diferentes: En primer lugar, puede crear su cuadro desde cero. O bien, puede descargar la plantilla de Excel de amortización de préstamos de Someka y no molestarse con el formato o los cálculos:
- Listo para usar
- Diseño elegante
- Cálculo dinámico del préstamo
- Imprimible y editable
Espero que este artículo le resulte útil.
Lecturas recomendadas:
¿Cómo calcular el tipo de interés de un préstamo?
Fórmula VAN en Excel: ¿Cómo calcular el Valor Actual Neto en Excel?
Puestos relacionados
