4. The Market Model: Calculate weekly excess returns for each of the international funds and the S&P 500 (the market) by subtracting the weekly risk-free rate from each weekly return. For each of the two international funds, create an X-Y scatterplot with the excess returns of the international fund on the Y- axis and the excess returns of the market (S&P) on the X-axis. Add a trendline (security characteristic line) to each graph. Using either the trendline options or other excel functions, calculate the Alpha and Beta for each of the international funds and fill in the related information in the table below. Attach the graph and related calculations as Exhibit 3. Alpha Beta R2 Market Model Estimates S&P 500 International Security 1: Ticker = International Security 2: Ticker = 5. Market vs. Firm-Specific Risks: One of the benefits of the market model is that it allows us to decompose total risk (variance) into two components. Using the equation we discussed in class, calculate the market component of risk and the firm-specific component of risk for each of the securities. Fill in the related table below and Attach any related calculations as Exhibit 4. Total Risk Market Risk Firm-Specific Risk Decomposition of Risk S&P 500 International Security 1: Ticker = International Security 2: Ticker = Which of the three securities is the riskiest based on total risk? How does your answer change if you consider only systematic risk? How does your answer change if you consider only firm-specific risk? 4. The Market Model: Calculate weekly excess returns for each of the international funds and the S&P 500 (the market) by subtracting the weekly risk-free rate from each weekly return. For each of the two international funds, create an X-Y scatterplot with the excess returns of the international fund on the Y- axis and the excess returns of the market (S&P) on the X-axis. Add a trendline (security characteristic line) to each graph. Using either the trendline options or other excel functions, calculate the Alpha and Beta for each of the international funds and fill in the related information in the table below. Attach the graph and related calculations as Exhibit 3. Alpha Beta R2 Market Model Estimates S&P 500 International Security 1: Ticker = International Security 2: Ticker = 5. Market vs. Firm-Specific Risks: One of the benefits of the market model is that it allows us to decompose total risk (variance) into two components. Using the equation we discussed in class, calculate the market component of risk and the firm-specific component of risk for each of the securities. Fill in the related table below and Attach any related calculations as Exhibit 4. Total Risk Market Risk Firm-Specific Risk Decomposition of Risk S&P 500 International Security 1: Ticker = International Security 2: Ticker = Which of the three securities is the riskiest based on total risk? How does your answer change if you consider only systematic risk? How does your answer change if you consider only firm-specific risk