funciones: calcular la amortización mensual de un préstamo

Existen formas sencillas de hacer las cosas, y esta es una de ellas. No es un tema complejo y en realidad todo se basa en la utilización de una sola función: PAGO(). Muchas veces tomamos la calculadora e intentamos sacar a la luz distintos parámetros de un préstamo, cuando con una simple hoja de cálculos bastará para armar algo muy completo y útil. La entrada se dividirá en tres partes, cada una de las cuales irá sumando complejidad al ejemplo:

1) Calculo de la cuota

2) Utilización de la herramienta "Buscar Objetivo"

3) Creación del listado de cuotas.

[+/-] Ver el resto / Ocultar

1) Calculo de la cuota:

Lo realizaremos mendiante la función =PAGO().

Sintaxis:

=PAGO(interes, plazo, capital,[vf],[tipo])

los tres primeros argumentos son obligatorios y los últimos dos, opcionales, que no serán tratados en este post, dado que resultan irrelevantes para el calculo que efectuaremos.

Comencemos con el ejemplo: necesito un préstamo de $120.000, a veinte años, cuyo interés anual es del 14.50%. Aqui cabe aclarar que deberemos unificar los criterios de tiempo: veinte años equivalen a 240 cuotas mensuales, así que al interés anual de 14.50% lo dividiremos por 12. Por lo tanto diseñamos la siguiente tabla y en la última celda colocaremos la función: =PAGO(B5/12;B4;B3)

en B6 observamos la cuota que abonaremos. La celda B5 (interes) posee formato de fracción, con dos decimales.

Una vez que tenemos todo listo, empezaremos a mejorar la plantilla y crear celdas que nos reflejen datos del préstamo, como por ejemplo cuanto es el total a devolver y cuanto abonaremos solamente en concepto de intereses:

si... mas vale que adelantemos cuotas, por que sale bastante caro. También podríamos agregar la comisión bancaria e impuestos varios, cosas que se resuelven sencillamente.

2) Buscar Objetivo: (pestaña datos / analisis y si / buscar objetivo)

Esta herramienta de análisis no es muy utilizada por lo general, pero nos permite realizar cálculos de forma muy sencilla. Vamos aun ejemplo: según los datos obtenidos en el punto 1), mi cuota será de $1.536,00 mensuales... pero je, no me alcanza. Mi bolsillo soporta una cuota máxima de $1.150,00

Nos familiarizaremos primero con la interfaz gráfica de esta herramienta, la cual brindo con los datos ya completados:

el primer argumento es la función PAGO(), que arroja 1536

como segundo, que resultado quiero que devuelva (1150, lo máximo que puedo pagar)

y el último que celda cambio para lograr ese objetivo.

En la imagen superior le estoy diciendo: a la cuota de 1536 (B6) llevámela a un maximo de 1150, modificando el importe total del préstamo. El resultado:

si puedo pagar hasta $1150, entonces el importe del préstamo a obtener no podrá superar los $89843.91

Ahora bien, quizás la gente del banco sea por demás de buena y humanitaria y me diga "-flaco... no te hagas problemas, vos sacá los 120.000, que nosotros te variamos el interes para que la cuota no supere los $1150"

Eso quizás en alguna entidad financiera de Utopos, pero bueno, sirve para el ejemplo. Si así fuese, deberíamos cambiar los parámetros de Buscar Objetivo por los siguientes:

verán que ahora cambio la celda referente al interés

y el resultado:

que gente buena... me bajaron la tasa anual al 9.90%, para que sacando $120.000 la cuota no supere los $1.150,00. Obtuve una bonificación de 4.6 puntos

Creo que el ejemplo ha quedado bien claro y contamos a estas alturas con algo bastante "completito" a la hora de medir nuestras posibilidades. Pasemos entonces a la tercer parte, en donde detallaremos cuota x cuota.

3) Creación del listado de cuotas:

Referesquemos conceptos: estamos utilizando el sistema de amortización "francés". Y este sistema, como el alemán o el americano, tiene su propia forma de amortizar capital e interés. Su particularidad consiste en que, si bien la cuota final siempre es la misma (sigamos con los $1.150) en cada cuota se incrementa la parte de capital y decrece lo abonado en concepto de intereses. En la siguiente tabla tienen un ejemplo, que no guarda relación con los vistos hasta ahora, que servirá para ejemplificar esto:

la cuota final siempre son $500, pero "internamente" cada vez pagamos mas de capital y menos de interés.

Volviendo a nuestro trabajo, ahora le ordenaremos a Excel que emita un listado de cuotas correspondiente a nuestro préstamo, indicándonos cuanto pagamos de capital e interés en cada una de ellas:

observarán como va decreciendo el interés abonado y a la vez aumentando el capital, pero la cuota es siempre $985,14

les muestro ahora el final de la tabla:

al final del préstamo, solo terminamos pagando 6,32 de intereses.

El ejemplo propuesto se basa en los parámetros del sistema Francés. En futuros ejemplos incluiré otro muy utilizado: el Alemán (que calcula el interés sobre el saldo de capital). Es conveniente mencionar que el primero se utiliza generalmente en las líneas de préstamos hipotecarios y personales, mientras que el segundo se otorga en financiamiento del tipo estacional (como por ejemplo, el agropecuario), en donde los ingresos del solicitante están acordes a ciertas épocas del año (siguiendo con el sector agropecuario, semestralmente).

Si, todo muy lindo... pero ¿como armé la tabla? En realidad posee funciones muy sencillas, pero me resultaría muy prolongado y poco productivo detallarlas aquí. Les dejo el link de descarga para que tengan el archivo a mano y puedan estudiarlo. Cualquier duda... pregunten.