lunes, 11 de junio de 2018

El método de Montecarlo en Excel

Trataremos hoy el tan extendido Método de Montecarlo.
La esencia del método es demostrar como mediante simulaciones tendentes al infinito podemos aproximar una solución a un modelo de cualquier tipo.

En el ejemplo de hoy calcularemos, al hilo de la entrada anterior, la integral definida de una función:
f(x)=2x2
para el intervalo entre 1 y 3.


Para ello comenzaremos a trabajar sobre un rango alto de celdas, en concreto 20.000.
Así pues nos aprovecharemos de la función ALEATORIO() con la que conseguiremos una serie de números distribuidos uniformemente entre 1 y 3.
Excel genera estos aleatorios a partir del algoritmo Mersenne Twister.

En nuestro ejemplo en el rango A9:A20008 insertamos:
=ALEATORIO.ENTRE(1;2)+ALEATORIO()

Por otra parte, empleando estos valores como las x de nuestra función nos permite, en el rango B9:B20008, incluir la fórmula:
=2*POTENCIA(A9;2)



Para realizar el estudio comparativo generamos una nueva serie aleatoria en el rango C9:C20008 con la fórmula:
=ALEATORIO.ENTRE(0;17)+ALEATORIO()
que devolverá un valor entre 0 y 18, valores entre los que se puede mover nuestra función a estudio.


En último termino comparamos estos valores aleatorios entre 0 y 18 con los obtenidos con nuestra función, y en D9:D20008 insertamos el condicional:
=SI(C9<=B9;C9;0)
que nos dirá cuantos de los puntos distribuidos en el rectángulo entre 1-3 y 0-18 caen por debajo de la línea de nuestra ecuación
f(x)=2x2


Si llevamos estas series a un gráfico de dispersión tendríamos:

El método de Montecarlo en Excel



Al conocer el número de puntos por debajo de la línea de nuestra función, podemos extrapolar la proporción del área total del rectángulo general (1-3 y 0-18) que correspondería precisamente con el área buscada, esto es, con el cálculo de la integral definida.

Mediante un sencillo cálculo:
=CONTAR.SI(D9:D20008;">0")
conoceremos el número de puntos por debajo de la línea, dentro del área a calcular.
Como sabemos el total de puntos del estudio: 20000
la proporción es fácil de calcular dividiendo uno entro otro...

Esta proporción la multiplicaremos por el área total del rectángulo general (base x altura): (3-1)x(18-0) = 36
Llegando a la integral definida entre 1 y 3 de la función...



El valor obtenido se aproximará bastante a esos 17,333 conseguido con el cálculo integral...

Recuerda que a mayor número de simulaciones más aproximado será el valor conseguido!!

No hay comentarios:

Publicar un comentario