Finance and Statistics Models Set (Set 1) contains different topics in finance and statistics from simple model such as computing standard deviation and mean to more advanced models such as Monte Carlo simulation, multivariate standard normail distribution, multiple regression, and option pricing models.


Finance and Statistics Models Set (Set 1)

---

Standard Deviation and Mean
This example is a relatively easy one. The objective is to compute the mean and the standard deviation from an array. In this example, the array is from A1 to A10 of sheet1 of an opened Excel workbook. This program has two function procedures and one sub procedure. The sub procedure reads in the numbers from the array (A1..A10), calls up the function procedures and returns the mean and standard deviation value

Lotto Number Generator
This program involves sampling without replacement. The sub-procedure, DoubleSort( ), sorts the numbers of one array based on the numbers of another array. The sub-procedure, Resample( ), creates numbers from 1 to 54 for array two (Hold2) and 54 random numbers for array one (Hold), and calls in the sub-procedure, DoubleSort( ). When executed,the program will return 5 sets of 6 lotto numbers in column B through F starting from row 13 of the worksheet.

Playing Card Probability
This example intends to answer the following question - what is the probability of getting 3 cards with red hearts and two other cards when 5 cards are drawn from a deck? ...... note that the probability of this particular scenario can be derived using the hypergeometric distribution which is shown below

Normal Distribution Random Number Generator
This example shows how to create random numbers from a normal distribution given the standard deviation and the mean, and then computes the confidence interval given the level of significance, alpha. User can type in the alpha level, number of iterations, mean and standard deviation, then execute the Macro command to obtain the output. This program also generates a 20 class histogram

Monte Carlo Integration
To find the area under a curve, one can use integral calculus. If the curve has no close form, such as the normal curve, then the area can not be derived analytically. However, with today's computer technology, one can use Monte Carlo Integration to achieve such task. The area under a distribution is also known as probability. In this example, we want to compute the area under standard normal probability distribution from 0 to z