Cómo Calcular la Amortización de Préstamos de Forma Sencilla

La función PAGO en Excel es una herramienta financiera fundamental que permite calcular el pago periódico y constante de un préstamo, basándose en un tipo de interés y un número de pagos fijos. Es la fórmula ideal para determinar rápidamente la cuota mensual de una hipoteca, un préstamo para un coche o cualquier otra forma de financiación que implique una amortización gradual de la deuda.

Esta función asume que tanto los pagos como el tipo de interés no varían durante toda la vida del préstamo. El resultado que devuelve la función PAGO incluye el capital y los intereses, pero no contempla impuestos, comisiones de apertura o cualquier otro gasto asociado al préstamo que pueda ser necesario considerar en un cálculo real.

Sintaxis

=PAGO(tasa; nper; va; [vf]; [tipo])

Para el cálculo de la amortización de un préstamo, la sintaxis más común y sencilla es:

=PAGO(tasa; nper; -valor_prestamo)

A continuación, se detallan los argumentos de la función:

• tasa (requerido): Es el tipo de interés del préstamo para un período. Es crucial que la unidad de tiempo de la tasa coincida con la de los pagos. Por ejemplo, si los pagos son mensuales, aquí se debe indicar el tipo de interés mensual (el tipo de interés anual dividido entre 12).
• nper (requerido): Es el número total de pagos del préstamo. De nuevo, la unidad de tiempo debe ser coherente con la tasa. Si los pagos son mensuales para un préstamo a 10 años, nper sería 120 (10 años * 12 meses).
• va (requerido): Es el valor actual o la cantidad total del préstamo, también conocido como el capital. En nuestro ejemplo simplificado, lo hemos llamado -valor_prestamo. Se introduce como un valor negativo porque, desde la perspectiva del prestatario, es una entrada de dinero (un valor positivo), pero para la fórmula de amortización, los pagos son salidas. Al poner el valor del préstamo en negativo, el resultado de la función PAGO será positivo, lo que es más intuitivo.
• vf (opcional): Es el valor futuro o el saldo en efectivo que se desea tener después de realizar el último pago. Si se omite, Excel asume que es 0, lo cual es lo habitual en un préstamo que se quiere amortizar por completo.
• tipo (opcional): Es un valor lógico (0 o 1) que indica cuándo vencen los pagos. Si se omite o se introduce un 0, se asume que los pagos se realizan al final del período. Si se introduce un 1, los pagos se realizan al principio del período.

Ejemplos

Ejemplo 1: Cálculo de la cuota de un préstamo personal

Supongamos que solicitamos un préstamo de 15.000 € a un tipo de interés anual del 6%, para devolverlo en 5 años con pagos mensuales. Para calcular la cuota mensual, debemos ajustar la tasa y el nper a meses.

• tasa mensual: 6% / 12 = 0,5%
• nper total de meses: 5 años * 12 meses/año = 60
• va: -15000

=PAGO(6%/12; 60; -15000) -> Resultado esperado: 290,02 €

La cuota mensual a pagar para amortizar completamente este préstamo sería de 290,02 €.

Ejemplo 2: Usando referencias de celda para calcular una hipoteca

Este es el método más flexible, ya que permite cambiar los datos de entrada y ver cómo afecta a la cuota mensual sin tener que reescribir la fórmula. Imaginemos los datos de una hipoteca en una tabla de Excel.

=PAGO(B2/12; B3*12; -B1) -> Resultado esperado: 808,33 €

La fórmula en la celda B4 calcula automáticamente la cuota mensual. Si cambiáramos el tipo de interés en la celda B2, la cuota se recalcularía al instante.

Ejemplo 3: Calcular el total de intereses pagados

La función PAGO también nos sirve como base para otros cálculos. Usando los datos del ejemplo anterior, podemos calcular fácilmente el total de intereses que se pagarán durante la vida de la hipoteca.

1. Primero, calculamos el total pagado: Cuota mensual * Número de meses.
2. Luego, restamos el capital inicial del préstamo.

=(PAGO(3,5%/12; 30*12; -180000) * (30*12)) - 180000 -> Resultado esperado: 110.998,80 €

A lo largo de los 30 años de la hipoteca, se pagarían casi 111.000 € solo en intereses.

Aplicaciones Prácticas

• 1Planificación Financiera Personal: Determinar la viabilidad de solicitar un préstamo personal o para un coche, entendiendo el impacto mensual en el presupuesto.
• 2Análisis de Hipotecas: Comparar diferentes ofertas de hipotecas de varios bancos, cambiando el tipo de interés y la duración para encontrar la opción más conveniente.
• 3Gestión de Deudas Empresariales: Las empresas pueden usar esta fórmula para planificar la amortización de préstamos para inversión, maquinaria o expansión.
• 4Creación de Tablas de Amortización: Aunque PAGO calcula la cuota total, es el primer paso para construir una tabla de amortización completa, que desglosa cuánto de cada cuota se destina a capital y cuánto a intereses, usando funciones como PAGOINT y PAGOPRIN.

Observaciones

Consistencia en las unidades: El error más común al usar la función PAGO es no hacer coincidir las unidades de tiempo de la tasa y de nper. Si los pagos son mensuales, la tasa de interés debe ser mensual y el número de períodos debe ser el total de meses. Si los pagos fueran trimestrales, la tasa debería ser trimestral (anual/4) y nper el número total de trimestres.

Convención de flujo de caja: Excel utiliza una convención de flujo de caja donde el dinero que se recibe (el capital del préstamo) es un número positivo, y el dinero que se paga (las cuotas) es negativo. Por eso, al usar =PAGO(tasa; nper; valor_prestamo) con un valor_prestamo positivo, el resultado será negativo. Para obtener un resultado positivo e más intuitivo, se introduce el capital del préstamo como un valor negativo: =PAGO(tasa; nper; -valor_prestamo).

Errores comunes

• #¡NUM!: Ocurre si el argumento tasa es menor o igual que -1, o si nper es igual a 0. Un préstamo no puede tener 0 cuotas.
• #¡VALOR!: Se produce si alguno de los argumentos no es un valor numérico. Asegúrate de que las celdas a las que haces referencia contienen números y no texto.

Funciones complementarias

Aunque no son alternativas directas para calcular la cuota, las siguientes funciones son esenciales para desglosar los componentes de esa cuota y crear un cuadro de amortización completo:

• =PAGOINT(tasa; período; nper; va; [vf]; [tipo])

  Calcula la porción de intereses de una cuota específica. El argumento período indica el número de la cuota que se quiere analizar (por ejemplo, 1 para la primera cuota).

• =PAGOPRIN(tasa; período; nper; va; [vf]; [tipo])

  Calcula la porción de capital que se amortiza en una cuota específica.

Para cualquier cuota, siempre se cumplirá que: PAGO = PAGOINT + PAGOPRIN.