mÓdulo de formaciÓn: excel basico · versión: 1 fecha: mayo 2010 regional bogota centro de...

REGIONAL BOGOTA

CENTRO DE SERVICIOS FINANCIEROS UNIDAD DE TECNOLOGIA E INFORMATICA

EXCEL FUNCIONES FINANCIERAS TALLER DE APRENDIZAJE

Fecha: MAYO 2010 Código Versión: 1 Página 1_de 22_

REGIONAL BOGOTA

CENTRO DE SERVICIOS FINANCIEROS

UNIDAD DE TECNOLOGIA E INFORMATICA

MÓDULO DE FORMACIÓN:

EXCEL BASICO

TALLER DE APRENDIZAJE FUNCIONES FINANCIERAS

REGIONAL BOGOTA




La naturaleza hace que los hombres nos parezcamos unos a otros y nos

juntemos; la educación hace que seamos diferentes y que nos alejemos.

Confucio

1. PRESENTACIÓN

Amig@ Aprendiz:

El presente taller de Aprendizaje tiene como finalidad, orientar su proceso de

aprendizaje que le permita “Adquirir los conocimientos para la el manejo de algunas funciones financieras", a partir de la realización de las acciones,

descritas en las estrategias metodológicas como: la exploración de saberes

previos, la apropiación del aprendizaje y la aplicación del conocimiento en la

práctica de ejercicios que conduzcan a alcanzar los Resultados de aprendizaje

planteados en esta Unidad de Aprendizaje y reunir las Evidencias de

Aprendizaje.

REGIONAL BOGOTA




2. DESCRIPCION DEL TALLER DE APRENDIZAJE

MÓDULO DE FORMACIÓN: EXCEL BASICO

UNIDAD DE APRENDIZAJE: FUNCIONES FINANCIERAS

RESULTADOS DE APRENDIZAJE:

1. Formar una base sólida sobre los conceptos esenciales en el manejo de

algunas de las funciones financieras

2. Creación de hojas de trabajo utilizando las diferentes funciones de

Excel

ACTIVIDADES:

Esta Unidad de Aprendizaje comprende las siguientes actividades

1. Leer el material de apoyo "Excel Básicos”.

2. Realizar los ejercicios propuestos.

DURACIÓN: 10 HORAS

REGIONAL BOGOTA




EVIDENCIAS DE APRENDIZAJE:

Para evaluar su proceso de aprendizaje, se requiere que usted reúna las

siguientes evidencias de aprendizaje.

1. Desarrollar los ejercicios en un solo libro de Excel y en cada hoja del

mismo elaborar un ejercicio.

REGIONAL BOGOTA




Función PAGO

Calcula el pago de un préstamo basándose en pagos constantes y una tasa de interés

constante.

Sintaxis PAGO(tasa;nper;va;vf;tipo)

Tasa: es la tasa de interés del préstamo

Nper : es le numero total de pagos del préstamo

Va: es el valor actual

Vf : es el valor futuro. Si el argumento vf se omite, se asume que es 0

(o el valor futuro de un préstamo es cero)

Tipo: es un numero 0 o 1 e indica el vencimiento de pagos

Tipo: 0 al final del periodo

Tipo: 1 al inicio del periodo

Observaciones: El pago devuelto incluye el capital y el interés

Ejemplo

En el caso de producirse el pago al inicio del periodo

Función PAGO para producir un ahorro en un tiempo determinado

REGIONAL BOGOTA




La función PAGO, también puede determinar los pagos mensuales que deberían

efectuarse para producir un ahorro de $ 20.000.000 en 10 años a una tasa anual del

10% de interés en una cuenta de ahorro

Si se depositan $508,684.30, cada mes en una cuenta de ahorro, que paga el 6.5% de

interés, al final de 2 años se abra ahorrado $ 13.000.000

Función PAGOINT

Calcula el interés pagado en un periodo especificado por una inversión basándose en

una tasa de interés constante y pagos en periodos constantes.

Sintaxis PAGOINT(tasa;periodo;nper;va;vf;tipo)

Tasa: es la tasa de interés del periodo

Periodo: es el periodo para el que se desea calcular el interés y

deben estar entre 1 y el argumento nper

Nper: es número total de pagos del préstamo

Va: es el valor actual de una serie de pagos futuros

Vf: es el valor futuro de una serie de pagos futuros. Si se omite se calcula

como cero

Tipo: es un numero 0 o 1 e indica el vencimiento de pagos

Tipo : 0 al final del periodo

Tipo : 1 al inicio del periodo

REGIONAL BOGOTA




Ejemplo

En este caso se puede apreciar en la celda B7 que se calcula la parte correspondiente

a intereses que incluye la cuota calculada anteriormente de $ 928.566,20 en el

primer pago es decir en el mes 1

En este caso se calcula en la celda B7 los intereses que integran la cuota de pago en él

último mes es decir el mes 15

Función PAGOPRIN

Calcula el pago sobre el capital de una inversión durante un periodo determinado,

basándose en una tasa de interés constante y pagos periódicos constantes

Sintaxis PAGOPRIN(tasa;periodo;nper;va;vf;tipo)

Tasa: es la tasa de interés del periodo

Periodo: es el periodo para el que se desea calcular la amortización y

deben estar entre 1 y el argumento nper

Nper: es numero total de pagos del préstamo

Va: es el valor actual de una serie de pagos futuros

Vf : es el valor futuro de una serie de pagos futuros.

Si se omite se calcula como cero

Tipo : es un numero 0 o 1 e indica el vencimiento de pagos



REGIONAL BOGOTA




Ejemplo

En este caso se puede apreciar en la celda B7 que se calcula la parte correspondiente

a amortización que incluye la cuota calculada anteriormente de $ 928.566,20 en el

primer pago es decir en el mes 1

En este caso se calcula en la celda B7 la amortización que integra la cuota de pago en

él ultimo mes es decir el mes 15

Como puede apreciarse produciendo la suma de amortización e interés en ambos casos

se obtiene el valor de la cuota a pagar

$113,750 + $814,816.20 = $ 928.566,20

$8,054.48 + %920,511.73 = $ 928.566,20

El sistema desarrollado para calcular el préstamo es según el sistema francés donde el

valor de la cuota es constante.

Función PAGO.PRINC.ENTRE

Calcula la cantidad acumulada de capital pagado de un préstamo entre dos periodos

(per_inicial y per final)

REGIONAL BOGOTA




Sintaxis PAGO.PRINC.ENTRE(tasa;nper;vp;per_inicial;per_final;tipo)

Tasa: es la tasa de interés

Nper: es él numero total de periodos de pago

Per_inicial: es el primer periodo en el calculo.

Per_final: es le ultimo periodo en el calculo

Tipo: es el tipo de pago (al comienzo o al final del periodo) el valor debe ser 0 o 1



La Celda B7, calcula la cantidad acumulada de capital pagado del préstamo entre los

periodos

3 y 9

En este caso la Celda B7, calcula la cantidad acumulada de capital pagado del préstamo

entre el periodo inicial 1 y el periodo final 1, el resultado coincide con el obtenido

aplicando la función PAGOPRIN, correspondiente al mes 1

Función PAGO.INT.ENTRE

Calcula la cantidad de interés pagado de un préstamo entre dos periodos (per_inicial y

per_final)

REGIONAL BOGOTA




Sintaxis PAGO.INT.ENTRE(tasa;nper;vp;per_inicial;per_final;tipo)

Tasa: es la tasa de interés

Nper: es él numero total de periodos de pago

Per_inicial: es el primer periodo en el calculo.

Per_final: es le ultimo periodo en el calculo

Tipo: es el tipo de pago de intereses

(al comienzo o al final del periodo); el valor debe ser 0 o 1

Tipo :0 al final del periodo

Tipo :1 al inicio del periodo

Ejemplo

La Celda B7, calcula la cantidad acumulada de intereses pagado del préstamo entre los

periodos 3 y 9

La suma de los intereses y amortización acumulados entre los periodos 3 y 9, equivalen

a la suma de las cuotas ejecutadas en los mismos periodos

En este caso la Celda B7, calcula la

cantidad acumulada de interés pagado del préstamo entre el periodo inicial 1 y el

periodo final 1, el resultado coincide con el obtenido aplicando la función PAGOINT,

correspondiente al mes 1

REGIONAL BOGOTA




Función INT.EFECTIVO

Calcula la tasa efectiva del interés anual, si se conocen la tasa de interés anual nominal

y él numero de periodos de interés compuesto por año.

Sintaxis INT.EFECTIVO(Int_nominal;num_por_año)

Int_nominal: es la tasa de interés nominal

Num_por_año: es él numero de pagos de interés compuesto por año.

La celda B5 calcula el interés efectivo para una periodicidad de 12 pagos anuales

Función TASA. NOMINAL

Calcula la tasa de interés nominal anual, si se conocen la tasa efectiva y él numero de

periodos de interés compuesto por año

Sintaxis TASA.NOMINAL(tasa_efectiva;num_per)

Tasa_efectiva es la tasa de interés efectiva anual

Num_per es él numero de pagos de interés por año

REGIONAL BOGOTA




La celda B5 calcula la Tasa Nominal anual del préstamo, tomando el interés efectivo y

la periodicidad de 12 pagos anuales

Función TASA

Calcula la tasa de interés por periodo de una anualidad

Sintaxis TASA(nper;pago;va;vf;tipo;estimar)

Nper es él numero total de periodos de pago en una anualidad

Pago es el pago que se efectúa en cada periodo y que no puede cambiar durante la vida de anualidad. Generalmente el argumento pago incluye el capital y el interés, pero no incluye ningún otro arancel o impuesto.

Va es el valor actual de la cantidad total de una serie de pagos futuros

Vf es el valor futuro o saldo en efectivo que desea lograr después de efectuar él ultimo pago. Si el argumento vf se omite, se asume que el valor es cero (por ejemplo el valor futuro de un préstamo es cero)

Tipo: es el valor debe ser 0 o 1 e indica el vencimiento de los pagos



Ejemplo

En la celda B7 la Función Tasa calcula la tasa de interés mensual, que en la celda B8 al

multiplicarla por 12 periodicidad anual devuelve la tasa anual.

Se trabajo sobre el mismo ejemplo anterior para verificar que los valores obtenidos

son los mismos

REGIONAL BOGOTA




Función NPER

Calcula el numero de pagos de un préstamo, basado en pagos constantes, periódicos y a

una tasa de interés constante

Sintaxis NPER(tasa;pago;va;vf;tipo)

Tasa es la tasa de interés por periodo

Pago es el pago efectuado en cada periodo, debe permanecer constante durante la vida de la anualidad

Va es el valor actual o la suma total de una serie de futuros pagos

Vf es el valor futuro o saldo en efectivo que desea lograr después de efectuar él ultimo pago. Si el argumento vf se omite, se asume que el valor es cero (por ejemplo el valor futuro de un préstamo es cero)

Tipo: es el valor debe ser 0 o 1 e indica el vencimiento de los pagos



La celda B6 calcula la cantidad de cuotas necesarias para saldar el préstamo según las

características del mismo ( Interés, cuota, monto). Se mantiene el mismo ejemplo para

verificar los datos.

Función RENDTO.VENCTO

Calcula el rendimiento anual de un valor bursátil que paga interese al vencimiento.

SintaxisRENDTO.VENCTO(liq;vencimiento;emision;tasa;precio;base)

Liq: es la fecha de liquidación del valor bursátil. Fecha de compra del cupón.

REGIONAL BOGOTA




Vencimiento: es la fecha de vencimiento del valor bursátil

Emisión: es la fecha de emisión del valor bursátil

Tasa: es la tasa de interés en la fecha de emisión del valor bursátil

Precio: es el precio del valor del bono por cada $ 100 de valor nominal

Base: determina en que tipo de base deben ser contados los días,

adoptamos 3 correspondiente a 365 días al año.

Bono 1: El 10/11/00 se emite un bono a un año, con vencimiento el 10/11/01. Se

efectúa una compra el 22/03/01 de $ 100,15 por cada $ 100 de valor nominal. La celda

B8 contiene la función que calcula el rendimiento de la operación su vencimiento.

Bono 2: El 11/11/96 se emite un bono a cinco años, con vencimiento el 10/11/01. Se

efectúa una compra el 22/03/98 de $ 88,15 por cada $ 100 de valor nominal. La celda

C8 contiene la función que calcula el rendimiento de la operación su vencimiento.

FUNCION SI

La Función SI permite evaluar una condición, en el caso de que esa condición sea

verdadera el la celda tendrá un valor, y en el caso de que sea falsa tendrá otro valor.

Para entender esta función vamos a realizar una pequeña planilla donde tendremos por

cada registro o fila el nombre, y 3 notas para cada persona. En la cuarta columna

calcularemos el Promedio de estas tres notas (si o recuerda como calcular el promedio

vea el ejercicio básico de excel).

http://www.bufoland.cl/apuntes/excel/excel.php

REGIONAL BOGOTA




La quinta columna nos servirá para indicar la situación del alumno, la cual será

aprobado en el caso de que su promedio sea mayor o igual a 4.0, o reprobado en el

caso de que sea menor que 4.

Para estimar la situación de cada alumno procederemos de la siguiente manera: nos

situamos en la celda donde vamos a calcular la situación del primer alumno (celda F2) y

presionamos el piloto de funciones . aparecerá la ventana Insertar función. En ella

seleccionaremos la categoría Lógicas y luego seleccionaremos la función SI. En la

parte inferior del panel aparece una breve reseña de la función:

SI(prueba_lógica;valor_si_verdadero;valor_si_falso)

Comprueba si se cumple ua condición y devuelve un valor si se evalúa como

VERDADERO y otro valor si se evalúa como FALSO.

Presionamos Aceptar y pasamos a la siguiente ventana donde indicaremos los

argumentos de la función. Para nuestro caso debemos evaluar si el promedio es mayor

o igual que 4, por lo tanto en el argumento prueba_lógica colocamos el nombre de la

celda donde está el promedio (podemos seleccionarla con el puntero del mouse), y a

continuación escribimos mayor o igual que 4 de la forma: E2>=4.

En el argumento Valor_si_verdadero colocamos el valor que deseamos para el caso que

nuestra condición sea verdadera, que sería Aprobado.

REGIONAL BOGOTA




En el argumento Valor_si_falso colocamos el valor que deseamos para el caso que

nuestra condición sea falsa, que sería Reprobado.

Aceptamos, y luego autocompletamos para el resto de los registros.

Hay que hacer notar que si seleccionamos la celda donde hemos aplicado la función si,

veremos que la forma de escribir la formula manualmente es:

=si(E2>=4;"Aprobado","Reprobado")

Formato Condicional

Las planillas de cálculo nos permiten también formatear los datos tabulados en base a

alguna condición. Por ejemplo podemos hacer que si cualquiera de las 3 notas o el

promedio es menor que 4 se escriba con color rojo o que si es mayor o igual que 4 se

escriba con color azul.

REGIONAL BOGOTA




Para ello primero seleccionaremos todas las celdas que tienen contenido numérico.

Luego vamos al menú Formato, seleccionamos la opción Formato condicional tras lo

cual aparecerá la ventana Formato Condicional.

La Condición 1 la ajustaremos para que el valor de la celda sea mayor o igual que 4.

Luego presionaremos el botón formato correspondiente a la Condición 1 y ajustaremos

el color de fuente para que sea azul.

REGIONAL BOGOTA




Aceptamos, y de vuelta en la ventana Formato Condicional agregamos una segunda

condición mediante el botón Agregar. La condición será que el valor de la celda sea

menor que 4, y el formato será el color rojo.

Finalmente nuestro archivo debería verse así:

Hay que hacer notar que si cambiamos alguna de las notas, automáticamente cambiará

el promedio, y si la nota pasa de ser mayor o igual que 4 a ser menor que 4, cambiará

de color y viceversa.

Esta planilla podría servirnos para saber que nota debemos sacarnos para aprobar una

asignatura conociendo previamente 2 de las 3 notas.

EJERCICIO FUNCION SI

1. Hotel Sol y Playa

Los gastos del primer trimestre del Hotel Sol y Playa han sido:

Las nóminas del personal de los meses de Enero y Febrero 25 millones y

de Marzo 30 millones.

En alimentación 10 millones cada mes.

En electricidad por los tres meses ha pagado 10 millones.

Los gastos de teléfono de las oficinas son: el primer mes $354.000 ., el

segundo $315.000 ., y el tercero $375.000 .

La póliza del seguro del hotel es de $3.408.000. anuales, pagadera

trimestralmente.

REGIONAL BOGOTA




El hotel tiene contratado un servicio de mantenimiento y paga una

factura mensual de $250.000.. También tiene contratado un servicio de

jardinería por $75.000. al mes

En Marzo el hotel renovó el 40% de su lencería con un coste de

$850.000 .

El grupo musical Hermanos Pérez anima el hotel cada noche cobrando

por actuación $16.000. En carnaval se organizó una fiesta con un coste

de $175.000.

Para la publicidad el hotel tiene contratado un servicio a una Empresa de

Marketing por $100.000. al mes. En Marzo, debido al comienzo de la

temporada alta invierten $250.000. más en publicidad.

Para la prevención de riesgos tienen un contrato con una mutua por

$1.300.000 . al año. Por falta de liquidez acuerdan pagarla

mensualmente.

Los ingresos han sido los siguientes:

1. Como resultado de la actividad hotelera en Enero, Febrero y Marzo,

$55.620.000., $48.750.000. y $49.123.000. respectivamente.

2. Por inversiones financieras ha obtenido unos resultado de 14, 16 y 5

millones en los meses de Enero, Febrero y Marzo respectivamente.

Ejercicio:

1. Copia en una tabla de Excel todos los datos anteriores.

2. Halla el total de los gastos e ingresos en cada mes.

3. Halla el resultado de cada mes. (Ingresos - Gastos).

4. Halla el promedio de cada partida de ingreso y gasto durante los tres

meses.

5. Halla el tanto por ciento de cada partida de ingreso y gasto en relación

con el total

6. Haz una fórmula en la que dependiendo de si obtenemos beneficios o no,

se nos aplique el 35% del impuesto de sociedades.

REGIONAL BOGOTA




Si hay beneficios y se nos aplica el impuesto de sociedades ¿cuál es el

resultado mensual?. ¿Y el trimestral?.

EJERCICIO FUNCIONES BUSCARV Y SI

El siguiente ejercicio consistirá en calcular el total de una factura dependiendo de los

precios que tengamos almacenados.

Crearemos un libro llamado “Facturación.xls” que contendrá dos hojas.

La primera hoja de ella la llamaremos “Productos” y contendrá una serie de productos

en los que se indicará el código, descripción y precio por unidad. Tendrá el siguiente

aspecto:

REGIONAL BOGOTA




La segunda hoja se llamará “Factura”. Escribiremos un código de artículo de la hoja

anterior y nos aparecerá su descripción automáticamente en la celda de la derecha.

También nos aparecerá el precio unitario. Luego introduciremos la cantidad deseada y

Excel nos calculará el total de cada fila, y el total de toda la columna. Aplicaremos el

descuento indicado si el pago es al contado. Para ello deberemos introducir el tipo de

pago “contado” que no debe ser sensible a mayúsculas (da igual si lo escribimos en

mayúsculas o minúsculas). Aplicaremos un IVA del 16% y mostraremos el total a pagar.

La factura deberá tener el siguiente aspecto:

Cada factura se compondrá de 5 productos, que será necesario indicar

obligatoriamente.

REGIONAL BOGOTA




EJERCICIO FORMATO CONDICIONAL

El siguiente ejercicio consistirá en llevar un control de entradas y salidas de dinero al

estilo de una cuenta corriente. En la columna del Entrada situaremos las entradas de

dinero y en la columna del Salida las salidas. La última columna la utilizaremos para

llevar el control del saldo, que se calculará sumando al saldo anterior la última posible

entrada y restándole la última posible salida de dinero. Si el saldo es negativo deberá

de aparecer en color rojo. Sólo se permite un concepto por movimiento.

Crearemos un libro llamado “Control de Cuenta” con una sola hoja llamada “Cuenta”.

Para facilitar la visualización y entrada de fórmulas, crearemos espacio para 10

movimientos.

CONTROL DEL DOCUMENTO

Equipo de Trabajo de

Informática

Alvaro Martin Salazar Tamayo

Ruben Eduard Bernal Peña

Mary Luz Rubiano

Revisión y Aprobación

mÓdulo de formaciÓn: excel basico · versión: 1 fecha: mayo 2010 regional bogota centro de...

Documents