2013

[SEPARATA N. 03] Copyright – Ing. Carlos Lon Kan Prado

EXCEL AVANZADO PARA LOS NEGOCIOS

1

CURSO : Excel Avanzado para los negocios PROFESOR : Ing. Carlos Lon Kan Prado

LABORATORIO DIRIGIDO 03 INDICACIONES Leer todos los pasos preliminares antes de empezar a desarrollar el presente laboratorio.

La carpeta principal se llama: 03-excel-avanzado

Ejemplo Ruta-

C:/Alumno.Utp

03-excel-avanzado

Apoyo

03-Guia.docx

Objetivo: Utilizar la herramienta Excel para Funciones financieras:

Tasa interna de retorno, Valor presente, valor futuro, pagos mensuales de una inversión.

Preliminares:

1. Tasa interna de retorno TIR

La función TIR calcula la rentabilidad o "Tasa Interna de Retorno" de una serie de cobros y pagos.

Veamos el siguiente ejemplo para entenderla mejor.

Ejemplo: Un inversor evalúa un negocio. En el rango E17:E21 está la inversión necesaria, los

beneficios esperados y el valor de venta de la inversión. En la celda G18 desea averiguar la Tasa

Interna de Retorno del negocio. A continuación se presenta la solución.

Sintaxis:

=TIR(valores;[estimar]) //

valores : representan los cobros y pagos (flujos de fondos).

estimar: esta función calcula por "iteración" por lo que se puede definir una tasa con la cual comenzar

la iteración. Es opcional, si se omite la función comienza a iterar desde 0%.

2

2. Valor actual netos (VNA)

La función VNA calcula el valor actual neto de un flujo de fondos (ingresos y egresos) y de importe

variable. Veamos el siguiente ejemplo para entenderla mejor.

Ejemplo:

Un inversor evalúa un negocio. En el rango E17:E21 está la inversión necesaria, los beneficios

esperados y el valor de recupero de la inversión. En la celda E23 está la tasa de descuento a utilizar.

En la celda G18 desea averiguar el valor actual neto de su inversión. A continuación se presenta la

solución.

Sintaxis:

= VNA(tasa; valor 1; valor 2;….)

tasa: es la tasa de interés utilizada para descontar los valores.

valor 1: pueden ser referencias a celdas o rangos que contengan el importe de los valores a descontar.

3. Valor presente

La función VA calcula el valor actual de una serie de pagos de importe similar. Veamos el siguiente

ejemplo para entenderla mejor

Ejemplo:

Un empresario analiza la concesión de un negocio cuya duración está indicada en la celda E19 y cuyas

ganancias anuales se indican en la celda E17. La tasa de interés del dinero es la de la celda E18. En la

celda G19 desea calcular el valor actual del negocio. A continuación se presenta la solución.

Sintaxis:

3

= VA(tasa;nper;pago)

tasa: es la tasa de interés con la que se descontarán los pagos.

nper: es el total de pagos o cuotas existentes.

pago: es el importe del o los pagos (se asumen pagos similares).

4. Valor futuro

La función VF calcula el valor futuro de una serie de pagos de importe similar. Veamos el siguiente

ejemplo para entenderla mejor.

Ejemplo:

Un inversor desea incrementar su riqueza. En la celda E19 se expresa el plazo de su inversión y en la

celda E17 está el monto que podría invertir anualmente. La celda E18 tiene la tasa de interés que

ganaría. En la celda G18 desea calcular el dinero que tendrá al final de su inversión. A continuación se

presenta la solución.

Sintaxis:

= VF(tasa;nper;pago)

tasa: es la tasa de interés con la que se capitalizarán los pagos.

nper: es el total de pagos o cuotas a realizar.

pago: es el importe del o los pagos (se asumen pagos similares).

5. Pagos mensuales de un préstamo o inversión.

La función PAGO calcula el importe de la cuota que amortiza un préstamo. Esta función utiliza el

método francés que genera un pago constante y similar para todos los períodos y que incluye pago de

capital e intereses. Veamos el siguiente ejemplo para entenderla mejor.

Ejemplo:

Una persona analiza los términos de un préstamo hipotecario indicados en el rango D17:E19. En la

celda G18 desea averiguar el pago mensual que deberá realizar para cancelar el mismo. Veamos la

solución.

4

Sintaxis:

tasa: es la tasa de interés aplicable al préstamo (o la inversión). Si la tasa es anual, nper debe estar

expresado en años. Si la tasa es mensual, nper debe estar expresado en meses.

nper: es el plazo en el que se debe cancelar o amortizar por completo el préstamo. El argumento nper

también se interpreta como el total de pagos o cuotas que se harán para cancelar el préstamo.

va: es el valor actual o valor del préstamo o la inversión.

vf: es un valor futuro pendiente de pago en caso que no se pague todo el préstamo al final (es

opcional).

tipo: Es “1” si los pagos son al comienzo del período o “0” si son al final. Si se omite este valor la

función considera “0”.

De aquí en adelante se usará el archivo Excel finanzas.xlsx

Actividad 1

En la hoja TIR:

Utilizando la función TIR determine cuál es la rentabilidad de un negocio cuyos cobros y pagos están

expresados en el rango C9:C13. Inserta la función en la celda E8.

Actividad 2

En la hoja TIR2

Evalúe los flujos de cada uno de los tres periodos (2010, 2011 y 2012) y determine el periodo y la TIR para la

mejor rentabilidad.

5

Actividad 3

En la hoja VAN

La Empresa REGALOS SAC tiene cuatro posibles proyectos para invertir (mutuamente excluyentes). Los cash

flows de los proyectos son los siguientes.

a. Determine el VAN de cada proyecto para distintos tipos de descuento (0%, 5%, 10%, 15%, 20%). Luego

indique que proyecto se prefiere en cada caso.

b. Construir un gráfico de los proyectos versus la tasa de descuento (Similar a la imagen que se adjunta).

Seleccione el rango: B16:G21, Insertar, Gráficos Dispersión, dispersión con líneas suavizadas.

6

Actividad 4

En la hoja VAN2

a. Encuentre el valor del VAN y del TIR para el flujo de caja

b. Construir la gráfica VAN vs Tasa

c. Analizar el comportamiento del VAN por efectos de la variación de la tasa de descuento en el intervalo

[ 0, 30 ] %

Actividad 5

En la hoja PAGOS

a. ESCENARIO 1: Utilizando la función PAGO determine el pago mensual necesario para cancelar un

préstamo cuyas condiciones se indican:

7

Préstamo de 120000 dólares

Tasa: 11%

Plazo: a 2 años

b. ESCENARIO 2: Utilizando la función PAGO determine el pago trimestral necesario para cancelar un

préstamo cuyas condiciones se indican:

Préstamo de 120000 dólares

Tasa: 11%

Plazo: a 2 años

c. ESCENARIO 3: Utilizando la función PAGO determine el pago semestral necesario para cancelar un

préstamo cuyas condiciones se indican:

Préstamo de 120000 dólares

Tasa: 11%

Plazo: a 2 años

d. Para cada escenario. Determine el importe total que se debe pagar a la institución financiera

e. Para cada escenario. Determine los intereses totales que se debe pagar a la institución financiera.

f. Analice los tres escenarios y evalúe desde el punto de vista CLIENTE cuál de los tres es el más

conveniente.

Actividad 6

En la hoja PAGOS2

Crear el cronograma de pagos para el mejor escenario de la actividad anterior.

Actividad 7

En la hoja VA1

Se propone realizar un plan de jubilación que nos page 500 soles mensuales durante 15 años. El plan nos cuesta 35.000 soles y el dinero pagado devenga un interés anual de 10%. Utilizaremos la función VA para calcular si

merece la pena hacer el plan de jubilación.

Plan 35,000.00S/.

Tasa 10% anual

Plazo 15 años

Cuota 500.00S/. mensuales

8

Actividad 8

En la hoja VF1

Planteamos ahorrar dinero hasta una fecha límite y con una fecha de inicio. Con un ingreso inicial de 2.000 soles, sabemos que el interés devengado por la cuenta de ahorro es del 7%, vamos a ingresar cada mes 100 soles

y vamos a esperar 12 meses (1 año) a ver qué resultado nos ofrece.

Inicial 2,000.00S/.

Tasa 7% anual

Plazo 12 meses

Cuota 100.00S/. mensuales