This project compares the performance of a portfolio of domestic stocks to a portfolio that includes bothdomestic and foreign investments.
1. Select three US companies in different industries and three foreign companies. Each foreign companymust be from one of the industries represented by the US companies so all three industries are represented in both the US and foreign sample. The three foreign companies must be located in different foreign countries. One foreign companymust be acountryother than Canada, England, Germany, France, Japan and China. All companies must have prices available continuously for the last nine years.
[You can identify publicly traded foreign companies in an industry in yahoo finance by going to the page for the company you are interested in, selecting "industry" on the left-hand menu, clicking on the industry name, and selecting "company index" from the left-hand menu for the industry page, the "Private/Foreign". (You may be able to identify foreign companies just by selecting "competitors" on the menu for the company's page.)
You can also search for American Depository Receipts on "adr.com". Select "search DRs" under the "investors" menu, the select "DR Search" on the "All DRs" bar. Then specify the "region" or "country", the "exchange", and the "sector".]
2. Obtain monthly prices for each company for the last nine years.
[I recommend using the "adjusted closing price" from the historical price link at yahoo finance. It accounts for dividends and for stock splits. If you other website's historical prices or other measures of value, such as "opening price" or "closing price", you must make appropriate adjustments for dividends, stock splits, and etc. yourself.]
3. For each company, calculate (a) the monthly returns for each month in the nine year period,(b) the average monthly return on the company's stock and (c) the standard deviation of the nine years of monthly returns on the company's stock.
4. For an equally weighted portfolio of the three US companies, calculate (a) the monthly portfolio return each month in the nine year period, (b) the average monthly portfolio return for the nine year period and (c) the standard deviation of the nine years of monthlyreturns on the portfolio.
[Note that the standard deviation of monthly returns on the portfolio is not equal to the average of the company's standard deviations, nor is it the standard deviation of the company's average returns or the standard deviation of the standard deviation of the company's returns.]
5. For an equally weighted portfolio of all six companies, calculate (a) the monthly portfolio return each month in the nine year period, (b) the average monthly portfolio return for the nine year period and (c) the standard deviation of the nine years of monthly returns on the portfolio.
6. Contrast the risk of the US portfolio and theglobal portfolio. Explain the reason for your findings.
7.
a. Select a return that is greater than the returns from 4(b) and less than the highest returns from 3(b).
b. Use the solver in excel to determine what percentage of funds should be invested in each US stockto provide the lowest risk domestic portfolio that has the return selected in part a.
8. Use the solver in excel to determine what percentage of funds should be invested in each US and foreign stockthat to provide the lowest risk domestic portfolio that has the return selected in 7(a).
9. Contrast the results for questions 7 and 8 and explain the reasons for your findings.
Attachment:- Data_Sheet.xlsx