El presente MANUAL, es complementario de la
Guía
Rápida, "APLICACIONES FINANCIERAS DE EXCEL – Con
Matemáticas Financieras", en
circulación en portales tan importantes como:
y
monografías.com, que ponen al
alcance de todos –sin costo alguno- los más diversos
temas de investigación y edición.
El MANUAL, esta en formato HTML y es visualizable con
Internet Explorer, en formato de diseño sencillo y
ágil. Además de las formulas y funciones utilizadas
en la obra, contiene los ejercicios resueltos y plantillas de las
funciones; en las que el lector podrá operar con sus
propios datos; esto último viene en formato Excel
vinculado al MANUAL.
FUNCIONES | CREAR UNA FORMULA | BUSCAR OBJETIVO |
INT.EFECTIVO | TASA NOMINAL | VF | VA | PAGO |
TASA | NPER | VNA o VAN | TIR | PLANTILLAS EXCEL |
1. Microsoft Excel Xp
Microsoft Excel es una planilla de cálculos de
Microsoft Office y sirve para hacer cálculos por medio de
fórmulas o funciones, a través de sus celdas,
formadas por columnas y filas.
Su principal función es realizar operaciones
matemáticas –de la misma manera que trabaja la
más potente calculadora-, pero también la de
computar complejas interrelaciones y ordenar y presentar en forma
de gráfico los resultados obtenidos. Excel permite
colocar, ordenar y buscar datos, así como insertar bloques
de texto e imágenes. Los principales elementos de trabajo
son:
Fila: Conjunto de varias celdas
dispuestas en sentido horizontal.
Título de fila: Está siempre a la
izquierda y nombra a las filas mediante números, que en el
caso de Excel Xp van desde el 1 hasta el 65,536.
Columna: Conjunto de varias celdas dispuestas en sentido
vertical.
Título de columna: Está siempre arriba y
nombra a las columnas mediante letras, van desde la A hasta la
IV. Luego de la columna Z viene la AA, AB, AC, etc.; luego de la
AZ viene la BA, la BB, la BC, etc.; y así
sucesivamente.
Celda: Es la intersección de una
fila y una columna y en ella introducimos los gráficos,
trátese de texto, números, fecha u otros datos. Una
celda lleva el nombre de la columna, seguido del nombre de la
fila. Por ejemplo, la celda que es la intersección de la
fila 29 con la columna F, es F29.
Rango: Los rangos son una referencia a un conjunto de
celdas de una planilla de cálculos. Son definidas mediante
letras y números. Denominada mediante la celda de una
esquina del rango (generalmente la superior izquierda), luego dos
puntos y la esquina opuesta. Por ejemplo, al rango que comprende
las celdas C4, C5, C6, C7, D4, D5, D6, D7, E4, E5, E6 y E7 es
C4:E7.
Las funciones son fórmulas predefinidas que
ejecutan cálculos utilizando valores específicos,
denominados argumentos, en un orden determinado o estructura. Las
funciones pueden utilizarse para ejecutar operaciones simples o
complejas.
Si una función no está disponible y
devuelve el error #¿NOMBRE?, instale y cargue el programa
de complementos Herramientas para análisis.
¿Cómo?:
En el menú Herramientas, elija
Complementos.
En la lista Complementos disponibles, seleccione el
cuadro Herramientas para análisis y, a
continuación, haga clic en Aceptar.
Si es necesario, siga las instrucciones del programa de
instalación.
Las fórmulas son ecuaciones que efectúan
cálculos con los valores de la hoja de cálculo. Una
fórmula comienza por un signo igual (=). Por ejemplo, la
siguiente fórmula multiplica 2 por 3 y, a
continuación, suma 5 al resultado. = 2*3+5
Es parte de una serie de comandos a veces denominados
herramientas de análisis Y si. En el caso de que conozca
el resultado deseado de una fórmula sencilla, pero no la
variable que determina el resultado, podrá utilizar la
función Buscar objetivo haciendo clic en Buscar objetivo
en el menú Herramientas. Al realizar una búsqueda
de objetivo, Microsoft Excel varía el valor de celda
específica hasta que una fórmula dependiente de
dicha celda devuelve el resultado deseado.
Ajustar el valor de una celda para obtener un resultado
específico para otra.
1. En el menú Herramientas, haga clic en Buscar
objetivo.
2. En el cuadro Definir celda, escriba la referencia de
la celda que contenga la fórmula (fórmula:
secuencia de valores, referencias de celda, nombres, funciones u
operadores de una celda que producen juntos un valor nuevo. Una
fórmula comienza siempre con el signo (=).) que desee
resolver.
3. En el cuadro Con el valor, introduzca el resultado
que desee.
4. En el cuadro Para cambiar la celda, introduzca la
referencia de la celda que contenga el valor que desee ajustar. A
esta celda debe hacer referencia la fórmula en la celda
especificada del cuadro Definir celda.
5. Haga clic en Aceptar.
Ejercicio 1
(Aplicando la función Buscar Objetivo de
Excel)
Sí un préstamo de UM 5,000 al 3.8% mensual
para su pago en 6 meses, se triplica cada dos meses, calcular las
cuotas a pagar.
Solución:
VA= 5,000; i = 0.038; n = 6; C1…6 = ?
La primera cuota puede ser cualquier valor; lo
importante es que las demás cuotas (de la segunda en
adelante) dependan de la primera; de modo que cuando cambie la
primera, las demás cuotas y el resto de la tabla cambien
también. Habrá que cambiar el valor de la primera
cuota hasta cuando el saldo final sea cero. Es posible hacer esto
a mano, pero el computador lo hace más rápido con
la opción Buscar objetivo ya mencionada. Definimos la
celda donde está el saldo final del último
período con el valor cero y pedimos que cambie la celda
donde está la primera cuota.
Operando con Buscar Objetivo de Excel.
1º Elaboramos la tabla de amortización, como
ilustramos en el extracto de la hoja de Excel.
En la columna E3 (Pago), ingresamos 10 un valor
arbitrario, de la siguiente forma:
Celda E3 10 [Ingresamos a la celda sin poner el signo
(=)]
Celda E4 =E3
Celda E5 =E4*2 (de acuerdo a la condición del
problema).
Celda E6 =E5
Celda E7 =E6*2
Celda E8 =E9
INTERES = SALDO INICIAL x 0.038
PAGO = BUSCAR OBJETIVO
AMORTIZACION = PAGO – INTERES
( =E3 – C3 ) … ( =E8 – C8)
Cuando la tabla es de muchos períodos (filas) y
no exista la condición doble o UM X más cada 2, 3,
etc. cuotas; la forma más rápida de operar, es
ingresar a la primera celda (PAGO) cualquier número, luego
ingresamos a la segunda celda (PAGO) el signo (=) y hacemos clic
con el mouse en la primera celda PAGO. Finalmente, colocamos el
puntero en la 2º celda PAGO y del ángulo inferior
arrastramos el puntero en forma de cruz hasta la celda PAGO final
de la tabla.
La opción Buscar Objetivo es de aplicación
cuando calculemos el valor de las cuotas de cualquier
préstamo o inversión con flujos
uniformes.
2. Funciones Financieras
Aún con la rapidez que brinda la hoja de
cálculo Excel, la solución de problemas complejos
requiere de tiempo y esfuerzo. El tema de las funciones
financieras en el presente libro lo dividimos en dos grandes
grupos: 9.5.1. Funciones para conversión de tasas de
interés y 9.5.2. Funciones para el manejo de series
uniformes.
2.1. Funciones para conversión de tasas de
interés
Dentro de este grupo clasificamos dos funciones que
sirven para convertir tasas de interés efectivas en
nominales y viceversa.
Los argumentos que utilizan las funciones financieras
para conversión de tasas son los siguientes:
Núm_per: Es el número de períodos
de interés compuesto por año. (Cuando operamos con
TASA.NOMINAL).
Núm_per_año: Es el
número de períodos de interés compuesto por
año. (Cuando operamos con INT.EFECTIVO).
Int_nominal: Es la tasa de interés nominal anual
expresada en términos decimales.
Tasa_efectiva: Es la tasa de interés efectiva
anual, es decir, la rentabilidad efectiva recibida cuando los
intereses son reinvertidos en las mismas condiciones por el
tiempo que resta del año.
Período de interés compuesto: El tiempo
que transcurre entre dos fechas de pago de interés; en el
caso de estas funciones suponemos que el interés pagado no
es retirado ni consumido, si no reinvertido por el tiempo que
resta del año.
Devuelve la tasa efectiva del interés anual si
conocemos la tasa de interés anual nominal y el
número de períodos de interés compuesto por
año. De aplicación cuando los períodos de
pago son exactos.
Sintaxis
INT.EFECTIVO(int_nominal;núm_per_año)
Si alguno de los argumentos es menor o igual a
cero o si el argumento núm_per_año es menor a uno,
la función devuelve el valor de error
#¡NUM!
La respuesta obtenida viene enunciada en términos
decimales y debe expresarse en formato de porcentaje. Nunca
divida ni multiplique por cien el resultado de estas funciones.
Esta función proporciona la tasa efectiva de
interés del pago de intereses vencidos. Para intereses
anticipados debe calcularse la tasa efectiva aplicando la
fórmula.
El argumento núm_per_año se trunca a
entero cuando los períodos son irregulares, hay que tener
especial cuidado con esta función, sólo 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).
El resultado proporcionado por esta función lo
obtenemos también con la siguiente
fórmula:
EJERCICIO 2 (Aplicación
de la función INT.EFECTIVO)
(A) Cuando los períodos de pago son exactos y el
resultado es confiable:
FECHA INICIAL : 15-03-2004
FECHA FINAL : 15-06-2004
TASA NOMINAL : 68% anual, compuesto
trimestralmente
Solución:
n = (15/03/2004 – 15/06/2004) = 90/30 = 3, m = (12/3) =
4
Aplicando ambos métodos:
(B) Cuando los períodos de pago son inexactos y
por lo tanto el resultado es irreal.
FECHA INICIAL : 15-03-2004
FECHA FINAL : 15-06-2004
TASA NOMINAL : 68% anual, compuesto cada 2.20
meses
Solución:
n = (15/03/2004 – 21/05/2004) = 66/30 = 2.2, m =
(12/2.2) = 5.2174
Aplicando ambos métodos:
Observando ambos resultados,
constatamos que son diferentes. En estos casos es recomendable el
uso de las fórmulas, sus resultados son más
reales.
2.1.2. Función Financiera
TASA.NOMINAL
Devuelve la tasa de interés nominal anual si
conocemos la tasa efectiva y el número de períodos
de interés compuesto por año.
Sintaxis
TASA.NOMINAL(tasa_efectiva;
núm_per)
El argumento núm_per trunca a entero, hay que
tener especial cuidado con esta función, sólo
produce resultados confiables cuando la cantidad de
períodos de pago en el año (núm_per) tiene
valores exactos; por ejemplo: mensual (12), trimestral (4),
semestral (2) o anual (1).
Si alguno de los argumentos es menor o igual a cero o si
el argumento núm_per es menor a uno, la función
devuelve el valor de error #¡NUM!
La respuesta obtenida viene enunciada en términos
decimales y debe expresarse en formato de porcentaje. Nunca
divida ni multiplique por cien el resultado de estas
funciones.
Esta función proporciona la tasa nominal del pago
de intereses vencidos. Para el interés anticipado debe
calcularse la tasa nominal aplicando la
fórmula:
Ejemplo
i = 0.3449; n = 12; j = ?
2.2. Funciones para el manejo de series uniformes
Presenta las funciones que 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 series uniformes suponemos que
los valores recibidos o pagados durante el tiempo del negocio son
reinvertidos por el tiempo restante del plazo total, en las
mismas condiciones existentes para la inversión original.
Un problema es 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 (de liquidación de
los pagos efectuados dentro del tiempo de la inversión) es
constante.
Los argumentos que utilizan las funciones financieras de
series uniformes son los siguientes:
Va, P en los términos financieros. Es el valor
actual de una serie de pagos futuros iguales. Si este argumento
es omitido, se considerará 0.
Pago, C en los términos financieros. Es el pago
efectuado 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 van con signo
positivo y los egresos con signo negativo.
Nper (n en términos financieros). Es la cantidad
total de períodos en una anualidad, es decir el plazo
total del negocio.
Tasa (i en los términos financieros). 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 la
uniformidad en el uso de las unidades cuando especifiquemos Tasa
y Nper.
Vf (F en los términos financieros). Es el valor
futuro o el saldo en efectivo que desea lograr después de
efectuar el último pago. Si el argumento Vf es omitido,
asumimos que el valor es 0.
Tipo Es el número 0 ó 1 e indica la forma
de pago de la cuota entre vencido y anticipado.
Defina tipo
0 ó se omite al final del
período
1 Al inicio del período
Período. Especifica el número ordinal de
la cuota en estudio, 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 un
período para el análisis respectivo de 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
es omitido, suponemos que es 10%.
Permite calcular VF a partir de C o de VA.
También sirve para calcular el valor de VF indicando si es
cuota anticipada (tipo=1) o vencida (tipo=0). Si lo que queremos
calcular es VF a partir de VA omitimos el valor de C; si la cuota
es vencida, omitimos el valor tipo.
Devuelve el valor futuro de la inversión,
equivalente a los pagos periódicos uniformes a una tasa de
interés constante.
Sintaxis: VF(tasa;nper;pago;va;tipo)
El resultado proporcionado por esta función lo
obtenemos también con la siguiente
fórmula:
EJERCICIO 3 (Aplicación
de la función VF)
Si ahorramos UM 350 mensuales durante 3 años en
un banco que paga el 18% nominal anual y deseamos saber
cuánto dinero tendremos ahorrado al final de los 3
años:
Solución:
C = 350; n = (3*12) = 36; i = 0.015 (0.18/12); VF =
?
Aplicando ambos métodos, tenemos:
Ingresamos los datos en los argumentos de función
en el orden indicado en el cuadro de la sintaxis:
En la solución de los ejemplos y ejercicios en el
presente libro, utilizaremos el formato simplificado indicado en
el cuadro de la Sintaxis, cuando operemos con la herramienta
Funciones Financieras de Excel. Esta metodología de
ingresar los datos es aplicable a todas las funciones de Excel,
utilizadas en la obra, desde luego, cada con su propia persiana
de argumentos de función.
Hay tres aspectos a considerar en este
ejemplo:
a) El interés incluido en el argumento Tasa debe
estar en la misma unidad de tiempo utilizada para el argumento
Nper. En este caso, como son cuotas mensuales, la tasa de
interés debe ser mensual, es necesario dividir por doce la
tasa anual nominal.
b) VA puede omitirse como apreciamos 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) Si deseamos que las cifras en la hoja de
cálculo sean positivas, introducimos el argumento Pago con
signo negativo, como apre- ciamos en el asistente para funciones
(-350, en C2).
Permite calcular VA a partir de C o de VF.
También sirve para calcular el valor de VF indicando si es
cuota anticipada (tipo=1) o vencida (tipo=0). Para calcular VA a
partir de VF, omitir el valor de C; y cuando operemos con cuotas
vencidas, omitir el valor tipo. Devuelve el valor actual de la
inversión. El valor actual es la suma de una serie de
pagos a futuro. Por ejemplo, cuando pedimos dinero prestado, la
cantidad del préstamo es el valor actual para el
prestamista.
La versión XP de Excel, recomienda el empleo de
fx insertar función de la barra de fórmulas. Al
oprimir fx aparece el menú de funciones y escogemos la
función buscada. Esta función conserva las mismas
observaciones efectuadas para VF.
Sintaxis: VA(tasa;nper;pago;vf;tipo)
El resultado proporcionado por esta función lo
obtenemos también con la siguiente
fórmula:
Por
ejemplo:
Si ahorramos UM 350 mensuales durante 3 años en
un banco que paga el 18% nominal anual y deseamos saber
cuánto representan estas mensualidades al día de
hoy.
Solución:
C = 350; n = (3*12) = 36; i = 0.015 (0.18/12); VA =
?
Aplicando ambos métodos, tenemos:
Calcula el pago de un préstamo basándose
en pagos constantes y con la tasa de interés
constante.
Sintaxis
PAGO(tasa;nper;va;vf;tipo)
Sugerencia:
Para encontrar la cantidad total pagada durante el período
del préstamo, multiplique el valor devuelto por PAGO por
el argumento nper.
El resultado proporcionado por esta función lo
obtenemos también con la siguiente
fórmula:
EJERCICIO 4
(Aplicación de la función PAGO)
Obtenemos un crédito de UM 10,000 para su pago en
24 cuotas trimestrales iguales, a la tasa nominal anual de 36%
por trimestre vencido:
Solución:
VA = 10,000; n = 24; i = (0.36/12) = 0.03; C =
?
Aplicando ambos métodos, tenemos:
En algunos casos puede darse la necesidad de requerir
tanto el VA como el VF; como en el caso del leasing, en el cual,
además del valor inicial de un equipo tenemos cuotas
mensuales iguales y al final del pago existe la opción de
compra para que el usuario adquiera el bien.
Por ejemplo:
En un leasing de UM 50,000 a 24 meses con la tasa de
interés del 2.87% mensual y la opción de compra del
12%, la función Pago para calcular la cuota mensual a
pagar operaría de la siguiente forma:
Solución:
VA = 50,000; i = 0.0287; n = 24; VF = 12%; C =
?
2.2.4. Función Financiera TASA
Devuelve la tasa de interés por período de
la anualidad. La TASA es calculada por iteración 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!.
Con esta función es posible calcular la tasa de
interés, combinando no sólo VA y VF, sino
también VA y C, C y VF y VA, C y VF.
Por ser la tasa del período tiene la
característica de ser simultáneamente nominal y
efectiva, para convertir ésta tasa en tasa anual debe
tenerse cuidado con la fórmula utilizada, dependiendo de
qué tasa queremos calcular: la tasa nominal o la tasa
efectiva anual (TEA).
Sintaxis
TASA(nper;pago;va;vf;tipo;estimar)
Por ejemplo:
VA = 5,000; n = 5; C = 1,250; i = ?
Función utilizada para calcular la tasa
periódica de las anualidades. No existen fórmulas
para obtener la tasa de las anualidades.
2.2.5. Función Financiera NPER
Devuelve la cantidad de períodos que debe tener
la inversión para que sea equivalente a la serie de pagos
periódicos iguales.
Sintaxis
NPER(tasa, pago, va, vf, tipo)
La unidad de tiempo consignada en la función Nper
debe ser la misma que la utilizada en la tasa de
interés.
El resultado proporcionado por esta función lo
obtenemos también con las siguientes fórmulas,
según los casos:
Por
ejemplo:
i = 0.06; C = 14,000; VA = 93,345.50; n = ?
2.2.6. Función Financiera VNA o VAN
Calcula el valor actual neto de la inversión a
partir de la tasa de descuento y pagos futuros (valores
negativos) e ingresos (valores positivos).
Sintaxis
VNA(tasa;valor1;valor2; …)
Los valores incluidos en el flujo de caja no tienen que
ser constantes. Esta es la principal diferencia frente a la
función VA, conserva la condición de que tanto la
tasa de interés como la periodicidad son constantes; es
decir, todo el flujo de caja descuenta a la misma tasa y los
valores incluidos en él ocurren a intervalos
iguales.
Dentro del rango del flujo de caja excluimos el valor
presente ubicado en el período cero (0), dicho valor
está en UM de hoy. La inversión inicial de la celda
con período 0 no ingresa en el argumento valores,
posteriormente restamos del resultado que arroje la
función.
El valor actual neto es un indicador sobre la
conveniencia económica de la inversión, involucra
la subjetividad del inversionista, que debe seleccionar la tasa
de interés para descontar el flujo de caja. Al calcular
con dos tasas diferentes obtenemos dos resultados, para evaluar
estos casos debe tenerse en cuenta que la respuesta esta
expresada en UM del período cero y su significado puede
interpretarse de la siguiente manera:
a. VNA > 0, un resultado positivo indica que el
negocio estudiado arroja rentabilidad superior a la exigida por
el inversionista, deducida la inversión, luego es
conveniente llevar a cabo el negocio.
b. VNA = 0, en caso de presentarse, un resultado igual a
cero indica que el negocio arroja rentabilidad igual a la exigida
por el inversionista, la ejecución del proyecto es
opcional.
c. VNA < 0, valor presente neto negativo no significa
que el negocio estudiado arroje pérdidas,
únicamente la rentabilidad es inferior a la exigida por el
inversionista y para él, particularmente, no es
conveniente el negocio.
De lo anterior concluimos cuando anunciemos el VNA de un
proyecto debe aclararse cuál fue la tasa de descuento
utilizada para calcularlo, es decir, cuál fue el valor
ingresado en el argumento Tasa.
Devuelve la tasa interna de retorno (tasa de
rentabilidad) de los flujos de caja representados por los
números del argumento valores. Estos flujos de caja no son
constantes, como en las anualidades. 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
ocurren en períodos regulares.
Sintaxis
TIR(valores;estimar)
Para el cálculo de la función TIR
incluimos en el rango de valores todo el flujo de caja y es
necesario que existan valores positivos y negativos. El argumento
Estimar es opcional. En caso de omitirse, el Excel asume la tasa
inicial del 10%.
La TIR sólo involucra las condiciones
particulares de un proyecto y no está afecta por la
subjetividad del inversionista. Sin embargo, dificultades de
orden matemático llevan a desconfiar de los resultados que
arroja.
Finalizamos esta parte con el siguiente
ejemplo ilustrativo
César ahorra UM 350 mensuales durante 3
años en un banco que paga el 18% nominal anual y desea
saber cuánto dinero tendrá ahorrado al final de los
3 años:
Solución:
C = 350; n = 36 (3*12); i = 0.015 (0.18/12); VF =
?
Hay tres aspectos a considerar en este
ejemplo:
a) El interés incluido en el argumento tasa debe
estar en la misma unidad de tiempo que el utilizado en el
argumento Nper, en este caso, como son cuotas mensuales, la tasa
de interés debe ser men sual, por lo tanto hay que dividir
por doce la tasa anual nominal.
b) VA puede omitirse como apreciamos 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) Para que las cifras en la hoja de cálculo sean
positivas, el argumen- to Pago va con signo negativo, como
apreciamos en el asistente para funciones (-350).
En la solución de los ejemplos y ejercicios en el
presente libro, utilizamos el formato simplificado (del ejemplo
de aplicación) cuando operemos con la herramienta
Funciones Financieras de Excel.
Copyright ©2005 César Aching
Guzmán
César Aching Guzmán