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

el tutorial muestra cómo construir un programa de amortización en Excel para detallar los pagos periódicos en un préstamo o hipoteca de amortización.

un préstamo de amortización es solo una forma elegante de definir un préstamo que se paga en cuotas a lo largo de todo el plazo del préstamo.

Básicamente, todos los préstamos se amortizan de una manera u otra. Por ejemplo, un préstamo con amortización completa 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 principal e interés, y muestra el saldo restante después de cada pago.,

Cómo crear un programa de amortización de préstamos en Excel
Programa de amortización para un número variable de períodos
Programa de amortización de préstamos con pagos adicionales
plantilla de amortización de Excel

cómo crear un programa de amortización de préstamos en Excel

li> Función PMT – calcula la cantidad 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 va hacia el 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, vamos a ir a través del proceso paso a paso.,

configurar 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, intereses, capital, saldo) en A7:E7., En la columna período, ingrese una serie de números iguales al número total de pagos (1 – 24 en este ejemplo):

con todos los componentes conocidos en su lugar, pasemos a las fórmulas de amortización de préstamos de parte más interesantes.

calcular el importe total del pago(fórmula PMT)

el importe del pago se calcula con la función PMT (tasa, nper, pv,,).

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

tasa-divida la tasa de interés anual por el número de períodos de pago por año ($C 2 2/C C 4 4).
Nper-multiplique el número de años por el número de períodos de pago por año (C C 3 3*C C 4 4).
Para el argumento pv, ingrese el monto del préstamo (C C 5 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:

=PMT($C$2/$C$4, $C$3*$C$4, $C$5)

por favor preste atención, que usamos referencias de celda absolutas porque esta fórmula debe copiarse a 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:

Calcule el interés (fórmula IPMT)

para encontrar la parte de interés de cada pago periódico, use la función IPMT(rate, per, NPER, pv,,):

=IPMT($C$2/$C$4, A8, $C$3*$C$4, $C$5)

todos los argumentos son los mismos que en la fórmula PMT, excepto el argumento per que especifica el período de pago., Este argumento se suministra como una referencia de celda relativa (A8) porque se supone que cambia en función de la posición relativa de una fila a la que se copia la fórmula.,

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

Find principal (fórmula PPMT)

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

=PPMT($C$2/$C$4, A8, $C$3*$C$4, $C$5)

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:

tip. Para comprobar si sus cálculos son correctos en este punto, sume los números en las columnas Principal e interés., La suma debe ser igual al valor en la columna de Pago en la misma fila.

obtenga el saldo restante

para calcular el saldo restante para 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):

=C5+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 posteriores, agregue el saldo anterior y el principal de este período:

=E8+D9

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 todo! Nuestro programa mensual de amortización de préstamos se realiza:

Consejo: devuelve los pagos como números positivos

debido a que un préstamo se paga de su cuenta bancaria, las funciones de Excel devuelven el pago, los intereses y el capital como números negativos., De forma predeterminada, estos valores se resaltan en rojo y se incluyen entre paréntesis, como puede ver en la imagen de arriba.

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

para las fórmulas de saldo, use resta en lugar de suma como se muestra en la captura de pantalla a continuación:

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

en el ejemplo anterior, creamos un programa 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ífica.

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.

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 de Autocompletar de Excel para ingresar una serie de números más rápido.,

Use declaraciones IF en fórmulas de amortización

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

asumiendo 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):

=IF(A8<=$C$3*$C$4, PMT($C$2/$C$4, $C$3*$C$4, $C$5), "")

interés (C8):

=IF(A8<=$C$3*$C$4, IPMT($C$2/$C$4, A8, $C$3*$C$4, $C$5), "")

Principal (D8):

=IF(A8<=$C$3*$C$4,PPMT($C$2/$C$4, A8, $C$3*$C$4, $C$5), "")

Balance:

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

=C5+D8

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

=IF(A9<=$C$3*$C$4, E8+D9, "")

como resultado, tiene un cronograma de amortización calculado correctamente y un montón de filas vacías con los números del período Después de que se pague el préstamo.,

ocultar números de períodos adicionales

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

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

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

=$A8>$C$3*$C$4

Nota importante!, Para que la fórmula de formato condicional funcione correctamente, asegúrese de usar referencias de celda absolutas para las celdas de plazo del préstamo y pagos por año que multiplique ($C*3 * C C 4 4). El producto se compara con la celda del Período 1, para la que se utiliza una referencia de celda mixta: columna absoluta y fila relativa ($A8).

después de eso, haga clic en el botón Format Format y elija el color de fuente blanco. Hecho!,

haga un resumen de 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):

=-SUM(B8:B367)

interés Total (F3):

=-SUM(C8:C367)

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

Eso es todo! Nuestro programa de amortización de préstamos se ha completado y listo para ir!,

Descargar Programa de amortización de préstamos para Excel

cómo hacer un programa 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, dejan fuera una característica útil que muchos pagadores de préstamos están interesados en – pagos adicionales para pagar 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.

defina las 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:

InterestRate – 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)
ExtraPayment – C6 (pago adicional por período)

Calcule un pago programado

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

=IFERROR(-PMT(InterestRate/PaymentsPerYear, LoanTerm*PaymentsPerYear, LoanAmount), "")

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

ingrese esta fórmula en alguna celda (G2 en nuestro caso) y nombre esa celda ScheduledPayment.,

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 del período 0 más tarde si es necesario).

si desea crear un programa de amortización reutilizable, introduzca el número máximo posible de períodos de pago (de 0 a 360 en este ejemplo).

para el período 0 (fila 9 en nuestro caso), tire del valor del saldo, que es igual al monto original del préstamo., Todas las otras células en esta fila se quedan vacías:

la Fórmula en G9:

=LoanAmount

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

Esta 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.

Nota. En este ejemplo, el período 0 está en la fila 9 y el período 1 está en la fila 10. Si su tabla de amortización comienza en una fila diferente, asegúrese de ajustar las referencias de celda en consecuencia.,

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 al saldo restante (G9), use el pago programado. De lo contrario, agregue el saldo restante y los intereses del mes anterior.

=IFERROR(IF(ScheduledPayment<=G9, ScheduledPayment, G9+G9*InterestRate/PaymentsPerYear), "")

Como una precaución adicional, cerramos esta y todas las fórmulas en la función SI.ERROR., Esto evitará un montón de errores si algunas de las celdas de entrada están vacías o contienen valores no válidos.

pago Extra (C10):

Use una fórmula IF con la siguiente lógica:

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

=IFERROR(IF(ExtraPayment<G9-E10, ExtraPayment, G9-E10), "")

Tip. Si tiene pagos adicionales variables, simplemente escriba los montos individuales directamente en la columna pago adicional.,

pago Total (D10)

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

=IFERROR(B10+C10, "")

Principal (E10)

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

=IFERROR(IF(B10>0, MIN(B10-F10, G9), 0), "")

tenga en cuenta que el principal solo incluye la parte del pago programado (¡no el pago adicional!) que va hacia el principal del préstamo.,

interés (F10)

si el pago programado para un período dado 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, devuelva 0.

=IFERROR(IF(B10>0, InterestRate/PaymentsPerYear*G9, 0), "")

saldo (G10)

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

=IFERROR(IF(G9 >0, G9-E10-C10, 0), "")

Nota. Debido a que algunas de las fórmulas de referencia cruzada entre sí (no referencia circular!), pueden mostrar resultados incorrectos en el proceso. Por lo tanto, no comience a solucionar problemas hasta que ingrese la última fórmula en su tabla de amortización.

si todo se hace correctamente, su programa de amortización del préstamo en este punto debería tener un aspecto similar a este:

Ocultar períodos adicionales

configure una regla de formato condicional para ocultar los valores en períodos no utilizados como se explica en este consejo.,pagos por año:

=LoanTerm*PaymentsPerYear

número real de pagos:

contar celdas en la columna de pago Total que son mayores que cero, comenzando con el período 1:

=COUNTIF(D10:D369,">"&0)

pagos adicionales Totales:

sumar celdas en la columna comenzando con el período 1:

=SUM(C10:C369)

interés total:

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

=SUM(F10:F369)

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

Descargar Programa de amortización de préstamos con pagos adicionales

programa de amortización Plantilla Excel

para hacer un programa de amortización de préstamos de primera categoría en poco tiempo, haga uso de las plantillas incorporadas de Excel. Simplemente vaya a Archivo > nuevo, escriba «programa de amortización»en el cuadro de búsqueda y elija la plantilla que le guste, por ejemplo, esta con pagos adicionales:

así es como crea un programa de amortización de préstamo o hipoteca en Excel., Gracias por leer y espero verte en nuestro blog la próxima semana!

descargas disponibles

programa de amortización Libro de trabajo de muestra

Cómo calcular el interés compuesto en Excel
Cómo encontrar CAGR (tasa de crecimiento anual compuesto) en Excel
calcular porcentaje en Excel con ejemplos de fórmulas

cómo hacer un programa de amortización de préstamos en Excel (con pagos adicionales)