Como calcular el cuadro de amortización de un préstamo por el método francés

En el método francés, o también llamado de cuotas constantes, la devolución de un préstamo se realiza mediante el pago de unas cuotas constantes y periódicas, mientras no varíe el tipo de interés.

Del importe que pagamos en cada cuota, una parte está dedicada a la devolución del principal (amortización financiera)  y la otra son intereses. En las primeras cuotas, la mayor parte del pago corresponde a intereses, mientras que la parte dedicada a la amortización es muy pequeña, pero conforme avanzamos en el tiempo la situación será la contraria.

Para explicar este método supongamos que queremos comprar un vehículo que cuesta 10.000,00€. Nuestra entidad bancaria nos ofrece el siguiente préstamo con un interés fijo durante toda la vida del préstamo:

Capital solicitado: 10.000,00€

Tipo de interés: 8,00%

Duración en años del préstamo: 5 años

Cuotas por año: 4 (pagos trimestrales)

Nota: Para calcular nuestro cuadro de amortización usaremos la tasa de interés nominal (a partir de ahora tipo de interés) y no el TAE.

En nuestra hoja de cálculo introduciremos los siguientes datos:

En la celda B1 el importe del préstamo que vamos a solicitar

En la celda B2 el Tipo de Interés (Tasa de interés nominal)

En la celda B3 el número de años del préstamo

En la celda B4 el número de cuotas por año

En la fila 6 introducimos los títulos de nuestro cuadro de amortización: Número de cuota – Intereses – Amortización – Cuota – Capital Amortizado – Capital Vivo

Os explico que representarán cada una de estas columnas:

  • Número de cuota: indicará en que periodo estamos (irá desde la número 1 hasta años*número de cuotas por año).
  • Intereses: parte de la cuota que pagamos en cada periodo destinada al pago de los intereses.
  • Amortización: parte de la cuota que pagamos en cada periodo destinada a la devolución del principal.
  • Cuota: cantidad que pagamos en cada periodo al banco o entidad financiera.
  • Capital Amortizado: cantidad que hemos pagado al banco desde la primera cuota en concepto de devolución del principal. En la última cuota su valor deberá ser el total que habíamos pedido al banco.
  • Capital Vivo: cantidad que queda pendiente de pago al banco en cada periodo. En la última cuota su valor deberá ser 0,00€.

Bien, constuyamos nuestro cuadro de amortización.

Vamos a empezar por rellenar la columna “Número de cuota”. Para ello escribimos “1” en la celda A7 (ya que en esta fila irán los datos de la primera cuota de nuestro préstamo). Seguidamente vamos a Rellenar-Series dentro del menú Inicio (En versiones anteriores a Excel 2010 se puede encontrar en el menú Edición- Rellenar-Series). Marcamos “series en columnas”, a “incremento” le damos el valor 1 (ya que queremos que en cada celda vaya sumando uno respecto de la anterior) y en límite introducimos el total de cuotas de nuestro préstamo, en este caso 20 ( 5años*4 cuotas al año).

Préstamo_Número de cuota_Series

Comprobamos como se rellenan automáticamente todas las celdas desde la A7 a la A27.

A continuación rellenaremos la columna “Intereses“.

Los intereses en cada periodo se calculan aplicando la tasa de interés por periodo al capital pendiente de pago. Para calcular la tasa de interés por periodo tendremos que dividir el tipo de interés (o tipo de interés nominal) entre el número de cuotas al año. En nuestro caso para calcular que parte de la cuota que pagamos al banco (cuyo importe aún no hemos calculado) en el primer periodo bastaría con hacer la siguiente operación

I1=10.000,00 x (0,08/4)=200,00€

Es decir, el primer trimestre pagaremos 200,00€ en concepto de intereses y el resto de la cuota corresponderá a amortización del capital.

En principio parece fácil calcular los intereses en cada periodo el único problema es que tenemos que ir calculando en cada periodo el principal pendiente (o capital vivo). Para evitar esto podemos usar una fórmula que tiene excel:  PAGOINT, esta nos calcula para cada periodo que parte de la cuota corresponde a los intereses.

Las funciones en Excel, para los que no estáis familiarizados con este programa, las podemos encontrar dentro del menú fórmulas, en Excel 2010, o dentro del menú Insertar-funciones para versiones mas antiguas.

Nosotros calcularemos los intereses de cada una de las cuotas con esta función. Nos situamos en la celda B7 y buscamos dicha función dentro del menú Fórmulas-Financieras.

Tenemos que rellenar los siguientes argumentos:

TASA: Es la tasa de interés por periodo. Para calcularla tendremos que dividir el tipo de interés nominal entre el número de cuotas al año. En nuestro caso sería TASA=$B$2/$B$4=8,00%/4.

PERIODO: Es el periodo para el que se desea encontrar el interés. Deberá estar comprendido entre 1 y Nper. En nuestro caso PERIODO=A7 para la primera cuota, A8 para la segunda, … así que introduciremos A7 (cuando copiemos al resto de las celdas su valor automáticamente).

NPER: Es el número total de periodos de pago de una inversión, es decir, el número de años que dura el préstamo por el número de cuotas en cada año. En nuestro caso NPER=$B$3*$B$4=5X4=20.

Nota: Introducimos $ en la referencia a una celda para que deje ese valor fijo y no lo cambie cuando copiemos al resto de celdas como en “Periodo”.

VA=Es el valor actual del préstamo, es decir, el importe que vamos a solicitar. En nuestro caso VA=$B$1=10.000,00€

VF=Es el valor futuro o saldo en efectivo que se desea obtener después de efectuar el último pago, es decir, cuando finaliza el préstamo. El valor por defecto si no se indica otro distinto es 0.

TIPO: Es un valor lógico que representa cuando vencen los pagos. Sus valores serán: 0 si vencen al final del periodo o 1 si vencen al principio del mismo. Su valor por defecto si no se indica otro distinto es 0.

Ahora copiamos en el resto de las celdas de la columna B hasta llegar a la última cuota arrastrando desde la esquina inferior derecha.

Préstamo_Método Francés_Intereses

Lo siguiente es calcular la columna “Amortización”. Para ello vamos a usar la función PAGOPRIN. Nos situamos en la celda C7,  vamos a Fórmulas-Financieras y la buscamos. Los argumentos de esta función son los mismos que los de la función PAGOINT. Luego copiamos al resto de celdas arrastrando desde la esquina inferior derecha.

Préstamo_Método Francés_Amortización

A continuación vamos a calcular la columna “Cuota”. La cuota que pagamos en cada periodo al banco o entidad financiera se calcula mediante una complicada fórmula matemática. Por suerte en excel tenemos una función que hace su cálculo muy sencillo: la función PAGO. Los argumentos de esta función son los mismos que los de las dos anteriores excepto que no tenemos que informar el número de periodo de la cuota (debido que la cuota es la misma en todos los periodos, mientras no cambie el tipo de interés).

Préstamo_Método Francés_Cuota

Como podemos comprobar el valor de la columna cuota es la suma de la columna intereses y amortización.

Para calcular la columna “Capital Amortizado” podemos seguir dos métodos:

  1. El primero de ellos es sumando la columna amortización desde la primera cuota (C7 en nuestro ejemplo) hasta la cuota en la que nos encontremos. Para ello escribiremos la fórmula “=SUMA($C$7:C7)” en la celda E7 y copiaremos al resto arrastrando con el ratón desde la esquina inferior derecha. Podrás comprobar que en el resto de celdas sigue estando fijo el primer argumento de la función suma ($C$7) mientras que el segundo argumento varía en función del periodo en el que te encuentres.
  2. El segundo de ellos es usando la función PAGO.PRINC.ENTRE. Esta función nos devuelve el capital amortizado entre dos periodos. Los argumentos TASA, NPER Y VA tendrán los mismos valores que en las funciones anteriores. En el argumento PERIODO_INICIAL indicaremos el número de cuota correspondiente a la primera cuota, es decir $A$7. En el argumento PERIODO_FINAL indicaremos el número de cuota correspondiente al periodo en el que nos encontramos, por ello escribiremos A7 (así variará dependiendo del periodo en que nos encontremos). En este caso si debemos informar el argumento TIPO al cual le daremos el valor 0.

Si hemos hecho los cálculos bien en el último periodo el Capital Amortizado deberá ser 10.000,00€.

Préstamo_PagoPrincEntre

Préstamo_Capital Amortizado

A continuación calcularemos la columna “Capital Vivo”. Para ello restaremos, para cada periodo, del capital inicial del préstamo (10.000,00€) el capital amortizado. Así para el primer periodo escribiremos la fórmula =$B$1+E7 (ponemos + en vez de – ya que la celda C7 tiene saldo negativo). Luego como siempre copiaremos arrastrando desde la esquina inferior derecha al resto de celdas de la columna. Si hemos hecho de forma correcta los cálculos el capital vivo del último periodo debe ser 0.

El resultado será:

Interés-fijo-método-francés

Si no quieres perderte ninguna entrada, puedes seguirme en facebooktwitter, google + o suscribirte a la newsletter de Campus Administrativo y recibir en tu e-mail novedades y consejos para administrar una empresa de forma más eficaz.

4 Comments

Deja un comentario