Please help answer these homework questions. Please try your best, anything helps!

FIN 346 - INVESTMENTS: ASSIGNMENT 3 ASSIGNMENT due date: Friday Mar 4, 2016 at 5.00PM. Late grade penalty: 5 % of your grade for every two hours that the assignment is late, for a maximum penalty of 20 %. Unless there are extenuating circumstances, assignments that are received more than 24 hours late will receive a grade of zero. Email me [msbroman@syr.edu] an electronic copy of the excel spreadsheet. One (1) copy per group. Please name your excel spreadsheet as follows: FIN346_A3_LAST_NAME.xlsx. Also make sure to indicate your full (official) names in the sheet \"NAMES\". Q1A) Your client wants to invest in two risky assets (a portfolio of U.S. equities and a portfolio of U.S. long-term corporate bonds) as well as a risk-free asset. Portfolio of U.S. Long-term corporate bonds U.S. equities T-bill 1 month TICKER* E(r) Std. Dev. LQD ITOT 0.533 1.26 0.028 2.42 4.43 In order to achieve her investment objective, the client requires an expected return of 0.7 % per month. Your task is to i) estimate the efficient frontier consisting of the two risky assets, ii) find the optimal risky portfolio (P) and iii) provide the client with details on exactly how she should invest her $100 dollars in the risk-free rate, and the two risky assets. In order to obtain full marks you must show all steps of the calculation (either using excel, or calculation by hand). Q1B) After carefully considering your client's wishes, you decide to convince your client of expanding her investment universe to also include foreign equities in order to improve the diversification and the risk-return trade-off of the optimal portfolio. (The client still wants an expected return of 0.7 %). You recommend the client to invest in the following risky assets: Portfolio of Canadian equities French equities German equities Japanese equities Swiss equities U.K. equities U.S. equities U.S. Long-term corporate bonds TICKER* EWC EWQ EWG EWJ EWL EWU ITOT E(r) 0.88 1.19 1.09 0.91 1.13 1.23 1.26 Std Dev 5.78 6.29 6.26 6.99 5.40 5.55 4.43 Variance 33.41 39.56 39.19 48.86 29.16 30.80 19.62 LQD 0.53 2.42 5.88 T-bill 1 month 0.028 *: In order to invest in these risky assets the client can buy Exchange-Traded Funds. The ticker names of these funds are indicated in the second column. This information is NOT needed for this assignment. We will revisit this example later when we talk about Exchange-Traded Funds in week 12. I. II. III. IV. Use excel solver to find the efficient frontier by minimizing the portfolio standard deviation (C38) for a given level of desired expected return (C43). Find the optimal risky portfolio P (the portfolio with the highest Sharpe ratio). How much should the client invest in each risky asset and the risk-free asset? Is the client better of investing in the risky portfolio obtained in Q1) or Q1B)? Justify your answer. In order to obtain full marks you must provide me with the excel spreadsheet used to solve this problem. NOTES: The excel spreadsheet provided can be used for both Q1A and Q1B. If you prefer to solve Q1A by hand, you can do that as well. You need solver for Q1B. To enable solver in excel, click FileOptionsAdd-Ins Analysis tool pack, click Go check Solver. It will now appear in the Data tab. If you need help with the excel spreadsheet feel free to come and talk with me during office hours or make an appointment. There is also an excellent online resource that provides step-bystep instructions on how to solve for the efficient frontier using excel. The steps are nearly identical to those used in my excel spreadsheet. http://economistatlarge.com/portfolio-theory/efficient-frontier Group member FULL NAME 1 2 3 4 5 per month U.S. Long-term corporate bonds U.S. equtieis T-bill 1 month E(r_) 0.533 1.26 0.028 Std Dev 2.42 4.43 Variance 5.88 19.62 Weights 0.000 1.000 Weights 1.000 0) 0.000 COVARIANCE (6) U.S. Long-term corporate bonds 5.88 U.S. 1.50 0.00 In solver: This cell must equal 1 (i.e. weights sum to 100 %) PORTFOLIO STATISTICS PORTFOLIO AVG RET STD. DEV. SHARPE RATIO RISK_FREE RATE TARGET EXPECTED RETURN 1) 1.000 (7) 1.50 19.62 19.62 SOLVER SOLUTION 1.2600 4.4300 0.2780 IN SOLVER: This should equal the target expected return Minimze this 0.028 1.260 Choose a different value, use Excel solver to find the optimal portfolio, copy the numbers in ORANGE 0) to the yellow field 1) and repeat PORTFOLIO AVG RET STD. DEV. SHARPE RATIO EFFICIENT PORTFOLIOS PORTFOLIO AVG RET STD. DEV. SHARPE RATIO 0 2) 4.0 Transpose all the numbers from 1) 3.5 3.0 2.5 2.0 1.5 1.0 0.5 MAX sharpe Select the maximum sharpe ratio Capital Allocation Line (CAL) = E(rc) = rf + Sharpe Ratio * Std. Dev. (rc) 0.03 0.03 0.03 0.03 0.03 0.03 0.03 0.03 0.03 0.03 0.0 0.0 1.0 2.0 3.0 4.0 5.0 6.0 7.0 8.0 9.0 per month Canadian equities French equities German equities Japanese equities Swiss equities U.K. equities U.S. equities U.S. Long-term corporate bonds T-bill 1 month 0.000 0.000 0.000 0.000 0.000 0.000 1.000 0.000 Weights 1.000 0) Std Dev 5.78 6.29 6.26 6.99 5.40 5.55 4.43 2.42 PORTFOLIO STATISTICS PORTFOLIO AVG RET STD. DEV. SHARPE RATIO Variance 33.41 39.56 39.19 48.86 29.16 30.80 19.62 5.88 0.000 (2) 16.72 39.56 27.17 18.03 20.72 19.90 13.93 1.07 0.00 0.000 COVARIANCE (1) Canadian equities 33.41 French equities 16.72 German equities 15.20 Japanese equities 13.33 Swiss equities 14.36 U.K. equities 18.29 U.S. equities 18.95 U.S. Long-term corporate bonds 1.45 0.00 In solver: This cell must equal 1 (i.e. weights sum to 100 %) RISK_FREE RATE TARGET EXPECTED RETURN 1) E(r_) 0.88 1.19 1.09 0.91 1.13 1.23 1.26 0.53 0.028 0.000 (3) 15.20 27.17 39.19 14.44 22.65 17.37 12.48 0.96 0.00 Weights 0.000 (4) 13.33 18.03 14.44 48.86 15.48 16.29 9.60 0.74 0.00 SOLVER SOLUTION 1.2600 4.4300 0.2780 0.000 (5) 14.36 20.72 22.65 15.48 29.16 17.68 12.20 0.93 0.00 0.000 (6) 18.29 19.90 17.37 16.29 17.68 30.80 14.26 1.09 0.00 1.000 (7) 18.95 13.93 12.48 9.60 12.20 14.26 19.62 1.50 19.62 0.000 (8) 1.45 1.07 0.96 0.74 0.93 1.09 1.50 5.88 0.00 IN SOLVER: This should equal the target expected return Minimze this (Set objective, minimze) 0.028 1.260 Choose a different value, use Excel solver to find the optimal portfolio, copy the numbers in ORANGE 0) to the yellow field 1) and repeat PORTFOLIO AVG RET STD. DEV. SHARPE RATIO EFFICIENT PORTFOLIOS PORTFOLIO AVG RET STD. DEV. SHARPE RATIO 0 2) Transpose all the numbers from 1) 0 0 0 1 0 0 0 0 0 0 0 4.0 3.5 3.0 2.5 2.0 1.5 1.0 0.5 MAX sharpe 0.00 Select the maximum sharpe ratio Capital Allocation Line (CAL) = E(rc) = rf + Sharpe Ratio * Std. Dev. (rc) 0.03 0.03 0.03 0.03 0.03 0.03 0.03 0.03 0.03 0.03 0.03 0.03 0.0 0.0 1.0 2.0 3.0 4.0 5.0 6.0 7.0 8.0 9.0