# Course Info Excel Template - DOC

Document Sample

```					                            Generating Efficient Set
The purpose of this assignment is to generate an efficient set. That is, this template
(effsetmatrix_10.xls) will help you to pick the best mix (that which provides the best
returns with a minimum risk) of the 10 stocks of your choice. The Excel template will

Calculate the monthly returns and standard deviation and the correlation coefficients for a
portfolio of 10 stocks. Form an efficient portfolio. No short-sales are allowed. Find the
minimum risk portfolio and find the tangency portfolio.

Getting Started

2) Open SV Connect by double clicking on the icon. Click Enable Macros.
3) Notice that there are two sheets in this workbook. Make sure you are on the
“data” sheet.

share prices using Stockval.

6/30/02.

In the sheet “Data”, fill in your ticker symbols in the range C4 to L4. Treasury bill
rate and S&P 500 are in cells M4 and N4.

Or, if you are using a composite with 10 stocks, make sure you are in cell C4 and
from Stockval menu, choose “Retrieve Ticker Symbols.” Specify the name of your
composite in “All Stocks Within the Given Composite” and click “Across the
Columns”. Click Okay to see the new ticker symbols.

Now, you can click “Update” to get the prices during the specified months.

1
Returns Table

This template calculates automatically the monthly returns in the range from Q7 to
AB48 in the data sheet and then copies these returns to “effset” sheet in the range C5 to
N46.

Creating Tables For Your Efficient Set Calculations

1)   Move to the other worksheet entitled “effset.” View the tables on that spreadsheet.
This template will be used for calculations used to find your efficient set.

2)   Notice that under the returns table in effset sheet, there are tables with Standard
Deviation, Variance, Variance-Covariance Matrix, and Correlations. These values
will also compute automatically.

Using the Solver to Minimize Your Portfolio Risk

1) Go to the “Solution Table” under the correlation table. The percentages here
represent the weights of each security in the portfolio (cells C82 to L82). They
add up to 100%. The initial weights do not matter; that is, you do not have to
change them.

Cells M82, N82, and L82 are the portfolio’s average return, standard deviation
and Sharp ratio respectively:

Cell M82: {=MMULT(C82:L82,TRANSPOSE(C49:L49))}
Cell N82:
{=MMULT(MMULT(MMULT(C82:L82,C54:L63),TRANSPOSE(C82:L82))))}

2) Click Tools/Solver
3) Once the dialog box appears, click the radio button to indicate that you want to
minimize.

2
4) In the “By Changing Cells” field, enter the cells for the percentages of each stock.
That is, highlight these cells and click enter.
5) Leave the constraints as they are set (there should be only two constraints in the
box). But make sure that they make sense. That is, look at the formulas given for
the constraints and verify that they correspond to the locations of your data.

6) Click SOLVE.

7) Click OK on the next menu that appears.

8) The numbers that were generated show you the percentages you should invest in
each security in order to yield the minimum risk.
9) Copy the values only the solution percentages as well as the return and risk of the
solution to the efficient portfolio table in the “Global Min-Risk” row. You will fill
in the rest of the table (the rows for Portfolios 1-6).

3
Using the Solver to Generate More Points on the Efficient Set Curve

1) Next you are going to use the solver to find a few more points on the efficient set
curve. You will do this by specifying returns greater than those for the minimum
risk. These values you choose should rise in about even increments. That is, for
each additional time you use the solver, you will increase the amount of the
return, being careful not to exceed the maximum average returns of the stocks in
your portfolio. The average returns are found at the bottom of the first table on
this sheet.
AEOS has the maximum monthly average return of 2.55%. You may want to use for
the average returns 0.005, 0.01, 0.015, 0.02, 0.022,and 0.025 for the six portfolios.
2) Click tools/solver
3) This time, add more constraints. Do this by clicking the ADD button next to the
Constraints box. Click the arrow to the right of the “Cell Reference” field. This
4) Click on the cell for the Expected Return(M82). Hit Enter. By setting this as the
cell reference, we are forcing the Expected Return to conform to a value we
assign.
5) In the next field over, indicate that you want the cell to EQUAL the amount you
specify in the constraint field. So select the “ = ” sign.

4
6) Go to the “Constraint” field and assign your first increment. Click OK
7)  Click Solve when you are returned back to the Solver menu.
8)  Click okay when you get the Solver Results menu.
9) Copy and paste these solutions under the minimum risk portfolio you created
earlier
10) Create about 5 additional portfolio scenarios (each time increasing the value of
the new constraint) and copy and paste them below the minimum risk portfolio
table..
11) You have now created a sufficient number of portfolios to plot the efficient set.
The chart contained in the worksheet will automatically adjust to your data.

Plotting the Tangency Line and the Capital Allocation Line

1) Now we want to plot the straight line from the risk free rate to the tangency point on
the efficient set curve. To do that, we have to maximize the slope (the Sharp ratio)
of that straight line.

NOTE: The Sharp Ratio comes from the following formula:

[(Expected Return of Portfolio) – Risk Free Rate] / Risk of Portfolio

2) To maximize the Sharp ratio, go to Tools on the tool bar. Click solver.
3) Set the target cell at the Sharp Ratio cell (O82) on the table above
4) Set the solver to maximize.
5) Make sure you delete the last constraint you added in. That is, the only constraints
that should be present are those two that were there originally.
6) Click solve. Click okay on the next menu that appears.
7) Copy your solution for the maximum sharp ratio to the Max Sharp Ratio table. This
solution indicates the percentages you should invest in each security in order to
obtain the maximum Sharp ratio.

5
Note that the model generates two more points on the tangency line: No risk
portfolio (cells M95.N95) and the portfolio with the same risk level as the highest
risk portfolio on the efficient set (M97.N97).

The graph of the efficient set without the risk-free rate and the tangency portfolios
will be automatically plotted. It should have the same general shape as the graph
shown below.

6

```
DOCUMENT INFO
Shared By:
Categories:
Stats:
 views: 17 posted: 3/17/2011 language: English pages: 6
Description: Course Info Excel Template document sample
How are you planning on using Docstoc?