En este ejercicio en Excel crearemos un modelo que nos permita introducir los datos mínimos necesarios para la realización de las correspondientes cuotas correspondientes a la operación financiera.
Los resultados únicamente aparecerán de forma automática una vez que hayamos introducido todos los datos anteriores.
Entrada de datos de las variables necesarias para la realización de los cálculos:
Damos formato de millares con dos decimales, al tipo de interés nominal le damos formato porcentual con dos decimales.
En la celda correspondiente al tipo de liquidación de los intereses pondremos una validación de lista (D; M; T; S; A): según se trate Diaria; Mensual; Trimestral; semestral; Anual.
Por último, la celda donde pondremos el periodo del que deseamos obtener el importe que corresponde a los intereses y del capital amortizado del total de la cuota del periodo, estableceremos otra validación de número entero para evitar que se introduzca una cifra superior al número de periodos que corresponden a la operación financiera.
En la ficha Datos, hacemos clic sobre la opción Validación de Datos, y seleccionamos esta misma opción de la lista desplegable.
La validación de datos se usa para controlar el tipo de datos o los valores que los usuarios deseamos se puedan escribir en una celda.
En la nueva ventana de Validación en la opción Permitir seleccionaremos Número entero, en datos Menor o igual que; finalmente limitaremos el valor al importe que habrá de tener la celda B9. Si pulsamos sobre el botón a la derecha de esta celda Máximo, se contrae la ventana para que podamos seleccionar la celda en cuestión. En este caso el sistema añade el operador =.
Una vez que los formatos de las celdas han quedado configurados, procederemos a introducir los datos de esta primera parte.
En la segunda parte, diseñaremos el sistema que deseamos se muestre una vez los anteriores datos fueron introducidos; comenzamos con
La operación que deseamos que se realice en esta celda B8 es una división el valor contenido en la celda B3 entre el valor que resulta según el tipo de liquidación de intereses.
Para el cálculo del tipo de interés equivalente habrá que dividir el interés nominal anual entre el número de periodos inferiores al año.
Los valores que ira en la posición del denominador de la división son: A (anual) el valor que se deberá tomar es 1; si la liquidación es S (semestral), el valor será 2; si T (trimestral) el valor será 4; si M (mensual) serían 12 y si D (diaria) podremos 365.
Debido a que el tipo de liquidación en este momento no se conoce, emplearemos una de las funciones lógicas de Excel, la función Si; los argumentos (parámetros) de la función son: La prueba lógica (condición o criterio que ha de cumplirse o no); valor si…verdadero (valor que ha de dar si criterio se cumple); valor si… falso (valor que ha de dar en caso de que el criterio no se cumple).
Debido a que el número de variables posibles que deberían tomarse, necesitaremos anidar en el argumento falso otra función si para poder integrar las cinco variables; en este caso necesitaremos tantas funciones si como variables menos una dispongamos.
De esta forma emplearemos la función si anidada:
SI(B5=»A»;1;SI(B5=»S»;2;SI(B5=»T»;4;SI(B5=»M»;12;365))))
En consecuencia para obtener el resultado es decir el interés equivalente del periodo plateamos la formula en la celda para lo que abrimos y cerramos todo ello entre paréntesis
=(B3/ SI(B5=»A»;1;SI(B5=»S»;2;SI(B5=»T»;4;SI(B5=»M»;12;365))))).
Seguimos con el número de periodos:
En este caos deberemos determinar el número de periodos totales de la vida de la operación, para ello multiplicaremos la celda B4 y lo multiplicaremos por el valor que resulta de emplear nuevamente la función si anidada anterior:
SI(B5=»A»;1;SI(B5=»S»;2;SI(B5=»T»;4;SI(B5=»M»;12;365))))
De esta forma en la celda B9 redactaremos la operación siguiente:
=(B4* SI(B5=»A»;1;SI(B5=»S»;2;SI(B5=»T»;4;SI(B5=»M»;12;365)))))
A continuación le corresponde, por fin calcular la cuota del préstamo:
Tenemos de partes, en la celda A10 quiero que aparezca siempre el nombre del periodo al que corresponde la liquidación de los intereses, en este caso aparece semestral, pero bien pudiera haber sido otro periodo, por lo que tendremos que recurrir nuevamente a anidar la función si. Por otra parte quiero que siempre el periodo vaya precedido de la expresión cuota, de forma que tendré que concatenar dos expresiones de la siguiente manera:
=(«CUOTA»&»»&SI(B5=»A»;»ANUAL»;SI(B5=»M»;»MENSUAL»;SI(B5=»T»;»TRIMESTRAL»; SI(B5=»D»;»DIARIA»; SEMESTRAL»)))))
Para obtener el importe de la cuota del préstamo emplearemos la función PAGO, que tiene como argumentos (parámetros) la tasa equivalente del periodo correspondiente; los periodos de la operación y el importe del préstamo:
=PAGO(B8;B9;-B2)
Seguidamente no interesa obtener la cuantía a que ascienden los intereses que corresponden a la cuota:
Estos les calcularemos con la función Pagoint, los argumentos de la función, serían la tasa de interés equivalente del periodo, el periodo concreto del periodo (en número); el total de periodos de la operación y el importe total de la operación.
PAGOINT(B8;B6;B9;-B2)
Y por último calculamos el importe de cuota que corresponde a la amortización del capital, recurrimos también a las funciones financieras de Excel con Pagoprin:
PAGOPRIN(B8;B6;B9;-B2)
Los argumentos son los mismos que la función pagoint.
Hasta aquí hemos presentado las operaciones para que nos den los resultados en sus correspondientes celdas. A continuación, lo que vamos a preparar para que los resultados se muestren únicamente cuando aparezcan todos los datos de las variables.
Recurriremos a agregar al comienzo de cada formula recurriremos a los siguientes expresiones:
-
B3=»» que significa que compruebe si la celda B3 esta vacia o no tiene ningún valor
-
B3<>»» que significa que compruebe si la celda B3 contiene un valor distinto a nada o si está vacia.
Para obtener el tipo de interés equivalente del periodo, recurrimos a la función lógica Y para que nos analice el contenido existente en las celdas B3 y B5
=SI(Y(B3<>»»;B5<>»»);(B3/ SI(B5=»A»;1;SI(B5=»S»;2;SI(B5=»T»;4;SI(B5=»M»;12;365))))).;»»)
La siguiente celda para dar el numero de periodos:
=SI(Y(B4<>»»;B5<>»»);(B4* SI(B5=»A»;1;SI(B5=»S»;2;SI(B5=»T»;4;SI(B5=»M»;12;365)))));»»)
Para obtener la cuota la formula será:
=SI(B5<>»»;»CUOTA»&»»&=(«CUOTA»&»»&SI(B5=»A»;»ANUAL»;SI(B5=»M»;»MENSUAL»; SI(B5=»T»;»TRIMESTRAL»; SI(B5=»D»;»DIARIA»; SEMESTRAL»)))))
Y para obtener el valor
=SI(Y(B8<>»»;B9<>»»;B2<>»»);PAGO(B8;B9;-B2);»»)
Y, por último, el valor del capital amortizado en el periodo
=SI(B6=»»;»»;PAGOPRIN(B8;B6;B9;-B2))
Ejercicios de Excel 