For The Specific Assignment Due Date Please Visit The Coursework Page

Portfolio Report #2

For the specific assignment due date, please visit the Coursework page.

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.

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.

1. CAPM BETA’s (10 points)
1. 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.

.uoguelph.ca/content/enforced/159741-ECON_3660_DE_S13/images/Portfolio_Report_2-section1a.gif?_&d2lSessionVal=xyysBs7ZZnUD7WvpwrFZk3fM8″>

2. What can you tell about each of the betas? intercepts? Are they significant? Was it a good fit?
2. APT/Multi-Index model (20 points)
1. 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
…

2. Construct a correlation coefficient matrix for your factors (market index included)
3. 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.
4. 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…

3. 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.
1. 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.

2. Let the original portfolio be your current portfolio holding; calculate, record and report your security composition along with the weights.
3. 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?
4. 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!
5. In a single.uoguelph.ca/content/enforced/159741-ECON_3660_DE_S13/images/Evaluation_clip_image008.gif?_&d2lSessionVal=xyysBs7ZZnUD7WvpwrFZk3fM8″>graph:
1. construct SML, derive and report SML equation;
2. plot each of the securities;
3. plot your original portfolio
4. plot minimum variance portfolio
5. plot two of your new portfolio

Discuss whether any of your securities and portfolio under or overvalued assuming that CAPM hold of course.

4. Evaluation of Investment Performance (20 points)

The following returns must be expressed as annual effective rates:

1. 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.):

.uoguelph.ca/content/enforced/159741-ECON_3660_DE_S13/images/Evaluation_clip_image010.gif?_&d2lSessionVal=xyysBs7ZZnUD7WvpwrFZk3fM8″>

Example:Calculating Taxes on Investment Returns

2. Both, for your original and your new portfolio find the following:

.uoguelph.ca/content/enforced/159741-ECON_3660_DE_S13/images/Evaluation_clip_image012.gif?_&d2lSessionVal=xyysBs7ZZnUD7WvpwrFZk3fM8″>

Cash Inflows	Cash Outflows
Dividends		Taxes on dividends
Capital gains		Capital Losses
Coupon payments		Income Tax
Other (specify)		Transaction costs
		Passive Investment fees
		Other (specify)

3. Strategy Evaluation (1-2 pages):
• Discuss the difference between Sharpe’s and Treynor’s measures. Which do you think would be more applicable to your portfolio?
• How did your new portfolio perform as compared to the original portfolio?
• Is your new portfolio superior/inferior to the market?
• What would you expect if the market proxy would change?
• Were you satisfied with your investment strategy? Did you change it during the second half of the portfolio simulation? Why?
• Be sure to discuss what you have lea