UNSW iLab 1 Assessment Instructions 2022 Term 3 Scenario You are evaluating a portfolio of U.S. equities drawn from the S\&P500. The five unique, randomly drawn stocks in your portfolio have the FactSet identifiers - STOCK1: ADBE-US - STOCK2: AXP-US - STOCK3: LUV-US - STOCK4: META-US - STOCK5: NVDA-US In order to complete the assessment, you must answer all the response fields via Moodle iLab1 Questionnaire Link and submit your Excel worksheet as outlined in the "Excel Instructions". Report your answers as decimals (not percentages) EXACTLY following the example provided in the Moodle questionnaire link. In addition, the assessment contains items marked as "Excel Instructions". You must follow these instructions and create the indicated plots in an Excel workbook. This workbook must also contain the worksheets necessary to answer the numerical questions. You must SUBMIT this workbook as required for your assignment to be complete. Note that if you only submit the Excel spreadsheet but do not enter the solution into Moodle questionnaire, you will not receive any marks for this assessment. Similarly, if you only answer the questionnaire but do not submit the spreadsheet you will not receive any marks for this assessment. Efficient Frontier Construction - We will first derive the Minimum Variance Frontier (MVF): For a portfolio constructed from your assigned securities, find the portfolio weightings that would minimise its annualised standard deviation/variance of returns at each expected annual portfolio return level between 0% and 30% (in increments of 5% ). What is the minimum attainable standard deviation of annual returns for 14. an expected return level of 0% 15. an expected return level of 5% 16. an expected return level of 10% 17. an expected return level of 15% 18. an expected return level of 20% 19. an expected return level of 25% 20. an expected return level of 30% 21. Excel Instructions: Plot the MVF, clearly label it, and include it in your Excel spreadsheet submission. - Next, we will derive the portfolio weightings for the Global Minimum Variance Portfolio (GMVP): For a portfolio constructed from your assigned securities, find the GMVP security weights that results in the portfolio having the lowest possible variance without any constraint on expected portfolio return. What is the GMVP portfolio weight in 22. STOCK1 23. STOCK2 24. STOCK3 25. STOCK4 26. STOCK 5 - Compute the annualised expected return and annualised standard deviation of the GMVP. 27. annualised expected return? 28. annualised standard deviation? - Now, we can derive the Efficient Frontier by discarding any portfolio that is inefficient: that is, any portfolio on the MVF that has a return lower than the GMVP 29. Excel Instructions: Plot the Efficient Frontier, clearly label it, and include it in your Excel spreadsheet submission. Capital Allocation Line and the Optimal Risky Portfolio P* We will derive the portfolio weightings for the Optimal Risky Portfolio P. This portfolio is the point on the Efficient Frontier that has the highest possible Sharpe Ratio. Use a riskfree rate of 3.00\% APR (i.e., fixed at 0.25% per month) for this analysis. For your assigned stocks, find the security weights for the Optimal Risky Portfolio using solver. What is the portfolio weight in P of 30. STOCK1 31. STOCK2 32. STOCK3 33. STOCK4 34. STOCK5 - Compute the annualised expected return and annualised standard deviation for P : 35. annualised expected return 36. annualised standard deviation - Now, we can derive the Capital Allocation Line by joining the risk-free rate (the yintercept) with P in a line. Note: this should be tangent to your efficient frontier if it is not, then extend your efficient frontier target level expected returns beyond 30% until you have at least one return level greater than the P expected return and they should now be tangent to each other. 37. Excel Instructions: Plot the Capital Allocation Line, clearly label it, and include it in your Excel spreadsheet submission. - Assume the optimal allocation to risky assets y for an investor with utility function U= E(r)21A2 is given by: y=Ap2E(rp)rf The Optimal Complete Portfolio (C) is the portfolio combination of risky assets (composed of P ) and risk-free assets that provides an investor with the highest possible utility, given their level of risk aversion. 38. What is the risk aversion coefficient, A, for Investor I, whose optimal allocation to risky assets y is 100% ? 39. What is investor I's Optimal Complete Portfolio annualised Sharpe Ratio? - Investor J's Optimal Complete Portfolio has an annualised standard deviation of 10%. What is Investor J's 40. optimal allocation to risky assets y ? 41. risk aversion coefficient, A ? 42. Optimal Complete Portfolio annualised expected return? 43. Optimal Complete Portfolio annualised Sharpe ratio? 44. What is Investor J's Optimal Complete Portfolio utility score? 45. Excel Instructions: (Optional - you are not required to complete this question) Plot investor J 's indifference curve with the utility score derived in the previous question. Overlay the Capital Allocation Line and efficient frontier, and show C as the point of tangency between the indifference curve and the CAL. Excel File 46. Excel Instructions: Submit your Excel file via the related link on Moodle. Make sure you've included the three required plots described above inside your Excel Workbook. UNSW iLab 1 Assessment Instructions 2022 Term 3 Scenario You are evaluating a portfolio of U.S. equities drawn from the S\&P500. The five unique, randomly drawn stocks in your portfolio have the FactSet identifiers - STOCK1: ADBE-US - STOCK2: AXP-US - STOCK3: LUV-US - STOCK4: META-US - STOCK5: NVDA-US In order to complete the assessment, you must answer all the response fields via Moodle iLab1 Questionnaire Link and submit your Excel worksheet as outlined in the "Excel Instructions". Report your answers as decimals (not percentages) EXACTLY following the example provided in the Moodle questionnaire link. In addition, the assessment contains items marked as "Excel Instructions". You must follow these instructions and create the indicated plots in an Excel workbook. This workbook must also contain the worksheets necessary to answer the numerical questions. You must SUBMIT this workbook as required for your assignment to be complete. Note that if you only submit the Excel spreadsheet but do not enter the solution into Moodle questionnaire, you will not receive any marks for this assessment. Similarly, if you only answer the questionnaire but do not submit the spreadsheet you will not receive any marks for this assessment. Efficient Frontier Construction - We will first derive the Minimum Variance Frontier (MVF): For a portfolio constructed from your assigned securities, find the portfolio weightings that would minimise its annualised standard deviation/variance of returns at each expected annual portfolio return level between 0% and 30% (in increments of 5% ). What is the minimum attainable standard deviation of annual returns for 14. an expected return level of 0% 15. an expected return level of 5% 16. an expected return level of 10% 17. an expected return level of 15% 18. an expected return level of 20% 19. an expected return level of 25% 20. an expected return level of 30% 21. Excel Instructions: Plot the MVF, clearly label it, and include it in your Excel spreadsheet submission. - Next, we will derive the portfolio weightings for the Global Minimum Variance Portfolio (GMVP): For a portfolio constructed from your assigned securities, find the GMVP security weights that results in the portfolio having the lowest possible variance without any constraint on expected portfolio return. What is the GMVP portfolio weight in 22. STOCK1 23. STOCK2 24. STOCK3 25. STOCK4 26. STOCK 5 - Compute the annualised expected return and annualised standard deviation of the GMVP. 27. annualised expected return? 28. annualised standard deviation? - Now, we can derive the Efficient Frontier by discarding any portfolio that is inefficient: that is, any portfolio on the MVF that has a return lower than the GMVP 29. Excel Instructions: Plot the Efficient Frontier, clearly label it, and include it in your Excel spreadsheet submission. Capital Allocation Line and the Optimal Risky Portfolio P* We will derive the portfolio weightings for the Optimal Risky Portfolio P. This portfolio is the point on the Efficient Frontier that has the highest possible Sharpe Ratio. Use a riskfree rate of 3.00\% APR (i.e., fixed at 0.25% per month) for this analysis. For your assigned stocks, find the security weights for the Optimal Risky Portfolio using solver. What is the portfolio weight in P of 30. STOCK1 31. STOCK2 32. STOCK3 33. STOCK4 34. STOCK5 - Compute the annualised expected return and annualised standard deviation for P : 35. annualised expected return 36. annualised standard deviation - Now, we can derive the Capital Allocation Line by joining the risk-free rate (the yintercept) with P in a line. Note: this should be tangent to your efficient frontier if it is not, then extend your efficient frontier target level expected returns beyond 30% until you have at least one return level greater than the P expected return and they should now be tangent to each other. 37. Excel Instructions: Plot the Capital Allocation Line, clearly label it, and include it in your Excel spreadsheet submission. - Assume the optimal allocation to risky assets y for an investor with utility function U= E(r)21A2 is given by: y=Ap2E(rp)rf The Optimal Complete Portfolio (C) is the portfolio combination of risky assets (composed of P ) and risk-free assets that provides an investor with the highest possible utility, given their level of risk aversion. 38. What is the risk aversion coefficient, A, for Investor I, whose optimal allocation to risky assets y is 100% ? 39. What is investor I's Optimal Complete Portfolio annualised Sharpe Ratio? - Investor J's Optimal Complete Portfolio has an annualised standard deviation of 10%. What is Investor J's 40. optimal allocation to risky assets y ? 41. risk aversion coefficient, A ? 42. Optimal Complete Portfolio annualised expected return? 43. Optimal Complete Portfolio annualised Sharpe ratio? 44. What is Investor J's Optimal Complete Portfolio utility score? 45. Excel Instructions: (Optional - you are not required to complete this question) Plot investor J 's indifference curve with the utility score derived in the previous question. Overlay the Capital Allocation Line and efficient frontier, and show C as the point of tangency between the indifference curve and the CAL. Excel File 46. Excel Instructions: Submit your Excel file via the related link on Moodle. Make sure you've included the three required plots described above inside your Excel Workbook