Cálculo de cuota de préstamo en Excel
En este artículo te dejamos todo lo que necesitas saber para obtener el cálculo de una cuota de préstamo en Excel.
1. Lo básico para el cálculo de cuota de préstamo en Excel
¿Cómo funciona un préstamo?
Una entidad financiera nos presta una cantidad de dinero por un periodo de tiempo, con una tasa de interés. Pero, debemos devolver esa cantidad de dinero junto con los intereses cobrados por el préstamo, ¿cómo? Debemos pagar todos los periodos una cuota de préstamo.
¿Qué es el cálculo de cuota de préstamo?
Es el cálculo que realizaremos para saber la cantidad de dinero que pagaremos todos los periodos para devolver el monto de dinero que nos prestaron.
¿Qué contiene la cuota de préstamo?
La cuota de préstamo está compuesta por el pago de intereses, y por el pago del principal o préstamo (amortización).
Haremos una tabla de amortización para realizar el cálculo de la cuota de préstamo en Excel correspondiente a cada periodo. Así podremos saber el monto perteneciente al pago de intereses y el monto perteneciente al pago de capital para cada uno de ellos.
2. Tabla de amortización para el cálculo de cuota de préstamo en Excel
¿Qué es una tabla de amortización de préstamos Excel?
Es una tabla que nos va a indicar los pagos que realizaremos todos los periodos para devolver el crédito.
Para crear la tabla de amortización de préstamos bancarios en Excel y poder realizar el cálculo de la cuota de préstamo, necesitamos los siguientes datos:
- Monto del crédito (principal): cuánto dinero nos presta la entidad financiera.
- Tasa de interés: tasa que nos cobra la institución por el préstamo de dinero, vía por donde la entidad financiera obtiene ganancias.
- Cantidad de pagos (cantidad de periodos): cuántos pagos realizaremos para devolver el dinero prestado.
Es importante identificar y ordenar estos datos en una tabla de Excel.
Imaginemos el siguiente ejemplo con un monto de crédito de $500.000, el cual se debe devolver en 24 pagos mensuales, con una tasa de interés anual de 6%. Anotamos estos datos en una tabla de Excel como vemos en la imagen.
Datos importantes:
- Son 24 meses; nos demoramos 2 años en pagar el préstamo.
- Debemos convertir la tasa de interés al periodo correspondiente. Nos dan la tasa de interés anual, y los pagos son mensuales. Entonces debemos cambiar la tasa de interés anual a una tasa de interés mensual. ¿Cómo? Dividimos la tasa en 12 como se muestra en la imagen, y nos queda que la tasa de interés mensual es 0,5%. Si fueran pagos semestrales, dividimos en 2, si fueran trimestrales, dividimos en 4.
¿Cómo es una tabla de amortización de créditos Excel?
Una tabla de amortización para realizar el cálculo de cuota de préstamo en Excel se compone de 5 elementos:
- El periodo
- El pago total
- El pago del interés
- El pago del principal
- El balance
En esta imagen podrás ver los 5 elementos que componen la tabla de amortización para realizar el cálculo de la cuota de préstamos en Excel. Cada uno de estos está asociado a una función o fórmula, las cuales veremos en detalle a continuación.
Realizaremos la tabla con estos 5 componentes en Excel como muestra la figura, la cual de a poco iremos completando.
3. Función PAGO
La función PAGO calcula el monto de dinero que pagaremos en cada periodo. Este pago está compuesto del pago de intereses, y del pago del principal de nuestro préstamo. La función PAGO se basa en una tasa de interés constante y pagos fijos en el tiempo.
Sintáxis: =PAGO(tasa; nper; va; [vf], [tipo])
Argumentos:
- tasa: tasa de interés del crédito (obligatorio).
- nper: cantidad de pagos totales del préstamo (obligatorio).
- va: valor actual; suma total de pagos futuros contabilizados en el presente. Es el valor del préstamo hoy (obligatorio).
- [vf]: valor futuro; saldo que queremos dejar después de realizar el último pago. (Opcional: si no ponemos [vf], Excel establece valor futuro igual a cero).
- [tipo]: momento en que vencen los pagos. Puede ser 1 o 0. [1]: al inicio del periodo, [0]: al final del periodo. (Opcional: si no ponemos [tipo], Excel establece el vencimiento de los pagos para el final del periodo).
Si quieres aprender más sobre la función PAGO, puedes apretar el siguiente link.
Todos los elementos de la función PAGO son fijos siempre. Entonces fijamos estos datos, como vemos en la imagen.
Es tan fácil como eso, ¡ya hemos realizado el cálculo de cuota de préstamo! La cuota de préstamo será $22.160,31. Este monto de dinero debemos pagar todos los meses para devolver el crédito que nos prestaron.
4. Función PAGOINT
La función PAGOINT calcula la parte del pago total correspondiente al pago de intereses de cada periodo. Es un componente del cálculo de cuota de préstamo en Excel.
Sintáxis: =PAGOINT(tasa; periodo; nper; va; [vf], [tipo])
Argumentos:
- tasa: tasa de interés del crédito (obligatorio).
- periodo: periodo correspondiente para el cual se calcula el interés.
- nper: cantidad de pagos totales del préstamo (obligatorio).
- va: valor actual: suma total de pagos futuros contabilizados en el presente. El valor del préstamo hoy (obligatorio).
- [vf]: valor futuro; saldo que queremos dejar después de realizar el último pago. (Opcional: si no ponemos [vf], Excel establece valor futuro igual a cero).
- [tipo]: momento en que vencen los pagos. Puede ser 1 o 0. [1]: al inicio del periodo, [0]: al final del periodo. (Opcional: si no ponemos [tipo], Excel establece el vencimiento de los pagos para el final del periodo).
Fijamos la tasa, la cantidad de pagos, y el valor actual. Lo único que no fijamos es el periodo. Para cada periodo se va a cobrar un interés distinto porque cambia el saldo que queda por pagar.
Sólo con esta fórmula obtenemos el pago de interés correspondiente al periodo 1, es decir, de los $22.160 que pagaremos de la cuota de préstamo, $2.500 serán de pago de intereses.
También podemos calcular el pago del interés multiplicando la tasa de interés, por el monto del balance anterior, y nos dará lo mismo.
5. Función PAGOPRIN
La función PAGOPRIN calcula la parte del pago total correspondiente al pago del principal de cada periodo. Es un componente del cálculo de cuota de préstamo en Excel. En nuestro ejemplo, veremos cuánto de los $500.000 que nos prestaron, vamos a pagar en cada periodo.
Ocupa los mismos valores que la función PAGOINT.
Sintáxis: =PAGOPRIN(tasa; periodo; nper; va; [vf], [tipo])
Argumentos:
- tasa: tasa de interés del crédito (obligatorio).
- periodo: periodo correspondiente para el cual se calcula el interés.
- nper: cantidad de pagos totales del préstamo (obligatorio).
- va: valor actual: suma total de pagos futuros contabilizados en el presente. El valor del préstamo hoy (obligatorio).
- [vf]: valor futuro; saldo que queremos dejar después de realizar el último pago. (Opcional: si no ponemos [vf], Excel establece valor futuro igual a cero).
- [tipo]: momento en que vencen los pagos. Puede ser 1 o 0. [1]: al inicio del periodo, [0]: al final del periodo. (Opcional: si no ponemos [tipo], Excel establece el vencimiento de los pagos para el final del periodo).
Al igual que en PAGOINT, fijamos la tasa, cantidad de pagos, y monto de crédito. Dejamos variable el periodo, ya que el pago del principal será distinto en cada periodo.
Así obtenemos que $19.660,31 será el pago perteneciente para saldar el monto del crédito.
- De los $22.160,31 que pagamos como cuota de préstamo mensualmente, $19.660,31 pertenecen al pago del principal.
- De los $500.000 iniciales que debíamos del préstamo, pagamos $19.660,31 en el primer periodo, ¿cuánto debemos ahora del préstamo total? Veamos cómo se calcula el balance a continuación.
6. Balance del cálculo de cuota de préstamo en Excel
El balance o saldo es la cantidad de dinero que nos queda por devolver en cada periodo del monto del crédito, el cual se puede calcular a partir del cálculo de la cuota de préstamo de Excel. Se calcula de la siguiente manera:
En nuestro ejemplo, calculamos que en el periodo uno, pagamos $19.660,31. Por lo tanto, a los $500.000 iniciales que debíamos, le restamos los $19.660,31 para saber el nuevo saldo que debemos, como podemos ver en la imagen. (Sumaremos en Excel, ya que el “Pago principal” ya tiene incorporado el signo menos).
No fijamos nada, ya que tanto el balance anterior como el pago del principal cambiarán periodo a periodo.
De esta manera sabemos que nos queda por pagar $480.339,69 de los $500.000 que nos prestaron en un principio.
7. Tabla amortización de todos los periodos para el cálculo de cuota de préstamos Excel
La tabla de amortización de créditos nos indicará en detalle el cálculo de cuota de préstamo de cada periodo. Es decir, nos mostrará los pagos correspondientes a; el pago total, el pago de intereses, el pago principal y el balance para TODOS los periodos.
¿Cómo lo hacemos?
Simplemente repetimos estos pasos para los siguientes 23 pagos que nos faltan por realizar, ¿de qué manera? Seleccionamos el rango E4:I4 y arrastramos hacia abajo hasta el periodo 24, es decir, hasta la fila 27, como vemos en la imagen.
¡Así de simple es el cálculo de cuota de préstamo en Excel! Notamos entonces, cómo varían el pago de interés, el pago del principal y el balance periodo a periodo, y cómo se mantiene el valor del pago total. Vemos cómo en el periodo 24 el balance es cero, es decir, debemos $0, ya que hemos pagado todo nuestro préstamo.
Notamos que:
- Disminuye el pago del interés.
- Disminuye el balance, es decir, después de cada periodo debemos menos dinero.
- Aumenta el pago del principal.
Veamos ahora qué pasa si disminuimos la cantidad de pagos totales: de 24 a 12. Nos damos cuenta que cambia el monto de la cuota de préstamo en Excel. Observamos que el pago total aumenta, ya que tenemos menos periodos para pagar la deuda total, pero la suma total de los intereses es menor.
¡Así de simple es realizar el cálculo de la cuota de préstamo en Excel! ¿Qué esperas?
Si quieres aprender más sobre otras funciones financieras, te recomiendo mirar este post que habla sobre la función CAGR, que sirve para encontrar la tasa de crecimiento anual compuesta de una inversión.
