This assignment requires you to use real data from finance.yahoo.com to answer the following questions. you can find data under the markets tab.

1. Pick 3 stocks from the Most Actives List and download the last three years of monthly adjusted prices for these stocks and S&P 500 Index table 1

2. Calculate the monthly returns of these three stocks in table 1.

3. Calculate the standard deviation of returns for three stocks and a portfolio with equal investment in three stocks using Table 5. Use excel to check your answer and enter your findings in table 2.

3. calculate the beta, market variance and firm specific variance for three stocks and a portfolio with equal investment in three stocks using trend function in table 4.

4. Use the CORREL Function to calculate the correlation coefficient of monthly returns for each pair stock and enter in table 3. which pair provides the greatest gain from diversification? why?

PLEASE SHOW ALL FORMULAS AND IDENTIFY THE THREE STOCKS YOU SHOW. SHOW FORMULAS IN EXCEL!!

Clipboard Font Alignment K73 X fr E G H B D 1 2 FINANCE ON THE WEB ASSIGNMENT 2. Risk and Return This assignment requires you to use real data from finance.yahoo.com to answer the following questions. You can find the data under the Markets tab 3 4 5 1. Pick 3 stocks from the Most Actives List and download the last three years of monthly adjusted prices 6 for these stocks and S&P 500 index to Table 1 7 2. Calculate the monthly retuns of these three stocks in Table 1 8 TABLE 1 Adj Price S&P 500 Adj Price Adj Price Adi Price Stock 1 Stock 2 Stock 3 Monthly Monthly Return Return S&P 500 Stock 1 Monthly Return Stock 2 Monthly Return Stock 3 10 Date 11 0 12 13 14 15 16 17 18 19 20 21 22 23 24 1 2 3 4 5 6 7 8 9 10 11 12 13 Risk Sheet1 + B 9 Formatos table 2 Der PA 1 WA OA $ 96 28 Cell Styles Format ch Coboard rent Alime Number 3 > M b de for the day with usmentin time Sed the custo STOV the Counction to calculate the correlation content of the forthelook in unter Table whick pair provides the rest is from die Anwestos) 15 TABLE 2 Uw Table stock St UNITEVE Stock Stock Hestfolie stock 1 Portfolio Table 3 Monthly spected Montandard Iaeviation Arvulpted Correlation stock Creation Stocks Cotion stock Amanda loc them for the spot with TABLE4 RELA General Conditional Fom Paste B I VA A $ 2 Format as Table A > % - 8& Cell Styles Clipboard Font Alignment K73 Number C 62 G Calculate the beta, market anance and time variance for three stocks and a portfolio with equal investment in three stocksuing the HD function in Table BE TABLE 4 10 7 Stock 1 Stock Portfolio 34 BETA R Market Variance im Specific Variance 7 12 TABLES Monthly Stock Stock 2 Monthly Monthly Portfolio Heturn Return Stock 0 Stock Squared Date Portfolio Mock 1 FOL_Squared Deviation Stock Squared Deviation Portfolio Squared Deviation 1 2 0 10 Risk Sheet Clipboard Font Alignment K73 X fr E G H B D 1 2 FINANCE ON THE WEB ASSIGNMENT 2. Risk and Return This assignment requires you to use real data from finance.yahoo.com to answer the following questions. You can find the data under the Markets tab 3 4 5 1. Pick 3 stocks from the Most Actives List and download the last three years of monthly adjusted prices 6 for these stocks and S&P 500 index to Table 1 7 2. Calculate the monthly retuns of these three stocks in Table 1 8 TABLE 1 Adj Price S&P 500 Adj Price Adj Price Adi Price Stock 1 Stock 2 Stock 3 Monthly Monthly Return Return S&P 500 Stock 1 Monthly Return Stock 2 Monthly Return Stock 3 10 Date 11 0 12 13 14 15 16 17 18 19 20 21 22 23 24 1 2 3 4 5 6 7 8 9 10 11 12 13 Risk Sheet1 + B 9 Formatos table 2 Der PA 1 WA OA $ 96 28 Cell Styles Format ch Coboard rent Alime Number 3 > M b de for the day with usmentin time Sed the custo STOV the Counction to calculate the correlation content of the forthelook in unter Table whick pair provides the rest is from die Anwestos) 15 TABLE 2 Uw Table stock St UNITEVE Stock Stock Hestfolie stock 1 Portfolio Table 3 Monthly spected Montandard Iaeviation Arvulpted Correlation stock Creation Stocks Cotion stock Amanda loc them for the spot with TABLE4 RELA General Conditional Fom Paste B I VA A $ 2 Format as Table A > % - 8& Cell Styles Clipboard Font Alignment K73 Number C 62 G Calculate the beta, market anance and time variance for three stocks and a portfolio with equal investment in three stocksuing the HD function in Table BE TABLE 4 10 7 Stock 1 Stock Portfolio 34 BETA R Market Variance im Specific Variance 7 12 TABLES Monthly Stock Stock 2 Monthly Monthly Portfolio Heturn Return Stock 0 Stock Squared Date Portfolio Mock 1 FOL_Squared Deviation Stock Squared Deviation Portfolio Squared Deviation 1 2 0 10 Risk Sheet