Narrative, SAT, UC, Scholarship, Descriptive essay examples

Cree un programa de amortización de préstamos en Excel (con pagos adicionales si es necesario)

Cree un programa de amortización de préstamos en Excel (con pagos adicionales si es necesario)
13 enero, 2021
Author:
Category: Cuentas Bancarias

El tutorial muestra cómo crear un programa de amortización en Excel para detallar los pagos periódicos de un préstamo o hipoteca que se amortiza.

Un préstamo amortizable es solo una forma elegante de definir un préstamo que se reembolsa en cuotas durante todo el plazo del préstamo.

Básicamente, todos los préstamos se amortizan de una forma u otra. Por ejemplo, un préstamo con amortización total durante 24 meses tendrá 24 pagos mensuales iguales. Cada pago aplica una cantidad al principal y otra a los intereses. Para detallar cada pago de un préstamo, puede crear un calendario de amortización del préstamo.

Un programa de amortización es una tabla que enumera los pagos periódicos de un préstamo o hipoteca a lo largo del tiempo, desglosa cada pago en capital e interés y muestra el saldo restante después de cada pago.

Cómo crear un calendario de amortización de préstamos en Excel

Para construir un cronograma de amortización de préstamos o hipotecas en Excel, necesitaremos usar las siguientes funciones:

  • Función PMT: calcula el monto total de un pago periódico. Esta cantidad se mantiene constante durante toda la duración del préstamo.
  • Función PPMT: obtiene la parte principal de cada pago que se destina al principal del préstamo, es decir, la cantidad que pidió prestada. Esta cantidad aumenta para pagos posteriores.
  • Función IPMT: encuentra la parte de interés de cada pago que se destina a intereses. Esta cantidad disminuye con cada pago.

Ahora, repasemos el proceso paso a paso.

1. Configure la tabla de amortización

Para empezar, defina las celdas de entrada donde ingresará los componentes conocidos de un préstamo:

  • C2 – tasa de interés anual
  • C3 – plazo del préstamo en años
  • C4 – número de pagos por año
  • C5 – monto del préstamo

Lo siguiente que debe hacer es crear una tabla de amortización con las etiquetas ( Período , Pago , Interés , Principal , Saldo ) en A7: E7. En la columna Período , ingrese una serie de números igual al número total de pagos (1 a 24 en este ejemplo):

Con todos los componentes conocidos en su lugar, pasemos a la parte más interesante: fórmulas de amortización de préstamos.

2. Calcule el monto total del pago (fórmula PMT)

El monto del pago se calcula con la función PMT (rate, nper, pv, [fv], [type]).

Para manejar correctamente diferentes frecuencias de pago (como semanal, mensual, trimestral, etc.), debe ser coherente con los valores proporcionados para la tasa y los argumentos nper :

  • Tasa : divida la tasa de interés anual por el número de períodos de pago por año ($ C $ 2 / $ C $ 4).
  • Nper : multiplique la cantidad de años por la cantidad de períodos de pago por año ($ C $ 3 * $ C $ 4).
  • Para el argumento pv , ingrese el monto del préstamo ($ C $ 5).
  • Los argumentos fv y type se pueden omitir, ya que sus valores predeterminados funcionan bien para nosotros (el saldo después del último pago se supone que es 0; los pagos se realizan al final de cada período).

Juntando los argumentos anteriores, obtenemos esta fórmula:

Preste atención, usamos referencias de celda absolutas porque esta fórmula debe copiarse en las celdas siguientes sin ningún cambio.

Ingrese la fórmula PMT en B8, arrástrela hacia abajo en la columna y verá un monto de pago constante para todos los períodos:

3. Calcule el interés (fórmula IPMT)

Para encontrar la parte de interés de cada pago periódico, utilice la función IPMT (rate, per, nper, pv, [fv], [type]):

Todos los argumentos son los mismos que en la fórmula PMT, excepto el argumento por que especifica el período de pago. Este argumento se proporciona como una referencia de celda relativa (A8) porque se supone que debe cambiar según la posición relativa de una fila en la que se copia la fórmula.

Esta fórmula va a C8 y luego la copia en tantas celdas como sea necesario:

4. Encuentre el principal (fórmula PPMT)

Para calcular la parte principal de cada pago periódico, utilice esta fórmula PPMT:

La sintaxis y los argumentos son exactamente los mismos que en la fórmula IPMT discutida anteriormente:

Esta fórmula va a la columna D, comenzando en D8:

5. Obtenga el saldo restante

Para calcular el saldo restante de cada período, usaremos dos fórmulas diferentes.

Para encontrar el saldo después del primer pago en E8, sume el monto del préstamo (C5) y el principal del primer período (D8):

Debido a que el monto del préstamo es un número positivo y el principal es un número negativo, este último en realidad se resta del primero.

Para el segundo y todos los períodos siguientes, sume el saldo anterior y el capital de este período:

La fórmula anterior va a E9 y luego la copia en la columna. Debido al uso de referencias de celda relativas, la fórmula se ajusta correctamente para cada fila.

¡Eso es! Nuestro calendario mensual de amortización de préstamos se realiza:

Consejo: devuelva los pagos como números positivos

Debido a que un préstamo se paga desde su cuenta bancaria, las funciones de Excel devuelven el pago, el interés y el capital como números negativos . De forma predeterminada, estos valores están resaltados en rojo y entre paréntesis, como puede ver en la imagen de arriba.

Si prefiere tener todos los resultados como números positivos , coloque un signo menos antes de las funciones PMT, IPMT y PPMT.

Para las fórmulas de Balance , use la resta en lugar de la suma como se muestra en la siguiente captura de pantalla:

Calendario de amortización para un número variable de períodos

En el ejemplo anterior, creamos un calendario de amortización de préstamos para el número predefinido de períodos de pago. Esta solución rápida de una sola vez funciona bien para un préstamo o hipoteca específico.

Si está buscando crear un programa de amortización reutilizable con un número variable de períodos, tendrá que adoptar un enfoque más completo que se describe a continuación.

1. Ingrese el número máximo de períodos

En la columna Período , inserte el número máximo de pagos que va a permitir para cualquier préstamo, por ejemplo, de 1 a 360. Puede aprovechar la función Autocompletar de Excel para ingresar una serie de números más rápido.

2. Use declaraciones IF en fórmulas de amortización

Debido a que ahora tiene muchos períodos excesivos, de alguna manera debe limitar los cálculos al número real de pagos de un préstamo en particular. Esto se puede hacer envolviendo cada fórmula en una declaración IF. La prueba lógica de la declaración IF verifica si el número de período en la fila actual es menor o igual que el número total de pagos. Si la prueba lógica es VERDADERA, se calcula la función correspondiente; si es FALSE, se devuelve una cadena vacía.

Suponiendo que el Período 1 está en la fila 8, ingrese las siguientes fórmulas en las celdas correspondientes y luego cópielas en toda la tabla.

Pago (B8):

Interés (C8):

Principal (D8):

Equilibrio :

Para el período 1 (E8), la fórmula es la misma que en el ejemplo anterior:

Para el período 2 (E9) y todos los períodos posteriores, la fórmula toma esta forma:

Como resultado, tiene un programa de amortización calculado correctamente y un montón de filas vacías con los números de período después de que se cancela el préstamo.

3. Ocultar números de períodos adicionales

Si puede vivir con un montón de números de períodos superfluos que se muestran después del último pago, puede considerar el trabajo realizado y omitir este paso. Si se esfuerza por lograr la perfección, oculte todos los períodos no utilizados estableciendo una regla de formato condicional que establezca el color de fuente en blanco para cualquier fila después de que se haya realizado el último pago.

Para esto, seleccione todas las filas de datos de su tabla de amortización (A8: E360 en nuestro caso) y haga clic en la pestaña Inicio > Formato condicional > Nueva regla… > Use una fórmula para determinar qué celdas formatear .

En el cuadro correspondiente, ingrese la fórmula siguiente que verifica si el número de período en la columna A es mayor que el número total de pagos:

Después de eso, haga clic en el botón Formato … y elija el color de fuente blanco. ¡Hecho!

4.Haz un resumen del préstamo

Para ver la información resumida sobre su préstamo de un vistazo, agregue un par de fórmulas más en la parte superior de su programa de amortización.

Pagos totales (F2):

Interés total (F3):

Si tiene pagos como números positivos, elimine el signo menos de las fórmulas anteriores.

¡Eso es! ¡Nuestro programa de amortización de préstamos está completo y listo!

Cómo hacer un cronograma de amortización de préstamos con pagos adicionales en Excel

Los programas de amortización discutidos en los ejemplos anteriores son fáciles de crear y seguir (con suerte :). Sin embargo, omiten una característica útil en la que están interesados ​​muchos prestamistas: pagos adicionales para liquidar un préstamo más rápido. En este ejemplo, veremos cómo crear un programa de amortización de préstamos con pagos adicionales.

1. Definir celdas de entrada

Como de costumbre, comience con la configuración de las celdas de entrada. En este caso, nombremos estas celdas como se escribe a continuación para que nuestras fórmulas sean más fáciles de leer:

  • Tasa de interés – C2 (tasa de interés anual)
  • LoanTerm – C3 (plazo del préstamo en años)
  • PaymentsPerYear – C4 (número de pagos por año)
  • LoanAmount – C5 (monto total del préstamo)
  • Pago adicional – C6 (pago adicional por período)

2. Calcule un pago programado

Además de las celdas de entrada, se requiere una celda predefinida más para nuestros cálculos adicionales: el monto del pago programado , es decir, el monto a pagar en un préstamo si no se realizan pagos adicionales. Esta cantidad se calcula con la siguiente fórmula:

= SI.ERROR (-PMT (Tasa de interés / Pagos por año, Término del préstamo * Pagos por año, Monto del préstamo), “”)

Preste atención a que colocamos un signo menos antes de la función PMT para que el resultado sea un número positivo. Para evitar errores en caso de que algunas de las celdas de entrada estén vacías, incluimos la fórmula PMT dentro de la función IFERROR.

Introducir esta fórmula en alguna célula (G2 en nuestro caso) y el nombre de esa célula ScheduledPayment .

3. Configure la tabla de amortización

Cree una tabla de amortización de préstamos con los encabezados que se muestran en la captura de pantalla a continuación. En la columna Período , ingrese una serie de números que comiencen con cero (puede ocultar la fila Período 0 más adelante si es necesario).

Si su objetivo es crear un programa de amortización reutilizable, ingrese el número máximo posible de períodos de pago (0 a 360 en este ejemplo).

Para el período 0 (fila 9 en nuestro caso), extraiga el valor del saldo , que es igual al monto del préstamo original. Todas las demás celdas de esta fila permanecerán vacías:

4. Cree fórmulas para el calendario de amortización con pagos adicionales

Ésta es una parte clave de nuestro trabajo. Debido a que las funciones integradas de Excel no proporcionan pagos adicionales, tendremos que hacer todos los cálculos por nuestra cuenta.

Ingrese las siguientes fórmulas en la fila 10 ( Período 1 ) y luego cópielas para todos los períodos restantes.

Pago programado (B10):

Si el monto del Pago Programado (llamado celda G2) es menor o igual que el saldo restante (G9), use el pago programado. De lo contrario, agregue el saldo restante y los intereses del mes anterior.

Como precaución adicional, envolvemos esta y todas las fórmulas posteriores en la función IFERROR. Esto evitará varios errores si algunas de las celdas de entrada están vacías o contienen valores no válidos.

Pago adicional (C10):

Utilice una fórmula SI con la siguiente lógica:

Si el monto del Pago Adicional (llamado celda C6) es menor que la diferencia entre el saldo restante y el capital de este período (G9-E10), devuelva el Pago Adicional ; de lo contrario, use la diferencia.

Pago total (D10)

Simplemente, agregue el pago programado (B10) y el pago adicional (C10) para el período actual:

Principal (E10)

Si el pago programado para un período determinado es mayor que cero, devuelva uno menor de los dos valores: pago programado menos intereses (B10-F10) o el saldo restante (G9); de lo contrario, devuelve cero.

= SI.ERROR (SI (B10> 0, MIN (B10-F10, G9), 0), “”)

Tenga en cuenta que el capital solo incluye la parte del pago programado (¡no el pago adicional!) Que se destina al capital del préstamo.

Intereses (F10)

Si el pago programado para un período determinado es mayor que cero, divida la tasa de interés anual (llamada celda C2) por el número de pagos por año (llamada celda C4) y multiplique el resultado por el saldo restante después del período anterior; de lo contrario, devuelve 0.

= SI.ERROR (SI (B10> 0, Tasa de interés / Pagos por año * G9, 0), “”)

Equilibrio (G10)

Si el saldo restante (G9) es mayor que cero, reste la porción principal del pago (E10) y el pago adicional (C10) del saldo restante después del período anterior (G9); de lo contrario, devuelve 0.

= SI.ERROR (SI (G9> 0, G9-E10-C10, 0), “”)

Si todo se hace correctamente, el calendario de amortización de su préstamo en este momento debería verse así:

5. Ocultar períodos extra

Configure una regla de formato condicional para ocultar los valores en períodos no utilizados como se explica en este consejo. La diferencia es que esta vez aplicamos el color de fuente blanco a las filas en las que Pago Total (columna D) y Saldo (columna G) son iguales a cero o vacíos:

Voilà, todas las filas con valores cero están ocultas a la vista:

6.Haz un resumen del préstamo

Como toque final de perfección, puede generar la información más importante sobre un préstamo utilizando estas fórmulas:

Número de pagos programados:

Multiplique la cantidad de años por la cantidad de pagos por año:

Número real de pagos:

Cuente las celdas de la columna Pago total que sean mayores que cero, comenzando con el Período 1:

Pagos adicionales totales:

Sume celdas en la columna Pago adicional , comenzando con el Período 1:

Interés total:

Sume celdas en la columna de interés , comenzando con el período 1:

Opcionalmente, oculte la fila del Período 0 y su programa de amortización de préstamo con pagos adicionales estará listo. La siguiente captura de pantalla muestra el resultado final:

Plantilla de Excel de calendario de amortización

Para realizar un programa de amortización de préstamos de primer nivel en poco tiempo, utilice las plantillas integradas de Excel. Simplemente vaya a Archivo > Nuevo , escriba ” programa de amortización ” en el cuadro de búsqueda y elija la plantilla que desee, por ejemplo, esta con pagos adicionales:

Así es como se crea un programa de amortización de préstamos o hipotecas en Excel. ¡Gracias por leer y espero verte en nuestro blog la próxima semana!