Referencias absolutas y relativas en las fórmulas de Excel

Descargar el fichero: dolares.xls

$ Los Dólares en una fórmula de Excel nos permiten hacer absolutas las referencias a celdas que deseamos que no se muevan al copiar la fórmula a otro sitio. Cuando en una fórmula de Excel no se utilizan dólares, al copiar la fórmula a otro sitio las celdas a las que se hace referencia en la fórmula cambian de forma relativa. Para que no cambien se han de convertir en celdas absolutas, y esto se hace añadiendo dólares.

Supongamos que una fórmula hace referencia a la celda B3, nos podemos encontrar con estos casos:

• =B3 No lleva dólares. La referencia es relativa.
• =$B3. Únicamente hemos puesto dólares antes de la letra B. Esto supone fijar la columna B.
• =B$3. Únicamente hemos puesto dólares antes del número 3. Esto supones fijar la fila 3.
• =$B$3. Esta es una referencia ABSOLUTA. Al fijar la fila 3 y la columna B, queda fijada la celda.

Para poner los dólares mientras se escribe la fórmula podemos poner los dólares a mano o pulsar la tecla de función F4. Pulsándola alternativamente podemos conseguir cada uno de los estados anteriormente mencionados.

También podemos poner los dólares después de haber escrito la fórmula y antes de copiarla a otro lugar. Para ello se ha de EDITAR la fórmula, lo cual se consigue de dos formas:

• Haciendo doble clic con el ratón sobre la celda que contiene la fórmula que deseamos editar
• Pulsando la tecla de función F2, mientras estamos en la celda que deseamos editar

Si seleccionamos un rango de celdas y luego escribimos algo, sea un texto, un número o una fórmula, al pulsar Intro queda escrita únicamente en la primera celda. Si deseamos que quede escrita en todas las celdas debemos pulsar simultáneamente la teclas CONTROL e INTRO.

VAN y TIR

Descargar el fichero VanTir.xlsm

Puedes escuchar la explicación de viva voz, contando como se crea el fichero. Es conveniente tener abierto el fichero de Excel e ir siguiendo el desarrollo del caso práctico.

En el siguiente enlace puedes escuchar el audio explicativo de cómo calcular el VAN y la TIR.

El VAN es el Valor Actual Neto. En inglés NPV (Net Present Value).

La TIR es la Tasa Interna de Retorno o Tasa Interna de Rentabilidad. En ingles se denomina IRR (Internal Rate of Return). Ver enlace de la Wikipedia.

En Excel el VAN se calcula con la función =VNA(tasa;flujos)+desembolso inicial

Los flujos que se han de poner dentro de la función VNA únicamente son los que vencen entre el instante t=1 y t=n. El desembolso inicial (que vence en t=0) se pone sumando fuera de la fórmula.

En general el desembolso inicial es negativo y los flujos de caja posteriores, en general, son positivos, aunque pueden existir casos en los que tengamos alguno negativo. Lo importante es que prestación y contraprestación tengan signos distintos.

Al calcular el VAN lo que hacemos es descontar todos los flujos de caja hasta el origen (t=0). Para ello utilizamos una cierta tasa de descuento (k) que ha de ser un tanto efectivo relativo al periodo de tiempo en el eque vengan los flujos de caja. Si los flujos son mensuales, debemos descontar a un tanto efectivo mensual. Si los flujos son de periodicidad anual, debemos descontar a un tanto efectivo anual.

Podemos ver en el gráfico del VAN que al aumentar la tasa de descuento (k) el Valor Actual va disminuyendo. Y que en general, comenzamos en la parte positiva, y terminamos en la parte negativa. El punto de corte con el eje es la TIR que es la tasa a la que el VAN se hace cero.

La TIR se interpreta como la rentabilidad de la operación financiera. A mayor TIR mayor rentabilidad.

La fórmula de la TIR requiere que pongamos todos los fljujos de caja, incluido el desembolso incial. Tiene un segundo argumento (estimar) que en general dejaremos vacio. Sirve para que en caso de existir TIR múltiple (varios puntos de corte del gráfico con el eje horizonatal) indiquemos a Excel al estimación de la TIR y nos dará la más próxima a dicha estimación. El caso de TIR múltiple nunca se da si el desembolso incial es negativo y TODOS los demás flujos de caja (las recuperaciones) son positivos.

=TIR(flujos de caja;estimar)

Si los flujos de caja son mensuales el valor que se obtiene con la fórmula =TIR(flujos de caja) es una TIR mensual, que luego se ha de anualizar, hasta llegar al tanto efectivo anual. Si los flujos son anuales, la TIR que se obtiene es ya anual efectiva. Si nos hablan simplemente de TIR se entiende que se trata siempre de la TIR anual.

Hoja de cálculo de Google

Vamos a resolver el caso anterior en una Hoja de Calculo de Google.

Audio

Histograma de frecuencias

Descargar el fichero: HistoricoIBEX.xlsx

En las columans B y C encontraremos los datos correspondientes a las cotizaciones de cierre del índice IBEX-35 de la bolsa española, desde su creación hasta julio 2008. El Ibex esta compuesto por los 35 valores de mayor capitalización de la bolsa española y se utiliza como su principal indicador.

En la columna D calculamos la variación porcentual diária. Pretendemos hacer un estudio para determinar cómo se ditribuye esta variación diária que representa lo que ha subido o bajado la bolsa en un día. Lo más habitual son los días en los que las variaciones son pequeñas y los días en los que se producen grandes ganancias o grandes caidas en bolsa son más escasos. Esta idea ha de venir recogida en un gráfico de frecuencias que si la teoría es correcta se debe comportar según una distribución normal.

¿Cómo se hace el análisis de frecuencias?

Pasos:

1. Calculamos la variación máxima y mínima, y junto a ellas consideramos esos valores más o menos un poquito más para asegurarnos que entre esos dos extremos se encuentran tambien incluidos los valores máximo y mínimo. Celdas H3 y H4.



2. Determinamos de forma arbitraria el número de intervalos de frecuencia, que en el gráfico se traducirá en el número de barras. Tomamos 50 intervalos. Esto hace que en la columna F hagamos una lista que va de 0 a 50, esto determinará los 50 intervalos.



3. En la celda G6 ponemos el mínimo (por defecto).



4. En la celda G7 ponemos una fórmula que nos de los valores de los intervalos. La fórmula es: Anterior + amplitud del intervalo entre 50. Y copiamos esa fórmula hacia abajo.



5. En la columna H vamos a emplear la función =FRECUENCIA que es una fórmula matricial y nos dará cuantos valores de la columna D (variación porcentual) estan dentro de cada intervalo definido en la columna G. Hemos de estar atentos ya que se trata de una función matricial cuyas peculiaridades aclaramos seguidamente.

La función:

=FRECUENCIA(datos;grupos)

es una función matricial. Estas funciones normalmente devuelven el resultado en varias celdas. Para su buen funcionamiento requiren tres pasos:

1. Seleccionar con el ratón o con el teclado el rango de celdas donde la función matricial depositará sus resultados. En nuestro caso, hemos de tener seleccionado el rango: H6:H56.



2. Escribir la función. En nuestro caso: =FRECUENCIA(D:D;G6:G56)



3. Para validar una función matricial no se ha de pulsar la tecla INTRO, sino que se han de pulsar las tres teclas siguientes: ctrl+shift+enter

Finalmente hacemos un gráfico de columnas verticales y veremos la distribución que en la mayoría de los casos se ajustará a una normal (campana de Gauss).

También puede ser interesante que vea el siguiente post.

• Simulación del Máximo Beneficio

Impedir Repetidos

Descargar el fichero: NoRepe.xlsx

Mediante Datos, Validación podemos conseguir que dentro de un rango de celdas se impida escribir un dato repetido. Para ello será necesario que utilicemos una condición que utiliza la fórmula =CONTAR.SI. Lo que haremos es crear una validación personalizada, esto es, con fórmula.


Dentro de Datos, Validación se elige 'Personalizada' y la fórmula que se utiliza es la siguiente:

=CONTAR.SI($B$8:$G$15;+B8)=1

Si en la pestaña 'Mensaje de Error' se elige 'Advertencia' no se impide la entrada de un dato repetido y simplemente se avisa.

En este caso, Excel 2007, se puede lugo utilizar Datos, Validación de Datos, Rodear con un círculo datos no válidos y así podemos identificar los datos repetidos.Otro aspecto a considerar es que la Validación de Datos únicamente es efectiva cuando se escriben los valores en las celdas, pero si éstas se copian la validación no es efectiva.

Función que elige una al azar

Descargar el fichero: TomaUna.xlsm

Disponemos de un rango de celdas con diferentes valores, por ejemplo, ciudades. Se trata de diseñar una función que permita elegir al azar un valor de esa lista. Programaremos la función en el Editor de Visual Basic, de esta forma estará disponible en el Asistente de Funciones en la categoría de Funciones creadas por el usuario.


La función es la siguiente.

Function UNA(LISTA As Range)
Dim n As Long
Dim ale As Long
Randomize
n = LISTA.Count
ale = Int(Rnd * n) + 1
UNA = LISTA.Value2(ale, 1)
End Function

Randomize hace que los valores sean algo más aleatorios. Es aconsejable utilizarlo siempre que se use Rnd que es para calcular un valor aleatorio uniforme entre cero y uno.

Randomize se pronuncia [Randomais]

La función TEXTO

La función TEXTO permite convertir un valor numérico en un texto. La función contraria es =VALOR. Podemos comprobar que al escribir un número en una celda queda alineado a la derecha, y si se escribe como texto (anteponiéndole una comilla simple) quedará alineado a la izquierda. Ejemplo: '54

La función TEXTO tiene dos argumentos que según la ayuda son:

=TEXTO(valor;formato)

valor: es el número que queremos convertir a texto

formato: es el formato que queremos darle. Es un argumento optativo

Pero la ayuda no se corresponde con la realidad de la función, ya que en la práctica esta función tiene los argumentos cambiados de orden.

Pruebe =TEXTO(;"43")

Pero si prueba =TEXTO("43") obtendrá un error

Otro ejemplo.

Prube a introducir la expresión siguente:

=TEXTO("2-11";"mmm-dd")

obtendrá: nov-02. Aquí si funciona bien la función TEXTO, tal y como esta definida en la ayuda.

Pero si introduce la expresión siguiente:

=TEXTO(;"2-11")

obtendrá: 2-11

Quite las comillas y vea lo que se obtiene con la expresión:

=TEXTO(;2-11)

obtendrá: -9.

Organizar la vista de varios libros (Extensión .xlw)

Podemos tener varios libros abiertos y verlos simultáneamente en mosaico, en vista horizontal o vetical, en cascada. Si deseamos podemos grabar esta forma personalizada de verlos para la próxima vez. Esto se hace grabando un fichero con extensión .xlw. La extensión es el acrónimo de eXceL Workbook. 


Para organizar las ventanas:
* En Excel 2007: Vista, Organizar todo


Para guardar el fichero xlw
* En Excel 2007: Vista, Guardar área de trabajo

Random Walk LogNormal

Un Random Walk es un paseo aleatorio o camino aleatorio. Se genera con alguna función cuya función de distribución de probabilidad nos permita generar números aleatorios que se apoyan en el valor anteriormente calculado. Podríamos utilizar una simple distribución uniforme (véase otro post anterior), pero en este caso vamos ha utilizar una ditribución LogNormal, o también denominada LogoNormal. En Excel existe una función que genera números que se ajustan precisamente a una distribución Lognormal.

=DISTR.LOG.INV(probabilidad;media;desv_estándar) =DISTR.LOG.INV(0,6;LN(10);0,5)

cuyo resultado es: 11,3504642 Con esta función podríamos crear una serie temporal que se comporte como un Random Walk (paseo aleatorio). Por ejemplo, si en la celda B1 ponemos el primer valor (10) y en la celda B2 ponemos la fórmula:

=DISTR.LOG.INV(ALEATORIO();LN(B1);0,02)

copiando la celda B2 hasta la B1000, obtendríamos una serie temporal de mil datos cuyos incrementos se comportan según una distribución Lognormal. Esto que hemos realizado se podría hacer con una macro como la siguiente.

Código:

Sub serie1()
Dim A() As Double
Dim n
Dim i
n = 1000
ReDim A(n)
Randomize
A(0) = 10
For i = 1 To n
A(i) = Application.WorksheetFunction.LogInv(Rnd, LN(A(i - 1)), 0.02)
Next i
For i = 0 To n
Cells(i + 1, "B") = A(i)
Next i
End Sub

Function LN(x)
LN = Log(x) / Log(Exp(1))
End Function

Una variante de la macro anterior es la siguiente.

Código:

Sub serie2()
Dim A As Variant
Dim n
Dim i
n = 1000
Randomize
Range("B1") = 10
A = [B1:B1001]
For i = 2 To n
A(i, 1) = Application.WorksheetFunction.LogInv(Rnd, LN(A(i - 1, 1)), 0.02)
Next i
[B1:B1001] = A
End Sub

Function LN(x)
LN = Log(x) / Log(Exp(1))
End Function

Simulación de una Distribución Normal

Puede descargar el archivo: normalBoxMuller.xlsm

En Excel disponemos de una función de distribución que genera directamente valores ajustados a una distribución normal, pero en esta ocasión vamos a generarlos sin recurrir a las funciones de Excel. La denominada transformada de Box-Müller permite generar una distribución Normal. Para ello se utilizan dos distribuciones Uniformes [0;1], que en Excel se obtienen con la función:

=ALEATORIO()

La función es la siguiente:

=+RAIZ((-2*LN(ALEATORIO())))*SENO(2*PI()*ALEATORIO())

Enlace a la Wikipedia.

Podríamos programar una función que nos de una Normal de media mu y desviación típica sigma.

Código:

Function xNORMAL(mu, sigma)
Dim NORMAL01
Const Pi As Double = 3.14159265358979
Randomize
NORMAL01 = Sqr((-2 * LN(Rnd))) * Sin(2 * Pi * Rnd)
xNORMAL = mu + sigma * NORMAL01
End Function

Function LN(x)
LN = Log(x) / Log(Exp(1))
End Function

Por ejemplo, la función =xNORMAL(100;2) nos proporcionará un número aleatorio que se ajusta a una distribución normal de media 100 y desviación típica 2.

Edad

Para determinar la edad de alguien podemos poner su fecha de nacimiento en la celda A1 y emplear la siguiente fórmula:

=SIFECHA(A1;HOY();"Y")&" Años, "&SIFECHA(A1;HOY();"Ym")&" Meses, "&SIFECHA(A1;HOY();"Md")&" Dias""

La función =HOY() nos da la fecha del sistema.

Último día del mes

Para determinar el último día del mes de febrero de 2008, podemos emplear esta fórmula:

=FECHA(2008;3;1)-1

o bien, esta otra:

=FECHA(2008;3;0)

Observar, que el día que se pone es CERO.

El resultado es: 29/02/2008

y así nos damos cuenta de que el año 2008 es bisiesto.

Actualización

En versiones más recientes de Excel ya disponemos de una función que calcula el último día del mes. Se llama:

=FIN.MES(fecha_inicial;mes)

Si en el argumento mes ponemos cero nos estamos refiriendo al mes de la fecha_inicial, y si ponemos 2 nos referimos a dos meses más respecto a la fecha_inicial.

Por ejemplo, si usted escribe la siguiente función de función sabrá cuantos días tiene el mes actual.

=FIN.MES(HOY();0)

La función =HOY() nos da la fecha del sistema. Si su ordenador está correctamente puesto en fecha la función HOY proporciona el día actual.

Random Walk

Descargar el fichero: RandomWalk.xlsm

Para que el fichero se abra correctamente debe tener habilitadas las macros.

Un Random Walk es un Paseo Aleatorio o Camino aleatorio. Se trata de una serie temporal de valores obtenidos de forma aleatoria que se caracterizan por que son independientes unos respecto a los previos.


Este concepto es muy importante en finanzas. Si la bolsa se comporta según un Random Walk se cumple que el mercado es eficiente y se dice que la bolsa no tiene memoria.

• Hoja1. Resuelto con la función SI donde se analiza si la función ALEATORIO() es mayor o menor de 0,5. Esto equivale a tirar una moneda a cara o cruz, para decidir si el valor se incrementa o se reduce.
• Hoja2. Resuelto con la función que nos proporciona la distribución normal inversa. Ahora el resultado es más suave, no es tan drástico.
• Hoja3. Es un caso similar al anterior pero ahora se utiliza la inversa de la distribución lognormal. La ventaja de este sistema es que se evita que aparezcan valores negativos. Por ejemplo, si no deseamos que aparezca un precio negativo esta sería la función adecuada.
• Hoja4. Este es un ejemplo donde se utiliza la inversa de la distribución normal aplicada a tipos de interés.