CALCULADORA DE PRÉSTAMOS EN EXCEL SEGÚN EL MÉTODO DE AMORTIZACIÓN FRANCÉS
Créditos: Canal de Youtube “1minSensei”
Hola a todos,
Aquí os adjunto un aplicación/macro para Excel que calcula las cuotas a pagar, gastos de constitución y TAE (Tasa Anual Equivalente) de un préstamo bancario de cualquier tipo que se amortiza por el método francés, es decir, las cuotas que se pagan son siempre constantes. Es importante resaltar por tanto, que en un préstamo francés la cuota está formada por el principal que se devuelve al banco (es decir, dinero que nos ha prestado el banco) e intereses (ganancia que obtiene el banco por prestarnos el dinero), y aunque ambas cantidades van cambiando a lo largo de la vida del préstamo, su suma (que es la cuota que se paga) es constante.
Para poder hacer funcionar la aplicación, tenéis que tener habilitadas las macros en Excel. Os váis al menú: Herramientas, Macro, Seguridad, eligiendo un nivel de seguridad medio. Luego abrís el archivo Excel y cuando os pregunte en una pantalla habilitáis las macros.
Las variables para calcular la cuota del préstamo son:
- Introduzca la cantidad a financiar en euros. Ej. 250.000. Es decir, estaríamos pidiendo un préstamo a la entidad bancaria por valor de €250.000.
- Número de años de duración del préstamo. Ej. 35.
- Introduzca el interés nominal anual del préstamo en %. Ej. 5. Por lo general los préstamos bancarios están referenciados a un tipo de interés variable que se llama EURÍBOR más un tipo fijo (llamado diferencial y que dependiendo de la entidad bancaria y la solvencia del cliente será mayor o menor). Este interés fluctúa diariamente, pero para cálculos hipotecarios se debería usar un valor promedio del 5%. Téngase en cuenta que el interés nominal anual no es el coste anual del préstamo o TAE. Por favor consulte el curso de matemáticas financieras para aclarar los conceptos.
- Con qué periodicidad se pagarán las cuotas: 1. Mensual, 2. Trimestral, 3. Semestral. 4. Anual. Para el ejemplo hemos elegido pago mensual de la cuotas, marcamos “1”.
- Introduzca los gastos de constitución y apertura. Son un % sobre el importe financiado. Ej. 0,5. Es decir, la entidad bancaria nos cobra una comisión del 0,5% sobre los €250.000 financiados (€1.250) por estudiar y formalizar el préstamo bancario.
Para esta simulación la cuota mensual a pagar sería de 1.261€ mensuales, con un coste total de la operación del 5,13% TAE (que como podemos ver no es del 5%, porque adicionalmente hay costes de formalización del préstamo). Adicionalmente la aplicación os muestra un gráfico con la cantidad de principal que se amortiza e intereses bancarios pagados a lo largo de la vida de préstamo.
Para entender cuál es la lógica programada en la macros, debemos comprender:
- Al resolver un préstamo, lo primero que debemos calcular es la cuota a pagar. En todo préstamo hay que resolver la ecuación financiera siguiente: la cantidad que nos presta el banco en t=0, en este caso €250.000 debe ser igual a la deuda que contraemos con en banco en t=0. Si la duración del préstamo es 35 años (35*12=420 cuotas mensuales constantes), al banco le debemos 420 cuotas mensuales, que todas ellas llevadas a t=0 deben tener un valor de €250.000. Por matemáticas financieras y valor temporal del dinero, moveremos flujos monetarios individuales colocados en distintos instantes de tiempo, con la fórmula del interés compuesto usando un tipo de interés nominal del 5%. El resultado de esta operación es la siguiente fórmula:
- Capital financiado = Cuota constante?* [1-(1+i)^(-n*t)]/i
- Para nuestro caso concreto, como los pago son mensuales, el interés nominal a usar debe ser el nominal mensual (5%/12) y el tiempo en meses (12 meses * 35 años = 420 meses).
- Aplicando la fórmula obtenemos:
- 250.000=Cuota*[1-(5%/12)^(-420)]/(5%/12)
- Despejando la ecuación, obtenemos una cuota mensual de €1.250.
- A continuación, debemos calcular los intereses pagados el primer mes: (5%/12)*250.000. Y la cantidad amortizada de principal el primer mes será: 1.250-(5%/12)*250.000. La deuda pendiente con el banco al finalizar el primer mes ya no será €250.000, sino 250.000-cantidad amortizada (que no es la cuota!) de 1.250-(5%/12).
- Este proceso lo repetimos de forma iterativa durante los 420 periodos.
- Para comprobar que hemos resuelto correctamente el préstamo, la cantidad de principal que debemos al banco en t=35 años=420 meses, debe ser €0.
Cualquier comentario es bienvenido!
Saludos y espero que os sea de utilidad.
Antonio Alcocer