Curso Exel

CASO 1.

Uso de funciones básicas de Excel

TABLA DE AMORTIZACIÓN

La tabla de amortización

Siempre que nos presentamos a solicitar un préstamo a una institución financiera, sea esta bancaria o no bancaria; usualmente nos informan sobre tres variables claves del cálculo de la cuota de dicho préstamo, esto es la tasa de interés, que representa el costo financiero del crédito solicitado, el plazo o período paga pagar dicha deuda y el Monto o cantidad que requerimos que nos presten. Estas tres variables forman la base del cálculo de cualquier cuota a pagar por mes por un crédito en condiciones normales.

Ahora bien junto con dichos datos, también es usual que nos proveean de una Tabla de Pagos, o Tabla de Amortización, la cual no es ni más ni menos que un detalle de cómo se irán aplicando las cuotas pagadas a la amortización de la deuda y el correspondiente pago de intereses. Es de notar que aún cuando la cuota siempre es la misma, la composición entre intereses y amortización varía, siendo la cantidad asignada a los primeros mayor en los primeros pagos y menor en los últimos; la amortización sigue el proceso opuesto. Lo anterior claro está si cancelamos religiosamente nuestras deudas

Veamos un ejemplo

Con el objetivo de incrementar sus ventas, un cliente suyo propietario de una agencia de autos usados, está interesado en que usted le ayude a diseñar un sistema para calcular fácilmente la tabla de pagos de los créditos que ofrecerá a sus compradores, como opción de financiamiento en la compra de los vehículos. Debido a que en la actualidad no cuenta con los recursos suficientes para comprar un software específico para tal efecto, la misma deberá realizarse en un archivo Excel, automatizando en todo lo posible dicha hoja.

Información adicional:

a) El monto mínimo de financiamiento que ofrecerá el propietario será de ¢300,000.00 y el máximo de ¢3,000,000.00

b) Se desea que la hoja tome la fecha de la tabla del computador directamente.

c) En la celda correspondiente al Principal adeudado, debe indicar formato blanco con letras negras, cuando el préstamo solicitado sea igual o inferior a ¢1,000,000.00; Amarillo con letras negras, cuando el préstamo exceda el ¢1,000,000.00 pero sea igual o inferior a ¢2,000,000.00 y finalmente; fondo rojo con letras blancas en negrita, cuando el mismo supere dicha cantidad.

d) El monto de la cuota debe leerse en grande y estar redondeado al entero más próximo.

e) La hoja sólo deber permitir a un usuario cualquiera de la misma el modificar los datos correspondientes al Principal, la Tasa y el Plazo. Todos los demás datos deben estar protegidos.

f) A fin de facilitar el manejo de los datos, la tabla debe contener botones de comando que ayuden al usuario a limpiar los datos iniciales, imprimir la tabla, y cerrar el archivo una vez usado.

g) Asimismo se requiere de asignar un password a la hoja electrónica a fin de protegerla contra accesos indebidos.

h) Adicionar todos los elementos necesarios para que la tabla se muestre estéticamente aceptable.

Antes de iniciar por favor, active el archivo EJER_1_TABLAMORT incluído en el CD de prácticas entregado por su instructor.

1. Función HOY.

Esta función no tiene argumentos pero es sumamente útil ya que actualiza una celda con la fecha del computador, cada nuevo día. Posiciónese en la celda de FECHA, y mediante el asistente de funciones introduzca la función HOY (TODAY) . Observe que la celda indica la fecha del computador.

2. Rellenado automático de celdas.

Posiciónese en la columna NO_CUOTA introduzca el número 1 y marque un rango suficiente hacia abajo (más de 60 líneas), luego haga click en la ficha inicio y seleccione la opción Rellenar (Fill) del grupo Modificar y haga click en Series. Tal y como se muestra a continuación:

El incremento es el valor que deseamos en la progresión, y el límite es el valor al que deseamos llegar, la progresión puede ser lineal, geométrica cronológica o autorrellenar (cuando deseamos que Excel rellene por defecto.

Posiciónese en la celda FECHA, y digite la fecha inicial equivalente a un mes después de la fecha actual, y con ayuda de los comandos anteriores rellene las fechas de pago, marcando esta vez el rango hasta la cuota 60 mediante una secuencia cronológica mensual, el cuadro de diálogo mostrará entonces:

Observe que las fechas son rellenadas exactamente en el rango indicado, a intervalos de un mes exacto de la fecha actual.

3. Calculo de intereses y amortización Antes de aplicar estas funciones debemos de introducir algunos datos que nos sirvan de prueba como se indica:

PRINCIPAL ₡ 350,000.00

TASA 28.00%

PLAZO 60

Vaya a la columna INTERESES y mediante la función PAGOINT (IPMT), calcule los intereses por cuota. Luego haga lo mismo con la columna amortización mediante la función PAGOPRIN (PPMT).

Observe que en este caso tanto la Tasa, como el número total de períodos (nper), así como el valor actual (Va) deben definirse fijos, a lo largo de todo el préstamo, debido a que la fórmula siempre se referirá a ellos para el cálculo . Asimismo el resultado se presenta negativo por tratarse de un pago, por lo que si se desea que aparezca positiva deberá adicionar un signo menos (-) delante de la función.

Una vez realizadas ambas funciones, cópielas hacia abajo marcando el rango de ambas y haciendo doble click en la esquina inferior derecha de la celda, verá que las fórmulas se copian exactamente hasta la cuota No. 60

4. Uso de nombres de rango.

Un nombre de rango es un nombre específico que le damos a una celda o conjunto de celdas, para ello nos posicionamos en la celda o marcamos el rango deseado y posteriormente ubicado en el indicador de celda digitamos el nombre particular. Posiciónese en la celda contigua a la palabra PRINCIPAL, vaya al indicador de celda ubicado en la esquina superior izquierda de la tabla antes de los fichero, y digite la palabra PRINCIPAL, luego oprima la tecla enter. Haga lo mismo con las otras celdas de TASA y PLAZO. Los nombres de rango, facilitan la búsqueda de rangos de celda específicos y simplifican los cálculos y las fórmulas como lo veremos a continuación. Debe ser un nombre CORTO, sin espacios, y que no sea una palabra reservada para Excel como por ejemplo SUMA. También es posible

...