PMT() Excel: Calcular las cuotas del préstamo y las aportaciones al ahorro

La función PMT() de Excel calcula el pago periódico necesario para liquidar un préstamo o inversión con pagos constantes y un tipo de interés fijo. Se utiliza mucho en modelos financieros, presupuestos y cálculos de préstamos para determinar cuánto pagarás cada periodo por un préstamo o cuánto necesitas aportar para alcanzar un objetivo de ahorro.

Por ejemplo, si estás pensando en comprar una casa y necesitas entender los pagos mensuales de tu hipoteca, la función PMT() te ofrece una imagen clara de tu compromiso financiero. Del mismo modo, las empresas utilizan esta función para planificar la financiación de equipos o determinar las estructuras de pago de los planes de cuotas de los clientes.

Esta sencilla fórmula tiene en cuenta el importe del préstamo, el tipo de interés y el periodo de tiempo para ofrecer cálculos de pago precisos que ayuden a particulares y organizaciones a tomar decisiones financieras con conocimiento de causa.

Respuesta rápida

La función PMT() de Excel calcula importes de pagos regulares para préstamos o inversiones con pagos constantes y un tipo de interés fijo. Para una hipoteca típica de 300.000 $ a 30 años con un tipo de interés anual del 4,5%, la cuota mensual sería de aproximadamente 1.520,06 $. La fórmula sería

=PMT(4.5%/12, 30*12, 300000)

Sintaxis y parámetros de la función PMT()

La sintaxis de la función PMT() es:

=PMT(rate, nper, pv, [fv], [type])

Vamos a desglosar cada parámetro:

• rate: Tipo de interés por periodo. Si tu préstamo tiene un tipo de interés anual del 6% pero los pagos se hacen mensualmente, utilizarías 6%/12 o 0,5% al mes.

• nper: Número total de periodos de pago. Para un préstamo a 5 años con pagos mensuales, utilizarías 5*12 o 60 periodos.

• pv: Valor actual (importe del préstamo o principal). Es la cantidad inicial prestada o invertida.

• fv (opcional): Valor futuro tras el pago final. Si se omite, pasa a 0, lo que significa que el préstamo está totalmente pagado.

• type (opcional): Plazo de pago. Utiliza 0 para los pagos al final del periodo (por defecto) o 1 para los pagos al principio.

Es importante comprender la convención del flujo de caja en Excel: Los pagos (dinero que sale) se expresan en números negativos, mientras que las entradas (dinero que entra) son positivas. Por eso, los resultados de la función PMT() suelen aparecer como valores negativos: representan los pagos que estás haciendo.

Comprender los ajustes de frecuencia de pago

Cuando utilices la función PMT(), deberás ajustar el tipo y el número de periodos en función de la frecuencia de capitalización del préstamo. Muchos préstamos indican tipos de interés anuales, pero los pagos suelen ser más frecuentes.

A continuación te explicamos cómo ajustar la tasa anual y el número de periodos en función de las distintas frecuencias de pago:

• MensualmenteTasa anual ÷ 12, número de años × 12
• TrimestralmenteTasa anual ÷ 4, número de años × 4
• SemestralmenteTipo anual ÷ 2, número de años × 2

Por ejemplo, si tienes un préstamo a 5 años con un tipo de interés anual del 6% y cuotas mensuales, utilizarías:

• Tasa: 6%/12 = 0,5% al mes
• Número de periodos: 5×12 = 60 meses

Esta tabla resume los ajustes para un préstamo a 5 años con un tipo de interés anual del 6%:

Hacer estos ajustes garantiza que tus cálculos de pago reflejen con precisión la frecuencia de capitalización de tu préstamo o inversión.

Usos comunes de la función PMT() de Excel

Veamos cuáles son probablemente los dos usos más comunes. 

Función PMT() para préstamos hipotecarios

Veamos un ejemplo real de cálculo de las cuotas mensuales de un préstamo hipotecario:

• Importe del préstamo: $400,000
• Tipo de interés: 6% anual
• Plazo del préstamo: 20 años
• Frecuencia de pago: Mensualmente

En primer lugar, tenemos que ajustar la tasa y los periodos:

• Tasa ajustada: 6%/12 = 0,5% al mes
• Periodos ajustados: 20×12 = 240 meses

La fórmula sería

Función PMT() para préstamo hipotecario. Imagen del autor.

=PMT(6%/12, 20*12, 400000)

Esto nos da un resultado de -2.865,72$. El signo negativo indica que se trata de un pago (salida de efectivo). Así que la cuota mensual de tu hipoteca sería de 2.865,72 $.

Función PMT() para ahorros e inversiones

La función PMT() también puede ayudarte a calcular cuánto necesitas ahorrar regularmente para alcanzar un objetivo financiero futuro.

Por ejemplo, supongamos que quieres ahorrar 50.000 $ en 18 años con una rentabilidad anual del 6%. ¿Cuánto debes aportar mensualmente?

En este caso:

• Valor futuro (vf): 50.000 $ (tu objetivo)
• Valor actual (vp): 0 (suponiendo que partas de cero)
• Tasa: 6%/12 = 0,5% mensual
• Periodos: 18×12 = 216 meses

La fórmula sería

Función PMT() para Ahorros e Inversiones. Imagen del autor.

=PMT(6%/12, 18*12, 0, 50000)

Esto supone un rendimiento aproximado de 129,08 $, lo que significa que tendrías que depositar unos 130 $ mensuales para alcanzar tu objetivo de 50.000 $ en 18 años, suponiendo un rendimiento anual del 6%.

En este caso, utilizamos un valor positivo para el valor futuro (50000) porque representa dinero que recibirás en el futuro. Al calcular cuánto ahorrar para alcanzar un objetivo, el resultado de PMT() será negativo (indicando que necesitas aportar/pagar esa cantidad), y el valor futuro será positivo (indicando el dinero que tendrás al final).

Errores comunes y solución de problemas

Cuando trabajes con la función PMT(), puedes encontrarte con mensajes de error o resultados inesperados. Aquí tienes algunos problemas comunes y cómo resolverlos:

Errores #NUM! y #VALOR!

La función PMT() puede devolver estos errores por varias razones:

• #NUM! suele aparecer cuando Excel no puede calcular un resultado válido con las entradas dadas. Esto puede ocurrir si tu tipo de interés es extremadamente alto o tu plazo es inusualmente largo.

• #VALUE! suele indicar que hay un problema con uno de tus valores de entrada, como un texto donde se espera un número.

Errores comunes que debes evitar

Olvidar ajustar el tipo y el nper para los pagos mensuales/trimestrales

Uno de los errores más frecuentes es utilizar directamente el tipo de interés anual en lugar de dividirlo por el número de pagos al año. Por ejemplo, utilizar el 5% en lugar del 5%/12 para los pagos mensuales.

Incorrect: =PMT(5%, 30*12, 250000)
Correct: =PMT(5%/12, 30*12, 250000)

Introducir valores no numéricos

Asegúrate de que todos los parámetros contienen valores numéricos o referencias de celda a valores numéricos. Incluir accidentalmente texto o celdas en blanco provocará errores.

Utilizar signos incorrectos (positivo en lugar de negativo)

Recuerda que en las funciones financieras, el dinero que pagas debe ser negativo y el que recibes debe ser positivo. Si el importe de tu préstamo (pv) se introduce como negativo, tu pago aparecerá como positivo (y viceversa). Para préstamos:

If PV is positive: PMT will be negative (you're paying out)
If PV is negative: PMT will be positive (appears as money coming in)

Mezclar los recuentos de periodos

Asegúrate de que tu nper (número de periodos) coincide con tu periodo de tarifa. Si has ajustado tu tarifa a una cifra mensual, tus periodos también deben ser en meses.

Detectar estos errores comunes te ayudará a obtener resultados precisos cuando utilices la función PMT() para cálculos financieros.

Casos de uso avanzados y funciones relacionadas

La función PMT() funciona bien junto con otras funciones financieras de Excel para crear análisis completos de préstamos y modelos financieros. He aquí cómo PMT() se integra con otras funciones financieras:

IPMT (parte del pago correspondiente a los intereses)

La función IPMT() calcula la parte de intereses del pago de un préstamo durante un periodo determinado. Si lo combinas con PMT(), podrás ver qué parte de cada pago se destina a intereses.

=IPMT(rate, per, nper, pv, [fv], [type])

Volviendo a nuestro ejemplo de hipoteca de 300.000 $ de antes, podemos hallar la parte de intereses del 12º pago (hipoteca a 30 años al 4,5% de interés anual):

Función IPMT() para Préstamo Hipotecario. Imagen del autor.

=IPMT(4.5%/12, 12, 30*12, 300000)

Esto da -1.108,39 $, lo que significa que 1.108,39 $ de este pago se destinan a intereses.

PPMT (parte principal del pago)

La función PPMT() calcula la parte del principal del pago de un préstamo durante un periodo determinado. Esto muestra qué parte de tu pago reduce el saldo real del préstamo.

=PPMT(rate, per, nper, pv, [fv], [type])	

Utilizando el mismo ejemplo de hipoteca, para hallar la parte de capital del 12º pago:

Fórmula PPMT para Préstamo Hipotecario. Imagen del autor.

=PPMT(4.5%/12, 12, 30*12, 300000)

El resultado es -411,66 $, lo que significa que se destinan 411,66 $ a reducir el saldo del préstamo.

Observa que la suma de las partes de interés y principal (-1.108,39 $ - 411,66 $ = -1.520,05 $) equivale aproximadamente al importe total de nuestro pago mensual. Esta relación siempre se mantiene: PMT = IPMT + PPMT para un periodo determinado.

NPER (número de periodos necesarios para un préstamo)

Si sabes cuánto puedes pagar mensualmente, NPER() te ayudará a determinar cuánto tiempo tardarás en amortizar un préstamo:

=NPER(rate, pmt, pv, [fv], [type])

Por ejemplo, si puedes pagar 2.000 $ mensuales por un préstamo de 300.000 $ al 4,5% de interés anual:

Fórmula NPER para Préstamo Hipotecario. Imagen del autor.

=NPER(4.5%/12, -2000, 300000)

Esto da un resultado de 220,86, lo que significa que se tardaría aproximadamente 221 meses (unos 18,4 años) en pagar el préstamo. Esto es significativamente más rápido que el plazo estándar de 30 años, porque pagas más que el pago mínimo.

TIPO (encontrar el tipo de interés)

Cuando conoces el importe del préstamo, el importe del pago y el plazo, RATE() puede encontrar el tipo de interés:

=RATE(nper, pmt, pv, [fv], [type], [guess])	

Para un préstamo de 300.000 $ con cuotas mensuales de 1.520,06 $ a 30 años:

Fórmula del TIPO para el Préstamo Hipotecario. Imagen del autor.

=RATE(30*12, -1520.06, 300000)*12

Esto nos devuelve un 4,5%, lo que confirma que nuestro pago mensual de 1.520,06 $ corresponde a un tipo de interés anual del 4,5%. Multiplicamos el resultado por 12 para convertir la tasa mensual en una tasa anual.

Técnicas de búsqueda de objetivos

La función Buscar Objetivo de Excel puede ayudar a responder preguntas como:

• ¿Cuánto puedo pedir prestado si puedo permitirme un pago mensual concreto?
• ¿Qué tipo de interés tendría que negociar para mantener los pagos por debajo de una cantidad determinada?

Por ejemplo, si queremos mantener nuestros pagos mensuales en 2.000 $ para la hipoteca de 400.000 $ de nuestro ejemplo anterior (tipo de interés del 6%, plazo de 20 años), podríamos utilizar Goal Seek para determinar cuánto tendríamos que pagar de entrada. Para obtener un tutorial detallado sobre este tema, echa un vistazo a nuestra guía sobre Cómo utilizar la búsqueda de objetivos en Excel.

Estos enfoques integrados permiten una planificación financiera y una toma de decisiones más sofisticadas, más allá de los cálculos básicos de pagos. Combinando PMT() con estas funciones relacionadas, puedes construir planes completos de amortización de préstamos y analizar diversos escenarios financieros.

Puntos clave

Utilizar correctamente la función PMT() en Excel requiere atención a los detalles, sobre todo al ajustar tasas y periodos. Aquí tienes los puntos más importantes que debes recordar:

• Ajusta siempre el tipo de interés y el número de periodos en función de la frecuencia de pago. Para los pagos mensuales de un tipo anual, divide el tipo por 12 y multiplica los años por 12.

• La función PMT() sigue una convención específica de flujo de caja: los pagos que realizas aparecen como números negativos, mientras que el dinero que recibes aparece como positivo.

• PMT() forma parte de una familia de funciones financieras (IPMT(), PPMT(), NPER(), RATE()) que juntas pueden crear análisis completos de préstamos e inversiones.

• Los errores comunes pueden evitarse comprobando dos veces tus entradas, asegurándote de que has ajustado correctamente la frecuencia de pago y utilizando los signos correctos para los flujos de caja.

• La función PMT() no es sólo para préstamos: es igualmente valiosa para la planificación de ahorros e inversiones cuando necesitas determinar cantidades de aportación periódicas.

Las plantillas de Excel que incorporan la función PMT() pueden ahorrar tiempo y reducir los errores al trabajar con cálculos financieros repetidos. Intenta crear una plantilla reutilizable para comparaciones de préstamos o planes de ahorro para agilizar tu análisis financiero.

Conclusión

La aplicación de la función PMT() en Excel proporciona una base sólida para tomar decisiones financieras con conocimiento de causa. Tanto si calculas los pagos de la hipoteca, como si planificas las aportaciones al ahorro o evalúas las oportunidades de inversión, esta versátil función te ayuda a comprender las implicaciones financieras de distintos escenarios.

Utilizando la función PMT() con funciones relacionadas como IPMT(), PPMT(), NPER() y RATE(), puedes crear modelos financieros completos que respondan a preguntas complejas sobre préstamos e inversiones. Si añades la Búsqueda de objetivos y el análisis de escenarios, mejorarás aún más tu capacidad de explorar distintas posibilidades financieras.

Si quieres profundizar en tus conocimientos de Excel, te ofrecemos excelentes recursos para continuar tu viaje de aprendizaje:

• Nuestro curso de Fundamentos de Excel es perfecto para quienes desean adquirir una base completa en Excel, que abarca desde la preparación y visualización de datos hasta las técnicas de análisis. Esta vía no requiere experiencia previa y te guía a través de ejercicios prácticos con escenarios del mundo real.
• Para los interesados específicamente en aplicaciones financieras, nuestro curso de Modelización Financiera en Excel te enseña a trabajar con flujos de caja, análisis de escenarios, valor temporal del dinero y presupuesto de capital. Este curso de nivel intermedio lo imparten profesionales del sector e incluye ejercicios prácticos con datos financieros.

Con estos conocimientos y recursos, estarás bien equipado para aplicar la función PMT() y las herramientas de Excel relacionadas a tus procesos de toma de decisiones financieras, creando planes de amortización de préstamos y construyendo sofisticados modelos de inversión.