Building the Model

The data for this model is shown in the table in A27:D50 of the worksheet.

1. Enter the labels for output table: Enter the appropriate labels for the table in G27:I28. To avoid any possible confusion, indicate in the label that the values in the table are for the beginning of the years.

2. Enter formulas to calculate the values of the investments for each year: Start by entering $1 in G30:I30. To calculate the investment value at the beginning of 1983 for small-cap stocks, in G31 enter the formula =G30*(1+B30). Now copy the formula into H31 and I31 and then copy G31:I31 into all the rows down to row number 51. Even though your data ends in 2002, your table needs to extend to 2003 because that row represents the values of the investment for the beginning of 2003 (equivalent to the end of 2002).

3. Calculate the average annual returns: To calculate the average annual return for the small-cap index, in G24 enter the formula =(G51/G30)a(1/21)-1. Note that the period 1982-2002 covers 21 years, not 20. Copy the formula into H24 and I24.

4. Create chart: Create an XY chart with the yearly values for the three assets along the Y-axis and the years along the X-axis. Appropriately format and label the chart.

0 0

Post a comment