Fórmula para calcular el pago de una anualidad en Excel

Los pagos de préstamos son más fáciles y rápidos de calcular con Microsoft Office Excel. Se dedica mucho más tiempo al cálculo manual. Este artículo se centrará en los pagos de anualidades, las características de su cálculo, ventajas y desventajas.

¿Qué es el pago de una anualidad?

Un método de pago mensual de un préstamo, en el que la cantidad depositada no cambia durante todo el período del préstamo. Aquellos. en ciertas fechas de cada mes, una persona deposita una cantidad específica de dinero hasta que el préstamo se paga por completo.

Además, el interés del préstamo ya está incluido en el monto total pagado al banco.

Anualidad de clasificación

Los pagos de anualidades se pueden dividir en los siguientes tipos:

1. Fijado. Los pagos que no cambian tienen una tasa fija independientemente de las condiciones externas.
2. Divisa. La capacidad de cambiar el monto del pago en caso de una caída o aumento en el tipo de cambio.
3. indexado. Pagos según el nivel, indicador de inflación. Durante el período del préstamo, su tamaño cambia a menudo.
4. Variables. Renta vitalicia, que puede cambiar dependiendo del estado del sistema financiero, instrumentos.

¡Presta atención! Los pagos fijos son preferibles para todos los prestatarios, porque tienen poco riesgo.

Ventajas y desventajas de los pagos de anualidades.

Para comprender mejor el tema, es necesario estudiar las características clave de este tipo de pagos de préstamos. Tiene las siguientes ventajas:

• Estableciendo un monto específico de pago y la fecha de su pago.
• Alta disponibilidad para los prestatarios. Casi cualquier persona puede solicitar una renta vitalicia, independientemente de su situación financiera.
• La posibilidad de rebajar el importe de la cuota mensual con un aumento de la inflación.

No sin inconvenientes:

• Alta tasa. El prestatario pagará en exceso una cantidad mayor de dinero en comparación con el pago diferencial.
• Problemas derivados del deseo de saldar la deuda antes de lo previsto.
• Sin recálculos por pagos anticipados.

¿Cuál es el pago del préstamo?

El pago de la anualidad tiene los siguientes componentes:

• Interés pagado por una persona al liquidar un préstamo.
• Parte del monto principal.

Como resultado, la cantidad total de interés casi siempre excede la cantidad aportada por el prestatario para reducir la deuda.

Fórmula básica de pago de anualidad en Excel

Como se mencionó anteriormente, en Microsoft Office Excel puede trabajar con varios tipos de pagos de préstamos y anticipos. Anualidad no es una excepción. En general, la fórmula con la que puedes calcular rápidamente las cotizaciones de rentas vitalicias es la siguiente:  

¡Importante! Es imposible abrir corchetes en el denominador de esta expresión para simplificarla.

Los principales valores de la fórmula se descifran de la siguiente manera:

• AP – pago de anualidad (el nombre se abrevia).
• O – el tamaño de la deuda principal del prestatario.
• PS: la tasa de interés presentada mensualmente por un banco en particular.
• C es el número de meses que dura el préstamo.

Para asimilar la información, basta con dar algunos ejemplos del uso de esta fórmula. Se discutirán más adelante.

Ejemplos de uso de la función PAGO en Excel

Damos una condición simple del problema. Es necesario calcular el pago mensual del préstamo si el banco presenta un interés del 23% y el monto total es de 25000 rublos. El préstamo tendrá una duración de 3 años. El problema se resuelve de acuerdo con el algoritmo:

1. Haga una hoja de cálculo general en Excel basada en los datos de origen.

2. Active la función PMT e ingrese los argumentos para ella en el cuadro apropiado.
3. En el campo "Apuesta", ingrese la fórmula "B3/B5". Esta será la tasa de interés del préstamo.
4. En la línea “Nper” escribe el valor en la forma “B4*B5”. Este será el número total de pagos durante todo el plazo del préstamo.
5. Rellene el campo "PD". Aquí debe indicar la cantidad inicial tomada del banco, escribiendo el valor "B2".

6. Asegúrese de que después de hacer clic en "Aceptar" en la tabla de origen, se haya calculado el valor "Pago mensual".

¡Información Adicional! Un número negativo indica que el prestatario está gastando dinero.

Un ejemplo de cómo calcular el monto del sobrepago de un préstamo en Excel

En este problema, debe calcular la cantidad que pagará en exceso una persona que tomó un préstamo de 50000 rublos a una tasa de interés del 27% durante 5 años. En total, el prestatario realiza 12 pagos al año. Solución:

1. Compile la tabla de datos original.

2. Del monto total de los pagos, reste el monto inicial de acuerdo con la fórmula «=ABS(ПЛТ(B3/B5;B4*B5;B2)*B4*B5)-B2». Debe insertarse en la barra de fórmulas en la parte superior del menú principal del programa.
3. Como resultado, el monto de los sobrepagos aparecerá en la última línea de la placa creada. El prestatario pagará de más 41606 rublos.

La fórmula para calcular el pago mensual óptimo del préstamo en Excel

Una tarea con la siguiente condición: el cliente ha registrado una cuenta bancaria por 200000 rublos con la posibilidad de reposición mensual. Es necesario calcular la cantidad de pago que una persona debe hacer cada mes, para que después de 4 años tenga 2000000 rublos en su cuenta. La tasa es del 11%. Solución:

1. Cree una hoja de cálculo basada en los datos originales.

2. Ingrese la fórmula en la línea de entrada de Excel «=ПЛТ(B3/B5;B6*B5;-B2;B4)» y presione "Enter" desde el teclado. Las letras diferirán según las celdas en las que se coloque la tabla.
3. Compruebe que el importe de la contribución se calcula automáticamente en la última línea de la tabla.

¡Presta atención! Por lo tanto, para que el cliente acumule 4 rublos a una tasa de 2000000% en 11 años, debe depositar 28188 rublos cada mes. El signo menos en la cantidad indica que el cliente incurre en pérdidas al dar dinero al banco.

Características del uso de la función PAGO en Excel

En general, esta fórmula se escribe de la siguiente manera: =PAGO(tasa; nper; ps; [bs]; [tipo]). La función tiene las siguientes características:

1. Cuando se calculan las contribuciones mensuales, solo se toma en consideración la tasa anual.
2. Al especificar la tasa de interés, es importante recalcular en función del número de cuotas por año.
3. En lugar del argumento "Nper" en la fórmula, se indica un número específico. Este es el período de pago.

Cálculo de pago

En general, el pago de la anualidad se calcula en dos etapas. Para entender el tema, cada una de las etapas debe ser considerada por separado. Esto se discutirá más adelante.

Etapa 1: cálculo de la cuota mensual

Para calcular en Excel la cantidad que debes pagar cada mes de un préstamo con tasa fija, debes:

1. Compile la tabla de origen y seleccione la celda en la que desea mostrar el resultado y haga clic en el botón "Insertar función" en la parte superior.

2. En la lista de funciones, seleccione "PLT" y haga clic en "Aceptar".

3. En la siguiente ventana, establezca los argumentos para la función, indicando las líneas correspondientes en la tabla compilada. Al final de cada línea, debe hacer clic en el icono y luego seleccionar la celda deseada en la matriz.

4. Cuando se completen todos los argumentos, la fórmula apropiada se escribirá en la línea para ingresar valores, y el resultado del cálculo con un signo menos aparecerá en el campo de la tabla "Pago mensual".

¡Importante! Después de calcular la cuota, será posible calcular la cantidad que el prestatario pagará de más durante todo el período del préstamo.

Etapa 2: detalles de pago

El monto del sobrepago se puede calcular mensualmente. Como resultado, una persona comprenderá cuánto dinero gastará en un préstamo cada mes. El cálculo detallado se realiza de la siguiente manera:

1. Crear una hoja de cálculo para 24 meses.

2. Coloque el cursor en la primera celda de la tabla e inserte la función "OSPLT".

3. Complete los argumentos de la función de la misma manera.

4. Al completar el campo "Período", debe referirse al primer mes en la tabla, indicando la celda 1.

5. Verifique que la primera celda de la columna “Pago por la entidad del préstamo” esté llena.
6. Para llenar todas las filas de la primera columna, debe estirar la celda hasta el final de la tabla

7. Seleccione la función "PRPLT" para completar la segunda columna de la tabla.
8. Complete todos los argumentos en la ventana abierta de acuerdo con la captura de pantalla a continuación.

9. Calcula el pago mensual total sumando los valores de las dos columnas anteriores.

10. Para calcular el "Saldo a pagar", debe agregar la tasa de interés al pago en el cuerpo del préstamo y estirarlo hasta el final de la placa para cubrir todos los meses del préstamo.

¡Información Adicional! Al calcular el resto, se deben colgar signos de dólar en la fórmula para que no se mueva cuando se estire.

Cálculo de los pagos de anualidades de un préstamo en Excel

La función PMT se encarga de calcular la anualidad en Excel. El principio de cálculo en general es realizar los siguientes pasos:

1. Compile la tabla de datos original.
2. Cree un cronograma de pago de la deuda para cada mes.
3. Seleccione la primera celda en la columna "Pagos del préstamo" e ingrese la fórmula de cálculo “PLT ($B3/12;$B$4;$B$2)”.
4. El valor resultante se estira para todas las columnas de la placa.

Cálculo en MS Excel amortización del importe principal de la deuda

Los pagos de anualidades deben hacerse mensualmente en montos fijos. Y la tasa de interés no cambia.

Cálculo del saldo del importe principal (con BS=0, tipo=0)

Supongamos que se toma un préstamo de 100000 rublos por 10 años al 9%. Es necesario calcular el monto de la deuda principal en el 1er mes del 3er año. Solución:

1. Compile una hoja de datos y calcule el pago mensual utilizando la fórmula PV anterior.
2. Calcule la parte del pago requerida para pagar parte de la deuda utilizando la fórmula «=-PAGO-(PS-PS1)*artículo=-PAGO-(PS +PAGO+PS*artículo)”.
3. Calcule el monto de la deuda principal para 120 períodos utilizando una fórmula conocida.
4. Usando el operador HPMT encuentre la cantidad de interés pagado por el mes 25.
5. Comprobar resultado.

Cálculo de la cantidad de capital que se pagó entre dos períodos

Este cálculo se realiza mejor de una manera sencilla. Debe usar las siguientes fórmulas para calcular la cantidad en el intervalo para dos períodos:

• =«-BS(elemento; con_período; plt; [ps]; [tipo]) /(1+tipo *elemento)».
• = “+ BS(tarifa; inicio_período-1; plt; [ps]; [tipo]) /SI(inicio_período =1; 1; 1+tipo *tasa)”.

¡Presta atención! Las letras entre paréntesis se reemplazan con valores específicos.

Amortización anticipada con plazo o pago reducido

Si necesita reducir el plazo del préstamo, deberá realizar cálculos adicionales utilizando el operador IF. Así será posible controlar el saldo cero, que no debe alcanzarse antes del final del período de pago.

Para reducir los pagos, debe volver a calcular la contribución de cada mes anterior.

Calculadora de préstamos con pagos irregulares

Existen varias opciones de anualidades donde el prestatario puede depositar montos variables en cualquier día del mes. En tal situación, el saldo de la deuda y los intereses se calculan para cada día. Al mismo tiempo en Excel necesitas:

1. Ingrese los días del mes para los cuales se realizan los pagos e indique su número.
2. Verifique las cantidades negativas y positivas. Se prefieren los negativos.
3. Cuente los días entre dos fechas en las que se depositó el dinero.

Cálculo del pago periódico en MS Excel. Depósito a plazo

En Excel, puede calcular rápidamente la cantidad de pagos regulares, siempre que ya se haya acumulado una cantidad fija. Esta acción se realiza mediante la función PAGO después de compilar la tabla inicial.

Conclusión

Así, los pagos de anualidades son más fáciles, rápidos y eficientes de calcular en Excel. El operador de PMT es responsable de su cálculo. Más arriba se pueden encontrar ejemplos más detallados.