Home » Microsoft Excel Custom Essay

# 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 (rT), 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 rT

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 = rT (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)

## Calculate the price of your order

550 words
We'll send you the first draft for approval by September 11, 2018 at 10:52 AM
Total price:
\$26
The price is based on these factors:
Number of pages
Urgency
Basic features
• Free title page and bibliography
• Unlimited revisions
• Plagiarism-free guarantee
• Money-back guarantee
On-demand options
• Writer’s samples
• Part-by-part delivery
• Overnight delivery
• Copies of used sources
Paper format
• 275 words per page
• 12 pt Arial/Times New Roman
• Double line spacing
• Any citation style (APA, MLA, Chicago/Turabian, Harvard)

# Our guarantees

Delivering a high-quality product at a reasonable price is not enough anymore.
That’s why we have developed 5 beneficial guarantees that will make your experience with our service enjoyable, easy, and safe.

### Money-back guarantee

You have to be 100% sure of the quality of your product to give a money-back guarantee. This describes us perfectly. Make sure that this guarantee is totally transparent.

### Zero-plagiarism guarantee

Each paper is composed from scratch, according to your instructions. It is then checked by our plagiarism-detection software. There is no gap where plagiarism could squeeze in.

### Free-revision policy

Thanks to our free revisions, there is no way for you to be unsatisfied. We will work on your paper until you are completely happy with the result.