Calcular préstamos en Excel con tu propio simulador

Desde nuestro blog os hemos facilitado un simulador, bastante flexible, para calcular vuestros propios préstamos en Excel y tener una idea antes de acudir a cualquier entidad financiera. También hemos creado entradas para saber cómo se calcula, pero en esta iremos algo más allá y directamente os enseñaremos a crear un simulador propio en Excel. De esta manera sabréis como se monta y las posibilidades que tendremos a nuestra disposición, además, podrás adaptarlo a tus necesidades personales.

Para ver todo el proceso hemos creado un vídeo en el que veremos como crear un simulador como el nuestro, debajo del vídeo iremos explicando todo el proceso. El post es largo y está explicado en detalle para que cualquier persona pueda hacerlo. También te dejamos el archivo en el siguiente enlace para que puedas examinar con tranquilidad las fórmulas empleadas: Ejemplo calcular préstamos en excel

Ante cualquier sugerencia o pregunta, no dudes en dejarnos un comentario.

Lo primero que vamos a tener son 2 partes.

1. Cuadro de datos
2. Cuadro de amortización

1. Cuadro de datos del préstamo

Formatearemos las celdas con el formato oportuno:

• Importe – Formato moneda
• Duración en años – Personalizada
• Tipo de interés – Porcentaje
• Período de pago – General
• Interés del período – General
• Total Pagos – General

Aquí vamos a hacer un inciso. El formato general es el que aparece por defecto, así que no hace falta formatear esas celdas. Ahora explicaremos como configuramos el contenido de estas celdas (las que solo requieren establecer el formato no las explicaremos, otras, las que veremos a continuación, necesitan una aclaración o tratamiento especial)

Duración en años

En “Duración años” hemos puesto personalizada porque vamos a añadir la palabra “años” al lado del número que introduzcamos. Para eso nos situamos encima de la celda, hacemos click con el botón derecho del ratón, y seleccionamos “Formato de celdas”. Una vez ahí seleccionamos del menú izquierdo la opción de “Personalizada” y elegimos en el cuadro que muestra el “Tipo” el 0. Ahora podremos escribir al lado del 0 la palabra años. Esto lo haremos entre comillas ya que es una cadena de texto. Con esto ya estaría, ahora solo tenemos que introducir un número en la celda y al lado siempre aparecerá la palabra “años”. Esta palabra no interfiere en el cálculo, es simplemente algo visual.

Tipo de interés

Aquí introduciremos el tipo de interés nominal anual de nuestro préstamo.

Período de pago

En esta celda lo que se pretende conseguir flexibilizar las opciones del préstamo. En la mayoría de simuladores que podemos encontrar se da por hecho que los pagos siempre son mensuales pero en la realidad esto no es así y por eso tratamos de incluir otros períodos de pago. En este caso pondremos pagos (mensuales, bimensuales, trimestrales, cuatrimestrales, semestrales y anuales). Independientemente de los que ponemos nosotros, se podrían crear otros que te hicieran falta (por ejemplo, quincenales), ya que la mecánica es la misma.

Por eso, la mejor opción para poder elegir entre estas opciones de pagos es la creación de una lista cerrada en la cual elegiremos el período que queramos. Para ello, nos situamos encima de la celda, después nos vamos a la pestaña de Excel que se denomina “Datos”. Allí seleccionaremos el pulsador de “Validación de datos” > “Validación de datos”. Con esto se nos abrirá una ventana con varias opciones. Solo usaremos las opciones de la pestaña “Configuración”. En “Permitir” elegiremos “Lista” y en “Origen” escribiremos los siguiente:

mensual;bimensual;trimestral;cuatrimestral;semestral;anual

Los “;” actúan de separadores del texto, podríamos decir que es como pulsar intro. Una vez hecho esto, pulsaremos “Aceptar” y nuestra celda se convertirá en una lista desplegable que nos permitirá elegir una de las opciones que período que hayamos configurado. Si necesitas quitar o añadir alguna, tienes que repetir el proceso y eliminar el que no quieras o añadir el que necesites. Esta celda ya estaría lista.

Interés del período

Si pagamos con una periodicidad mensual, el tipo de interés que necesitaremos será el efectivo mensual, lo mismo sucede con el resto de períodos. A nosotros el banco solo nos da el tipo de interés nominal anual. Para pasar del nominal anual al efectivo tendremos que hacer cálculos. El problema es que tenemos que hacer que este cálculo sea dinámico, es decir, si seleccionamos un período de pago trimestral tendríamos que calcular el interés efectivo trimestral, si pagamos mensualmente el tipo efectivo debería ser mensual. Es aquí donde surge la necesidad de crear condicionales 8 (se podría solucionar esto de otras maneras pero esta es bastante gráfica).

Para lograr esto tendremos que utilizar una función del tipo: Si “periodo de pago = “mensual” entonces “Función mensual (tipo de interés)” sino…….

Puede que la expresión resultante parezca larga, pero es siempre repetir lo mismo, simplemente cambiaremos los parámetros que tienen que ir variando (Período de pago y valores en la formula matemática para transformar el tipo de interés). Vamos allá, la expresión que nos quedaría sería la siguiente:

=SI(B5=”mensual”;((1+B4)^(1/12))-1;SI(B5=”bimensual”;((1+B4)^(1/6))-1;SI(B5=”trimestral”;((1+B4)^(1/4))-1;SI(B5=”cuatrimestral”;((1+B4)^(1/3))-1;SI(B5=”semestral”;((1+B4)^(1/2))-1;SI(B5=”anual”;((1+B4)^(1/1))-1))))))

Como vemos comparamos siempre el período. Si la celda B5 contiene el texto “mensual” entonces ejecuta la operación que sale a continuación sino evalúa la siguiente condición hasta dar con una que concuerde. Tenemos que incluir el texto entre comillas porque es una cadena de texto. Ahora vamos con la fórmula de cálculo.

Pasar de interés nominal a interés efectivo

Aquí tendremos 2 opciones. Primero la académica y la que marca la teoría de equivalencia financiera y la segunda es la que usan las entidades bancarias y mayoría de simuladores de prestamos que podemos encontrarnos por ahí. Nosotros usamos la primera porque es la correcta, pero explicaremos las dos.

1. La primera es la siguiente i_n=(1+i)^(1/n)-1

• i_n = Interés efectivo del período que vamos a calcular
• i =Interés nominal que nos da el banco
• n = Partes en las que divides el año. Por ejemplo si n=3, estarías calculando el interés efectivo cuatrimestral, si n=6, estarías calculando el bimensual, etc.

2. La segunda fórmula es más sencilla i_n=i/n

Las variables son las mismas. El resultado es muy similar si el cálculo lo hacemos con una fórmula u otra, pero no es lo mismo. En este sentido el dicho de que los “bancos siempre ganan” aquí cobra sentido, el uso de la segunda opción hace que los intereses del préstamo sean mayores. Una pequeña variación en el tipo de interés que se emplee en una hipoteca mensual a 35 años puede hacer una diferencia nada desdeñable, si a esto le sumas el número de hipotecas que concede una entidad, los beneficios son más que cuantiosos. La fórmula sería correcta en caso de ser un préstamo a corto plazo (menos de un año).

Total de Pagos

Esta es una celda que podríamos llamar de apoyo. En ella calcularemos todos los pagos que tendremos que hacer hasta el final de la amortización de la hipoteca. Por ejemplo, si tenemos un préstamo a 30 años y realizamos pagos mensuales, entonces tendremos que efectuar 320 pagos (1 por mes). la fórmula de la celda sigue la lógica de la del tipo de interés:

Si “período de pago =”mensual”” entonces “duración en años”*12 sino….

La fórmula de esta celda quedaría de la siguiente manera:

=SI(B5=”mensual”;B3*12;SI(B5=”bimensual”;B3*6;SI(B5=”trimestral”;B3*4;SI(B5=”cuatrimestral”;B3*3;SI(B5=”semestral”;B3*2;SI(B5=”anual”;B3*1))))))

Hasta aquí consistiría la configuración del cuadro de los datos. Podemos comprobar antes de seguir que todo funciona, por ejemplo seleccionar de la lista de “periodo de pago” las diferentes opciones para ver si los cálculos del tipo de interés y del  total de pagos es correcto. Si sale algún error es que algo habremos hecho mal (los errores más comunes suelen cometerse al omitir las comillas o confundirse con los paréntesis de las fórmulas).

2. Cuadro de amortización

Bueno hasta aquí no sabemos ni cual será la cuota del préstamo ni nada, así que seguiremos el mismo proceso, explicaremos columna a columna. Así que, tenemos las siguientes:

• Número de pagos – Contendrá el número de pago
• Cuota – Lo que pagaremos en cada período
• Intereses – Los intereses de cada período
• Amortización – Lo que amortizaremos del préstamo por período
• Capital Vivo – El capital pendiente de amortizar
• Capital Amortizado – Capital que ya hemos amortizado

En todas las columnas tendremos que introducir fórmulas. Lo que queremos es que nuestro cuadro de amortización sea “automático” es decir, a parte de que se recalcule al cambiar los valores del cuadro de datos, queremos que las celdas sean “inteligentes”. Si no se usan ciertas celdas, queremos que se muestren en blanco. Vamos allá. La primera fila la ponemos nosotros a mano, en el primer “Número de pagos” ponemos 0 y en la primer celda de “Capital Vivo” ponemos =”Importe de moneda”, en nuestro caso =B2.

Número de pagos

Lo que queremos que haga esta columna es que en función del total de pagos muestre los números de cada período, y si cambia el total de pagos que cambien estos números automáticamente. Lo mejor es verlo en acción para tener una idea más clara.

Nos situamos debajo de la celda del período 0. Ahora tenemos que hacer una comparación del tipo:

Si “Número de pagos” < “total de pagos” entonces poner “”celda anterior” + 1″ SINO “espacio en blanco”

Veamos como queda. Nos situamos sobre la celda D4 y escribirmos:

=SI(D3<$B$7;D3+1;””)

Analicemos la expresión

Es una expresión condicional que dice si D3 (que es igual a 0) es menor que B7 (que es el “total de pagos”) entonces súmale 1 a D3. En caso de que esto sea falso, es decir, D3 sea igual o mayor al “total de pagos” pon un espacio en blanco.

Una vez escribamos la fórmula para la primera celda podemos arrastrar hasta la fila 400 o las que necesitemos ya que las celdas que no cumplan la condición se mostrarán en blanco y las que la cumplan se rellenarán automáticamente, !por arte de magia!

Para hacer esto tenemos que tener en cuenta los dólares $. En Excel los dólares se usan para fijar filas, columnas o celdas concretas. Como la celda de “total de pagos” es única, la fijamos. Para hacerlo tenemos que pulsar F4 cuando escribamos la celda en la fórmula. Si pulsamos una vez fijamos una celda concreta, si pulsamos otra vez fijaremos la fila y si pulsamos de nuevo fijaremos la columna).

Por ejemplo, en nuestro ejemplo, si no fijáramos la celda “total de pagos” y arrastráramos la fórmula, Excel nos cogería automáticamente la celda B8,B9,B10…. que están vacías y nuestra fórmula no funcionaría. Como queremos que cada celda de la columna “Número de Pagos” se compare con la de “total de pagos”, éstas no llevan dólares. De este modo compararemos D3 con B7, D4 con B7, D5 con B7….hasta que llegue al final de donde hemos arrastrado la fórmula. Ya tenemos la primera columna lista.

Cuota

Antes que nada hay que decir que la cuota en un período de un préstamo es la suma de la amortización y de los intereses de ese período.

Como en la anterior columna también haremos una comparación para mostrar o no la cuota en función del período en el que nos encontremos. Sigue la misma idea que lo anterior pero cambia un poco, en este caso la comparación será.

=SI(D4<=$B$7;PAGO($B$6;$B$7;-H3);””)

Analicemos la expresión

Lo primero que hacemos es comparar el “número de pago” actual con el “total de pagos”. Si es inferior o igual entonces calculamos la cuota sino mostramos una celda en blanco. Es decir si nuestros préstamos en Excel tienen 300 pagos no es lógico que se calcule la cuota del período 301, 302….y así sucesivamente. Sólo calculará las cuotas de cada período. Esta comparación es la misma para el resto de columnas.

La fórmula para calcular la cuota es =PAGO($B$6;$B$7;-$B$2). Su estructura es la siguiente:

PAGO(tasa,nper,va,vf,tipo)

• Tasa va a ser “nuestro tipo de interés efectivo” como es una celda fija, usamos los dólares.
• NPER es el “Total de pagos”. También va con dólares.
• VA se corresponde con el capital que hemos pedido prestado. Le ponemos el signo “-” delante porque sin él para hacer positivo el valor y no trabajar con números negativos (aunque sería lo mismo). Le asignamos los dólares porque es una celda fija.
• VF y Tipo no nos hacen falta, así que no los utilizamos.

Una vez hayas puesto la fórmula en la celda correspondiente a la cuota del período 1 puedes arrastrar la fórmula o bien hacer doble clic en la esquina inferior derecha de la celda (es una manera rápida de arrastrar una celda si en la columna de la izquierda tienes datos).

Ahora no te asustes, te saldrán ceros en las cuotas que van más allá del período 1. Déjalo como está, te sorprenderás.

Intereses

Ahora nos toca calcula los intereses de cada período. Para hacerlo, simplemente tendremos que multiplicar el “tipo de interés efectivo” por el capital vivo del período anterior. Además, seguimos utilizando la misma condicional que explicamos en la columna de cuota para mostrar o no los valores de los intereses de un período. Veamos la fórmula:

=SI(D4<=$B$7;H3*$B$6;””)

Como en cada período amortizamos parte del capital, el capital vivo va variando y por tanto tendremos que ir recalculando los intereses. Los intereses al ser una celda fija lleva dólares. Ya solo tendremos que arrastrar la fórmula. Al igual que en el caso anterior, salvo los intereses del primer período, en el resto tendremos 0. No pasa nada, sigamos.

Amortización

Como hemos dicho, la cuota de un período es la suma de los intereses y la amortización de dicho período. Por lo tanto, si tenemos la cuota y los intereses, la amortización podemos obtenerla con una simple resta.

=SI(D4<=$B$7;E4-F4;””)

La comparación es la misma que en los casos anteriores y sirve para lo mismo. Como vemos, tenemos la resta E4-F4 y no ponemos dólares porque queremos que en cada período se resten los intereses correspondientes de la cuota para hallar el valor de la amortización.

Volvemos a arrastrar la fórmula y en este caso el valor que aparecerá, a partir del primer período, es el mismo que el de la cuota, es lógico ya que está restando la cuota menos cero . Ya pronto solucionaremos eso.

Capital Vivo

El capital vivo de cada período en un préstamo se refiere al capital que nos queda por amortizar después de haber pagado la cuota en cada período. Su fórmula sería en nuestro caso:

=SI(D4<=$B$7;H3-G4;””)

Es decir, sería la resta entre el capital vivo del período anterior menos la amortización de la cuota del período actual. Ahora arrastramos esta fórmula y ya vemos como las que anteriormente estaban mal se recalculan correctamente. Vamos con la última columna.

Capital amortizado

Esta columna lo que refleja es un acumulado del capital que hemos ido amortizando. Eso lo plasmamos con la siguiente fórmula:

=SI(D4<=$B$7;I3+G4;””)

Lo que hacemos es ir simplemente acumulando el capital. Cogemos la celda I3 que en este caso está vacía por comodidad. Como en el período 0 no hay capital amortizado cogemos I3 para automatizar y simplificar la fórmula. Por lo tanto en el período 1, el capital amortizado acumulado será el capital acumulado hasta ese momento (cero porque no hubo amortización anteriormente) más la amortización de ese período, y así sucesivamente.

Arrastramos la fórmula y en el último período, el “capital amortizado” debe ser igual al al “importe” del préstamo, sino algo habremos hecho mal.

Cómo saber si el préstamo está bien calculado

Para comprobar que los préstamos en Excel están correctamente calculados nos tenemos que fijar en la columna “Capital Vivo” y “Capital Amortizado”. En el último período los valores de estas columnas deben ser 0 y el importe del préstamo respectivamente. Si no hemos formateado las columnas (cuota, intereses, amortización, capital vivo y capital amortizado) con un formato moneda puede que el último valor de la columna de “capital vivo” no sea 0 y presente un valor del tipo:

1,8299E-12

Para arreglarlo dale formato moneda a todas esas columnas que hemos dicho, y si todo ha ido bien, ya te debería aparecer 0 en dicha celda. Prueba a variar todos los valores del cuadro de datos para ver si todo se vuelve a calcular correctamente.

Una vez hecho todo esto, ya tendríamos nuestro simulador con el cual calcular nuestros préstamos en Excel. En otro post posterior explicaremos como completar el simulador con opciones más avanzadas que te permitirán flexibilizar todavía más el cálculo.