Microsoft Excel Custom Essay

Finance 340: Introduction to Investments (Fall 2012)

Excel Assignment #1: Building Efficient Frontier

Objectives:

– Building efficient frontier of three stocks

– Using Excel Solver

There are different ways to construct an efficient frontier.  We will focus on the most intuitive way and use only three stocks.  The Excel file you download (next paragraph) provides a slightly different approach and uses eight stocks.  For a more advanced method, you can talk with your instructor.

Go to the textbook website www.mhhe.com/bkm => choose our textbook => Student edition => Chapter 6 => Excel Spreadsheets.  Download file ‘Efficient Frontier for Many Stocks’.  Use 61 monthly returns of BA, JPM and MCD (from the last worksheet ‘Dow Stocks for Problem’) to build an efficient frontier.

i) We might choose to follow the steps in the Excel file ‘Excel_01_example_hand-on’.

ii) Carry out the calculation by writing Excel expression or using Excel functions.  Please leave these formulas for me.

iii) Calculate average monthly return using function average; standard deviation using stdeva; and correlation between two stock returns using correl.

iv) Annualize monthly returns and standard deviations and convert to percentage as following:

v) Covariance of two stocks can be calculated as

vi) Variance of a portfolio of n stocks can be calculated as

In case of three stocks, we can expand the formula as following:

vii) Expected return of a portfolio of n stocks can be calculated as

In case of three stocks, we can expand the formula as following:

viii) The Solver is an add-in for Excel.  It is used for optimization problems (maximization or minimization).  The Solver also solves linear and non-linear equations.

For each target return (r_T), we will use the Solver to find the weights (the proportion that invested in each of stocks) that minimize portfolio variance (i.e. minimize standard deviation).  Thus we have a constrained minimization problem as following:

For given r_T

Find w1, w2 and w3

To minimize portfolio variance (formula 1 above)

Subject to:       w1+w2+w3 =1 (i.e. invest 100% of money)

Portfolio expected return = r_T (i.e. portfolio return equals the target return)

The solution of this minimization problem represents one point in the efficient frontier.  We will repeat the process for about 10 different target returns.  Connecting all the points, we will have efficient frontier.

ix) Plot the efficient frontier and add the individual stocks (see the example solution).

Good graph has appropriate titles.

x) Email your work to me using Blackboard.  Name your file as following:

your-last-name_your-first-name_excel1. (Please use one student’s name to name the file and write names of the group members in the assignment)