For the specific assignment due date, please visit the Coursework page.
Important Note
Portfolio Report #2 is time consuming, so I advise you to start working on it right away.
For Portfolio Report #2 you will continue working on your client’s portfolio but will find a new optimal portfolio through the Markowitz Optimization Model, and then will apply single (CAPM) and multi-index models. You will evaluate the performance of your client’s original portfolio as well as the new portfolio. Then you will compare both of portfolios to the chosen benchmark. You might want to refer to Section 7.5 “A Spreadsheet Model” of your textbook to help you with construction of the minimum variance portfolio frontier.
You may also use the file:Constructing Portfolio Frontier using Solver – Example.xls,as a template for your own portfolio of risky assets.
Format and Submission Guidelines
Like the first report, Portfolio Report #2 should be descriptive and opinionated. In evaluating your report I will take into account how clearly and concisely your thoughts are articulated with an effective and appropriate use of financial terms. As always, make sure to check for grammar and spelling. Make use of diagrams, (pie) charts, tables, table of contents, etc. Your report should be single spaced. There is no specific length constraint for the second report. Do not provide any raw data such as your security prices and daily returns in your main report butdo submit your Excel files containing your data and calculations.
Important Note
Your mark for Portfolio Report #2 will only be assigned upon the receipt of your rough work and calculations contained in your Excel file(s). Failure to provide your Excel file(s) will result in a zero mark for Portfolio Report #2.
Please submit your report in Word (.docx or .doc) or Rich Text Format (.rtf) format using the Dropbox link located in the top navigation bar of the course website. In addition, submit your Excel file (.xls or .xlsx) containing your data and calculations. Remember to include anAssignment Cover Sheet. If your Excel and Word/RTF files are large, you can submit your files archived (.rar and .zip) or as a PDF file. If you have difficulty submitting your assignments please contact Technical Support.
Assignment Instructions
Portfolio Report #2 will be worth a total of 70 points and must include the following sections.Please read the directions carefully.
CAPM BETA’s (10 points)
Calculate beta’s for each of the security in your portfolio by fitting a linear regression. You will need a vector of market returns. Please choose appropriate measure of market return. You might want to use Data Analysis -> Regression. Report your finding in a table, as follows: Note: If you are getting a poor fit (very low R^2) or insignificant betas (p-values for betas are greater than 0.05) you might want to try a different market proxy.
What can you tell about each of the betas? intercepts? Are they significant? Was it a good fit?
APT/Multi-Index model (20 points)
Use a multi-index model and add three or more additional systematic factors of your choice and discuss whether that had improved your CAMP-based model and whether the new factors are significant of not. Again, you might want to use Data Analysis -> Regression.
Stock
ß1
p-value
ß2
p-value
…
Adj R2
F-signif
…
Construct a correlation coefficient matrix for your factors (market index included)
Make sure the correlation between your factors is close to zero: FACTORS MUST BE UNCORRELATED WITH EACH OTHER TO PROVIDE A SOUND RESULT! (Multicolinearity problem). Include a correlation coefficient matrix for your independent variables.
Construct a table to compare your historical returns to returns predicted by the CAPM and Multi-index model of your choice, to your opinion which model outperformed the other?
Stock
Historical returns
CAPM returns
MULTI-Index returns
A
B
C etc…
Constructing Portfolio and Efficient Frontiers (20 points)
You may use the following Excel file as a template for your calculations. Solver’s objective and constraints have already had been set up. You will, however, need to extend this spreadsheet to the number of securities included in your own portfolio.
Template:Constructing Portfolio Frontier using Solver
Assume short sales are not allowed:
Find global minimum variance (GMV) portfolio and report its composition.
Find optimal risky portfolio and report its composition.
In a single.uoguelph.ca/content/enforced/159741-ECON_3660_DE_S13/images/Evaluation_clip_image006.gif?_&d2lSessionVal=xyysBs7ZZnUD7WvpwrFZk3fM8″>graph plot the following:
minimum variance portfolio frontier;
efficient portfolio frontier;
global minimum variance portfolio;
optimal risky portfolio;
all of the original securities;
risk-free rate;
your original portfolio;
construct CML, derive and report CML equation;
The graph must be clear and distinct. Use colors, label, legends, etc… Use a whole page if needed.
For the sake of the report, it would be advisable not to acquire any new assets at this point.
Let the original portfolio be your current portfolio holding; calculate, record and report your security composition along with the weights.
Let the new portfolio (no risk free) be a portfolio found on the efficient frontier (constructed based on composition of your original portfolio) with the same risk level (that is the same standard deviation) as your original portfolio but with a maximum possible E[r]. Calculate, record and report your new weights. Keep track of those transaction costs! Was it worth rebalancing your portfolio after all if you take transaction costs into account?
Let the new portfolio (with risk free) be a portfolio found on the efficient frontier (constructed based on composition of your original portfolio and availability of a risk free asset) with the same risk level (that is the same standard deviation) as your original portfolio but with a maximum possible E[r]. Calculate, record and report your new weights. Note that you can now land or borrow money at the risk free rate of return!
In a single.uoguelph.ca/content/enforced/159741-ECON_3660_DE_S13/images/Evaluation_clip_image008.gif?_&d2lSessionVal=xyysBs7ZZnUD7WvpwrFZk3fM8″>graph:
construct SML, derive and report SML equation;
plot each of the securities;
plot your original portfolio
plot minimum variance portfolio
plot two of your new portfolio
Discuss whether any of your securities and portfolio under or overvalued assuming that CAPM hold of course.
Evaluation of Investment Performance (20 points)
The following returns must be expressed as annual effective rates:
Calculate a simplistic after tax return based on the following formula (make sure you annualize it after calculating!, report your tax calculations – (for an example of calculating taxes on investment return see below Excel file), list each of your cash inflows/outflows, assume taxes are paid at the end of the stock simulation period which could easily be the time you start to finalize your Portfolio Report 2 submissions. Note that you pay tax on capital gains, dividends, interest…etc.):