El tutorial muestra cómo usar la función PAGO en Excel para calcular los pagos de un préstamo o inversión en función de la tasa de interés, la cantidad de pagos y el monto total del préstamo.
Antes de pedir dinero prestado, es bueno saber cómo funciona un préstamo. Gracias a las funciones financieras de Excel, como PMT, PPMT e IPMT, es fácil calcular el pago mensual o cualquier otro pago periódico de un préstamo. En este tutorial, veremos más de cerca la función PMT, analizaremos su sintaxis en detalle y mostraremos cómo crear su propia calculadora PMT en Excel.
¿Qué es la función PAGO en Excel?
La función PMT de Excel es una función financiera que calcula el pago de un préstamo en función de una tasa de interés constante, el número de períodos y el monto del préstamo.
«PMT» significa «pago», de ahí el nombre de la función.
Por ejemplo, si está solicitando un préstamo para automóvil a dos años con una tasa de interés anual del 7 % y el monto del préstamo de $30 000, una fórmula PMT puede indicarle cuáles serán sus pagos mensuales.
Para que la función PMT funcione correctamente en sus hojas de cálculo, tenga en cuenta estos datos:
- Para estar en línea con el modelo de flujo de efectivo general, el monto del pago se genera como un numero negativo porque es una salida de efectivo.
- El valor devuelto por la función PAGO incluye principal y interés pero no incluye ninguna tarifa, impuesto o pago de reserva que pueda estar asociado con un préstamo.
- Una fórmula PMT en Excel puede calcular el pago de un préstamo para diferentes frecuencias de pago, como semanalmente, mensual, trimestralo anualmente. este ejemplo muestra cómo hacerlo correctamente.
La función PAGO está disponible en Excel para Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010 y Excel 2007.
Función PMT de Excel: sintaxis y usos básicos
La función PAGO tiene los siguientes argumentos:
PAGO(tasa, nper, vp, [fv], [type])
Dónde:
- Velocidad (requerido) – la tasa de interés constante por período. Puede suministrarse como porcentaje o número decimal.
Por ejemplo, si haces anual pagos de un préstamo a una tasa de interés anual del 10 por ciento, use 10% o 0.1 como tasa. Si tu haces mensual pagos del mismo préstamo, luego use 10%/12 o 0.00833 para la tasa.
- Nper (obligatorio) – el número de pagos del préstamo, es decir, el número total de períodos durante los cuales se debe pagar el préstamo.
Por ejemplo, si haces anual pagos de un préstamo a 5 años, suministre 5 para nper. Si tu haces mensual pagos del mismo préstamo, luego multiplique el número de años por 12 y use 5*12 o 60 para nper.
- p.v. (requerido) – el valor actual, es decir, la cantidad total que todos los pagos futuros valen ahora. En el caso de un préstamo, es simplemente la cantidad original prestada.
- vf (opcional) – el valor futuro, o el saldo en efectivo que desea tener después de realizar el último pago. Si se omite, se supone que el valor futuro del préstamo es cero (0).
- Escribe (opcional) – especifica cuándo vencen los pagos:
- 0 u omitido: los pagos vencen al final de cada período.
- 1 – los pagos vencen al comienzo de cada período.
Por ejemplo, si pide un préstamo de $100,000 por 5 años con una tasa de interés anual del 7%, la siguiente fórmula calculará el pago anual:
=PMT(7%, 5, 100000)
para encontrar el mensualidad para el mismo préstamo, utilice esta fórmula:
=PMT(7%/12, 5*12, 100000)
O bien, puede ingresar los componentes conocidos de un préstamo en celdas separadas y hacer referencia a esas celdas en su fórmula PMT. Con la tasa de interés en B1, no. de años en B2 y el monto del préstamo en B3, la fórmula es tan simple como esta:
=PMT(B1, B2, B3)
Recuerde que el pago se devuelve como numero negativo porque esta cantidad será debitada (sustraída) de su cuenta bancaria.
Por defecto, Excel muestra el resultado en el Divisa formato, redondeado a 2 decimales, resaltado en rojo y encerrado entre paréntesis, como se muestra en la parte izquierda de la imagen a continuación. La imagen de la derecha muestra el mismo resultado en el General formato.
Si desea tener el pago como un positivo númerocoloque un signo menos antes de la fórmula PMT completa o del p.v. argumento (monto del préstamo):
=-PMT(B1, B2, B3)
o
=PMT(B1, B2, -B3)
Consejo. Para calcular el cantidad total pagado por el préstamo, multiplique el valor PMT devuelto por el número de períodos (npor valor). En nuestro caso, usaríamos esta ecuación: 24,389.07*5 y encontraríamos que la cantidad total es igual a $121,945.35.
Cómo usar la función PMT en Excel – ejemplos de fórmulas
A continuación, encontrará algunos ejemplos más de una fórmula PMT de Excel que muestra cómo calcular diferentes pagos periódicos para un préstamo de automóvil, préstamo de vivienda, préstamo hipotecario y similares.
Forma completa de la función PAGO en Excel
En su mayor parte, puede omitir los dos últimos argumentos en sus fórmulas PMT (como hicimos en los ejemplos anteriores) porque sus valores predeterminados cubren los casos de uso más típicos:
- vf omitido – implica saldo cero después del último pago.
- Escribe omitido – los pagos vencen en el final de cada periodo.
Si las condiciones de su préstamo son diferentes de las predeterminadas, utilice la forma completa de la fórmula PMT.
Como ejemplo, calculemos la cantidad de pagos anuales basado en estas celdas de entrada:
- B1 – tasa de interés anual
- B2 – plazo del préstamo (en años)
- B3 – monto del préstamo
- B4 – valor futuro (saldo después del último pago)
- B5 – tipo de anualidad:
- 0 (anualidad regular): los pagos se realizan al final de cada año.
- 1 (anualidad vencida): los pagos se realizan al comienzo del período, por ejemplo, pagos de alquiler o arrendamiento.
Proporcione estas referencias a su fórmula PMT de Excel:
=PMT(B1, B2, B3, B4, B5)
Y tendrás este resultado:
Calcula pagos semanales, mensuales, trimestrales y semestrales
Según la frecuencia de pago, debe utilizar los siguientes cálculos para los argumentos rate y nper:
- Para Velocidaddivida la tasa de interés anual por el número de pagos por año (que se considera igual al número de períodos de capitalización).
- Para npermultiplique el número de años por el número de pagos por año.
La siguiente tabla proporciona los detalles:
| Frecuencia de pago | Velocidad | Nper |
| Semanalmente | tasa de interés anual / 52 | años * 52 |
| Mensual | tasa de interés anual / 12 | años * 12 |
| Trimestral | tasa de interés anual / 4 | años * 4 |
| Semi anual | tasa de interés anual / 2 | años * 2 |
Por ejemplo, para encontrar el monto de un pago periódico de un préstamo de $5,000 con una tasa de interés anual del 8% y una duración de 3 años, use una de las fórmulas a continuación.
Semanalmente pago:
=PMT(8%/52, 3*52, 5000)
Mensual pago:
=PMT(8%/12, 3*12, 5000)
Trimestral pago:
=PMT(8%/4, 3*4, 5000)
Semi anual pago:
=PMT(8%/2, 3*2, 5000)
En todos los casos, se supone que el saldo después del último pago es $0 y los pagos vencen al final de cada período.
La siguiente captura de pantalla muestra los resultados de estas fórmulas:
Cómo hacer una calculadora PMT en Excel
Antes de seguir adelante y pedir dinero prestado, es lógico comparar diferentes condiciones de préstamo para encontrar las opciones que más le convengan. Para esto, creemos nuestra propia calculadora de pagos de préstamos en Excel.
- Para empezar, ingrese el monto del préstamo, la tasa de interés y el plazo del préstamo en celdas separadas (B3, B4, B5, respectivamente).
- Para poder elegir diferentes períodos y especificar cuándo vencen los pagos, cree listas desplegables con las siguientes opciones predefinidas (B6 y B7):
- Configure las tablas de búsqueda para Períodos (E2:F6) y Vencimiento de pagos (E8:F9) como se muestra en la siguiente captura de pantalla. Es importante que las etiquetas de texto en las tablas de búsqueda coincidan exactamente con los elementos de la lista desplegable correspondiente.
En las celdas junto a las listas desplegables, ingrese las siguientes fórmulas IFERROR VLOOKUP que extraerán el número de la tabla de búsqueda correspondiente al elemento seleccionado en la lista desplegable.Fórmula para Períodos (C6):
=IFERROR(VLOOKUP(B6, E2:F6, 2, 0), "")Fórmula para Pagos Vencidos (C7):
=IFERROR(VLOOKUP(B7, E8:F9, 2, 0), "")
- Escriba una fórmula PMT para calcular el pago periódico en función de sus celdas. En nuestro caso, la fórmula es la siguiente:
=IFERROR(-PMT(B4/C6, B5*C6, B3, 0, C7), "")Tenga en cuenta las siguientes cosas:
- los fv el argumento (0) está codificado en la fórmula porque siempre queremos un saldo cero después del último pago. En caso de que desee permitir que sus usuarios ingresen cualquier valor futuro, asigne una celda de entrada separada para el argumento fv.
- La función PMT está precedida por el signo menos para mostrar el resultado como un numero positivo.
- La función PMT está incluida en IFERROR para ocultar errores cuando algunos valores de entrada no están definidos.
La fórmula anterior va en B9. Y en la celda vecina (A9) mostramos una etiqueta correspondiente al período seleccionado (B6). Para ello basta concatenar el valor en B6 y el texto deseado:
=B6&" Payment"
- Finalmente, puede ocultar las tablas de búsqueda de la vista, agregar algunos toques finales de formato y su calculadora de PMT de Excel estará lista:
Descargar calculadora de PMT de Excel
La función PMT de Excel no funciona
Si su fórmula PMT de Excel no funciona o produce resultados incorrectos, es probable que se deba a las siguientes razones:
- ¡UN #NUM! puede ocurrir un error si el argumento de tasa es un número negativo o si nper es igual a 0.
- ¡UN VALOR! se produce un error si uno o más argumentos son valores de texto.
- Si el resultado de una fórmula PMT es mucho más alto o más bajo de lo esperado, asegúrese de ser coherente con las unidades proporcionadas para la tasa y los argumentos nper, lo que significa que ha convertido correctamente la tasa de interés anual a la tasa del período y la cantidad de años a semanas, meses o trimestres como se muestra en este ejemplo.
Así es como calculas la función PMT en Excel. Para ver más de cerca las fórmulas discutidas en este tutorial, puede descargar nuestro Ejemplos de fórmulas PMT. ¡Gracias por leer y espero verte en nuestro blog la próxima semana!