You are an analyst for a fund management company that specialises in global equity investments. Your manager is interested in the gains from diversification into international markets for UK investors, and has asked you to analyse this in the mean-variance framework. You are required to calculate risk and return characteristics using certain functions in Excel on a series of financial indices that they are available to you. Your excel spreadsheet should be submitted by 14:00pm on Friday 18th February 2022. REQUIREMENTS: In the Excel spreadsheet available in the Assignment sub-folder in LEARN, Total Return Index monthly observations are available for sixteen International Market Indices, with data coverage from 31/12/1999- 29/12/2017 PART A: CALCULATIONS IN EXCEL (FOR THE ENTIRE SERIES OF MONTHLY OBSERVATIONS GIVEN) 1) Select EIGHT indices and calculate the monthly returns (on either discrete basis or continuously compounding basis). (5 marks) 2) For the EIGHT indices used above in part a1), calculate the variances and standard deviations using the relevant functions in Excel. (5 marks) 3) For the EIGHT indices used in part a1), calculate the covariances using the relevant functions in Excel. (2 marks) 4) For the EIGHT indices used in part a1), calculate the correlations. (2 marks) 5) Create FIVE portfolios based on the EIGHT chosen indices with weights of your choice, each portfolio consisting of two or more indices. (i.e. Portfolio 1 consists of FTSE All Share with 30% weight and DAX with 70% weight, Portfolio 2 consists of NYSE with 50% weight and FTSE AIL Share with 50% weight and so on. Calculate the returns, variance and standard deviations of those FIVE portfolios. (6 marks) You are an analyst for a fund management company that specialises in global equity investments. Your manager is interested in the gains from diversification into international markets for UK investors, and has asked you to analyse this in the mean-variance framework. You are required to calculate risk and return characteristics using certain functions in Excel on a series of financial indices that they are available to you. Your excel spreadsheet should be submitted by 14:00pm on Friday 18th February 2022. REQUIREMENTS: In the Excel spreadsheet available in the Assignment sub-folder in LEARN, Total Return Index monthly observations are available for sixteen International Market Indices, with data coverage from 31/12/1999- 29/12/2017 PART A: CALCULATIONS IN EXCEL (FOR THE ENTIRE SERIES OF MONTHLY OBSERVATIONS GIVEN) 1) Select EIGHT indices and calculate the monthly returns (on either discrete basis or continuously compounding basis). (5 marks) 2) For the EIGHT indices used above in part a1), calculate the variances and standard deviations using the relevant functions in Excel. (5 marks) 3) For the EIGHT indices used in part a1), calculate the covariances using the relevant functions in Excel. (2 marks) 4) For the EIGHT indices used in part a1), calculate the correlations. (2 marks) 5) Create FIVE portfolios based on the EIGHT chosen indices with weights of your choice, each portfolio consisting of two or more indices. (i.e. Portfolio 1 consists of FTSE All Share with 30% weight and DAX with 70% weight, Portfolio 2 consists of NYSE with 50% weight and FTSE AIL Share with 50% weight and so on. Calculate the returns, variance and standard deviations of those FIVE portfolios. (6 marks)
