miércoles, 25 de noviembre de 2009

Ultimo valor. Una aplicación de DESREF

Descargar el fichero: ultimo_valor.xlsx

Ultimo valor nos permite calcular el último valor de una columna. Para ello se utiliza la función DESREF y COINCIDIR. Utilizaremos un caso que nos permite gestionar las participaciones de un Fondo de Inversión.

Disponemos de tres tablas y un gráfico.


En las celdas amarillas se introducen los datos y en las verdes se calculan las fórmulas.

La tabla central contiene el valor de una participación en diferentes fechas. Con esta información creamos el gráfico que nos muestra el valor cotizado a lo largo del tiempo de una participación del fondo de inversión. Esta tabla central irá creciendo hacia abajo a medida que se introduzcan nuevos valores en diferentes fechas.

La tabla de la izquierda permite ir anotando los movimientos (compras y ventas) que se producen en la cartera de este fondo de inversión en concreto. Las compras suponen un número positivo de participaciones y las ventas implican un número negativo de participaciones. A medida que se vayan produciendo nuevas compras y ventas de participaciones esta tabla irá creciendo hacia abajo, anotándose una nueva línea por cada operación realizada.


El precio de la celda E5 se obtiene con la expresión:

=DESREF(H4;COINCIDIR(B5;H:H)-4;1)

La función COINCIDIR tiene la siguiente sintaxis:
=COINCIDIR(valor_buscado; matriz_buscada; tipo_de_coincidencia])

Y la función DESREF utiliza los siguientes argumentos:

=DESREF(ref;filas;columnas;alto;ancho)

A la función COINCIDIR tenemos que restarle 4 porque las fechas de la columna H comienzan en la fila 5. Esto supone que los datos deben comenzar siempre en la fila 5, no pudiéndose insertar filas.

En la celda K5 determinamos las participaciones en cartera sumando todos los valores de la columna D. Esto se hace con la expresión:

=SUMA(D:D)
Esto supone que no se deben introducir valores numéricos ni fechas en las celdas
D1 a D4.

La tabla de la derecha contiene tres fórmulas, y es una tabla que no crece. Esto es, no es una base de datos como las dos anteriores sino que esta constituida por tres fórmulas de resultados. En ella se determina el valor actual de la cartea de participaciones de este fondo de inversión concreto. Este valor actual se calcula como el producto del número de participaciones existentes en el momento actual, multiplicado por el último valor conocido de la participación.

La celda L5 se calcula con la expresión:

=DESREF(I4;COINCIDIR(MAX(H:H);H:H)-4;0)
Al ser la columna H la de fechas, su máximo nos da el valor de la fecha correspondiente a la fecha más actual, la más tardía. La función COINCIDIR nos indica cuantas fechas hay en la columna H. Restamos 4 ya que los datos comienzan en la fila 5. Por eso no se deben insertar o eliminar filas antes de la fila 5. La función DESREF nos lleva hasta el último precio de la participación anotado en la columna I. Por tanto la celda L5 contiene el último precio conocido de la participación.

En M5 calculamos el Valor Actual de la cartera simplemente multiplicando el número de participaciones que tenemos en cartera por el último precio conocido de una participación.

=K5*L5

No hay comentarios:

Publicar un comentario