Manual sobre Excel Financiero

Manual sobre Excel Financiero

FirmaFGM .tech

EXCEL FINANCIERO

Excel Financiero 1.- INTRODUCCION ................................................................................................................................ 2 1.1.- FUNCIONES DE EXCEL ............................................................................................................................. 2 1.2.- FUNCIONES FINANCIERAS DE EXCEL ........................................................................................................... 3 2.- FUNCIONES PARA CALCULAR Y CONVERTIR TASAS DE INTERÉS ........................................................ 6 2.1.- INT.EFECTIVO ................................................................................................................................... 6 2.2.- TASA.NOMINAL ................................................................................................................................ 7 3.- FUNCIONES PARA SERIES UNIFORMES ............................................................................................ 10 3.1.- INTRODUCCIÓN ................................................................................................................................... 10 3.2.- FUNCIÓN VF ...................................................................................................................................... 11 3.3.- VA ................................................................................................................................................... 12 3.4.- VF.PLAN .......................................................................................................................................... 13 3.5.- PAGO .............................................................................................................................................. 13 3.6.- TABLA DE DATOS DE UNA VARIABLE PARA SIMULAR LA FUNCIÓN PAGO ......................................................... 15 3.7.- TABLA DE DATOS DE DOS VARIABLES PARA SIMULAR LA FUNCIÓN PAGO ........................................................ 15 3.8.- TASA ............................................................................................................................................... 15 3.9.- NPER ............................................................................................................................................... 16 3.10.- INT.PAGO.DIR ............................................................................................................................... 17 3.11.-HERRAMIENTA BUSCAR OBJETIVO .......................................................................................................... 18 4.- FUNCIONES APLICADAS EN PRESTAMOS ......................................................................................... 21 4.1.- PAGOINT ........................................................................................................................................ 21 4.2.- PAGOPRINT .................................................................................................................................... 22 4.3.- PAGO.INT.ENTRE ............................................................................................................................ 22 4.4.- PAGO.PRINC.ENTRE........................................................................................................................ 23 5.- FUNCIONES PARA EVALUAR PROYECTOS DE INVERSION ................................................................ 24 5.1.- VNA ................................................................................................................................................ 25 5.2.- ADMINISTRADOR DE ESCENARIOS EN LA DETERMINACIÓN DEL VAN .............................................................. 27 5.3.- TIR .................................................................................................................................................. 29 5.4.- ADMINISTRADOR DE ESCENARIOS EN LA DETERMINACIÓN DEL TIR ................................................................ 30 5.5.-VNA.NO.PER .................................................................................................................................... 32 5.6.- TIR.NO.PER ..................................................................................................................................... 33 5.7.- TIRM ............................................................................................................................................... 35 6.- FUNCIONES PARA CALCULAR AMORTIZACIONES ............................................................................ 37 6.1.- SLN ................................................................................................................................................. 37 6.2.- SYD ................................................................................................................................................. 37 6.3.- DB ................................................................................................................................................... 38 6.4.- DDB ................................................................................................................................................ 39 6.5.- AMORTIZ.LIN .................................................................................................................................. 40 6.6.- DVS ................................................................................................................................................. 41 6.7.- AMORTIZ.PROGRE .......................................................................................................................... 42

jose

1

Excel Financiero

1.- Introduccion 1.1.- Funciones de excel Una función es una fórmula predefinida por Excel (o por el usuario) que opera con uno o más valores y devuelve un resultado que aparecerá directamente en la celda o será utilizado para calcular la fórmula que la contiene. La sintaxis de cualquier función es: =nombre_función(argumento1;argumento2;...;argumentoN) Siguen las siguientes reglas: - Si la función va al comienzo de una fórmula debe empezar por el signo =. - Si la función es empleadacomo argumento no debeb llevar elsigno igual (=). - Los argumentos o valores de entrada van siempre entre paréntesis. No dejes espacios antes o después de cada paréntesis. - Los argumentos pueden ser valores constantes (número o texto), fórmulas o funciones. - Los argumentos deben de separarse por un punto y coma ;. Ejemplo: =SUMA(A1:C8) Tenemos la función SUMA() que devuelve como resultado la suma de sus argumentos. El operador ":" nos identifica un rango de celdas, así A1:C8 indica todas las celdas incluidas entre la celda A1 y la C8, así la función anterior sería equivalente a: =A1+A2+A3+A4+A5+A6+A7+A8+B1+B2+B3+B4+B5+B6+B7+B8+C1+C2+C3+C4+ C5+C6+C7+C8 Aquí hemos visto la ventaja de utilizar una fórmula. Las fórmulas pueden contener más de una función y pueden aparecer funciones anidadas dentro de una fórmula. Ejemplo =SUMA (A1:B3) / SUMA(C1:C4) Existen muchos tipos de funciones dependiendo del tipo de operación o cálculo que realizan. Así hay funciones matemáticas y trigonométricas, estadísticas, financieras, de texto, de fecha y hora, lógicas, de base de datos, de búsqueda y referencia y de información. Para introducir una fórmula debe escribirse en una celda cualquiera tal cual introducimos cualquier texto, precedida siempre del signo =.

jose

2

Excel Financiero 1.2.- Funciones financieras de Excel FUNCIÓN

DESCRIPCIÓN

AMORTIZ.LIN

Devuelve la amortización de cada uno de los períodos contables

AMORTIZ.PROGRE

Devuelve la amortización de cada período contable mediante el uso de un coeficiente de amortización

CANTIDAD.RECIBIDA

Devuelve la cantidad recibida al vencimiento de un valor bursátil completamente invertido

CUPON.DIAS

Devuelve el número de días del período (entre dos cupones) donde se encuentra la fecha de liquidación

CUPON.DIAS.L1

Devuelve el número de días desde el principio del período de un cupón hasta la fecha de liquidación

CUPON.DIAS.L2

Devuelve el número de días desde la fecha de liquidación hasta la fecha del próximo cupón

CUPON.FECHA.L1

Devuelve la fecha de cupón anterior a la fecha de liquidación

CUPON.FECHA.L2

Devuelve la fecha del próximo cupón después de la fecha de liquidación

CUPON.NUM

Devuelve el número de pagos de cupón entre la fecha de liquidación y la fecha de vencimiento

DB

Devuelve la amortización de un bien durante un período específico a través del método de amortización de saldo fijo

DDB

Devuelve la amortización de un bien durante un período específico a través del método de amortización por doble disminución de saldo u otro método que se especifique

DURACION

Devuelve la duración anual de un valor bursátil con pagos de interés periódico

DURACION.MODIF

Devuelve la duración de Macauley modificada de un valor bursátil con un valor nominal supuesto de 100 $

INT.ACUM

Devuelve el interés acumulado de un valor bursátil con pagos de interés periódicos

INT.ACUM.V

Devuelve el interés acumulado de un valor bursátil con pagos de interés al vencimiento

INT.EFECTIVO

Devuelve la tasa de interés anual efectiva

INT.PAGO.DIR

Calcula el interés pagado durante un período específico de una inversión

LETRA.DE.TES.EQV.A.BONODevuelve el rendimiento de un bono equivalente a una letra del Tesoro (de EE.UU.) LETRA.DE.TES.PRECIO

Devuelve el precio por un valor nominal de 100 $ de una letra del Tesoro (de EE.UU.)

LETRA.DE.TES.RENDTO

Devuelve el rendimiento de una letra del Tesoro (de EE.UU.)

MONEDA.DEC

Convierte una cotización de un valor bursátil expresada en forma fraccionaria en una cotización de un valor bursátil expresada en forma decimal

MONEDA.FRAC

Convierte una cotización de un valor bursátil expresada en forma decimal en una cotización de un valor bursátil expresada en forma fraccionaria

jose

3

Excel Financiero NPER

Devuelve el número de períodos de una inversión

PAGO

Devuelve el pago periódico de una anualidad

PAGO.INT.ENTRE

Devuelve el interés acumulado pagado entre dos períodos

PAGO.PRINC.ENTRE

Devuelve el capital acumulado pagado de un préstamo entre dos períodos

PAGOINT

Devuelve el pago de intereses de una inversión durante un período determinado

PAGOPRIN

Devuelve el pago de capital de una inversión durante un período determinado

PRECIO

Devuelve el precio por un valor nominal de 100 $ de un valor bursátil que paga una tasa de interés periódico

PRECIO.DESCUENTO

Devuelve el precio por un valor nominal de 100 $ de un valor bursátil con descuento

PRECIO.PER.IRREGULAR.1 Devuelve el precio por un valor nominal de 100 $ de un valor bursátil con un primer período impar PRECIO.PER.IRREGULAR.2 Devuelve el precio por un valor nominal de 100 $ de un valor bursátil con un último período impar PRECIO.VENCIMIENTO

Devuelve el precio por un valor nominal de 100 $ de un valor bursátil que paga interés a su vencimiento

RENDTO

Devuelve el rendimiento de un valor bursátil que paga intereses periódicos

RENDTO.DESC

Devuelve el rendimiento anual de un valor bursátil con descuento; por ejemplo, una letra del Tesoro (de EE.UU.)

RENDTO.PER.IRREGULAR.1 Devuelve el rendimiento de un valor bursátil con un primer período impar RENDTO.PER.IRREGULAR.2 Devuelve el rendimiento de un valor bursátil con un último período impar RENDTO.VENCTO

Devuelve el rendimiento anual de un valor bursátil que paga intereses al vencimiento

SLN

Devuelve la amortización por método directo de un bien en un período dado

SYD

Devuelve la amortización por suma de dígitos de los años de un bien durante un período especificado

TASA

Devuelve la tasa de interés por período de una anualidad

TASA.DESC

Devuelve la tasa de descuento de un valor bursátil

TASA.INT

Devuelve la tasa de interés para la inversión total de un valor bursátil

TASA.NOMINAL

Devuelve la tasa nominal de interés anual

TIR

Devuelve la tasa interna de retorno para una serie de flujos de efectivo periódicos

TIR.NO.PER

Devuelve la tasa interna de retorno para un flujo de efectivo que no es necesariamente periódico

TIRM

Devuelve la tasa interna de retorno donde se financian flujos de efectivo positivos y negativos a tasas diferentes

VA

Devuelve el valor actual de una inversión

jose

4

Excel Financiero VDS

Devuelve la amortización de un bien durante un período específico o parcial a través del método de cálculo del saldo en disminución

VF

Devuelve el valor futuro de una inversión

VF.PLAN

Devuelve el valor futuro de un capital inicial después de aplicar una serie de tasas de interés compuesto

VNA

Devuelve el valor neto actual de una inversión en función de una serie de flujos periódicos de efectivo y una tasa de descuento

VNA.NO.PER

Devuelve el valor neto actual para un flujo de efectivo que no es necesariamente periódico

jose

5

Excel Financiero

2.- Funciones para calcular y convertir tasas de interes Dentro de este grupo se clasifican dos funciones que sirven para convertir tasas de interés efectivas en nominales y viceversa. Por ser funciones de una relativa facilidad, no se presentan ejemplos utilizando la hoja de cálculo. Los argumentos que utilizan las funciones financieras para conversión de tasas son los siguientes: Núm_per y Núm_per_año Es la cantidad de períodos de capitalización de interés que hay en un año. Por período de capitalización se entiende el tiempo que transcurre entre dos fechas de pago de interés, pero en el caso de estas funciones se supone que el interés pagado no se retira ni se consume, si no que es reinvertido por el tiempo que resta del año. Int_nominal Es la tasa de interés nominal anual expresada en términos decimales. Se debe tener en cuenta que es la tasa anual y no la del período de capitalización. Tasa_efectiva Es la tasa de interés efectiva anual, es decir la rentabilidad que efectivamente se recibiría si los intereses que se perciben por la inversión son reinvertidos en las mismas condiciones por el tiempo que resta del año. 2.1.- INT.EFECTIVO

Devuelve la tasa de interés efectiva anual, si se conocen la tasa de interés nominal anual y el número de períodos de capitalización de interés compuesto que hay en un año. INT.EFECTIVO(int_nominal, núm_per_año) Argumentos Int_nominal: la tasa de interés nominal. Num_per_año: elnúmero de períodos de interes compuesto por año. Hay que tener especial cuidado con esta función, ya que solo produce resultados confiables cuando la cantidad de períodos de pago en el año (núm_per_año) tiene valores exactos, por ejemplo mensual (12), trimestral (4), semestral (2) o anual (1). Cuando los períodos son irregulares este argumento se trunca a entero y el resultado no es real, tal como se aprecia a continuación:

jose

6

Excel Financiero INTERES EFECTIVO FECHA INICIO 15/04/1998 FECHA FINAL 14/07/1998 DURACION 90 VALORINICIAL 1.000,000 € VALOR FINAL 1.066,667 € PERIODOS EN EL AÑO

4

RENDIMIENTO NOMINAL

26,67%

RENDIMIENTO EFECTIVO

29,46%

RENDIMIENTO EFECTIVO

29,46%

valor exacto

(1+B10/B8)^B8-1

Interes_efectivo.xlsx

INTERES EFECTIVO FECHA INICIO 15/04/1998 FECHA FINAL 14/07/1998 DURACION 90 VALORINICIAL 1.000,000 € VALOR FINAL 1.066,667 € PERIODOS EN EL AÑO

4,8

RENDIMIENTO NOMINAL

26,67%

RENDIMIENTO EFECTIVO

29,64%

RENDIMIENTO EFECTIVO

29,46%

valor NO exacto

(1+B10/B8)^B8-1

Interes_efectivo1.xlsx

2.2.- TASA.NOMINAL

Devuelve la tasa de interés nominal anual, si se conocen la tasa de interés efectiva jose

7

Excel Financiero anual y el número de períodos de capitalización de interés que hay en un año. TASA.NOMINAL(tasa_efectiva, núm_per) Argumentos: Tasa_efectiva: es la tasa de interés efectiva. Num_per: es el número de períodos de interés compuesto por año. El argumento núm_per_año se trunca a entero. Si alguno de los argumentos no es numérico, TASA.NOMINAL devuelve el valor de error #¡VALOR! Si alguno de los argumentos int_nominal o tasa_efectiva es menor o igual a cero o si el argumento núm_per o núm_per_año es menor que uno, la función devuelve el valor de error #¡NUM! En ambos casos la respuesta que se obtenga se expresa en términos decimales y debe dársele el formato de porcentaje. Nunca divida ni multiplique por cien el resultado de estas funciones. La relación de TASA.NOMINAL e INT.EFECTIVO es la siguiente: INT.EFECTIVO = (𝟏 +

𝒕𝒂𝒔𝒂_𝒏𝒐𝒎𝒊𝒏𝒂𝒍 𝒏𝒖𝒎_𝒑𝒆𝒓

Tasa de interes efectiva Periodo de interes compuesto por año

5,35% 4

tasa nominal

5,25%

interes efectivo conociendo la tasa nominal

5,35%

𝒏𝒖𝒎_𝒑𝒆𝒓

)

-1

Tasa_nominal.xlsx

Estas funciones se limitan a calcular el interés efectivo o nominal en el caso del pago de intereses vencidos. Si el problema se refiere a intereses anticipados debe encontrar la respuesta mediante la fórmula. Para su información se incluyen a continuación las fórmulas para calcular el interés efectivo y nominal anual en las jose

8

Excel Financiero modalidades de pago vencidas y anticipadas: Fórmula para el interés efectivo en modalidad vencida:

EA = (𝟏 +

𝑵𝑨 𝒏 𝒏

) –1

Fórmula para el interes nominal en modalidad vencida: NA = ((𝐄𝐀 + 𝟏)𝟏/𝒏 − 𝟏) ∗ 𝒏 Fórmula para el interés efectivo en modalidad anticipada:

EA=

𝟏

𝑵𝑨 𝒏

(𝟏− 𝒏 )

−𝟏

Fórmula para el interés nominal en modalidad anticipada:

NA= (𝟏 −

𝟏 ) (𝑬𝑨+𝟏)𝒏

∗𝒏

Donde: NA es el interés nominal anual EA es el interés efectivo anual n es la cantidad de períodos de capitalización de interés que hay en un año

jose

9

Excel Financiero

3.- Funciones para series uniformes 3.1.- Introducción

Las funciones de series uniformes sirven para resolver problemas en los cuales entre el valor inicial y el valor final de un negocio existen pagos de cuotas o valores recibidos. En todas las funciones de las series uniformes se supone que los valores recibidos o pagados durante el tiempo del negocio son reinvertidos por lo que resta del plazo total, en las mismas condiciones existentes para la inversión original. Un problema se considera de series uniformes cuando reúne las siguientes condiciones en su totalidad: a) El monto de los pagos efectuados dentro del tiempo de la inversión es constante b) La periodicidad de los pagos efectuados dentro del tiempo de la inversión es constante. c) La tasa de interés a la que se liquidan los pagos efectuados dentro del tiempo de la inversión es constante. En las soluciones de los problemas de series uniformes siempre hay equivalencia entre los pagos periódicos iguales y el valor presente o futuro que se halle. Los argumentos que utilizan las funciones financieras de series uniformes son los siguientes: Va: Es el valor actual de una serie de pagos futuros iguales. Si este argumento se omite, se considerará 0. Pago: Es el pago que se efectúa en cada período y que no cambia durante la vida de la anualidad. El Pago incluye el capital y el interés pero no incluye ningún otro cargo o impuesto. Este argumento debe tener signo contrario al de Va, para conservar las condiciones del flujo de caja: los ingresos se presentan con signo positivo y los egresos con signo negativo. Nper: n en los términos financieros. Es la cantidad total de períodos en una anualidad, es decir el plazo total del negocio. Tasa: Es la tasa de interés por período. Tener en cuenta que no es la tasa anual, si no la tasa nominal del período de pago expresada en términos decimales. Es importante mantener la uniformidad en el uso de las unidades con las que se especifica Tasa y Nper. Vf: Es el valor futuro o el saldo en efectivo que desea lograr después de efectuar el último pago. Si el argumento Vf se omite, se asume que el valor es 0 Período: Especifica el número ordinal de la cuota que se está estudiando, que debe encontrarse en el intervalo comprendido entre 1 y Nper. Per_inicial y Per_final: Especifican el número ordinal de la primera y la última cuota de jose

10

Excel Financiero un lapso de tiempo al cual se le van a analizar las cuotas pagadas. Estimar: Es una tasa de interés estimada para que el Excel empiece las iteraciones en el cálculo de la tasa de interés de una serie uniforme. Si el argumento Estimar se omite, se supone que es 10%.

3.2.- Función VF

Devuelve el valor futuro de una inversión, equivalente a los pagos constantes que se hacen periódicamente y a una tasa de interés constante. VF(tasa, nper, pago, va, tipo) Por ejemplo, si uno deposita $ 250.000 mensuales durante 2 años en una cuentaque paga el 36% nominal anual y desea saber cuanto dinero tendrá ahorrado al final, la respuesta la puede obtener con la función VF, como se aprecia en el siguiente ejemplo: TASA NPER PAGO TIPO

36% 24 250.000,00 € 0 8.606.617,55 € "=VF(B1/12;B2;-B3;;B4)"

a) El interés que se incluye en el argumento Tasa debe estar en la misma unidad de tiempo que se use para el argumento Nper, en este caso, como son cuotas mensuales, la tasa de interés debe ser mensual, por lo tanto hay que dividir por doce la tasa anual nominal (B1/12). b) Va puede omitirse como se aprecia en el asistente para funciones y en la barra de fórmulas automáticamente deja el espacio en la función, asumiéndolo como cero. c)

jose

Si se desea que las cifras en la hoja de cálculo sean positivas, el argumento Pago debe introducirse con signo negativo, como se aprecia en el asistente para funciones (-B3).

11

Excel Financiero 3.3.- VA

Devuelve el valor actual de una inversión que recibirá abonos periódicos constantes en el futuro, a una tasa de interés constante. Cuando pide dinero prestado, el monto del préstamo es el valor actual para el prestamista, que es equivalente a las cuotas iguales que recibirá en el futuro. Esta función conserva las misma observaciones efectuadas para VF. VA(tasa, nper, pago, vf, tipo)

Tasa Obligatorio. La tasa de interés por período. Por ejemplo, si obtiene un préstamo para un automóvil con una tasa de interés anual del 10 por ciento y efectúa pagos mensuales, la tasa de interés mensual será del 10%/12 o 0,83%. En la fórmula escribiría 10%/12, 0,83% o 0,0083 como tasa. Núm_per Obligatorio. El número total de períodos de pago en una anualidad. Por ejemplo, si obtiene un préstamo a cuatro años para comprar un automóvil y efectúa pagos mensuales, el préstamo tendrá 4*12 (o 48) períodos. La fórmula tendrá 48 como argumento núm_per. Pago Obligatorio. El pago efectuado en cada período, que no puede variar durante la anualidad. Generalmente el argumento pago incluye el capital y el interés, pero no incluye ningún otro arancel o impuesto. Por ejemplo, los pagos mensuales sobre un préstamo de 10.000 € a cuatro años con una tasa de interés del 12 por ciento para la compra de un automóvil, son de 263,33 €. En la fórmula escribiría -263,33 como argumento pago. Si se omite el argumento pago, deberá incluirse el argumento vf. Vf Opcional. El valor futuro o un saldo en efectivo que se desea lograr después de efectuar el último pago. Si el argumento vf se omite, se supone que el valor es 0 (por ejemplo, el valor futuro de un préstamo es 0). Si desea ahorrar 50.000 € para pagar un proyecto especial en 18 años, 50.000 € sería el valor futuro. De esta forma, es posible hacer una estimación conservadora a cierta tasa de interés y determinar la cantidad que deberá ahorrar cada mes. Si se omite el argumento vf, deberá incluirse el argumento pago. Tipo Opcional. El número 0 ó 1 e indica cuándo vencen los pagos. 0 u omitido: al final del periodo. 1: al inicio del periodo. Observaciones Mantenga uniformidad en el uso de las unidades con las que especifica los argumentos tasa y núm_per. Si realiza pagos mensuales de un préstamo de cuatro años con un interés anual del 12 por ciento, use 12%/12 para tasa y 4*12 para núm_per. Si realiza pagos anuales del mismo préstamo, use 12% para tasa y 4 para núm_per.

jose

12

Excel Financiero 500 Dinero pagado 8% Interes ganado por el dinero pagado 20 Nº años durante los cuales se efectuaran los pagos -59.777,15 € "=VA(A3/12;12*A4;A2;;0)"

El resultado es negativo porque representa el dinero que se pagaría, un flujo de caja saliente. Si le piden (60.000) para la anualidad, determinará que ésta no es una buena inversión, puesto que el valor actual de la anualidad (59.777,15) es inferior a lo que tendría que pagar. 3.4.- VF.PLAN

Devuelve el valor futuro de un capital inicial después de aplicar una serie de tasas de interés compuesto. Utilice VF.PLAN para calcular el valor futuro de una inversión con una tasa variable o ajustable. VF.PLAN(capital;plan_serie_de_tasas) Capital es el valor actual. Plan_serie_de_tasas es una matriz con las tasas de interés que se van a aplicar. Observación Los valores del argumento plan_serie_de_tasas pueden ser números o celdas en blanco; cualquier otro valor generará el valor de error #¡VALOR! para VF.PLAN. Las celdas en blanco se interpretan como ceros (sin interés).

1000 9% 11% 10%

capital inicial interes primer año interes segundo año interes tercer año

1330,89 "=VF.PLAN(A2;{0,09;0,11;0,1})" 1090 1209,9 1330,89

3.5.- PAGO

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

13

Excel Financiero interés constante. PAGO(tasa;nper;va;vf;tipo) Tasa Obligatorio. Es el tipo de interés del préstamo. Nper Obligatorio. Es el número total de pagos del préstamo. Va Obligatorio. Es el valor actual, o la cantidad total de una serie de futuros pagos. También se conoce como valor bursátil. Vf Opcional. Es el valor futuro o un saldo en efectivo que se desea lograr después de efectuar el último pago. Si el argumento vf se omite, se supone que el valor es 0 (es decir, el valor futuro de un préstamo es 0). Tipo Opcional. Es el número 0 (cero) o 1 e indica cuándo vencen los pagos. 0 u omitido: al final del periodo. 1: al inicio del periodo. Observaciones  El pago devuelto por PAGO incluye el capital y el interés, pero no incluye impuestos, pagos en reserva ni los gastos que algunas veces se asocian con los préstamos.  Mantenga uniformidad en el uso de las unidades con las que especifica los argumentos tasa y nper. Si realiza pagos mensuales de un préstamo de cuatro años con una tasa de interés anual del 12 por ciento, use 12%/12 para el argumento tasa y 4*12 para el argumento nper. Si efectúa pagos anuales del mismo préstamo, use 12 por ciento para el argumento tasa y 4 para el argumento nper.

8% Tasa de interes anual 10 Numero de meses de pagos 10000 Importe de prestamos

-1.037,03 € Pago mensual de un prestamo -1.030,16 € Pago mensual de un prestamo, los pagos vencen al principio del periodo

jose

14

Excel Financiero 6% tasa interes anual 18 años previstos de ahorro 50000 cantidad que desea ahorrar en 18 años -129,08 € cantidad que tendrá que ahorrar cada mes para disponer de 50000 euros despues de 18 años

3.6.- Tabla de datos de una variable para simular la función PAGO importe prestamo interes años mensualidades Pago mensual 5,00% 5,50% 6,00% 6,50% 7,00%

120000 4,50% 15 12 -917,99 € -948,9523521 -980,5001455 -1012,628194 -1045,328838 -1078,593925

3.7.- Tabla de datos de dos variables para simular la función PAGO importe prestamo interes años mensualidades Pago mensual

120000 4,50% 15 12 917,99 €

917,99 € 5,00% 5,50% 6,00% 6,50% 7,00%

10 1.272,79 1.302,32 1.332,25 1.362,58 1.393,30

20 791,95 825,46 859,72 894,69 930,36

30 644,19 681,35 719,46 758,48 798,36

3.8.- TASA

Devuelve la tasa de interés por período de una anualidad. TASA se calcula por iteración jose

15

Excel Financiero y puede tener cero o más soluciones. Si los resultados sucesivos de TASA no convergen dentro de 0,0000001 después de 20 iteraciones, TASA devuelve el valor de error #¡NUM! TASA(núm_per; pago; va; [vf]; [tipo]; [estimar])

Núm_per Obligatorio. El número total de períodos de pago en una anualidad. Pago Obligatorio. El pago efectuado en cada período, que no puede variar durante la vida de la anualidad. Generalmente el argumento pago incluye el capital y el interés, pero no incluye ningún otro arancel o impuesto. Si se omite el argumento pago, deberá incluirse el argumento vf. Va Obligatorio. El valor actual, es decir, el valor total que tiene actualmente una serie de pagos futuros. Vf Obligatorio. El valor futuro o un saldo en efectivo que se desea lograr después de efectuar el último pago. Si el argumento vf se omite, se supone que el valor es 0 (por ejemplo, el valor futuro de un préstamo es 0). Tipo Opcional. El número 0 ó 1 e indica cuándo vencen los pagos. 0 u omitido: al final del periodo. 1: al inicio del periodo. Estimar Opcional. La estimación de la tasa de interés. Si el argumento estimar se omite, se supone que es 10 por ciento. Si TASA no converge, trate de usar diferentes valores para el argumento estimar. TASA generalmente converge si el argumento estimar se encuentra entre 0 y 1.

4 Años del prestamo -200 Pago mensual 8000 Importe del prestamo 0,77% Tasa mensual 9,24% Tasa anual

3.9.- NPER

Devuelve el número de períodos de una inversión basándose en los pagos periódicos constantes y en la tasa de interés constante. NPER(tasa; pago; va; vf; tipo) jose

16

Excel Financiero

Para obtener una descripción más completa de los argumentos de NPER y más información acerca de las funciones de anualidades, vea VA. Tasa es la tasa de interés por período. Pago es el pago efectuado en cada período; debe permanecer constante durante la vida de la anualidad. Por lo general, pago incluye el capital y el interés, pero no incluye ningún otro arancel o impuesto. Va es el valor actual o la suma total de una serie de futuros pagos. Vf es el valor futuro o un saldo en efectivo que se desea lograr después de efectuar el último pago. Si el argumento vf se omite, se supone que el valor es 0 (por ejemplo, el valor futuro de un préstamo es 0). Tipo es el número 0 ó 1 e indica cuándo vencen los pagos. 0 u omitido: al final del periodo. 1: al inicio del periodo. 12% -100 -1000 10000 1

Tasa de interes anual Pago efectuadoen cada periodo Valor actual Valor futuro El pagovence al principio del periodo

59,6738657 pagos al final del periodo 60,0821229 pago al principio del periodo -9,57859404 valor futuro de 0

3.10.- INT.PAGO.DIR

Calcula el interés pagado durante un período específico de una inversión. Esta función se incluye para proporcionar compatibilidad con Lotus 1-2-3. INT.PAGO.DIR (tasa;período;núm_per;va)

jose

17

Excel Financiero Tasa es la tasa de interés de la inversión. Período es el período cuyo interés desea averiguar y que debe estar comprendido entre 1 y el número total de períodos. Núm_per es el número total de períodos de pago de la inversión. Va es el valor actual de la inversión. Para un préstamo, va es la cantidad del préstamo. Observaciones Mantenga uniformidad en el uso de las unidades con las que especifica los argumentos tasa y nper. Si realiza pagos mensuales de un préstamo de cuatro años con una tasa de interés anual del 12 por ciento, use 12%/12 para el argumento tasa y 4*12 para el argumento nper. Si realiza pagos anuales del mismo préstamo, use 12% para tasa y 4 para nper. Para todos los argumentos, el dinero que desembolse, como depósitos en una cuenta de ahorros u otros reintegrados, se representa con números negativos, mientras que el dinero que recibe, como cheques de dividendos y otros depósitos, se representa con números positivos. Para obtener más información acerca de las funciones financieras, vea la función VA.

10% 1 3 800.000

tasa de interes anual período numero de años de inversión importe del prestamo

-6.481,48 INTERES ABONADO PARA EL PRIMER PAGO MENSUAL DE UN PRÉSTAMOS -53.333,33 interes pagado en el primer año del prestamo

3.11.-Herramienta buscar objetivo Excel nos puede ayudar a calcular el valor de una celda partiendo de los datos de otras. La herramienta Buscar objetivo resuelve ecuaciones del tipo 5+X=7 donde el valor X lo calcula Excel. Vamos a ver un ejemplo muy sencillo para entender la herramienta. En la celda A1 tenemos el valor 45, la celda A2 será la variable que buscará Excel, y en la celda A3 tendremos la fórmula

jose

18

Excel Financiero =A1*A2. El valor de A3 quiero que sea 789, por lo que tendré la ecuación 45*X=789.

En la pestaña Datos > Análisis y si > Buscar Objetivo aparece el cuadro de dialogo

En la casilla definir celda indicaremos la referencia de la celda que vamos a modificar, en este ejemplo sería A3. En la casilla Con el valor indicaremos el resultado que debe aparecer en la celda indicada en el paso anterior. En nuestro caso 789. Para terminar en la casilla para cambiar la celda, le indicaremos la celda que queremos obtener el valor es decir la incógnita de nuestra ecuación que seria A3. Quedaría así:

Haremos clic en el botón Aceptar y se resolverá de la siguiente forma

jose

19

Excel Financiero

Excel mostrará en la celda indicada (A2) el resultado obtenido. Si queremos que el valor se mantenga en la celda pulsaremos Aceptar y si deseamos que todo vuelva a su estado inicial pulsaremos cancelar.

jose

20

Excel Financiero

4.- Funciones aplicadas en prestamos 4.1.- PAGOINT Devuelve el interés pagado en un período específico por una inversión basándose en pagos periódicos constantes y en una tasa de interés constante. PAGOINT(tasa; período; núm_per; va; [vf]; [tipo]) Tasa Obligatorio. La tasa de interés por período. Período Obligatorio. El período para el que se desea calcular el interés; debe estar entre 1 y el argumento núm_per. Núm_per Obligatorio. El número total de períodos de pago en una anualidad. Va Obligatorio. El valor actual o la suma total de una serie de futuros pagos. Vf Opcional. El valor futuro o un saldo en efectivo que se desea lograr después de efectuar el último pago. Si el argumento vf se omite, se supone que el valor es 0 (por ejemplo, el valor futuro de un préstamo es 0). Tipo Opcional. El número 0 ó 1; indica cuándo vencen los pagos. Si tipo se omite, se considera que es 0, al final del periodo, si se indica 1 será al inicio del periodo. Mantenga uniformidad en el uso de las unidades con las que especifica los argumentos tasa y núm_per. Si realiza pagos mensuales de un préstamo de cuatro años con un interés anual del 12 por ciento, use 12%/12 para tasa y 4*12 para núm_per. Si realiza pagos anuales del mismo préstamo, use 12% para tasa y 4 para núm_per. En todos los argumentos el efectivo que paga, por ejemplo depósitos en cuentas de ahorros, se representa con números negativos; el efectivo que recibe, por ejemplo cheques de dividendos, se representa con números positivos.

10% 1 3 8000

interes anual período para elcual desea calcular interes años del prestamo valor actual del prestamo

-66,67 € interes que se pagará el primer mes para un prestamo -292,45 € interes que se pagará el ultimo año para un prestamo cuyos pagos se efectual de forma anual

jose

21

Excel Financiero 4.2.- PAGOPRINT Devuelve el pago sobre el capital de una inversión durante un período determinado basándose en pagos periódicos y constantes, y en una tasa de interés constante. PAGOPRIN(tasa; período; núm_per; va; [vf]; [tipo]) Tasa Obligatorio. La tasa de interés por período. Período Obligatorio. El período, que debe estar entre 1 y el valor de núm_per. Núm_per Obligatorio. El número total de períodos de pago en una anualidad. Va Obligatorio. El valor actual, es decir, el valor total que tiene actualmente una serie de pagos futuros. Vf Obligatorio. El valor futuro o un saldo en efectivo que se desea lograr después de efectuar el último pago. Si el argumento vf se omite, se supone que el valor es 0 (es decir, el valor futuro de un préstamo es 0). Tipo Opcional. El número 0 ó 1 e indica cuándo vencen los pagos. Mantenga uniformidad en el uso de las unidades con las que especifica los argumentos tasa y núm_per. Si realiza pagos mensuales de un préstamo de cuatro años con un interés anual del 12 por ciento, use 12%/12 para tasa y 4*12 para núm_per. Si realiza pagos anuales del mismo préstamo, use 12% para tasa y 4 para núm_per. 10% tasa de interes anual 2 numero de años del prestamo 2000 importe del prestamo -75,62 € pago del principal para el primer mes del prestamo

8% Tasa de interes anual 10 numero de años del prestamo 200000 importe del prestamo -27.598,05 € pago del principal para el ultimo año del prestamo

4.3.- PAGO.INT.ENTRE

jose

22

Excel Financiero Devuelve la cantidad de interés pagado de un préstamo entre los argumentos per_inicial y per_final. PAGO.INT.ENTRE(tasa; núm_per; vp; per_inicial; per_final; tipo) Tasa Obligatorio. La tasa de interés. Núm_per Obligatorio. El número total de períodos de pago. Vp Obligatorio. El valor actual. Per_inicial Obligatorio. El primer período del cálculo. Los períodos de pago se numeran comenzando por 1. Per_final Obligatorio. El último período del cálculo. Tipo Obligatorio. El momento del pago.

Mantenga uniformidad en el uso de las unidades con las que especifica los argumentos tasa y núm_per. Si realiza pagos mensuales de un préstamo de cuatro años con una tasa de interés anual del 10 por ciento, use 10%/12 para el argumento tasa y 4*12 para el argumento núm_per. Si realiza pagos anuales del mismo préstamo, use 10% para tasa y 4 para núm_per. Si el argumento tasa ≤ 0, el argumento núm_per ≤ 0 o el argumento Vp ≤ 0, PAGO.INT.ENTRE devuelve el valor de error #¡NUM! Si el argumento per_inicial < 1, per_final < 1 o per_inicial > per_final, PAGO.INT.ENTRE devuelve el valor de error #¡NUM! Si tipo es un número distinto de 0 ó 1, PAGO.INT.ENTRE devuelve el valor de error #¡NUM!

9% Tasa de interes anual 30 Años de préstamo 125.000,00 Valor actual -11.135,23 Interes total pagado en el segundo año -937,50 Interes pagado el primer mes -7,49 Interes pagado el ultimo mes

4.4.- PAGO.PRINC.ENTRE Devuelve la cantidad acumulada de capital pagado de un préstamo entre los períodos

jose

23

Excel Financiero (per_inicial y per_final). Si esta función no está disponible y devuelve el error #¿NOMBRE?, instale y cargue el programa de complementos Herramientas para análisis. PAGO.PRINC.ENTRE(tasa;nper;vp;per_inicial;per_final;tipo) Tasa es la tasa de interés. Nper es el número total de períodos de pago. Vp es el valor actual. Per_inicial es el primer período en el cálculo. Los períodos de pago se numeran empezando por 1. Per_final es el último período en el cálculo. Tipo es el momento del pago de intereses (al comienzo o al final del período).

Mantenga uniformidad en el uso de las unidades con las que especifica los argumentos tasa y nper. Si realiza pagos mensuales de un préstamo de cuatro años con una tasa de interés anual del 12 por ciento, use 12%/12 para el argumento tasa y 4*12 para el argumento nper. Si realiza pagos anuales del mismo préstamo, use 12% para tasa y 4 para nper. Los argumentos nper, per_inicial, per_final y tipo se truncan a enteros. Si el argumento tasa ≤ 0, nper ≤ 0 o el argumento vp ≤ 0, PAGO.PRINC.ENTRE devuelve el valor de error #¡NUM! Si el argumento per_inicial < 1, per_final < 1 o per_inicial > per_final, PAGO.PRINC.ENTRE devuelve el valor de error #¡NUM! Si tipo es un número distinto de 0 ó 1, PAGO.PRINC.ENTRE devuelve el valor de error #¡NUM!

9% Tasa de interes por año 30 Períodos en años 125.000,00 Valor actual -934,11 Pago total de capital en el segundo año -68,28 Capital pagado el primer mes -998,29 Capital pagado el último mes

5.- FUNCIONES PARA EVALUAR PROYECTOS DE INVERSION La evaluación financiera de proyectos consiste en la aplicación de algunos indicadores de

jose

24

Excel Financiero conveniencia económica al flujo de caja estimado de un negocio. En esta parte se presentarán las funciones financieras del Excel que pueden utilizarse como indicadores de conveniencia económica. Es bien sabido que en un proyecto real el flujo de efectivo resultante no obedece a ninguna de las series conocidas (anualidades, gradientes, etc.), puesto que depende de gran cantidad de variables, por lo tanto no existe una fórmula para calcular el valor presente neto o la tasa de retorno, es necesario trabajar cada componente del flujo como un elemento independiente. Es aquí donde el Excel presenta un gran aporte para la evaluación financiera de proyectos. Los argumentos que utilizan las funciones de evaluación de proyectos son los siguientes:

Tasa Es la tasa de descuento que se utiliza para calcular el valor presente. Debe expresarse en el mismo período que se utiliza para la serie de datos. Valor1, valor2, Son los rangos que contienen los valores (ingresos y egresos) a los cuales se les va a calcular el valor presente; la función acepta hasta 29 rangos. Valores Es un rango que contiene los valores (flujo de caja) a los cuales se desea calcular la tasa interna de retorno. El argumento valores debe contener al menos un valor positivo y uno negativo para calcular la tasa interna de retorno. Estos flujos de caja no tienen por que ser constantes, como es el caso en una anualidad, sin embargo, los flujos de caja deben ocurrir en intervalos regulares. Tasa_financiamiento Es la tasa de interés que se paga sobre el dinero utilizado en los flujos de caja. Tasa_reinversión Es la tasa de interés a la que se reinvierten los flujos de caja del proyecto a medida que ingresan. Fechas Es un calendario de fechas de pago del flujo de caja. La primera fecha indica el principio del calendario, el resto de las fechas deben ser posteriores, pero pueden ocurrir a intervalos irregulares

5.1.- VNA Calcula el valor neto presente de una inversión a partir de una tasa de descuento y una serie de pagos futuros (valores negativos) e ingresos (valores positivos). VNA(tasa;valor1;valor2; ...) Tasa Tasa de descuento a lo largo de un período. Valor1, valor2, ... son de 1 a 254 argumentos que representan los pagos e ingresos. Valor1; valor2; ... deben tener la misma duración y ocurrir al final de cada período. VNA usa el orden de valor1; valor2; ... para interpretar el orden de los flujos de caja. Asegúrese de escribir los valores de los pagos y de los ingresos en el orden adecuado. Los argumentos que consisten en números, celdas vacías, valores lógicos o representaciones textuales de números se cuentan; los argumentos que consisten en valores de error o texto que no se puede traducir a números se pasan por alto. Si el argumento es una matriz o una referencia, sólo se considerarán los números contenidos en la matriz o en la referencia. Se omitirán las celdas vacías, los valores lógicos o el texto

jose

25

Excel Financiero contenido en la matriz o en la referencia.

La inversión VNA comienza un período antes de la fecha del flujo de caja de valor1 y termina con el último flujo de caja de la lista. El cálculo VNA se basa en flujos de caja futuros. Si el primer flujo de caja se produce al principio del primer período, el primer valor se debe agregar al resultado VNA, que no se incluye en los argumentos valores. Para obtener más información, vea los siguientes ejemplos. Si n es el número de flujos de caja de la lista de valores, la fórmula de VNA es:

VNA es similar a la función VA (valor actual). La principal diferencia entre VA y VNA es que VA permite que los flujos de caja comiencen al final o al principio del período. A diferencia de los valores variables de flujos de caja en VNA, los flujos de caja en VA deben permanecer constantes durante la inversión. Para obtener más información acerca de anualidades y funciones financieras, vea VA. VNA también está relacionado con la función TIR (tasa interna de retorno). TIR es la tasa para la cual VNA es igual a cero: VNA(TIR(...); ...) = 0.

10% -10000 3000 4200 6800

Tasa anualde descuento Costo inicial de la inversion un año despues de la fecha actual Rendimiento del primer año Rendimiento del segundo año Rendimiento del tercer año

1.188,44 € Valor neto actual de esta inversion

En el ejemplo anterior se incluye el costo inicial de 10.000 € como uno de los valores porque el pago se produce al final del primer período.

jose

26

Excel Financiero 8% -40.000,00 8.000,00 9.200,00 10.000,00 12.000,00 14.500,00

Tasa anual de descuento. Puede ser la tasa de inflacion o la tasa de interes de una inversio Costo inicial de la inversion Rendimiento de primer año Rendimiento del segundo año Rendimiento del terceraños Rendimiento del cuarto año Rendimiento del quinto año

1.922,06 Valor neto actual de esta inversion -3.749,47 Valor neto actual de esta inversion, con una perdida en el sexto año de 9000 euros

En el ejemplo anterior no se incluye el costo inicial de 40.000 € como uno de los valores porque el pago se produce al principio del primer período.

5.2.- Administrador de Escenarios en la determinación del VAN La función de los escenarios es guardar con un nombre un conjunto de valores asignados a unas determinadas celdas de entrada, para después sustituir automáticamente los valores originales y observar cómo éstos afectan al resto de los datos de la hoja de cálculo. Los escenarios son útiles cuando se trabaja con variables que no sabemos exactamente que valores van a tomar. Por ejemplo si se desea crear un presupuesto pero no se sabe exactamente los valores de las distintas partidas, en este caso se podrán definir distintos valores (escenarios) para esas partidas y cambiar entre escenarios. En la pestaña Datos > Análisis Y si > Administrador de escenarios

jose

27

Excel Financiero Inicialmente vemos que no hay ningún escenario definido, hago clic en el botón Agregar y aparece el siguiente cuadro de diálogo:

Introduzco un nombre de escenario en Nombre de escenario. Indicaré las celdas que se van a modificar en Celdas cambiantes. En el cuadro de dialogo Valores de escenario escribiremos los valores para las celdas cambiantes.

Si los valores a cambiar fueran mas de uno la ventana de dialogo tendría este formato

Una vez introducidos los valores hago clic en Aceptar.

jose

28

Excel Financiero 5.3.- TIR Devuelve la tasa interna de retorno de los flujos de caja representados por los números del argumento valores. Estos flujos de caja no tienen por que ser constantes, como es el caso en una anualidad. Sin embargo, los flujos de caja deben ocurrir en intervalos regulares, como meses o años. La tasa interna de retorno equivale a la tasa de interés producida por un proyecto de inversión con pagos (valores negativos) e ingresos (valores positivos) que se producen en períodos regulares. TIR(valores; [estimar]) 



Valores Obligatorio. Una matriz o una referencia a celdas que contienen los números para los cuales desea calcular la tasa interna de retorno. o El argumento valores debe contener al menos un valor positivo y uno negativo para calcular la tasa interna de retorno. o TIR interpreta el orden de los flujos de caja siguiendo el orden del argumento valores. Asegúrese de escribir los valores de los pagos e ingresos en el orden correcto. o Si un argumento matricial o de referencia contiene texto, valores lógicos o celdas vacías, esos valores se pasan por alto. Estimar Opcional. Un número que el usuario estima que se aproximará al resultado de TIR. o Microsoft Excel utiliza una técnica iterativa para el cálculo de TIR. Comenzando con el argumento estimar, TIR reitera el cálculo hasta que el resultado obtenido tenga una exactitud de 0,00001%. Si TIR no llega a un resultado después de 20 intentos, devuelve el valor de error #¡NUM! o En la mayoría de los casos no necesita proporcionar el argumento estimar para el cálculo de TIR. Si se omite el argumento estimar, se supondrá que es 0,1 (10%). o Si TIR devuelve el valor de error #¡NUM!, o si el valor no se aproxima a su estimación, realice un nuevo intento con un valor diferente de estimar.

TIR está íntimamente relacionado a VNA, la función valor neto actual. La tasa de retorno calculada por TIR es la tasa de interés correspondiente a un valor neto actual 0 (cero). La fórmula siguiente demuestra la relación entre VNA y TIR: VNA(TIR(B1:B6);B1:B6) es igual a 3,60E-08 [Dentro de la precisión del cálculo TIR, el valor 3,60E-08 es en efecto 0 (cero).]

jose

29

Excel Financiero -70.000,00 12.000,00 15.000,00 18.000,00 21.000,00 26.000,00

Costo inicial de un negocio Ingresos netos el primer año Ingresos netos el segundo año Ingresos netos del tercer año Ingresos netos del cuarto año Ingresos netos del quinto año

-2% Tasa interna de retorno de la inversión después de 4 años 9% Tasa interna de retorno de la inversión después de 5 años -44% Tasa interna de retorno de inversion despues de 2 años, estimación 10%

0,00 €

5.4.- Administrador de Escenarios en la determinación del TIR La función de los escenarios es guardar con un nombre un conjunto de valores asignados a unas determinadas celdas de entrada, para después sustituir automáticamente los valores originales y observar cómo éstos afectan al resto de los datos de la hoja de cálculo. Los escenarios son útiles cuando se trabaja con variables que no sabemos exactamente que valores van a tomar. Por ejemplo si se desea crear un presupuesto pero no se sabe exactamente los valores de las distintas partidas, en este caso se podrán definir distintos valores (escenarios) para esas partidas y cambiar entre escenarios. En la pestaña Datos > Análisis Y si > Administrador de escenarios

jose

30

Excel Financiero

Inicialmente vemos que no hay ningún escenario definido, hago clic en el botón Agregar y aparece el siguiente cuadro de diálogo:

Introduzco un nombre de escenario en Nombre de escenario. Indicaré las celdas que se van a modificar en Celdas cambiantes. En el cuadro de dialogo Valores de escenario escribiremos los valores para las celdas cambiantes.

jose

31

Excel Financiero

Si los valores a cambiar fueran mas de uno la ventana de dialogo tendría este formato

Una vez introducidos los valores hago clic en Aceptar.

5.5.-VNA.NO.PER Devuelve el valor neto actual para un flujo de caja que no es necesariamente periódico. Para calcular el valor neto actual de una serie de flujos de caja periódicos, utilice la función VNA.

VNA.NO.PER(tasa;valores;fechas) Tasa

es la tasa de descuento que se aplica a los flujos de caja.

Valores es una serie de flujos de caja que corresponde a un calendario de pagos determinado por el argumento fechas. El primer pago es opcional y corresponde al costo o pago en que se incurre al principio de la inversión. Si el primer valor es un costo o un pago, debe ser un valor negativo. Todos los pagos sucesivos se descuentan basándose en un año de 365 días. La serie de valores debe incluir al menos un valor positivo y un valor negativo. Fechas es un calendario de fechas de pago que corresponde a los pagos del flujo de caja. La primera fecha de pago indica el principio del calendario de pagos. El resto de las fechas deben ser posteriores a ésta, pero pueden ocurrir en cualquier orden. 

 

Microsoft Excel almacena las fechas como números de serie secuenciales para que puedan utilizarse en los cálculos. De forma predeterminada, el 1 de enero de 1900 es el número de serie 1 y el 1 de enero de 2008 es el número de serie 39448 porque viene 39.448 días después del 1 de enero de 1900. Los números del argumento fechas se truncan a enteros. Si alguno de los argumentos no es numérico, VNA.NO.PER devuelve el valor de error

jose

32

Excel Financiero #¡VALOR! Si alguno de los números del argumento fechas no es una fecha válida, VNA.NO.PER devuelve el valor de error #¡VALOR! Si alguno de los números del argumento fechas precede a la fecha de inicio, VNA.NO.PER devuelve el valor de error #¡NUM! Si los argumentos valores y fechas contienen un número distinto de valores, VNA.NO.PER devuelve el valor de error #¡NUM!

  

VNA.NO.PER se calcula como sigue:

donde: di = es la iésima o última fecha de pago. d1 = es la fecha de pago 0. Pi = es el iésimo o último pago.

-10.000,00 2.750,00 4.250,00 3.250,00 2.750,00

01/01/2008 01/03/2008 30/10/2008 15/02/2009 01/04/2009

2.086,65 Valor neto actual de una inverison con elcosto y el retorno anteriores. Los flujos de caja se descuentan al 9 %

5.6.- TIR.NO.PER Devuelve la tasa interna de retorno para un flujo de caja que no es necesariamente periódico. Para calcular la tasa interna de retorno de una serie de flujos de caja periódicos, utilice la función TIR. Si esta función no está disponible y devuelve el error #¿NOMBRE?, instale y cargue el programa de complementos Herramientas para análisis. TIR.NO.PER(valores;fechas;estimar) Valores es una serie de flujos de caja que corresponde a un calendario de pagos determinado por el argumento fechas. El primer pago es opcional y corresponde al costo o pago en que se incurre al principio de la inversión. Si el primer valor es un costo o un pago,

jose

33

Excel Financiero debe ser un valor negativo. Todos los pagos sucesivos se descuentan basándose en un año de 365 días. La serie de valores debe incluir al menos un valor positivo y un valor negativo. Fechas es un calendario de fechas de pago que corresponde a los pagos del flujo de caja. La primera fecha de pago indica el principio del calendario de pagos. El resto de las fechas deben ser posteriores a ésta, pero pueden ocurrir en cualquier orden. Las fechas deben introducirse mediante la función FECHA o como resultados de otras fórmulas o funciones. Por ejemplo, utilice FECHA(2008;5;23) para el día 23 de mayo de 2008. Pueden producirse problemas si las fechas se introducen como texto. Estimar

es un número que el usuario estima que se aproximará al resultado de TIR.NO.PER.



Microsoft Excel almacena las fechas como números de serie secuenciales para que puedan utilizarse en los cálculos. De forma predeterminada, el 1 de enero de 1900 es el número de serie 1 y el 1 de enero de 2008 es el número de serie 39448 porque viene 39.448 días después del 1 de enero de 1900. Los números del argumento fechas se truncan a enteros. TIR.NO.PER espera al menos un flujo de caja positivo y otro negativo. De lo contrario, TIR.NO.PER devuelve el valor de error #¡NUM! Si alguno de los números del argumento fechas no es una fecha válida, TIR.NO.PER devuelve el valor de error #¡VALOR! Si alguno de los números del argumento fechas precede a la fecha de inicio, TIR.NO.PER devuelve el valor de error #¡NUM! Si valores y fechas contienen un número distinto de valores, TIR.NO.PER devuelve el valor de error #¡NUM! En la mayoría de los casos no es necesario proporcionar el argumento estimar para el cálculo de TIR.NO.PER. Si se omite el argumento estimar, se supone que es 0,1 (10%). TIR.NO.PER está íntimamente relacionada con VNA.NO.PER, función del valor neto actual. La tasa de retorno calculada por TIR.NO.PER es la tasa de interés que corresponde a VNA.NO.PER = 0. Excel utiliza una técnica iterativa para el cálculo de TIR.NO.PER. Utilizando una tasa variable (comenzando con el argumento estimar), TIR.NO.PER reitera el cálculo hasta que el resultado obtenido tenga una exactitud del 0,000001%. Si TIR.NO.PER no llega a un resultado después de 100 intentos, devuelve el valor de error #¡NUM! La tasa cambia hasta:

     



donde: di = es la iésima o última fecha de pago. d1 = es la fecha de pago 0.

jose

34

Excel Financiero Pi = es el iésimo o último pago.

-10.000 2.750 4.250 3.250 2.750

01/01/2008 01/03/2008 30/10/2008 15/02/2009 01/04/2009

37,34% Tasa interna de retorno

5.7.- TIRM Devuelve la tasa interna de rentabilidad de un flujo de caja periódico teniendo en cuenta el costo en que se incurre para financiar los egresos del negocio y el beneficio obtenido por la reinversión de los ingresos que genera la inversión. TIRM(valores, tasa_financiamiento, tasa_reinversión)

Valores es una matriz o una referencia a celdas que contienen números. Estos números representan una serie de pagos (valores negativos) e ingresos (valores positivos) que se realizan en períodos regulares. 



El argumento valores debe contener por lo menos un valor positivo y uno negativo para poder calcular la tasa interna de retorno modificada. De lo contrario, TIRM devuelve el valor de error #¡DIV/0! Si el argumento matricial o de referencia contiene texto, valores lógicos o celdas vacías, estos valores se pasan por alto; sin embargo, se incluirán las celdas con el valor cero. Tasa_financiamiento caja. Tasa_reinversión reinvierten.

es la tasa de interés que se paga por el dinero utilizado en los flujos de

es la tasa de interés obtenida por los flujos de caja a medida que se

TIRM usa el orden de valores para interpretar el orden de los flujos de caja. Asegúrese de introducir los valores de los pagos e ingresos en el orden deseado y con los signos correctos (valores positivos para ingresos en efectivo y valores negativos para pagos en efectivo). Si n es el número de flujos de caja en valores, tasaf es la tasa_financiamiento y tasar es la tasa_reinversión, la fórmula de TIRM es:

jose

35

Excel Financiero

-120.000 39.000 30.000 21.000 37.000 46.000 10% 12%

Coste Inicial Rendimiento primer año Rendimiento segundo año Rendimiento tercer año Rendimiento cuarto año Rendimiento quinto año Tasa interes anual del prestamo 120000 Tasa interes anual de los beneficios reinvertidos

13% Tasa de retorno modificada de la inversion despues de 5 años -5% Tasa de retorno modificada despues de 3 años 15% Tasa de retorno modificada despues de cinco años en una tasa de reinversión del 17 %

jose

36

Excel Financiero

6.- Funciones para calcular amortizaciones 6.1.- SLN Devuelve la depreciación por método directo de un bien en un período dado. SLN(costo; valor_residual; vida) Costo Obligatorio. El costo inicial del bien. Valor_residual Obligatorio. El valor al final de la depreciación (también conocido como valor residual del bien). Vida Obligatorio. El número de períodos durante los cuales se produce la depreciación del bien (también conocido como la vida útil del bien).

30000 Costo 7500 Valor residual 10 Añosvida util 2.250,00 € Depreciación permitida cada año

6.2.- SYD Devuelve la depreciación por suma de dígitos de los años de un bien durante un período específico. SYD(costo;valor_residual;vida;período) Costo

es el costo inicial del bien.

Valor_residual del bien).

es el valor al final de la depreciación (también conocido como valor residual

Vida es el número de períodos durante los cuales se produce la depreciación del bien (también conocido como la vida útil del bien). Período

jose

es el período, que debe utilizar las mismas unidades que el argumento vida.

37

Excel Financiero 

SYD se calcula como:

30.000 Costo Inicial 7.500 Valor Residual 10 Vida en años 4.090,91 € Depreciación anual permitida para el primer año 409,09 € Depreciación anual permitida para eldecimo año

6.3.- DB Devuelve la depreciación de un bien durante un período específico usando el método de depreciación de saldo fijo. DB(costo;valor_residual;vida;período;mes) Costo es el costo inicial del bien. Valor_residual es el valor al final de la depreciación (también conocido como valor residual del bien). Vida es el número de períodos durante los cuales se produce la amortización del bien (también conocido como vida útil del bien). Período es el período para el que se desea calcular la depreciación. Debe usar los mismos valores que el argumento vida. Mes es el número de meses en el primer año. Si se omite el argumento mes, se supondrá que es 12. El método de depreciación de saldo fijo calcula la depreciación a tasa fija. La función DB usa las fórmulas siguientes para calcular la depreciación durante un período: (costo - depreciación total de períodos anteriores) * tasa donde: tasa = 1 - ((valor_residual / costo) ^ (1 / vida)), redondeado hasta tres posiciones decimales. La depreciación del primer y último períodos son casos especiales. La función DB usa la fórmula siguiente para calcular el primer período: costo * tasa * mes / 12 Para calcular el último período, DB usa la fórmula siguiente: ((costo - depreciación total de períodos anteriores) * tasa * (12 - mes)) / 12

jose

38

Excel Financiero 1.000.000 Costo Inicial 100.000 Valor Residual 6 Vida en años 186.083,33 € Amortización delprimer año,calculando solo 7 meses 259.639,42 € Amortizacion delsegundo año 176.814,44 € Amortizacion del tercer año 23.632,18 € Amortizacion del septimo año, calculandosolo 5 meses

6.4.- DDB Devuelve la depreciación de un bien en un período específico con el método de depreciación por doble disminución de saldo u otro método que se especifique. DDB(costo; valor_residual; vida; período; [factor]) Costo Obligatorio. El costo inicial del bien. Valor_residual Obligatorio. El valor al final de la depreciación (también conocido como valor residual del bien). Este valor puede ser 0. Vida Obligatorio. El número de períodos durante los cuales se produce la depreciación del bien (también conocido como vida útil del bien). Período Obligatorio. El período para el que desea calcular la depreciación. Este valor debe utilizar las mismas unidades que el argumento "vida". Factor Opcional. La tasa de depreciación del saldo. Si se omite este valor, se supone que es 2 (el método de depreciación por doble disminución de saldo). Importante Los cinco argumentos deben ser números positivos. El método de depreciación por doble disminución del saldo calcula la depreciación a una tasa acelerada. La depreciación es más alta durante el primer período y disminuye en períodos sucesivos. La función DDB usa la fórmula siguiente para calcular la depreciación para un período: Min( (cost - total depreciation from prior periods) * (factor/life), (cost - salvage - total depreciation from prior periods) ) Si no desea usar el método de depreciación por doble disminución del saldo, cambie el argumento factor. Utilice la función DVS si desea pasar al método de depreciación lineal cuando la depreciación sea mayor que el cálculo de disminución del saldo.

jose

39

Excel Financiero

2.400 Costo Inicial 300 Valor residual 10 Vida en años 1,32 € 40,00 € 480,00 € 306,00 €

Amortizacion del primer dia. Excel supone que factor es 2 Depreciacion delprimer mes Depreciacion del primer año Amortizacion del segundo año, utilizando un factor de 1,5 en lugar del metodo de depreciacion por doble disminucion del saldo. 22,12 € Amortización deldecimo año.Excel supone que factor es2

6.5.- AMORTIZ.LIN Devuelve la amortización de cada período contable. Esta función se proporciona para el sistema contable francés. Si se compra un bien durante el período contable, la regla de amortización prorrateada se aplica al cálculo de la amortización. AMORTIZ.LIN(costo;fecha_compra;primer_período;costo_residual;período;tasa;base) Importante Las fechas deben especificarse mediante la función FECHA o como resultado de otras fórmulas o funciones. Por ejemplo, utilice FECHA(2008;5;23) para el día 23 de mayo de 2008. Pueden producirse problemas si las fechas se escriben como texto. Costo es el costo o valor de compra del bien. Fecha_compra es la fecha de compra del bien. Primer_período es la fecha del final del primer período. Costo_residual es el valor residual o valor del bien al final del período de la amortización. Período es el período. Tasa es la tasa de amortización. Base es la base anual utilizada.

Base 0 u omitido 1 3 4

Base para contar días 360 días (Método NASD) Real 365 al año 360 al año (Sistema europeo)

Microsoft Excel almacena las fechas como números de serie secuenciales para que puedan utilizarse en los cálculos. De forma predeterminada, el 1 de enero de 1900 es el número de serie 1 y el 1 de enero de 2008 es el número de serie 39448 porque viene 39.448 días después del 1 de enero de 1900.

jose

40

Excel Financiero 2400 19/08/2008 31/12/2008 300 1 15% 1

Costo Fecha de compras Finaldel primer periodo Valor residual Periodo Tasa de amortizacion Base real

360 Amortizacion del primer periodo

6.6.- DVS Devuelve la amortización de un bien durante un período especificado, inclusive un período parcial, usando el método de amortización acelerada con una tasa doble y según el coeficiente que especifique. Las iniciales DVS corresponden a disminución variable del saldo. DVS(costo; valor_residual; vida; período_inicial; período_final; [factor], [sin_cambios]) Costo Obligatorio. El costo inicial del bien. Valor_residual Obligatorio. El valor al final de la depreciación (también conocido como valor residual del bien). Este valor puede ser 0. Vida Obligatorio. El número de períodos durante los cuales se produce la depreciación del bien (también conocido como la vida útil del bien). Período_inicial Obligatorio. El período inicial para el que se desea calcular la depreciación. El argumento período_inicial debe usar las mismas unidades que el argumento vida. Período_final Obligatorio. El período final para el que se desea calcular la depreciación. El argumento período_final debe usar las mismas unidades que el argumento vida. Factor Opcional. El tipo de depreciación del saldo. Si el argumento factor se omite, se calculará como 2 (el método de depreciación por doble disminución del saldo). Si no desea usar el método de depreciación por doble disminución del saldo, cambie el argumento factor. Para obtener una descripción del método de amortización o de depreciación por doble disminución del saldo, vea la función DDB. Sin_cambios Opcional. Un valor lógico que especifica si deberá cambiar al método directo de depreciación cuando la depreciación sea mayor que el cálculo del saldo en disminución.  Si el argumento sin_cambios es VERDADERO, Microsoft Excel no cambia al método directo de depreciación aun cuando ésta sea mayor que el cálculo del saldo en disminución.  Si el argumento sin_cambios es FALSO o se omite, Excel cambia al método directo de depreciación cuando la depreciación es mayor que el cálculo del saldo en disminución. Importante Todos los argumentos, excepto el argumento sin_cambios, deben ser números positivos.

jose

41

Excel Financiero 2.400 Costo Inicial 300 Valor Residual 10 Vida en años 1,32 € 40,00 € 396,31 € 311,81 €

Depreciación del primer dia. Depreciación delprimer mes Depreciación entre los meses 6 y 18 Depreciación entre los meses 6 y 18 utilizando un factor de 1,5 en lugar del metodo de doble disminución del saldo 315,00 € Depreciación del primer ejercicio fiscal en elque ha poseido el bien, suponiendo que lasleyes fiscales limiten su depreciacion al 150 % del saldo en disminución. El bien se adquiere en medio del primer trimestre del ejercicio fiscal

6.7.- AMORTIZ.PROGRE Devuelve la amortización lineal de un bien al final de un ejercicio fiscal determinado. Esta función se proporciona para el sistema contable francés. Si se compra un activo durante el período contable, la regla de prorata temporis se aplica al cálculo de la amortización. Esta función es similar a AMORTIZ.LIN, excepto que el coeficiente de amortización se aplica al cálculo de acuerdo a la vida esperada del bien. AMORTIZ.PROGRE(costo,fecha_compra,primer_período,costo_residual,período,tasa,base) Importante Las fechas deben introducirse mediante la función FECHA o como resultado de otras fórmulas o funciones. Por ejemplo, utilice FECHA(2008;5;23) para el día 23 de mayo de 2008. Pueden producirse problemas si las fechas se introducen como texto. Costo es el costo o valor de compra del bien. Fecha_compra es la fecha de compra del bien. Primer_período es la fecha del final del primer período. Costo_residual es el valor residual o valor del bien al final del período de la amortización. Período es el período. Tasa es la tasa de amortización. Base es la base anual utilizada.

Base

Base para contar días

0 u omitido 1 3

360 días (Método NASD) Real 365 al año

jose

42

Excel Financiero 4

360 al año (Sistema europeo)

Microsoft Excel almacena las fechas como números de serie secuenciales para que puedan utilizarse en los cálculos. De forma predeterminada, el 1 de enero de 1900 es el número de serie 1 y el 1 de enero de 2008 es el número de serie 39448 porque viene 39.448 días después del 1 de enero de 1900. Microsoft Excel para Macintosh utiliza un sistema de fechas predeterminado diferente. Esta función devuelve la amortización hasta el último período de vida del bien o hasta que el valor acumulado de dicha amortización sea mayor que el valor inicial del bien menos el valor residual. Los coeficientes de amortización son:

Vida del bien (1/tasa) Entre 3 y 4 años Entre 5 y 6 años Más de 6 años

Coeficiente de amortización 1.5 2 2.5

La tasa de amortización crecerá un 50% para el período que precede al último período y crecerá un 100% para el último período. Si la vida del bien está entre 0 y 1, 1 y 2, 2 y 3 ó 4 y 5; la función devuelve el valor de error #¡NUM!

2400 19/08/2008 31/12/2008 300 1 15% 1

Costo Fecha de compra Finaldel primer periodo Valor Residual Periodo Tasa de amortización base real

776 Amortización del primer periodo

jose

43