Developing User Functions to Assess Risk in a Stock
Introduction. In the last two webpages we have talked about risk and returns associated with individual stocks and then with portfolios based on past performance. Now we will examine estimating risk on future returns in another way. It is quite typical to use past performance of a stock to develop some sort of distribution to estimate future returns. We are still going to look at the Stock A and Stock B that we have been working with before. The following tables estimate the probabilities of particular percentage growth rates in the next year. We are not going to get into how these probabilities were estimated. 


How can we compare the gains we might expect to see
for these stocks given this new way of presenting the information?
We can think of this new codification as a discrete distribution. The percentage rate of return is the random variable. The entries in each of the Growth columns is the potential values of the random variable. The entries in the Probability column are the probabilities that the growth will be the corresponding value in the Growth column. But as is always the case it is difficult, if not nearly impossible to compare entire distributions. Comparisons are more successful if they are done between single numbers. Again we will work with expectations. We will again want to use the mean, standard deviation and coefficient of variation. But Excel does not have any built in functions for these performance measures when we are working with distributions. The functions we used in the previous webpages related to sample expectations. The first thing we need to do is refer to a Statistics text to find the formulas for these expectations when we are working with distributions. Then we will need to develop our own functions for computing these performance measures. Expectations for Discrete Distributions. What we are working with in this situation are called discrete distributions. We will now present the formulas for our desired expectations for discrete distributions.
The distribution mean is
The distribution standard deviation is
The distribution coefficient of variation is

Setting Up the Spreadsheet. At this point you want to open a new workbook and fill in the cells so that they look like the following. 
User Defined Function for the
Mean.
Now we need to develop some VisualBasic code to implement the
computation of the expectations in a function. Then we can use
these newly developed functions in a spreadsheet just like we use the
Excel built in functions.
Notice that for computation of the mean of the distribution you need to multiply each value times the probability the random variable takes on that value and then add them up. You can consider the values that the random variable take on to be an array called Values. You can consider the probabilities that the random variable takes on each of these values to be an array called Probabilities. Considering this the following code defines a function that will computer the mean for the discrete distribution when used in a cell formula.
We will not go into a lot of detail in this webpage about this code. We will discuss it in class. Notice the name of the function is MeanDist for the mean of a distribution. Since it is a function and it is defined as single it will return a single precision value. Two arguments need to be passed into the function in order for it to work. The first argument is an array of the values that the random variable can take on. The second argument is an array of the probabilities for each value of the random variable. The value of MeanDist starts out at 0.0 and is then incremented by each value times the probability the random variable will take on that value. After having looped through each item in the array the summing of the increments is completed. You should be familiar with the For ... Next loop from your earlier courses. Now we will implement the function in the UserDefinedRiskFunctions.xls workbook by working through the following steps.



Now your spreadsheet should look like the following. 
User Defined Function for the Standard Deviation.
Now we want to go through very similar steps to develop the User Defined
function for computing the distribution
standard deviation. The following code should be copied
into the code window.
The meaning of the code will be discussed in detail in class. Notice the name of the function is now StdDevDist for the standard deviation of a distribution. Since it is a function and it is defined as single it will return a single precision value. Two arguments need to be passed into the function in order for it to work. The first argument is an array of the values that the random variable can take on. The second argument is an array of the probabilities for each value of the random variable. You need the mean of the distribution in order to compute the standard deviation so the MeanDist function is used to compute the Mean. The value of StdDevDist starts out at 0.0 and is then incremented by the quantity of each value minus the mean squared times the probability the random variable will take on that value. After having looped through each item in the array the summing of the increments is completed. Then since we need the standard deviation we need to take the square root of this sum. You now want to work through the following steps in order to compute the distribution standard deviations on the stock performance estimates.
This will fill in the distribution standard deviations for each stock. Now we will finish this by developing the function for the distribution coefficient of variation. Computing the Distribution Coefficient of Variation. This last computation will go quite quickly considering that the coefficient of variation is formed purely by the mean and standard deviation of the distribution. The code that should be copied into the code window follows.
You want to work through the following steps.
Now your spreadsheet should look like the following. 
The last thing to consider is all of the things that could go wrong when inputting the values for computing the functions. It is important to be able to make sure that the inputs have at least certain levels of validity. These issues will be taken up in the next webpage. 