La función IPMT en Excel es una herramienta poderosa que te permite calcular los intereses de un préstamo durante un período determinado. Con esta fórmula y la ayuda de una calculadora, podrás obtener información precisa sobre cómo se distribuyen los pagos entre el capital y los intereses. En este artículo, aprenderás paso a paso cómo utilizar la función IPMT en Excel y cómo utilizar una calculadora para facilitar aún más tus cálculos. ¡No te lo pierdas!
¿Qué es la función IPMT de Excel?
El Función IPMT en Excel determina la parte de interés del pago de un préstamo suponiendo una tasa de interés fija durante todo el plazo del préstamo.
¿Cómo uso la función IPMT en Excel?
La función IPMT de Excel calcula los pagos de intereses periódicos que un prestatario le debe a un prestamista por un préstamo, como una hipoteca o un préstamo para automóvil.
Al solicitar un préstamo, el prestatario está obligado a pagar intereses regulares al prestamista y reembolsar el monto original del préstamo al final del plazo del préstamo.
- Prestatario (deudor) → La tasa de interés refleja el costo de financiamiento para el prestatario, lo que afecta directamente el monto del pago de intereses (es decir, “salida de efectivo”).
- Prestamista (acreedor) → La tasa de interés refleja el rendimiento esperado dado el perfil de riesgo del prestatario, siendo el interés una de las fuentes de rendimiento para el prestamista (es decir, “entrada de fondos”).
La parte de interés del pago de un préstamo se puede calcular manualmente multiplicando la tasa de interés del período por el monto del préstamo, lo que tiende a ser la norma en los modelos financieros.
Sin embargo, la función IPMT de Excel fue diseñada específicamente para este propósito, es decir, para calcular el interés periódico adeudado.
El monto adeudado en cada período es función de la tasa de interés fija y del número de períodos transcurridos desde la fecha de emisión.
Más cerca de la fecha de vencimiento, el valor de los pagos de intereses disminuye junto con el saldo de capital del préstamo.
Sin embargo, si bien el interés pagado en cada período se basa en el monto principal pendiente, los pagos de intereses en sí NO reducen el monto principal.
Función Excel IPMT vs PMT: ¿Cuál es la diferencia?
La función PAGO en Excel calcula el pago periódico de un préstamo. Por ejemplo, los pagos mensuales de la hipoteca que debe un prestatario.
En cambio, el “IPMT” sólo calcula los intereses adeudados; de ahí el “yo” al frente.
- Función IPMT → Interés
- Función PMT → Capital + Interés
La función IPMT es parte de la función PMT, donde la primera solo calcula el componente de interés, mientras que la segunda calcula el pago completo, incluido el reembolso del principal y los intereses.
Sin embargo, ambos cálculos pueden implicar otras tarifas y costos, como impuestos, que pueden afectar la devolución recibida por el prestamista.
Fórmula de función IPMT de Excel
La fórmula para utilizar la función IPMT en Excel es la siguiente.
=IPMT(Tasa, por, nper, pv, [fv], [type])
Las entradas “fv” y “tipo” entre paréntesis son opcionales y se pueden omitir, es decir, dejarlas en blanco o ingresar un cero.
Dado que el pago de intereses es una «salida» de efectivo desde la perspectiva del prestatario, el pago calculado será negativo.
Para que nuestro cálculo del pago de intereses sea correcto, debemos hacer coincidir nuestras unidades.
| frecuencia | Ajuste de la tasa de interés (tasa de interés) | Ajuste del número de periodos (nper) |
|---|---|---|
| Mensual |
|
|
| Trimestral |
|
|
| Medio año |
|
|
| Anual |
Como ejemplo rápido, digamos que un prestatario obtuvo un préstamo a cuatro años con una tasa de interés anual del 9,0%, pagado mensualmente. En este caso, la tasa de interés mensual ajustada es del 0,75%.
- Tasa de interés mensual (tasa) = 9,0% ÷ 12 = 0,75%
Además, el número de períodos debe convertirse a meses multiplicando el plazo del préstamo especificado en años por la frecuencia de los pagos.
- Número de períodos (nper) = 4 × 12 = 48 períodos
Sintaxis de la función IPMT Excel
La siguiente tabla describe la sintaxis de la función IPMT de Excel con más detalle.
| Luchar | Descripción | ¿Necesario? |
|---|---|---|
| “tasa« |
|
|
| “nper« |
|
|
| “pv« |
|
|
| “fv« |
|
|
| “Tipo« |
|
Calculadora de funciones IPMT – Plantilla Excel
Pasamos ahora a un ejercicio de modelado, al que puede acceder rellenando el siguiente formulario.
1. Hipótesis de intereses sobre el ejercicio del crédito
Supongamos que un consumidor pidió un préstamo de 200.000 dólares para financiar la compra de un espacio de oficina.
El préstamo devenga intereses a una tasa de interés anual del 6,00% anual, con pagos realizados mensualmente al final de cada mes.
- Principal del préstamo (PV) = $400,000
- Tasa de interés anual (%) = 6,00%
- Plazo del préstamo = 20 años
- Frecuencia de capitalización = Mensual (12x)
Dado que nuestras unidades no son consistentes entre sí, el siguiente paso es convertir la tasa de interés anual a una tasa de interés mensual y convertir el plazo de nuestro préstamo a un valor mensual.
- Tasa de interés mensual (tasa) = 6,00% ÷ 12 = 0,50%
- Número de períodos (nper) = 10 años × 12 = 120 períodos
2. Frecuencia de pagos (crear lista desplegable)
Como siguiente paso opcional, crearemos una lista desplegable para alternar entre la frecuencia de pago siguiendo los siguientes pasos:
- Paso 1 → Seleccione la celda “Frecuencia de composición” (E8).
- Paso 2 → “Alt + A + V + V” abre el panel de validación de datos
- Paso 3 → Seleccione “Lista” en los criterios
- Paso 4 → Ingrese «Mensual», «Trimestral», «Semestral» o «Anual» en la línea «Fuente»
En la celda E9, creamos una fórmula con una serie de declaraciones «SI» para generar el número correspondiente que seleccionamos en la lista.
=SI(E8=“Mensual”,12,IF(E8=“Trimestral”,4,IF(E8=“Semestral”,2,IF(E8=“Anual”,1))))
Los dos argumentos restantes son «fv» y «tipo».
- Valor futuro → Para “fv”, la entrada permanece vacía porque suponemos que el préstamo se pagó en su totalidad al final del plazo (es decir, el prestatario no incumplió).
- Tipo → El otro supuesto, “Tipo”, se refiere al momento de los pagos, que omitimos al suponer que los pagos vencen al final de cada mes.
3. Calcule el pago de intereses utilizando la función IPMT de Excel.
En la parte final de nuestro tutorial de Excel, crearemos nuestro calendario de pagos de intereses utilizando los supuestos de los pasos anteriores.
La fórmula IPMT en Excel que utilizamos para calcular el interés de cada período es la siguiente.
=IPMT($6E$,B13,$E$10,$E$4)
Excepto la columna de puntos (por ejemplo, B13), las otras celdas deben anclarse haciendo clic en F4.
Una vez que ingresamos nuestras entradas en la función «IPMT» en Excel, el pago total de intereses sobre el préstamo a diez años es $9,722. Puede ver el interés total adeudado cada mes en nuestro plan de pago de intereses completo.
Utilizado en los principales bancos de inversión, el curso intensivo de Excel de Wall Street Prep lo convertirá en un usuario avanzado y lo diferenciará de sus competidores.
Aprende más
