Homework 2


(Functions) 1.  Create your own function to determine the Range in a set of values in a spreadsheet.  That is you want to determine the difference between the largest and smallest value.

Make sure you do not use the built-in Excel Max or Min functions in your solution.  This exercise is intended to push you through a few aspects of using Visual Basic to problem solve and develop your own functions.


(Portfolio Risk) 2.  Consider the following two stocks with past annual performances given in the two tables.  The first table represents stock in an Extreme Sports Paraphenalia company.  The second stock represents a Cigar distribution company.
Year Growth
1995 20%
1996 11%
1997 5%
1998 18%
1999 12%
Year Growth
1995 5%
1996 12%
1997 15%
1998 11%
1999 3%
Now you want to work with these two stocks like we did in class.
  1. Format a spreadsheet similarly to what was done in class and use this data.
  2. Calculate the mean, standard deviation and coefficient of variation for each stock.
  3. Determine the correlation between the two stocks.
  4. Consider a portfolio of the two stocks where different percentages of your money are spent on each stock.  Start at 10% for the first stock and then increase this in increments of 10% through the obvious range of possible combinations.  Make sure to output the mean, standard deviation and coefficient of variation of each of these combinations.
  5. Create a chart that illustrates which combination minimizes risk.


(Forms) 3. Modify the Annuities form and event code we developed in class to include some additional features.

  1. You want to insert a combo box in the required inputs that has a label telling the user to select the number of payments they will make each year.  The number of possible payments to make each year will be 1, 2, 4, 6, and 12.