Simulation with Excel Series: Lab 2 Estimating Pi

Title: Estimating the value of pi (π) using Monte Carlo Simulation.

Basic Theory:

In this lab, we are trying to find the value of pi(π) using Monte Carlo Simulation. The Monte Carlo simulation deploys the concept of a large collection of random data that satisfy some property to perform simulations in diverse fields including computer science. In this case, we are applying it for Mathematical analysis of estimating the value of pi(π).

Procedure:

For estimating the value of pi(π), we first specify a square area and plots random points inside it. We have used 13,186 points in this simulation (a lot of points!). It is still very simple because we plot the points in the region bounded by -1 to 1 in both X and Y axes. This can be described using a random function in Excel as,

X = 1-2*RAND() and same for y

Now, we plot the points which fall within the region satisfied by the equation of a circle. So, for the circle the description of the Excel formula will be,

X = IF((X^2+Y^2)<1,X,0), Y = IF((X^2+Y^2)<1,Y,0)

This formula shows that the points will be plotted only if they satisfy x²+y²<1 i.e within the radius of the circle. Note that the circle is inside the square bounded by -1 to 1 in both axes, so radius of the circle is also 1. Now we can perform our calculation as,

Area of circle / Area of Square = Points that satisfy circle equation / Total points plotted

π r^2 / (r^2+ r^2+ r^2+ r^2) = Random value / 13186

So, π r^2/4r^2 = COUNTIF(X0:Xn,"<>0" ) / 13186

Here, we found this as π r^2/4 r^2 = 0.785312425                        (from our random data)

π = 4*0.785312425 = 3.141249701

Hence, we found the value of π.

Sample Data:

Table1: Sample Data for Monte Carlo Simulation for finding pi (π).

Rectangle		Circle
X	Y	X	Y
0.700356	-0.32303	0.700356	-0.32303
-0.165	-0.58146	-0.165	-0.58146
0.973976	-0.76557	0	0
-0.92966	0.373582	0	0
0.22543	-0.51423	0.22543	-0.51423
0.853136	-0.11127	0.853136	-0.11127

Output:

Below are the graph plots for the points in the circle and square.

Conclusion:

Here, we could see that the estimated value of π is very close to real value. We can further increase the accuracy of the simulation by increasing the number of random points. However, the excel worksheet will become too slow if we generate a lot of random numbers because of large number of calculations.