Normal distributions -- an application to risk analysis

The goal of this lab is to compute the Value at Risk (VAR) of a single stock investment. Specifically, we want to determine the worst daily loss in dollars that can be expected with a 99% confidence. Two methods are considered, one of which involves normal probability.

The Excel functions NormDist and NormInv are explained below:
=NormDist(a,m,s,0) computes Pr(a) for the normal distribution with mean m and standard deviation s,
=NormDist(a,m,s,1) computes the cumulative Pr(x≤a) for the normal distribution with mean m and standard deviation s, (Note: the cumulative probability is obtained when the 4th argument is set to 1 instead of 0.)
=NormInv(p,m,s) gives the value of x so that NormDist(x,m,s,1) equals p in the normal distribution with mean m and standard deviation s,

We investigate the daily closing stock prices of IBM for 2008. Download to your computer the file IBM_2008history.xls that gives the closing stock price for each of the 255 trading days of 2008.

1. Plot the 255 daily closing prices for IBM verses time. Use a scale for the prices to display a nice graph.

2. We want to create a frequency distribution and histogram for the daily returns on the stock. The daily return is basically the daily percent of increase of the value. For example, the daily return for 12/31/2008 is found by dividing the difference between the closing prices on 12/31 and 12/30 by the closing price on 12/30.
A. Create a column with the daily return for each day.
B. Create a frequency distribution and a probability distribution for the daily returns by grouping the data from -10% to +10% using intervals of 2%.
C. Create a histogram (column graph) for the probability distribution.
D. In a text box explain the shape of the distribution. Why is this reasonable?

3. Historical VAR calculation. The historical method for calculating the risk at the 99% level is to determine the worst 1% of the daily returns and multiply this by the current value. Here are the steps:
A. Determine the number of data points (or fraction thereof) in the bottom 1%.
B. Determine the daily return corresponding to the bottom 1%. (The Excel function =small(List,i) will give the ith smallest value in the List.)
C. Use the worst 1% daily return found in Part B to estimate the dollar value of the worst 1%.

4. Variance Method to calculate VAR. This method assumes the daily returns follow a normal distribution. Here are the steps:
A. Find the mean and standard deviation of the daily returns.
B. Use the NormInv function to compute the 1%-ile and use this value to estimate the dollar value of the worst 1%.
C. In a text box explain why the values in Problems 3 and 4 are different and explain what these values mean.

5. (Bonus Problem)
Graph the normal probability density function with the mean and standard deviation found in Problem 4A. Use the =NormDist(a,m,s,0) function for this. Superimpose this graph with the histogram in Problem 2C to compare how well the histogram fits a normal distribution.