Préstamo: Amortización por el método americano

En esta entrada seguiré analizando los distintos métodos que existen para calcular la cuota e intereses cuando solicitamos un préstamo a una entidad financiera.

En este caso analizaremos el método americano y verás como puedes calcular un cuadro de amortización con excel.

El método americano o sistema de amortización con fondo se caracteriza por:

1. Hay un pago periódico de los intereses al prestamista.

2. El principal se devuelve en un único pago al vencimiento del préstamo.

3. Se constituye un fondo al que se realizará aportaciones periódicas para construir un capital con el que cancelar el principal del crédito a la fecha de vencimiento.

4. El interés  del préstamo suele ser mayor que el interés del fondo.

Los pagos a satisfacer en cada periodo por el prestatario pueden calcularse como suma de dos conceptos:

  1. Los intereses del préstamo
  2. La aportación periódica al fondo

Por lo que se refiere al préstamo, los términos amortizativos serán la cuota de interés para cada período salvo en el último pago en el que se incrementará en el importe del principal.

Si llamamos a:

C0 = al capital inicial que nos concede el banco

i = al tipo de interés

a = al número de años que dura el préstamo

m = al número de pagos que se hacen al año (12 si es mensual, 6 si es bimensual, 4 si es trimestral, …)

n = al número de períodos totales (sería a*m)

Tenemos que el término amortizativo del préstamo para cada periodo sería:

a=Co*(i/m) para los periodos que van desde 1 a n-1  (ya que solo se pagan intereses)

a=C0*(i/m)+Co para el periodo n  (ya que en el último periodo se devuelve también el principal)

En cuanto a las aportaciones que realizamos al fondo, éstas no tienen la condición de cuota de amortización ya que el importe del préstamo no decrece en el tiempo, sino que permanece constante durante toda la vida del mismo (no hay amortización de capital).

El fondo que se va constituyendo para hacer frente a la devolución del préstamo americano va creciendo por dos motivos:

  • las aportaciones periódicas efectuadas
  • y por los intereses que genera el saldo que permanece acumulado en el mismo.

Para el cálculo de las aportaciones al fondo se tendrá en cuenta la equivalencia financiera entre las aportaciones efectuadas al fondo y el capital que se quiere constituir finalmente (el importe del préstamo), empleando como tanto el interés aplicado al propio fondo.

Para calcular la cantidad que tendremos que aportar al fondo en cada periodo se utilizan complejas fórmulas de matemática financiera que veremos en entradas futuras, pero por ahora  vamos a utilizar las fórmulas equivalentes en excel que facilitan mucho su cálculo, en concreto vamos a usar la función VF (Valor Futuro) y la herramienta “Buscar Objetivo”.

La función VF nos devuelve el valor futuro de una inversión basado en pagos periódicos y constantes y una tasa de interés también constante (para facilitar el cálculo en esta entrada vamos a ver sólo el caso de que los tipos de interés permanezcan constantes, dejando para otra ocasión el caso con intereses variables). Podemos ver como utilizar esta función en nuestra anterior entrada “Excel: Fórmula Valor Futuro (VF)

La herramienta buscar objetivo nos permite variar el argumento de una función para que esta dé un resultado concreto. Podemos ver como usar esta herramienta en nuestra anterior entrada “Excel: Herramienta Buscar objetivo

La idea entonces es que usando la herramienta buscar objetivo, excel nos diga que valor debe tener el argumento pago de la función VF para que el resultado de esta función sea el capital inicial del préstamo.

Tomemos como ejemplo el que veníamos usando en las anteriores entradas (véase “como calcular el cuadro de amortización de un préstamo por el método francés” o “Préstamos. Amortización por el método italiano“).

Teníamos los siguientes datos:

C0 = 10.000€

i (interés del préstamo) =  8%

a = 5 años

m = 4 (pagos trimestrales)

n = 5*4 = 20

Además vamos a suponer que el fondo que constituimos tiene un tipo de interés del 5%

j (interés del fondo) = 5%

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

En la celda A1 “Capital Inicial” y en la B1 el importe del préstamo que vamos a solicitar.

En la celda A2 “Tipo de Interés préstamo” y en la B2 el Tipo de Interés (Tasa de interés nominal) del mismo.

En la celda A3 “Años y en la B3 el número de años del préstamo.

En la celda A4 “Cuotas por año” y en la B4 el número de cuotas por año

En la celda H1 “Valor Futuro” y la I1 la dejamos en blanco por el momento.

En la celda H2 “Aportación en cada periodo” y también la dejaremos en blanco.

En la celda H3 “Tipo de Interés fondo” y en la celda I3 introduciremos 5%.

Los demás datos, respecto a la duración del fondo, al ser los mismos que los del préstamo, si necesitamos hacer referencia a ellos usaremos los introducidos en la columna B.

Igual que en las entradas anteriores en la fila 6 introducimos los títulos de nuestro cuadro de amortización:

A6: Periodo o número de cuota

B6: Intereses

C6: Amortización

D6: Cuota o pago

E6: Capital Amortizado

F6: Capital Vivo

y ahora añadimos las siguientes, correspondientes al fondo:

H6: Aportación Fondo

I6: Intereses del fondo

J6: Capital en el fondo

K6: Pago total en cada periodo

Pasemos a calcular nuestro cuadro de amortización

La columna A, Periodo o número de cuota, la rellenaremos tal y como se explica en las entradas citadas anteriormente.

En la celda B7 calcularemos los intereses tal y como he explicado antes. Para ello introduciremos la siguiente fórmula =$B$1*($B$2/$B$4) pulsamos enter y copiamos al resto de las celdas de esta columna arrastrando desde la esquina inferior derecha de la celda.

La celda C7 y siguientes hasta la celda C25 tendrán el valor 0,00 ya que hasta el último periodo no se amortiza nada del préstamo. La celda C26 será igual a la celda B1.

En la celda D7 introducimos la fórmula =B7+C7, que será el término amortizativo del préstamo.

Las columnas de capital amortizado y capital vivo se calcularán como en las entradas que hemos indicado anteriormente. Podemos observar como el capital amortizado será 0,00 hasta la última cuota y como el capital vivo será 10.000,00 hasta la última cuota.

Préstamo_Método Americano

Vamos ahora a calcular la parte correspondiente al fondo.

Lo primero que tenemos que hacer es calcular la aportación que tenemos que realizar en cada periodo.  Para ello, como he comentado anteriormente, usaremos la fórmula VF de excel y la herramienta “Buscar objetivo”.

Nos situamos en la celda I1, buscamos la función VF en fórmulas y rellenamos los argumentos de la siguiente manera:

Tasa: I3/B4

Nper: B3*B4

Pago: I2

Por tanto, escribiremos la siguiente función:

=VF(I3/B4;B3*B4;I2)

Ahora mismo el resultado que da la fórmula es 0,00 ya que el valor de la celda I4 es 0,00. Para ver que valor tendría que tomar la celda I4 para que la función VF de 10.000,00 vamos a usar la Herramienta  “Buscar Objetivo”.

Vamos a Datos-Análisis Y Si-Buscar Objetivo  (o Herramientas-Buscar Objetivo en versiones de excel mas antiguos) y rellenamos el cuadro de la siguiente manera:

Definir la celda: I1

Con el valor: 10.000,00

Para cambiar la celda: I2

y le damos a aceptar.

Préstamo_Método Americano_Buscar Objetivo

Vemos como ahora el valor de I2 es -443,20€, que es lo que tendremos que aportar en cada periodo al fondo, y el de la celda I1 es 10.000,00€, que es el capital que tendremos al vencimiento en el fondo.

Nos vamos ahora a la celda H7 e introducimos =-$I$2 y copiamos al resto de las celdas de esa columna. Ya tenemos rellenada la columna de aportaciones al fondo.

Antes de ver los intereses que se van generando en el fondo vamos a ver la columna “Capital en el fondo”. Esta columna estará formada por la suma de las aportaciones que vayamos realizando al fondo mas los intereses que se vayan generando. Por tanto, escribimos las siguientes fórmulas:

– En la celda J7 = H7+I7

– En la celda J8 = J7+H8+I8

Nos situamos en la esquina inferior derecha de la celda J8 y copiamos la fórmula arrastrando con el ratón a las demás celdas de esa columna.

Ahora vamos a ver los intereses que van generando nuestras aportaciones al fondo. En el periodo uno no se generará ningún interés ya que no existía ningún capital anterior en ese fondo, así que nos situamos en la casilla correspondiente del periodo 2, la I8. En ella escribimos la siguiente fórmula =J7*$I$3/$B$4 y luego copiamos al resto de las celdas de esa columna.

Podemos comprobar como en el último periodo el valor del fondo es 10.000,00, justo la cantidad que tenemos que devolver en concepto de principal del préstamo que nos han concedido.

Préstamo_Método Americano 2

Préstamo_Método Americano_Fórmulas

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.

Deja un comentario