Simulador de amortización francesa en Excel

La amortización francesa distribuye un préstamo en cuotas constantes. En cada mes, una parte de la cuota paga intereses y la otra reduce el capital. En este tutorial crearás un simulador de amortización francesa en Excel para un préstamo de 12.000 euros, con 12 cuotas y una tasa anual del 12 %. La hoja mostrará capital, interés, cuota y saldo hasta llegar a cero. El método está disponible en las versiones de Excel indicadas para las funciones financieras usadas; Excel para la web y Excel para Mac pueden presentar diferencias de interfaz.

Qué es la amortización francesa y qué resuelve

La amortización francesa sirve para pagar un préstamo mediante cuotas iguales durante un número determinado de periodos. La cuota no cambia, pero su composición sí: al principio hay más intereses y menos capital; al final ocurre lo contrario.

En nuestro libro, la hoja 1 será el simulador. Los datos iniciales serán:

Cada fila del cuadro mensual mostrará el periodo, la cuota, el interés, el capital amortizado y el saldo pendiente. La relación esencial es:

cuota = interés del mes + capital del mes

El interés se calcula sobre el saldo que queda después del pago anterior. Al reducirse ese saldo, los intereses Bajan y la parte de capital aumenta.

Las cuatro variables que necesita el simulador

El simulador se organiza alrededor de cuatro variables:

La relación entre estas variables no es lineal: aumentar el plazo o reducir la tasa disminuye la cuota; aumentar el importe o la tasa la aumenta. Por eso conviene escribir los datos en celdas separadas, no dentro de cada fórmula del cuadro.

En B2 escribimos 12.000. En B3 escribimos 12. En B4 escribimos 12 %. Excel_guardará ese porcentaje como 0,12 y loshaw we will show it as 12 % si aplicamos formato de porcentaje.

La tasa mensual se obtiene dividiendo la tasa anual entre 12:

=B4/12

La cuota se calculará a partir del importe, la tasa mensual y el número de periodos. Cuando cambies cualquiera de esos datos, el simulador podrá actualizarse sin rehacer el cuadro.

Calcular la cuota mensual con PAGO

En la hoja 1, coloca el cursor en B6 y escribe la fórmula:

=PAGO(B4/12;B3;-B2)

PAGO (en inglés PMT) devuelve el pago periódico de un préstamo. El primer argumento es la tasa de cada periodo, el segundo es el número de pagos y el tercero es el valor presente del préstamo.

El signo negativo en -B2 hace que Excel trate el importe como una deuda y devuelva la cuota con signo positivo. El resultado debe aparecer con un formato monetario cercano a 1.061,25 euros. Si Excel muestra un signo negativo, revisa el signo del importe.

No escribas 1.061,25 dentro de una fórmula. Para表 refirieran el importe en la celda, usa el formato de celda o escribe solamente 12000 y aplica el formato correspondiente.

Construir el cuadro mes a mes

Ahora añade el cuadro de amortización debajo de los parámetros. En A9:E9 escribe estos encabezados: Periodo, Cuota, Interés, Capital y Saldo.

Después, copia los datos iniciales a las celdas de trabajo que usarás para el cálculo:

1. Selecciona A1:E4 y copia los encabezados o datos que quieras conservar.
2. Pega los parámetros en G9:H12, manteniendo importe, plazo y tasa.
3. En A10 escribe 1.
4. En B10 escribe =$B$6.
5. En C10 escribe el interés del primer mes sobre el importe prestado:

=$B$2*$B$4/12

1. En D10 calcula el capital de la primera cuota:

=B10-C10

1. En E10 calcula el saldo pendiente después del primer pago:

=$B$2-D10

Para continuar, arrastra el controlador de relleno desde A10:E10 hasta E21, pero no copies A10 como una fecha: debe mostrar 1, 2, 3… hasta 12. Si Excel no mantiene los números, escribe =A10+1 en A11 y arrastra hasta A21.

En las filas siguientes, el interés debe usar el saldo anterior, no el importe original:

=E10*$B$4/12

La capital será la parte de la cuota que reduce la deuda:

=B11-C11

El nuevo saldo será:

=E10-D11

La cuota se mantiene en:

=$B$6

Para evitar que la última cuota deje un residuo por redondeo, calcula el capital de la última fila como el saldo anterior menos el interés de ese mes. Así, el saldo final se ajusta a cero.

Los importes mostrados están redondeados a dos decimales; Excel conserva internamente más decimales. Si todos los meses se muestran iguales, puede que la cuota sea idéntica pero que la última fila esté absorbiendo el ajuste de precisión.

Comprobar que las cuotas se mantienen constantes

La característica de la amortización francesa es que la cuota total permanece constante. Para comprobarlo, añade una fórmula de control debajo del cuadro. En G13 escribe:

=MAX(B10:B21)-MIN(B10:B21)

Si el resultado es 0, todas las cuotas redondeadas son iguales. Si devuelve un valor pequeño, normalmente se debe a que la última cuota se ajustó para cerrar el saldo. También puedes usar:

=CONTAR.SI(B10:B21;B10)

Debe devolver 12 si las doce cuotas coinciden exactamente con la primera. Para comprobar solo las cuotas periódicas, usa el rango B10:B20:

=CONTAR.SI(B10:B20;B10)

En este ejemplo, la diferencia no es cero porque la última cuota se ajusta para liquidar exactamente el saldo. La comprobaciónHide el valor of the difference is a practical signal, not necessarily an error. To see the theoretical payment instead of the adjusted final payment, compare B10:B20.

Si el lector quiere comprobar la cuota teórica con más precisión, debe mantener los cálculos sin redondear visualmente. El redondeo de la cuota mensual puede provocar que la suma de los intereses y capitales mostrados no coincida exactamente con el importe.

Usar referencias absolutas al copiar el cálculo

Al copiar las fórmulas hacia abajo, Excel cambia las referencias relativas. Por eso los datos del préstamo deben permanecer fijos en $B$2, $B$3 y $B$4.

La fórmula correcta del interés utiliza referencias absolutas:

=$B$2*$B$4/12

Al copiar el interés a una fila posterior, utiliza el saldo anterior:

=E10*$B$4/12

La referencia $B$4 mantiene fija la tasa anual. Si escribes =E10*B4/12 y arrastras hacia abajo, Excel cambiará B4 a B5, B6 y así sucesivamente. En este modelo, B5 puede estar vacía, por lo que el interés de las filas siguientes seolenará a cero o puede aparecer un valor inesperado.

En Mac, la tecla para fijar referencias es Cmd+T; en Windows se utiliza F4. En un portátil puede ser necesario pulsar también Fn.

Resolver cuando el saldo no llega a cero

La cuota aparece con signo negativo

Si B6 muestra -1.061,25, el valor presente probably no está correctamente interpretado. En la fórmula del tutorial, B2 se introduce como crédito? Para obtener una cuota positiva, PAGO necesita un valor presente negativo:

=PAGO($B$4/12;$B$3;-$B$2)

Si cambiaste el signo o escribiste 12000 directamente como valor presente positivo, ajusta el tercer argumento con el signo negativo.

La última fila deja un saldo distinto de cero

Esto ocurre si cada fila usa siempre la misma fórmula y no permite que el último pago absorba el residuo. Comprueba que:

• E20 sea el saldo anterior al último pago.
• C21 sea el interés de la última cuota.
• D21 sea =E20-C21 o el capital que reduce exactamente ese saldo.
• E21 sea =E20-D21.

El interés cambia a cero

Si =E10*B4/12 se copió, B4 puede convertirse en B5. Usa referencias absolutas:

=E10*$B$4/12

Después, vuelve a copiar la fórmula hacia abajo.

El número de periodos no coincide

Si cambiaste B3 de 12 a 24, PAG recalculará la cuota, pero el cuadro solo llega al periodo 12. Amplía el rango de filas hasta la fila correspondiente al nuevo plazo y comprueba que las referencias siguen siendo relativas para el saldo, pero absolutas para importe, plazo y tasa.

Dónde buscar información oficial sobre las fórmulas

• PAGO (PMT) explica el cálculo de pagos periódicos.
• DB devuelve la amortización de un activo por saldo fijo.
• DDB devuelve la amortización mediante doble disminución de saldo.
• SLN devuelve la amortización por método directo.
• DVS devuelve la amortización mediante un saldo en disminución.

Ahora que el simulador de amortización francesa está terminado, el siguiente paso es comparar este método con otro sistema de amortización y observar cómo cambia la distribución de intereses y capital.

Dudas al crear una amortización francesa en Excel