FÓRMULAS FINANCIERAS EN EXCEL (II). PAGO DE PRÉSTAMOS. PAGO

Una de las fórmulas que la mayoría de nosotros hemos usado es el cálculo de la anualidad para la amortización de un préstamo. Digo que la hemos usado la mayoría porque se trata de la fórmula que se emplea para el cálculo de la anualidad de un crédito hipotecario, más conocido como HIPOTECA.

                Dicha fórmula es la siguiente:

 

                Siendo:

• A: La cuota a pagar en cada periodo
• C: El capital a amortizar
• i: El interés del periodo.
• t: El número de periodos.

              

  Es el conocido MÉTODO FRANCÉS para la amortización de préstamos.

                Es decir, si la amortización se hace anualmente, “t” será el número de años, mientras que “i” será el interés anual. Si por el contrario, dicha amortización fuera mensual, “t” sería el número de años multiplicada por 12 meses que tiene un año y el interés “i” sería el interés anual dividido entre 12.

                Otros periodos pueden ser fácilmente deducidos por el lector de la misma forma.

                Excel nos proporciona la función financiera PAGO que nos proporciona este valor de forma inmediata.

                 Al preguntar al asistente de fórmulas de Excel nos aparece un cuadro de diálogo como el que aparece a continuación  

              

             Es decir, siguiendo la nomenclatura anterior introduciremos:

• Tasa = i
• Nper = t
• Va = C

                La casilla Vf la deberemos dejar en blanco, en otro post explicaremos su uso, mientras que en tipo deberemos introducir “0” o dejarlo en blanco, cuando estemos usando la fórmula anterior, es decir, cuando el pago de la amortización se produce al final del periodo.

                En la hoja de cálculo

PAGO_PRESTAMO

se desarrollan los cálculos anteriores.

                Hay que observar de que el capital inicial se introduce en positivo (es decir, recibimos el dinero), mientras que la cuota a pagar nos aparecerá en negativo. 

                Los resultados de la hoja y sus fórmulas son las siguientes

A 1 CAPITAL A AMORTIZAR	B 100.000,00
2 TIEMPO EN AÑOS	25,00
3  PAGOS	MENSUAL
4  PERIODOS POR AÑO	12
5  PERIODOS TOTALES	300
6  INTERÉS ANUAL	3,00%
7  INTERÉS POR PERIODO	0,25%
8  CUOTA POR PERIODO	474,21
PAGO	-474,21 €

A 1 CAPITAL A AMORTIZAR	B 100000
2  TIEMPO EN AÑOS	25
3  PAGOS	MENSUAL
4  PERIODOS POR AÑO	=+BUSCARV(B3;M2:N7;2;FALSO)
5  PERIODOS TOTALES	=+B4*B2
6  INTERÉS ANUAL	0,03
7  INTERÉS POR PERIODO	=+B6/B4
8  CUOTA POR PERIODO	=B1*((B7*(1+B7)^B5)/((1+B7)^B5-1))
PAGO	=PAGO(B7;B5;B1)

M 1  PAGOS	N PERIODOS
2  ANUAL	1
3  MENSUAL	12
4  BIMESTRAL	2
5  TRIMESTRAL	4
6  CUATRIMESTRAL	3
7  SEMESTRAL	2