FINN 3222 Investments Homework 2: Portfolio return, risk, efficient frontier and Sharpe optimal portfolio Total: 6 points Due Wednesday October 31, 2018 (by end of day) Objectives and general instruction The objective of this assignment is to engage you in a "real life" asset allocation exercise. The assignment will require you to apply fundamental concepts of modern portfolio theory to achieve an efficient portfolio allocation to meet certain objectives. It allows you to learn how some of the key concepts discussed in class are derived by using simple examples. This is an Excel assignment, so it is also expected to sharpen your Excel skill. This assignment will use Excel "solver" program. You need to activate "Solver" Add-ins in your Excel. Please refer to 078ca to load the add-in. In order to receive full credit, you should follow the instructions and address the questions listed below in the Excel spreadsheet in a professional way. Specifically In your Excel file, I should see in the formula bar how you use cell formulas to get your results. . Tables should have titles and label clearly the variables you calculated wherever necessary Graph should have a title, x and y axes names, and legend labels. Grading policy: 0.125 points will be deducted for each error (minor or major), until zero points for each question Submission: Each student should upload to Canvas an Excel file that contains your tables and graph. A 10% reduction from full credit per day will be imposed if the assignment is submitted late after the due date. Data: This assignment makes use of annual data for two risky asset classes. The stock index proxies for equities while the bond index proxies for bonds. Annual return values for each of these securities during a 16-year period are provided in a spreadsheet named "HW2 data.xlsx" (hypothetical values). You also need an estimate of the risk-free rate (r). For this case we assume r: 2%. 1. Return Calculations 10.5 pts.]: Using the data in the excel sheet, calculate the Average and Standard Deviation of the returns of stock and bond; calculate the Correlation Coefficient and Covariance between the returns of the two securities. In the Excel file report the spreadsheet showing all of the relevant statistics as Table 1-statistics (refer to the table below for your Excel table set-up). Table 1. Statistics Stock Average Annual Return Standard Deviation of Annual Returns Return Correlation (Stock, Bond) Return Covariance (Stock, Bond) IS FINN 3222 Investments Homework 2: Portfolio return, risk, efficient frontier and Sharpe optimal portfolio Total: 6 points Due Wednesday October 31, 2018 (by end of day) Objectives and general instruction The objective of this assignment is to engage you in a "real life" asset allocation exercise. The assignment will require you to apply fundamental concepts of modern portfolio theory to achieve an efficient portfolio allocation to meet certain objectives. It allows you to learn how some of the key concepts discussed in class are derived by using simple examples. This is an Excel assignment, so it is also expected to sharpen your Excel skill. This assignment will use Excel "solver" program. You need to activate "Solver" Add-ins in your Excel. Please refer to 078ca to load the add-in. In order to receive full credit, you should follow the instructions and address the questions listed below in the Excel spreadsheet in a professional way. Specifically In your Excel file, I should see in the formula bar how you use cell formulas to get your results. . Tables should have titles and label clearly the variables you calculated wherever necessary Graph should have a title, x and y axes names, and legend labels. Grading policy: 0.125 points will be deducted for each error (minor or major), until zero points for each question Submission: Each student should upload to Canvas an Excel file that contains your tables and graph. A 10% reduction from full credit per day will be imposed if the assignment is submitted late after the due date. Data: This assignment makes use of annual data for two risky asset classes. The stock index proxies for equities while the bond index proxies for bonds. Annual return values for each of these securities during a 16-year period are provided in a spreadsheet named "HW2 data.xlsx" (hypothetical values). You also need an estimate of the risk-free rate (r). For this case we assume r: 2%. 1. Return Calculations 10.5 pts.]: Using the data in the excel sheet, calculate the Average and Standard Deviation of the returns of stock and bond; calculate the Correlation Coefficient and Covariance between the returns of the two securities. In the Excel file report the spreadsheet showing all of the relevant statistics as Table 1-statistics (refer to the table below for your Excel table set-up). Table 1. Statistics Stock Average Annual Return Standard Deviation of Annual Returns Return Correlation (Stock, Bond) Return Covariance (Stock, Bond) IS