Please help me with this excel assignment. Thank you!
COMM374: APPLIED FINANCIAL MARKETS (Term 2 2015) Team Assignment Properties of Returns Team Assignment How to submit the assignment? This assignment will be marked. Submit one solution per a group of 2 or 3 students from the same section of the course. All solutions to all questions below shall be in one Excel file, including explanations we are asking you to produce. Name the sheets in the Excel file so that it is easy to see all steps of your analysis and answers to individual tasks below. E-mail the Excel file to Jiri (e-mail: jiri.knesl@sauder.ubc.ca) no later than 10pm on February 1st, 2014. In the subject line of the e-mail, put "Assignment01". In the body of the e-mail, put names of the group members, and their student numbers and course section numbers. Provide names of the group members, and their student numbers and course section numbers in the first sheet of the Excel file. Name the Excel file using the convention "Assignment01_???.xlsx", where "???" stands for your section number followed by the family name of the group member who is sending the e-mail. Make sure that the Excel file is no bigger than 5MB. The file shall not contain any unnecessary data or computations. Background reading for this assignment is: Article in the Financial Analysts Journal by John Y. Campbell and Luis M. Viceira titled \"The Term Structure of Risk-Return Trade-Off\" available through web link http://www.cfapubs.org/doi/pdfplus/10.2469/faj.v61.n1.2682 The best assignments will provide accurate and informative statistics, presented in a user-friendly format, and will give useful economic interpretations of the findings. Points may be deducted if answers from two different teams look identical. From WRDS (CRSP / Annual Update / Index / S&P 500 Index), retrieve valueweighted historical realized with-dividends returns for the S&P 500 index for the period February 1987 to December 2012 at monthly frequency. Data For the same period, retrieve the historical monthly realized returns for US treasury securities from WRDS (CRSP / Annual Update / Index / Treasury and Inflation). Obtain returns for the following maturities: 30-day, 5-year maturity. For the same period, retrieve monthly rates of inflation as measured by the Consumer Price Index (CPI) from WRDS (CRSP / Annual Update / Index / Treasury and Inflation). -1- COMM374: APPLIED FINANCIAL MARKETS (Term 2 2015) Team Assignment For the same period, retrieve historical monthly return on S&P/Case-Shiller U.S National Home Price index (CS from now on). You can download the time series of the index prices from http://www.spindices.com/indices/real-estate/sp-caseshiller-us-national-home-price-index by clicking on the \"Export\" button in the graph. Task 1 Calculate monthly gross returns from the Case-Shiller price index level. This gives the nominal monthly CS gross return. Convert the returns on the S&P 500 index, the US treasury securities and S&P/CaseShiller U.S National Home Price index into real monthly gross returns. Use the following relationship between nominal returns and real returns you learned in COMM371 course: R t real = R t nominal / (1 + Inflation rate t ), where R t real is real gross return and R t nominal is nominal gross return. (Note: All returns you obtained from WRDS are nominal.) Convert real monthly gross returns into real monthly log returns. For the log real return series of each asset, calculate the mean return, variance of returns, and the standard deviation of returns for investment horizons of 1, 2, 3, 6, 12, 24, 36, and 60 months. Task 2 Create a single Excel plot depicting the relationship between the expected return (yaxis) and the investment horizon (x-axis). Expected returns of all assets shall be plotted in one plot. Create a single Excel plot depicting the relationship between risk measured by the standard deviation of returns (y-axis) and the investment horizon (x-axis). Standard deviations of returns of all assets shall be plotted in one plot. Create a single Excel plot depicting the relationship between the expected return/standard deviation ratio (Sharpe ratio) (y-axis) and the investment horizon (x-axis). Expected return/standard deviation ratios of all assets shall be plotted in one plot. Describe the most important patterns in each of the three figures. Keep your answers short to approximately two sentences per figure. Does risk increase with the horizon equally rapidly for all assets? How does the risk-return tradeoff change with the investment horizon for bonds and stocks? Task 3 -2- COMM374: APPLIED FINANCIAL MARKETS (Term 2 2015) Team Assignment For the log real return series of each asset, calculate 1, 6, 24, and 60 months variance ratios. Create a single Excel plot depicting the relationship between variance ratios (y-axis) and the investment horizon (x-axis). Variance ratios of all assets shall be plotted in one figure. Produce a one-paragraph summary that contains answers to the following questions, written so that a non-specialist in finance can understand them. a) Which assets are riskier in the short-run? Which assets are riskier in the long-run? b) Use the figure to analyze return predictability of real estate returns. How could an investor form a trading strategy to take advantage of the uncovered predictability? c) Which issues do you see with implementing the trading strategy? The following two tasks focus on optimal portfolio choice over different investment horizons. Consider investment horizons of 1 month, 2 years and 5 years. Task 4 For this purpose, using the data you obtained above, create monthly series of net real returns on the following two assets: the S&P/Case-Shiller U.S National Home Price index and the S&P 500 index for each investment horizon. Form portfolios in these two assets. In particular, consider a portfolio that allocates % to the S&P 500 index and (1-)% to the S&P/Case-Shiller U.S National Home Price index. Consider values of to be 0, 10, 20, 30, 40, 50, 60, 70, 80, 90, 100 percent (i.e., you will consider 11 different portfolios). Create monthly time series of net real returns on each portfolio for each investment horizon. Create a single Excel plot that shows the portfolio weight on stocks, %, on the xaxis and the standard deviation of net real portfolio returns on the y-axis. The plot should contain three lines, one for each investment horizon (1 month, 2 year, 5 years). For each investment horizon, compute the percentage increase in the portfolio risk (measured by the standard deviation of portfolio returns) when you split your wealth equally into stocks and S&P/Case-Shiller U.S National Home Price index, compared to putting all wealth into S&P/Case-Shiller U.S National Home Price index only. For each investment horizon, compute the percentage increase in the portfolio risk when you split your wealth 10% into stocks and 90% into S&P/Case-Shiller U.S National Home Price index, compared to investing into S&P/Case-Shiller U.S National Home Price index only. Tabulate the results. Explain the intuition of your results at different horizons in one paragraph. -3- COMM374: APPLIED FINANCIAL MARKETS (Term 2 2015) Team Assignment Among the 11 portfolios, at each of the three investment horizons, which portfolio has the highest mean-to-standard deviation (Sharpe) ratio? Please provide the Sharpe ratio-maximizing portfolio weight for each investment horizon. Task 5 Create a single Excel plot that shows the percentage asset allocations (i.e., the portfolio weight on the S&P 500 index, , and the weight on the S&P/Case-Shiller U.S National Home Price index, 1-), of these three portfolios. You should show the return horizon on the x-axis and the percentage allocation into each asset on the yaxis. Use clustered column chart in Excel that to show all percentage allocations in one single plot. Write one paragraph to explain your findings. Summarize how the asset allocation differs with investment horizon. This task investigates the optimal portfolio an investor might have chosen at the end of 2005. For this task, you should use the same data as before, but ignore any data after December 2005. Based on this data, re-do Task 2 and Task 5 (for Task 5 you need to create the portfolios as described in Task 4 but do not report the results from Task 4 again). Compare the results to the results of Task 2 and Task 5 - i.e. briefly comment on the changes in the results. How does the weight of S&P500 change in the portfolio with the highest Sharpe ratio for each investment horizon? Are the changes (if any) driven mainly by the changes in the characteristics of the return on S&P500 or on the S&P/Case-Shiller U.S National Home Price index? Task 6 -4
