In US date format, 3/14, it’s the so called Pi Day… Let’s enjoy it!
Pi is needed in finance, especially when building pricers as the probability density of the normal distribution is the following.
Yes but, let’s suppose you cannot use a preset function of Pi? Ok, easy route is to use 3.14159265359, this should be precise enough, but really, how to estimates Pi? It’s a very good exercise for programming; an easy problem trains to develop a sense of the art of writing algorithms. Programming is more about thinking about “how to” rather than knowing the language itself.
Let’s pick two random numbers, x and y, reels number between -0.5 and +0.5: how to determine if (x,y) is inside or outside the circle?
Back to primary school! Pythagoras’ theorem: on a right triangle, the square of the hypotenuse is equal to the sum of the squares of the other two sides. It’s all we need.
So our program is going to use this theorem, we are going to calculate how long is the hypotenuse of the right triangle of the following coordinates: (0,0; x,0; x,y), the right angle being obviously on the (x,0) coordinates. If hypotenuse is longer than the radius, (x,y) is outside the circle, if the hypotenuse is shorter than the radius it is inside. Radius is obviously 0.5 here.
Our square has an area of 1, while the circle has an area of:
In other words, π/4 is equal to the proportion of the area inside the circle compared to the area of the square. Let’s do in essence a Monte Carlo simulation. Doing a great amount of test, and thanks to the law of large numbers we should get a good approximation of Pi. Indeed, we’re going to test a lot of different random coordinates (the randomization is fortunately uniform between 0 and 1, so to make it uniform between -0.5 and +0.5 we’llsubstract 0.5) and count those which fall inside the circle and the ratio compared to the total number of simulations should be pretty close to π/4.
Our program is therefore the following:
Let’s use Visual Basic for Application and create a formula called PiApprox. The argument of the function is going to be the number of simulations, obviously a positive integer, let’s call it Simu (but ilet’s defined it as Double because Integer will only go to 32,767 and this may – or may not – be enough). If we’re referring to the chart, we have all we need. A For… Next… will be necessary and a If… Then.. also:
So here is our simple code!
The big question is how many simulations are required. This is the big question in Monte Carlo simulations. Not enough will be highly imprecise but too much will have memory issues. That’s why the variable was defined as Double rather than Integer, to allow to do more than 32,767 simulations.
Fortunately, =Pi() is wholly available in Microsoft Excel but the point was just…
Happy Pi Day!
A bit more insight on VBA training and VBA for finance here! http://www.slideshare.net/vinzjeannnin/vba-for-finance