FIN 406
Final Project
The purpose
of this project is to create a 3 asset efficient frontier using Microsoft
Excel. This is very similar to the 2
asset example that we created as an in-class example.
For this
project you will be required to do the following:
·
Using finance.yahoo.com download 10 years of
monthly price data for 3 stocks into Excel.
You may pick any three stocks you like, however stocks that are in
different industries will give you the best graphical results.
·
Calculate the monthly returns for each stock.
·
Calculate the variance/standard deviation for
each individual stock. You should
calculate one stock using the long method that we did in class. For the other two stocks you can use the
Excel built in functions.
·
Calculate the covariance between each
asset. You should calculate one
covariance using the long method that we did in class. For the other two, you can use the Excel
built in function.
·
Set up the Variance/Covariance matrix and
vectors for the weights and returns.
·
Using Matrix operations in Excel, calculate
portfolio Variance/Standard Deviation and Returns.
·
Graph Each Asset
·
Using Solver, calculate (and graph) the minimum
variance portfolio.
·
Using Solver, calculate (and graph) the
efficient frontier. Pay attention to the
range, and adjust accordingly.
·
Create (and graph) 25 random possible portfolios
The Assignment will be out of 100 points and the grading
will be as follows:
Downloading
stock data – 5 points
Calculating
Stock Returns – 5 points
Calculating
Variance/StDev – 10 points
Calculating
Covariance – 10 points
Setting up
Solver Functionality (Vectors, Var/Covar Matrix, etc…) – 20 points
Calculating
Minimum Variance Portfolio – 5 points
Calculating
Efficient Frontier – 10 points
Calculating
Random Portfolios – 5 points
Graphing Individual
Assets – 5 points
Graphing
Minimum Variance Portfolio – 5 points
Graphing
Efficient Frontier – 10 points
Graphing
Random Portfolios – 10 points
Last Completed Projects
| topic title | academic level | Writer | delivered |
|---|
