Download - Tema4 Excel
-
8/2/2019 Tema4 Excel
1/55
1
Funciones financieras de Excel
-
8/2/2019 Tema4 Excel
2/55
2
CONTENIDOS
Inversiones
Operaciones con Capitales
Prstamos Bancarios
Proyectos de Inversin
-
8/2/2019 Tema4 Excel
3/55
3
Capitales
Conceptos Valor Actual Inters/Tasa Nmero de periodos Valor Futuro
Vencimiento de los pagos
Inversiones
-
8/2/2019 Tema4 Excel
4/55
4
Valor Actual
VA Tasa por periodo Nmero de periodos
Pago cada periodo (si se omite VF obligatorio) Valor futuro o saldo efectivo despus del ltimo
pago (omisin 0)
Tipo de vencimiento (omisin 0) Al final 0 Al principio 1
Inversiones
-
8/2/2019 Tema4 Excel
5/55
5
Valor Actual: Ejemplo
Una empresa comercial ha decidido lanzar una nuevapromocin para uno de sus productos. La idea es que losganadores recibirn un sueldo mensual fijo de 600 durante losprximos 10 aos. El sorteo se celebrar entre todos aquelloscompradores que enven una etiqueta del producto, y se
sortearn 1000 premios. Cunto le costar a la empresa esta promocin a un tipo de
inters anual del 10%?
Inversiones
-
8/2/2019 Tema4 Excel
6/55
6
Valor Actual: Ejemplo
solucin
Inversiones
=VA(B2/12;B3*12;B1)
-
8/2/2019 Tema4 Excel
7/55
7
Valor Final
VF Tasa por periodo Nmero de periodos
Pago cada periodo (si se omite VA obligatorio) Valor actual o el importe total de una serie de
pagos futuros (omisin 0)
Tipo de vencimiento(omisin 0) Al final 0 Al principio 1
Inversiones
-
8/2/2019 Tema4 Excel
8/55
8
Valor Final : Ejemplo
Un particular acude a una entidad financiera para solicitar unproyecto de inversin personalizado a 20 aos. Una vez reunidocon el director de la entidad, decide abrir una inversin con lassiguientes caractersticas: Abrir un fondo de 6.000 El ltimo da de cada mes ingresar 600 durante los 20
aos El inters que le ofrece la entidad es del 8%
Con estos datos el particular se pregunta Con cunto dinerocontar despus de los 20 aos?
Inversiones
-
8/2/2019 Tema4 Excel
9/55
9
Valor Final : Ejemplo
solucin
Inversiones
=VF(C5/12;C8*12;C7;C6;0)
-
8/2/2019 Tema4 Excel
10/55
10
Inters por periodo
TASA (funcin iterativa) Nmero de periodos Pago cada periodo (si se omite VF obligatorio) Valor actual de la cantidad total de una serie de
pagos futuros Valor futuro o saldo efectivo que se desea lograr
despus del ltimo pago (omisin 0)
Tipo de vencimiento (omisin 0) Al final 0 Al principio 1
Valor inicial para comenzar la iteracin
Inversiones
-
8/2/2019 Tema4 Excel
11/55
11
Inters por periodo: Ejemplo Un empresario acude a una entidad bancaria para solicitar un
crdito de 6.000 . Una vez reunido con el director de laentidad, ste le indica que deber pagar 800 todos los mesesdurante los prximos 10 aos.
Con estos datos el empresario se pregunta Cul es el tipo deinters que me estn aplicando?
Inversiones
-
8/2/2019 Tema4 Excel
12/55
12
Inters por periodo: Ejemplo
solucin
Inversiones
=TASA(B3*12;B2;B1)*12
-
8/2/2019 Tema4 Excel
13/55
13
Nmero de periodos
NPER Tasa por periodo Pago cada periodo (si se omite VF obligatorio) Valor actual o la suma total de una serie de pagos
futuros Valor futuro o saldo efectivo que se desea lograr
despus del ltimo pago (omisin 0)
Tipo de vencimiento (omisin 0) Al final 0 Al principio 1
Inversiones
-
8/2/2019 Tema4 Excel
14/55
14
Nmero periodos: Ejemplo
Un empresario desea comprar una mquina de30.000 . Los tipos de inters a los que seencuentran los prstamos estn a un 10% anual y elempresario puede pagar como mximo cada mes
1200 . Durante cuntos aos deber estarpagando el empresario?
Inversiones
-
8/2/2019 Tema4 Excel
15/55
15
Nmero periodos: Ejemplo
solucin
Inversiones
=REDONDEAR(NPER(B3/12;B2;B1);0)
-
8/2/2019 Tema4 Excel
16/55
16
Comparacin de Inversiones
Habitualmente las funciones que se han estudiado seutilizan para realizar comparaciones entre distintasinversiones. Por ejemplo, qu es ms beneficioso unpremio de 100.000 o un premio de 600 durante
toda la vida. La respuesta depende del inters quese considere y del nmero de aos que uno esperevivir. Realice una tabla donde se valoren un conjuntode casos posibles.
Inversiones
-
8/2/2019 Tema4 Excel
17/55
17
Prstamos Bancarios
Prstamos Bancarios Dado un Capital que se desea pedirprestado
A un inters por periodo Durante un nmero de periodos Calcular por periodo:
el pago total el pago de capital el pago de intereses
Inversiones
-
8/2/2019 Tema4 Excel
18/55
18
Prstamos Bancarios
PAGO Tasa por periodo Nmero de periodos Valor actual o lo que vale ahora una cantidad de
pagos futuros Valor futuro o saldo en efectivo que se desea
lograr despus del ltimo pago (omisin 0, lonormal en los prstamos)
Tipo de vencimiento (omisin 0) Al final 0 Al principio 1
Inversiones
-
8/2/2019 Tema4 Excel
19/55
19
Prstamos Bancarios
PAGOPRIN Tasa por periodo Perodo Nmero de periodos Valor actual o lo que vale ahora una cantidad de
pagos futuros Valor futuro o saldo en efectivo que se desea
lograr despus del ltimo pago (omisin 0) Tipo de vencimiento (omisin 0) Al final 0 Al principio 1
Inversiones
-
8/2/2019 Tema4 Excel
20/55
20
Prstamos Bancarios
PAGOINT Tasa por periodo Perodo Nmero de periodos Valor actual o lo que vale ahora una cantidad de
pagos futuros Valor futuro o saldo en efectivo que se desea
lograr despus del ltimo pago (omisin 0) Tipo de vencimiento (omisin 0) Al final 0 Al principio 1
Inversiones
-
8/2/2019 Tema4 Excel
21/55
21
Prstamos: Ejemplo (1)
Un empresario del sector industrial desea renovar suequipamiento. El valor de estas mquinas es de120.000 . Acude a una entidad bancaria y le ofrecenun crdito por el total a un inters del 4% y a 20
aos. Calcular la cuota anual, mensual, trimestral ascomo una tabla donde se muestre el pago querealiza cada ao, la amortizacin de capital, el pagode intereses y la cantidad total que todava adeuda.
Inversiones
-
8/2/2019 Tema4 Excel
22/55
22
Prstamos: Ejemplo (1)
solucin
Inversiones
=PAGO(B4;B5;B3)
=PAGO(B4/12;B5*12;B3)
=PAGO(B4/4;B5*4;B3)
=PAGOPRIN($B$4;A13;$B$5;$B$3)
=PAGOINT($B$4;A13;$B$5;$B$3)
-
8/2/2019 Tema4 Excel
23/55
23
Prstamos: Ejemplo (2)
Hacer un estudio de los distintos pagosque habra que realizar: Modificando el inters y la cantidad que se
solicita a la entidad Modificando la cantidad solicitada y el
nmero de aos a un inters del 4%
Inversiones
-
8/2/2019 Tema4 Excel
24/55
24
Prstamos: Ejemplo (3)
Un empresario del sector industrial desea renovar suequipamiento. El valor de estas mquinas es de120.000 . Acude a una entidad bancaria y le ofrecenun crdito por el total, a un inters del 4% el primer
ao (renovable cada ao) a 20 aos. Calcular lacuota anual, mensual, trimestral as como una tabladonde se muestre el pago que realiza cada ao, laamortizacin de capital, el pago de intereses y la
cantidad total que todava adeuda, en funcin delinters anual.
Inversiones
-
8/2/2019 Tema4 Excel
25/55
25
Prstamos: Ejemplo (3)
Planteamiento de la Solucin En el ejemplo que se realiz anteriormentecon inters fijo se calculaba el capital quefaltaba por pagar. Precisamente cada aohay que calcular el pago de un prstamode esa cantidad al inters de ese ao.
Para hacerlo fcilmente modificable sedebe calcular el inters de cada ao apartir del anterior.
Inversiones
-
8/2/2019 Tema4 Excel
26/55
26
Proyectos de Inversin
Anlisis de Proyectos de Inversin Desembolsos del proyecto. Ingresos que se derivan del proyecto.
Momento en el que suceden los hechosanteriores. Duracin o vida del proyecto.
Proyectos de Inversin
-
8/2/2019 Tema4 Excel
27/55
27
Proyectos de Inversin
Cuanta de la Inversin Inversin Neta. Dinero invertido
Efecto de la des-inversin Efecto de los impuestos
Ingresos derivados del proyecto Flujo de caja = BDI + Amortizacin
Proyectos de Inversin
-
8/2/2019 Tema4 Excel
28/55
28
Ejemplo Proyecto de Inversin Proyecto de Sustitucin
Mquina antigua Volumen de produccin 100.000 unidades/ao
Costes para ese volumen Mano de obra = 300.000 Materiales = 450.000 Gastos Generales de Fabricacin = 200.000
Amortizacin anual = 15.000 Mquina nueva
Volumen 100.000 unidades/ao Costes para ese volumen Mano de obra = 270.000 Materiales = 400.000 Gastos Generales de Fabricacin = 190.000
Amortizacin anual = 20.000 Incremento 10.000 unidades/ao
Incremento en ingresos = 300.000 Incremento en costes y otros gastos:
Mano de obra = 6.000 Materiales = 36.000 Gastos Generales = 12.000
Impuestos 33%
Proyectos de Inversin
-
8/2/2019 Tema4 Excel
29/55
29
Ejemplo Proyecto de Inversin Proyecto deSustitucin
Proyectos de Inversin
=(C9-B9)*B1
=((B6-C6)+(B7-C7)+(B8-C8))*(1-B1)
=B23-B21-B20-B19
=D26*(1-B1)
=D11+D14+D27
-
8/2/2019 Tema4 Excel
30/55
30
ndices Simples de Anlisis Plazo de Recuperacin de la Inversin
Nmero de aos que se necesitan para recuperar, a travs delcash-flow, la inversin inicial
Frmula: Inversin Neta / Cash-Flow Rentabilidad Anual Simple
Relacin de Ingresos netos con la inversin requerida ndices Rentabilidad de la inversin inicial
Frmula: Cash-Flow anual medio/Inversin Inicial
Rentabilidad Contable de la inversin Media
Frmula: Beneficio Contable Anual medio / Inversin ContableMedia
El beneficio es despus de impuestos La inversin es la inversin bruta menos la amortizacin ao tras
ao y luego haciendo la media por el nmero de aos
Proyectos de Inversin
-
8/2/2019 Tema4 Excel
31/55
31
ndices Basados en el ValorActual
Proyectos de Inversin
Valor Actual Neto ndice de Rentabilidad Financiera Plazo de Recuperacin en Valor Actual Anualidad equivalente del VAN Tasa Interna de Rendimiento
-
8/2/2019 Tema4 Excel
32/55
32
Valor Actual Neto
Proyectos de Inversin
Valor Actual de todos los ingresosmenos el Valor Actual de todos losdesembolsos, supuesto un determinado
tipo de inters Si resulta un VAN negativo el proyectono es aceptable.
El proyecto con mayor VAN es el msconveniente para esa tasa
-
8/2/2019 Tema4 Excel
33/55
33
Valor Actual Neto. Ejemplo (1)
Proyectos de Inversin
Sea un proyecto cuya inversin inicial es de 1.000 que produce unos ingresos durante 5 aos de 300 a una tasa del 10%. Cul es el VAN?. Y para unatasa del 20%. Realice una tabla que permita
comparar los distintos valores del VAN para distintastasas e indique a partir de cual es el proyecto deja deser rentable.
-
8/2/2019 Tema4 Excel
34/55
34
Valor Actual Neto. Ejemplo (1)
Proyectos de Inversin
=VA($B$14;A10;-B6)=A13+B2
P t d I i
-
8/2/2019 Tema4 Excel
35/55
35
Valor Actual Neto. Funcin
Proyectos de Inversin
VNA La tasa de descuento durante un perodo Conjunto de valores que representan los
pagos e ingresos La duracin debe ser la misma Los pagos son al final del perodo
Se pueden introducir separados con el smbolo; o mediante una matriz
Proyectos de Inversin
-
8/2/2019 Tema4 Excel
36/55
36
Valor Actual Neto. Ejemplo (2)
Proyectos de Inversin
El ejemplo anterior puede resolverseutilizando la funcin VNA, pero hay quetener en cuenta que la inversin se
realiz al principio del primer periodo.En este caso la cantidad se suma alresultado de la funcin VNA.
Proyectos de Inversin
-
8/2/2019 Tema4 Excel
37/55
37
Valor Actual Neto. Ejemplo (2)
Proyectos de Inversin
=VNA(B14;$B$6:$B$10)+B2
Proyectos de Inversin
-
8/2/2019 Tema4 Excel
38/55
38
ndice de RentabilidadFinanciera
Proyectos de Inversin
Es la relacin entre el Valor Actual delos Ingresos dividido por la InversinInicial
Proyectos con el mismo VAN puedeocurrir que uno necesite mucha msinversin
Proyectos de Inversin
-
8/2/2019 Tema4 Excel
39/55
39
ndice de RentabilidadFinanciera. Ejemplo
Proyectos de Inversin
Para ver el significado del ndice de rentabilidadfinanciera (que liga el valor actual de los ingresoscon la inversin inicial) se puede realizar una tablaen la cul se calculen el VAN y el ndice para
distintas inversiones iniciales y distintos valoresactuales de los ingresos. Obsrvese que el VAN eneste caso no es ms que la resta entre el valor actualde los ingresos y la inversin inicial y el ndice su
divisin.
Proyectos de Inversin
-
8/2/2019 Tema4 Excel
40/55
40
ndice de RentabilidadFinanciera
Proyectos de Inversin
=A2-B2
=A2/B2
Proyectos de Inversin
-
8/2/2019 Tema4 Excel
41/55
41
Plazo de Recuperacin enValor Actual
y
Paralelo al plazo de recuperacinsimple Nmero de aos necesario para que el
valor actual de los ingresos iguale a lainversin inicial, para una determinadatasa
Proyectos de Inversin
-
8/2/2019 Tema4 Excel
42/55
42
Plazo de Recuperacin enValor Actual. Ejemplo Sea un proyecto de inversin inicial de 1.000.000
que permite que se produzcan unos ingresosanuales durante los 3 primeros aos de 200.000 ,durante los 3 siguientes de 125.000 y el resto de
100.000 . Suponiendo una tasa del 6% calcular elplazo de recuperacin simple y el plazo derecuperacin en valor actual.
Proyectos de Inversin
-
8/2/2019 Tema4 Excel
43/55
43
Plazo de Recuperacin enValor Actual. Ejemplo
=VA(6%; A11; -1)
=C12-C11
=B11*D11
Proyectos de Inversin
-
8/2/2019 Tema4 Excel
44/55
44
Anualidad Equivalente delVAN
Transformacin del VAN en un ingresoequivalente por cada ao en cada unode los aos de vida til del proyecto, es
decir tal que su valor actual seaprecisamente el VAN del mismo
Proyectos de Inversin
-
8/2/2019 Tema4 Excel
45/55
45
Anualidad Equivalente delVAN. Ejemplo
Supongamos que un proyecto tiene unavida til de 5 aos. Si el VAN del mismoes 100.000 a una tasa del 8%, se
desea conocer cul es la anualidadequivalente.
Proyectos de Inversin
-
8/2/2019 Tema4 Excel
46/55
46
Anualidad Equivalente delVAN. Ejemplo
=PAGO(B3;B1;-B2)
Proyectos de Inversin
-
8/2/2019 Tema4 Excel
47/55
47
Tasa Interna de Rendimiento
El ms utilizado Aquella Tasa que hace que el VAN seaigual a 0, es decir, iguala el Valor Actual
del flujo de Ingresos con el Flujo deDesembolsos
Proyectos de Inversin
-
8/2/2019 Tema4 Excel
48/55
48
Tasa Interna de Rendimiento Sea un proyecto con un desembolso inicial
de 10.000 y flujos de ingresos de 5.000 alao durante 5 aos. Se desea calcular elTIR. Si el VAN debe ser 0 entonces el VA de los
ingresos debe ser 10.000
Se debe calcular la tasa para ese valoractual y los pagos realizados
Proyectos de Inversin
-
8/2/2019 Tema4 Excel
49/55
49
Tasa Interna de Rendimiento
=TASA(A8;-B4;B1) =TIR(F1:F6)
Proyectos de Inversin
-
8/2/2019 Tema4 Excel
50/55
50
Tasa Interna de Rendimiento
TIR Conjunto de valores, incluyendo lainversin inicial y los ingresos/gastos
posteriores (al menos debe haber un valorpositivo y otro negativo) El ltimo parmetro es el valor comienzo
para el algoritmo iterativo de Excel (omitido10%)
Proyectos de Inversin
-
8/2/2019 Tema4 Excel
51/55
51
Tasa Interna de RendimientoCorregida
TIRM Conjunto de valores, incluyendo lainversin inicial y los ingresos/gastos
posteriores (al menos debe haber un valorpositivo y otro negativo) Una tasa de financiacin aplicable al
dinero mediante el que se financia Una tasa de reinversin aplicable al dinero
que se ingresa
Proyectos de Inversin
-
8/2/2019 Tema4 Excel
52/55
52
Ejemplo de TIR Supngase un proyecto de una duracin de 7 aos,
que los tres primeros aos requiere de una inversinde 1.000 , 800 , 500 (de sta, el ltimo ao serecuperan 1.500 ) y que comienza a producir
ingresos a partir del tercer ao a un ritmo de 250 ,1.000 , 1.200 , 1.800 , 3.200 . Calcular elmovimiento neto de fondos, el TIR y el TIR corregidosabiendo que para los flujos de caja negativos se
aplicar una tasa del 10% y para los positivos unatasa del 15%.
Proyectos de Inversin
-
8/2/2019 Tema4 Excel
53/55
53
Ejemplo de TIR
=TIRM(B5:H5;C7;C8)
=TIR(B5:H5)
Proyectos de Inversin
-
8/2/2019 Tema4 Excel
54/55
54
Ejemplo de Proyectos deInversin Determinar el TIR y el VAN para una tasa del 40% de del 45% en el siguiente proyecto de
inversin: Adquisicin de terrenos por 500.000 en el ao 2002 Construccin de edificios por 300.000 en el ao 2002 y por 400.000 en el ao 2003 Adquisicin de maquinaria y equipo por 300.000 en el ao 2002 y por 2.000.000 en
el ao 2003 El circulante necesario se estima en 300.000 y se materializar en el 2003
Las ventas previstas son de 3.000.000 en 2002 y del doble los dems aos Los costes variables son el 50% de las ventas Los costes fijos ascienden (excluidas las amortizaciones) a 400.000 en 2002 y
800.000 en cada uno de los aos siguientes Las amortizaciones sern las siguientes:
Maquinaria: se amortizar segn tablas, aplicndose un 15% Edificaciones: se amortizar segn tablas, aplicndose un 3% Se comienza a amortizar a partir del 2 semestre de 2002
La evaluacin del proyecto debe hacerse considerando su duracin hasta 2007inclusive
Proyectos de Inversin
-
8/2/2019 Tema4 Excel
55/55
55
Ejemplo de Proyecto=SUMA(B5:G5)*-1=(SUMA(B6:G6)-I26)*-1
=-C27
=REDONDEAR(SI(C17