Formulas Financieras Básicas de Excel.

Excel es una herramienta fundamental dentro de una organización, usada por varios departamentos, como el contable, financiero, administrativo, inventario, mercadeo entre otros. Esta herramienta es tan versátil que puede cumplir con cualquier requerimiento que se le proponga. En este ocasión veremos unas funciones de Excel que nos pueden ayudar a ahorrar tiempo y tener una de hoja de cálculo muchos más versátil dependiendo de los requerimientos que nos exijan.

Bueno como administradores debemos tener un conocimiento financiero y que mejor herramienta que Excel para ayudarnos en la toma de decisiones financieras; como por ejemplo a la hora de tomar un crédito con una entidad bancaria y esto lo podemos hacer creando una tabla de amortización con cuotas fijas y variables. para poder analizar si el préstamo que se quiere pedir es viable o no. Es por lo anterior que te presentare la siguiente guía básica para realizar el cálculo de cuota de un préstamo.

Fórmula Pago: Cálculo de cuota 

• Primero debemos realizar una pequeña tabla en Excel con los siguientes datos; Préstamo, Plazo, Interés Anual, y Vlr. A Pagar, según la siguiente grafica.

• Luego debemos ingresar la información en la celda de al frente de cada una de los datos que requiere la tabla, por lo generar esta información es suministrada por la identidad financiera, pero para nuestro caso ingresaremos los siguientes datos, que corresponden a un préstamo de vivienda de interés social.

Préstamo: $110.000.000 (Valor del Préstamo)

Plazo: 120 (Que equivalen a 10 años)

Interés: 11% (Anual)

• Luego en Vlr. A Pagar ingresaremos =Pago, y seleccionamos PAGO

=PAGO(tasa;nper;va)

• En tasa seleccionaremos la celda donde está el % de la tasa en nuestro caso la celda C4 y lo dividimos por 12 ya que esta tasa es anual y debemos hallar la tasa mensual.

• En nper seleccionaremos la celda donde está el número de periodos o plazo en nuestro caso la celda C3

• En “va” ingresamos el signo menos luego seleccionaremos la celda donde está el valor del préstamo, en nuestro caso la celda C2 y cerramos con un cerramos el paréntesis.

• Luego oprimimos enter y aparecerá el valor de la cuta mensual que se debe pagar por el préstamo.

Formulas para el cálculo de intereses con tabla de amortización y cuota fija.

• Para calcular la amortización, interés y saldo realizaremos la siguiente tabla.

• En la celda C9 ingresaremos el dato de la celda =C5 Presionamos F4 para que la celda escogida que de fija =$C$5, 

• oprimimos enter y arrastramos hasta el final de la tabla.

• Ahora en la cuota 0 en al celda F9, pondremos =C2 que es el valor del préstamo $110.000.000 

• Formula de amortización, nos ubicamos en la celda F10 e ingresamos la siguiente formula =F9-E10

• Ahora arrastramos el resultado hasta el final de la tabla.

Formula de Amortización: nos ubicamos en la cuota 1 en la columna de amortización celda E10, e ingresamos la siguiente formula =C10-D10

Luego arrastramos el resultado hasta el final de la tabla

• Fórmula de Interés: Para la fórmula de interés nos ubicamos en la celda D10 y seleccionamos el saldo anterior por el interés fijando esta ultima celda y dividiéndolo por la cantidad de cuotas al añoa que 12

=F9*$C$4/12

•  Por ultimo arrastramos el resultado hat el final de la tabla y verificamos que el saldo de la cuota 12 este en cero, lo cual nos indica que las formulas quedan bien aplicas.

Cálculo de intereses con tabla de amortización y cuota variable.

• Para calcular la amortización, interés y saldo realizaremos la siguiente tabla.

• En la celda C9 ingresaremos la formula de =PAGOINT

• Luego vamos seleccionando primero el interés Anual, fijamos esa celda con F4, la dividimos por 12.
• Luego seleccionamos el numero de la primera cuota, después seleccionamos la cantidad de cuotas, fijamos esa celda con F4.
• Por ultimo colocamos el signo menos seleccionamos el valor del préstamo, fijamos la celda con F4 y cerramos la formula con un paréntesis.

=PAGOINT($C$4/12;B9;$C$3;-$C$2)

• Arrastramos el resultado hasta el final de la tabla.

• Luego aplicaremos la formula para el pago Capital

• Seleccionamos el interés anual fijamos con la tecla F4, y dividimos por 12 que es el número de meses del año, luego seleccionamos la cuota inicial, después escogemos el plazo del crédito, fijamos la celda con F4, después colocamos el signo menos y escogemos el valor del crédito fijando con la tecla F4, damos enter y arrastraos el resultado hasta el final de la tabla.

=PAGOPRIN($C$4/12;B9;$C$3;-$C$2)

• Continuamos seleccionado el resultado, arrastramos hasta el final de la tabla y verificamos que el resultado de la última celda sea mayor que el inicial ya que tengamos en cuenta que en este tipo de créditos los interés son mayores al principio que al final por ende la cuota de capital es mayor al final que en la primera cuota.

• Para la fórmula de Saldo ingresaremos una fila entre la primera cuota y el título de la tabla colocamos los tres primeros valores en cero y en la columna de saldo copiamos el valor total del prestamos =C2.
• Luego agregamos la siguiente formula =E9-D10 es decir el saldo inicial menos el pago a capital de ese periodo

• Para finalizar seleccionamos el resultado, arrastramos hasta el final de la tabla y verificamos que el resultado de la última celda sea cero.

De esta manera finalizamos la amortización del crédito y podemos ver en cada periodo cuanto está destinado para interés de la cuota variable que se está pagando y nótese que la suma del vlr de interés y la suma del pago de capital es la misma del valor de la cuota a Pagar.

Categoría: Ofimatica

Autor: OSMAN CAMILO HERRERA MOSQUERA

Editor: CARLOS IVAN PINZON ROMERO

Código: UCHIG-7

Universidad Central

Fuentes:

El Tío Tech. (Septiembre 2021). Función Pago en Excel (Calculo de cuota de un préstamo basado en pagos y tasa de interés constantes) [Video]. YouTube. https://www.youtube.com/watch?v=e25hmNaIlSw

Excel Total. (2022). TABLA DE AMORTIZACIÓN EN EXCEL. Excel Total. https://exceltotal.com/tabla-de-amortizacion-en-excel/

Imagenes: