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)

Place your order
(550 words)

Approximate price: $22

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:
Academic level
Number of pages
Urgency
Basic features
  • Free title page and bibliography
  • Unlimited revisions
  • Plagiarism-free guarantee
  • Money-back guarantee
  • 24/7 support
On-demand options
  • Writer’s samples
  • Part-by-part delivery
  • Overnight delivery
  • Copies of used sources
  • Expert Proofreading
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.

Read more

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.

Read more

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.

Read more

Privacy policy

Your email is safe, as we store it according to international data protection rules. Your bank details are secure, as we use only reliable payment systems.

Read more

Fair-cooperation guarantee

By sending us your money, you buy the service we provide. Check out our terms and conditions if you prefer business talks to be laid out in official language.

Read more
Uncategorized