project in finance

Investments

Instructions for Diversification Paper

Diversification Analysis Step A - Please use the file “Data.xlsx” for the first part of the project. This file includes the time-series of monthly returns for two securities (HML and UMD). We will perform a diversification analysis for these two securities .

Step B - Make the following calculations and tabulate the results: using the returns data, compute the following statistics: mean return for each security, standard deviation for each security, and the correlation coefficient between the two. You can compute mean or av- erage in Excel by using the function “=average()”, standard deviation using the function “=stdev.s()”, and correlation coefficient using “=correl()”.

Explanation on the average function: https://support.office.com/en-us/article/AVERAGE-function-047bac88-d466-426c-a32b-8f33eb960cf6

Explanation on standard deviation: https://support.office.com/en-us/article/STDEV-function-51fecaaa-231e-4bbb-9230-33650a72c9b0

Explanation on the correlation coefficient function: https://support.office.com/en-us/article/CORREL-function-995dcef7-0c0a-4bed-a3fb-239d7b68ca92

Having computed the statistics, annualize all numbers. This means you need to multiply the averages by 12 (because there are 12 months in a year), and multiply the standard deviation by

√ 12 (because variance increase linearly with time, and standard deviation is the square

root of variance and therefore it increases with square root of time). Now, these statistics will be the inputs for the following section.

All of the following calculations are contained in the Spreadsheet “Two-Security-Diversification” on Canvas. All you need to do is to enter the statistics you calculated in the previous step in the blue colored cells located on top of the spreadsheet.

Change the returns and standard deviation located on th right hand side of “HML” and “UMD” at the top of the “Two-Security-Diversification” file to what you found in the previous sections. Change the correlation coefficient as well. The spreadsheet will compute portfolio characteristics for a range of weight combinations from 10% in HML and 90% in UMD, to 90% in HML and 10% in

1

UMD.

a) What is the expected return, standard deviation and reward to variability for a portfolio that includes 50% of each security. 1

b) Report the weights of each stock, and the portfolio characteristic for the minimum variance portfolio. c) Report the weights of each stock, and the portfolio characteristic for the optimal risky portfolio. d) Comment on your results and give an indication on the weights you might choose to invest in these stocks going forward. Final Instructions 1. Please copy/paste the relevant results and include everything in ONE document (Word, PDF, etc.). I will only grade one Document, not multiple spreadsheets. 2. Please do not include any raw data in your paper. Assume you are preparing a report for your boss/client and she only wants the highlights, not all the messy details. 3. Please make sure your paper is properly formatted (does not include any of my instruc- tions, fonts are consistent, columns are aligned, etc). This should be a professional looking document and I will grade it accordingly.

1You can find this info in the table that has various weights, here we are refering to the row with 0.5 weight in each).

2