Finance and Statistics Models Set (Set 1)

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.

* Two programs in this set also available in Set 3.

Standard Deviation and Mean **|** Lotto Number Generator **|** Playing Card Probability **|**

Normal Distribution Random Number Generator**|** Monte
Carlo Integration **|** Black-Scholes Option Pricing Model - European Call and Put **|** Binomial Option Pricing Model **|** Portfolio Optimization**|** Multiple Regression **|** Bootstrap - A Non-Parametric Approach **|** Multivariate Standard Normal Probability Distribution **|** Monte Carlo
Simulation **|** Option Greeks Based on Black-Scholes Option Pricing Model

Normal Distribution Random 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.

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.

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.

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.

In this example, we derived call and put option price based on the Black-Scholes model. The function procedures are used. The first function, SNorm(z), computes the probability from negative infinity to z under standard normal curve. This function provides results similar to those provided by NORMSDIST( ) on Excel. The second function and the third function compute call and put prices, respectively.

In this example, we derived call and put option price using the binomial model, also known as the Cox-Ross-Rubinstein option model. Note that binomial distribution will become normal when the number of steps (n) becomes large. Hence, when n increases, both of the call and put option prices estimated from the binomial model come close to the prices estimated from the Black-Scholes model.

The objective of this project is to learn how the Nobel Prize winning, Optimal Portfolio Theory (by Harry Markowitz), works in practice. The for the stock portfolio is plotted. The stock portfolio possibilities space is derived by assigning different weights for each stock using a random number generator. To let the computer select the optimal portfolio, the efficient frontierSharpe Ratio is used. In this case, the portfolio corresponding to the largest Sharpe Ratio is the optimal portfolio.

This example demostrates how to run multiple regression using matrix algebra by utilizing the Excel functions in a VBA program. The key Excel functions that are needed for matrix algebra in this example are, MInverse( ) and MMult( ), which perform matrix inversion and matrix multiplication, respectively. Matrix transposition is also used in this program.

Bootstrap is a derivation of Monte Carlo technique introduced by Efron in 1979. It uses the resampling with replacement method (unlike the resampling with no replacement method that we used in the Lotto Number Generator example). It is a convenient tool to extract estimates (such as standard deviation and confident interval) from a non-parametric data set (a data set with no underling distribution is assumed) or estimates that do not have a closed form (cannot be expressed in an equation).

This example is a more advanced version of the Monte Carlo Integration example given earlier. In addition to the material taken from the example mentioned above, this program also utilized a numerical procedure (specifically, Jocobi search method, for derivation of the Eigenvectors and Eigenvalues) and matrix algebra. The procedure for generating random numbers from a multivariate distribution is described in the 4 steps of the example shown later. This program computes probability from a multivariate standard normal probability distribution.

What is a Monte Carlo Simulation? Well, think about it as a computation process that utilized random numbers to derive outcome. So instead of having fixed inputs, probability distributions are assigned to some or all of the inputs. This will generate a probability distribution for the output after the simulation is ran.

Here is an example. A firm that sells product X under a pure/perfect competition market wants to know the probability distribution for the profit of this product and the probability that the firm will loss money when marketing it.

Here is an example. A firm that sells product X under a pure/perfect competition market wants to know the probability distribution for the profit of this product and the probability that the firm will loss money when marketing it.

This program contains option sensitivities (delta, gamma, vega, theta, and rho) formulas and source code. Option sensitivities are also know as the Greeks. They measures how sensitive the option price is toward changes in its parameters. All Greeks are available in user-defined VBA functions with mathematical formulas.

.

XL Modeling

All for only $29.95 per set.

Copyright (c) 2005-2006 EBSIC. All Rights Reserved.

click image for detail